Share
Explore BrainMass

# MS Excel for Corrigan Corporation

PROBLEM
4-25 Ratio analysis The Corrigan Corporation's 2004 and 2005 financial statements follow, along with some industry average ratios.
a. Assess Corrigan's liquidity position, and determine how it compares with peers and how the liquidity position has changed over time.
b. Assess Corrigan's asset management position, and determine how it compares with peers and how its asset management efficiency has changed over time.
c. Assess Corrigan's debt management position, and determine how it compares with peers and how its debt management has changed over time.
d. Assess Corrigan's profitability ratios, and determine how they compare with peers and how the profitability position has changed over time.
e. Assess Corrigan's market value ratios, and determine how their valuation compares with peers and how it has changed over time.
f. Calculate Corrigan's ROE, as well as the industry average ROE, using the extended Du Pont equation. From this analysis, how does Corrigan's financial position compare with the industry average numbers?
g. What do you think would happen to its ratios if the company initiated cost-cutting measures that allowed it to hold lower levels of inventory and substantially decreased the cost of goods sold? No calculations are necessary. Think about which ratios would be affected by changes in these two accounts.

Corrigan Corporation: Balance Sheets as of December 31
2005 2004
Cash \$ 72,000 \$ 65,000
Accounts receivable 439,000 328,000
Inventories 894,000 813,000
Total current assets \$1,405,000 \$1,206,000
Land and building 238,000 271,000
Machinery 132,000 133,000
Other fixed assets 61,000 57,000
Total assets \$1,836,000 \$1,667,000

Accounts and notes payable \$ 432,000 \$ 409,500
Accrued liabilities 170,000 162,000
Total current liabilities \$ 602,000 \$ 571,500
Long-term debt 404,290 258,898
Common stock 575,000 575,000
Retained earnings 254,710 261,602
Total liabilities and equity \$1,836,000 \$1,667,000
136 Part 2 Fundamental Concepts in Financial Management

Corrigan Corporation: Income Statements for
Years Ending December 31
2005 2004
Sales \$4,240,000 \$3,635,000
Cost of goods sold 3,680,000 2,980,000
Gross operating profit \$ 560,000 \$ 655,000
General administrative and selling expenses 236,320 213,550
Depreciation 159,000 154,500
Miscellaneous 134,000 127,000
Earnings before taxes (EBT) \$ 30,680 \$ 159,950
Taxes (40%) 12,272 63,980
Net income \$ 18,408 \$ 95,970

Per-Share Data
2005 2004
EPS \$0.80 \$4.17
Cash dividends \$1.10 \$0.95
Market price (average) \$12.34 \$23.57
P/E ratio 15.4x 5.65x
Number of shares outstanding 23,000 23,000

Industry Financial Ratiosa

Current ratio 2.7x
Inventory turnoverb 7.0x
Days sales outstandingc 32 days
Fixed assets turnoverb 13.0x
Total assets turnoverb 2.6x
Return on assets 9.1%
Return on equity 18.2%
Debt ratio 50.0%
Profit margin on sales 3.5%
P/E ratio 6.0x
Price/cash flow ratio 3.5x

a
Industry average ratios have been constant for the past 4 years.
b
Based on year-end balance sheet figures.
c
Calculation is based on a 365-day year.

#### Solution Preview

See the two attached files. The Excel sheet has the ratio calculations. To see how the ratios were ...

#### Solution Summary

Includes MS Word file as well

\$2.19