Question

Module 5 Student Version 4/4/96 Financial Statements for the Year Ended December 31, 1995 (Millions) ACE REPAIR, INC. Cost of Capital (Easy ERSION Cash & Sec. A/R Inventory $5.0 46.3 74.1 A/P Accruals N/P $39.0 14.7 35.5 This case illustrates the cost of capital estimation process. It demonstrates (1) the mechanics of determining the component costs of capital--specifically debt, preferred stock and common equity, (2) the effects of changes in the the target capital structure on the WACC. (3) the effects of callable bonds on the cost of debt, (4) and when to use YTC vs. YTM as the component cost of debt. CA Net Fixed Assets $125.4 93.1 СІ. LT Debt Pref. St] Common S1 RE $89.2 40.0 10.0 20.0 59.3 Tot Assets $218.5 Tot Clai, $218.5 ======= ========= =============== ====== ==== ====== Investment opportunities are also evaluated, and the marginal cost of capital (MCC) schedule is combined with an investment opportunity schedule (IOS) to illustrate the role of the cost of capital in the capital budgeting process To view a graph of the MCC and IOS Schedules, click on the 'MCC-IOS' tab at the bottom of the workbook. To return to the model, click on the tab labelled 'CASE 54M.' Sales Operating costs excluding depreciation Depreciation $400.5 364.6 4.1 $368.7 The model is long, because it does a lot of little calculations, and also because we copied values to use the IRR functions to find bond yields. Because of its length, it may take a while to operate the model, and it would be easy to mess it up. It would be easier to not use the model unless you plan to do sensitivity analysis Total Operating Costs Earnings before interest and taxes (EBIT Less Interest $31.8 6.6 Earnings Before Taxes (EBT) Taxes $25.2 10.1 If you are using the student version of the model, the following cells have been blanked out: C68, 271, 274, C90, B140: B141, C112, F161, F163, F166, E172, E174, E177: E178, D183, E187, and B201. Before using the model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. Net Income before preferred dividends Preferred dividends $15.1 0.8 Net income available to common stockholde $14.3 ======= Common dividends Addition to retained earnings $2.9 $11.5 $2.30 $0.46 ======= ==== ====== ====== ==EEEE EPS This model is quite large, but is broken down into easy to find sections. DPS These sections can be reached by looking for the Named Range of the area. To access the area, press F5 and then type the name of the area Cur Rati 1.41 detailed below, that you wish to go to Quick Ra 0.58 Section Range Name Inv. Tur 5.40 TA Turnc 1.83 1. Model Instructions Instructions D/A rati 59.1% 2. Variable Input/Key Output Input/output P/E rati 13.3 3. Balance Sheet & Income Statement Finstmt E/P rati 7.54% 4. Calculate Historical Growth Rate Growth PO Ratic 20.0% 5. Cost of Debt Calculations Debt 6. Calculation of Weighted Avg. Cost of Capital WACC Capital Structure 7 Selection of Optimal Capital Budget Capbud BV $ 8 Graph Data for MCC and IOS graphs GraphData LT Debt 40.00 Preferre 10.00 Common 79.30 Profit Margin on Sale Basic earning pur rat Return on Capital Return on Assets Book Value per Share Market Book Ratio TIE Ratio ROE 3.58% 14.55% 13.18% 6.55% $12.74 2.39 4.82 18.1% BV MV $ $ 30.9% 48.36 7.7% 0.00 61.3% ###### MV % 20.3% 0.0% 79.7%
can you help me solve the highlighted areas with excel formulas