Explore BrainMass
Share

Explore BrainMass

    MS Excel for Corrigan Corporation

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    COMPREHENSIVE/SPREADSHEET
    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.

    © BrainMass Inc. brainmass.com April 3, 2020, 5:52 pm ad1c9bdddf
    https://brainmass.com/business/financial-ratios/ms-excel-for-corrigan-corporation-180669

    Attachments

    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

    ADVERTISEMENT