C68, C74, C90, B140, C112, F161, F166, E172, E178 are cells I can't figure out on this worksheet, Can you help?

Module 5 Student Version 4/4/96 Financial Statements for the Year Ended December 31, 1995 (Millions) ACE REPAIR, INC. Cost of Capital (Easy) Cash & Sec. A/R Inventory This case illustrates the cost of capital estimation process. It demonstrates (1) the mechanics of determining the component costs of capitalspecifically 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 $5.0 46.3 74.1 $125.4 93.1 A/P Accruals N/P CL LT Debt Pref. Stk Common Stk RE $39.0 14.7 35.5 $89.2 40.0 10.0 20.0 59.3 $218.5 ======= ================ $218.5 Tot Claims ====== ========= =========== ================== ======== ========== Sales $400.5 Operating costs excluding depreciation 364.6 Depreciation 4.1 Total Operating Costs $368.7 Earnings before interest and taxes (EBIT) $31.8 Less Interest 6.6 Earnings Before Taxes (EBT) $25.2 Taxes 10.1 Net Income before preferred dividends $15.1 Preferred dividends 0.8 Net income available to common stockholders $14.3 ======= Common dividends $2.9 Addition to retained earnings $11.5 ======= EPS $2.30 DPS $0.46 ========= =========== ================== ======== ========== ================ Cur Ratio 1.41 Profit Margin on Sales 3.58% Quick Rati 0.58 Basic earning pwr ratio 14.55% Inv. Turno 5.40 Return on Capital 13.18% TA Turnove 1.83 Return on Assets 6.55% D/A ratio 59.1% Book Value per Share $12.74 P/E ratio 13.3 Market/Book Ratio 2.39 E/P ratio 7.54% TIE Ratio 4.82 PO Ratio 20.0% ROE 18.1% 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 'MCCIOS' tab at the bottom of the workbook. To return to the model, click on the tab labelled 'CASE 54M.' Tot Assets 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. If you are using the student version of the model, the following cells have been blanked out: C68, C71, C74, 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. This model is quite large, but is broken down into easy to find sections. 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 detailed below, that you wish to go to. Section Range Name 1. Model Instructions Instructions 2. Variable Input/Key Output Input/output 3. Balance Sheet & Income Statement Finstmt 4. Calculate Historical Growth Rate Growth 5. Cost of Debt Calculations Debt 6. Calculation of Weighted Avg. Cost of Capital WACC 7. Selection of Optimal Capital Budget Capbud 8. Graph Data for MCC and IOS graphs GraphData Capital Structure: BV $ BV % MV $ MV % LT Debt 40.00 30.9% 48.36 20.3% Preferred 10.00 7.7% 0.00 0.0% Common 79.30 61.3% 189.90 79.7% Total 129.30 100.0% 238.26 100.0% =============== ============ ====================== ============= ============ ========= INPUT DATA (Totals in Millions): Stock price (Po) Earnings per share (EPSo) Last dividend (Do) Analysts "LR" growth rate Payout ratio (PO) No. shares outstanding (N) Return on market (km) Riskfree rate (krf) Beta (b) Risk Prem over own bond, RP kd for avg. Arated firm Price of Preferred Stock Preferred dividend (Dp) No. prf shrs out. (Million) Par value of bonds Coupon rate (semiannual) Current price Call price Years to maturity (2012) Years to call (1998) Tax rate (T) For'cst comm earnings, 1996 Depreciation Flot. cost, common Flot. cost, preferred Investment Projects (Cost in Millions): Number/rank Cost 1 $25.00 2 $15.00 3 $30.00 4 $10.00 KEY OUTPUT: $30.50 Debt: $2.30 YTM 3.89% $0.46 YTC 73.53% 16.00% Common Equity: Constant DCF k CAPM k 20.00% k=kd+RP 6.2261 12.57% ks (Avg CAPM + RP) 1.30 (1) Convert DPS to @ln(DPS) and then run a regression between years (the x variable) and the natural log of dividends (the Y variable). (2) The regression coefficient is the exponent in the expression e^x. (3) Find the growth rate as @exp(x) 1. 17.75% 16.34% 12.00% 14.17% ========= 1991 1992 1993 1994 1995 DPS $0.12 0.30 0.30 0.33 0.46 Ln(DPS) 2.1203 1.2040 1.2040 1.1087 0.7765 Preferred Stock: 4.00% kps 0.00% Regression Output: 8.00% Constant 555.891 WACC1 10.65% Std Err of Y Est 0.274482 WACC2 0.00% R Squared 0.774071 $8.00 RE Breakpt $4.5000 No. of Observations 5 0.10 Degrees of Freedom 3 Accepted Projects: Project's $1,000.00 Num./rank ROR Cost X Coefficient(s) 0.2783 10.00% 1 13.00% $25.00 Std Err of Coef. 0.0868 $1,209.00 2 12.00% 15.00 $1,100.00 3 11.00% 30.00 17.00 4 10.00% 10.00 @EXP(X coeficient) = 1.3209 3.00 Growth rate = 32.1% Total Capital Budget = $80.00 ========= PointtoPoint Growth Rate based on @RATE: 40.00% Target cap structure: $17.127 % longterm debt 30.00% g = 39.9% 4.5 % preferred stock 5.00% 30.00% % common equity 65.00% Life 5 7 5 6 Ann. CF $7.10786 3.28677 8.11711 2.29607 (OUTPUT): IRR: 13.00% 12.00% 11.00% 10.00% =================================================================== |:: Cost of debt calculations (17 years remaining, semiannual compounding) Cash flows Regression Method for Determining the Historical Growth Rate: Cash flows if bond is if bond is 6mo period not called called 0 ($1,209.00) ($1,209.00) 1 $50.00 $50.00 2 $50.00 $50.00 3 $50.00 $50.00 4 $50.00 $50.00 5 $50.00 $50.00 6 $50.00 7 $50.00 8 $50.00 9 $50.00 10 $50.00 11 $50.00 12 $50.00 13 $50.00 14 $50.00 15 $50.00 16 $50.00 17 $50.00 18 $50.00 19 $50.00 20 $50.00 21 $50.00 22 $50.00 23 $50.00 24 $50.00 25 $50.00 26 $50.00 27 $50.00 28 $50.00 29 $50.00 30 $50.00 31 $50.00 32 $50.00 33 $50.00 34 YTM 3.89% YTC 73.53% Which yield would investors probably earn on the bonds? What interest rate would the company probably have to pay if it issued new 3year bonds? What interest rate would the company probably have to pay if it issued new 20year bonds? Component cost of retained earnings: DCF (based on "LR" g) CAPM Risk premium method: kd+RP 1.75% 0.00% 8.00% 16.00% 16.34% 4.00% 17.75% 16.34% 12.00% 14.17% ====== Average to use for ks (disregard DCF number): Component cost of ext. equity: Ke as estimated with Constant Growth Model with Po = 30.5(1F) = $21.35: Flotation adjustment: Flotationadj ke based on avg. ks: 17.75% 0.00% Cost of preferred stock (Dps/Pn): WACC calculations (In Millions of Dollars, BOOK VALUES) RE = Forecasted earnings * (1Payout ratio) BP RE = RE Breakpoint= RE/(target % common equity) + Depr'n Retained Earnings (RE, in millions): Depreciation (DEP, in millions): BP RE (incl Deprn): (BP = 21.08 w/o deprn) WACC = wd*Kd(1T) + wps*Kps + wce*(Ks or Ke) WACC1, when Equity is from RE: WACC2, when Equity is from New Common Stk: $4.50 $4.50 10.65% Based on MARKET VALUES, the results would be different: Total MV capitalization: LT debt $48.36 20.30% Preferred 0.00 0.00% Common 189.90 79.70% $238.26 100.00% Increase: WACC1, when Equity is from RE: 12.27% 1.62% WACC2, when Equity is from New Common Stk: 0.97% 0.97% This would substantially change the accepted projects and the cap. bud. Capital Budgeting Projects (In Millions of Dollars) Period Project 1 Project 2 Project 3 Project 4 0 25.00000 15.00000 30.00000 10.00000 1 7.10786 3.28677 8.11711 2.29607 2 7.10786 3.28677 8.11711 2.29607 3 7.10786 3.28677 8.11711 2.29607 4 7.10786 3.28677 8.11711 2.29607 5 7.10786 3.28677 8.11711 2.29607 6 3.28677 2.29607 7 3.28677 IRR: 13.00% 12.00% 11.00% 10.00% Range of financing: Capital Cost: $0.0000 10.7000% Up to: Over: $4.5000 $4.5001 Optimal Capital Budget: Project Number/Rank ROR 1 2 3 4 10.7000% 0.0000% Project Cost 13.00% $25.00 12.00% $15.00 11.00% $30.00 10.00% $10.00 $80.00 ============= MCC and IOS Data for Graph: IOS $ Amount WACC (Investment Return) 0.000 10.65% 13.00% 5.000 0.00% 13.00% 10.000 0.00% 13.00% 15.000 0.00% 13.00% 20.000 0.00% 13.00% 25.000 0.00% 13.00% 25.001 0.00% 12.00% Set manually 4.500 10.65% 13.00% Set manually 4.501 0.00% 13.00% Set manually 30.000 0.00% 12.00% 35.000 0.00% 12.00% 40.000 0.00% 12.00% 40.001 0.00% 11.00% Set manually 45.000 0.00% 11.00% Set manually 50.000 0.00% 11.00% 55.000 0.00% 11.00% 60.000 0.00% 11.00% 65.000 0.00% 11.00% 70.000 0.00% 11.00% 70.001 0.00% 10.00% Set manually 75.000 0.00% 10.00% Set manually 80.000 0.00% 10.00% 85.000 0.00% 10.00% END. ACE REPAIR, INC MCC a nd IOS Sch ed u les (Incl. Depr.) 0.15 0.14 WACC a nd IRR 0.13 0.12 0.11 0.1 0.09 0.08 0 10 20 30 40 50 60 Dolla rs Ra ised a nd Invested MCC IOS 70 80 90