can you help me solve the highlighted areas with excel formulas

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% Common 79.30 61.3% ###### 79.7% 2 3 4 Total 129. 30 100.0% ###### 100.0% ============ ========== ==== ===== =========== ===== EEEE ====== ======= 6 7 INPUT DATA (Totals in Millions): 8 Stock price (Po) $30.50 9 Earnings per share (EPS $2.30 0 Last dividend (Do) $0.46 1 Analysts "L-R" growth r 16.00% 2 3 4 5 Payout ratio (PO) 20.00% 6 No. shares outstanding 6.2261 7 Return on market (km) 12.57% 8 Risk-free rate (krf) 9 Beta (b) 1.30 0 1 Risk Prem over own bond, RP 2 kd for avg. A-rated firi 8.00% 3 4 Price of Preferred Stock 5 Preferred dividend (Dp) $8.00 6 No. prf shrs out. (Mill: 0.10 7 8 Par value of bonds $1,000.00 9 Coupon rate (semiannual 10.00% 0 Current price $1,209.00 1 Call price $1,100.00 2 Years to maturity (2012 17.00 3 Years to call (1998) 3.00 4 5 6 Tax rate (T) 40.00% 7 For'est comm earnings, : $17.127 8 Depreciation 4.5 9 Flot. cost, COMMON 30.00% 0 Flot. cost, preferred 1 Investment Projects (Cost in Millions): 2 Num Herre Cost Life CACE EAM MOTOCMAI KEY OUTPUT: Regression Method for Determining the Historical Growth Rate: Debt YTM 7.76% (1) Convert DPS to @ln(DPS) and then run a regression between years YTC 5.46% (the x variable) and the natural log of dividends (the Y variable). (2) The regression coefficient is the exponent in the expression ex. Common Equity (3) Find the growth rate as @exp(x) - 1. Constant DCF K 17.75% CAPM k 16.34% DPS Ln(DPS) k=kd+RP 8.00% 1991 $0.12 -2.1203 1992 0.30 -1. 2040 ks (Avg CAPM + RE 12.17% 1993 0.30 -1.2040 1994 0.33 -1.1087 1995 0.46 -0.7765 Preferred Stock: kps 0.00% Regression Output: Constant -555.9 WACC1 0.00% Std Err of Y Est 0.2745 WACC2 0.00% R Squared 0.7741 RE Breakpt ####### No. of Observations 5 Degrees of Freedom 3 Accepted Projects: Project's Numran ROR Cost X Coefficient(s) 0.2783 1 0.00% $25.00 Std Err of Coef. 0.0868 2 12.00% 15.00 3 3 11.00% 30.00 4 10.00% 10.00 @EXP(X coeficient 1.3209 Growth rate 32.1% Total Capital Budget $80.00 ======= Point-to-Point Growth Rate based on @RATE: Target cap structure: % long-term debt 30.00% g 39.9% % preferred stock 5.00% % Common equity 65.00% (OUTPUT): TRR: Ann CE