C68, C74, C90, B140, C112, F161, F166, E172, E178 are cells
Question # 00108366
Posted By:
Updated on: 09/26/2015 10:56 AM Due on: 10/26/2015
| Module 5 | Student Version | 4/4/96 | Financial Statements for the Year Ended December 31, 1995 (Millions) | ||||||||||
 |
|||||||||||||
| ACE REPAIR, INC. | Cash & Sec. | $5.0 | A/P | $39.0 | |||||||||
| Cost of Capital (Easy) | A/R | 46.3 | Accruals | 14.7 | |||||||||
| Inventory | 74.1 | N/P | 35.5 | ||||||||||
| This case illustrates the cost of capital estimation process. It | ------ | ------ | |||||||||||
| demonstrates (1) the mechanics of determining the component costs of | CA | $125.4 | CL | $89.2 | |||||||||
| capital--specifically debt, preferred stock, and common equity, (2) the | Net Fixed Assets | 93.1 | LT Debt | 40.0 | |||||||||
| effects of changes in the the target capital structure on the WACC, (3) | Pref. Stk | 10.0 | |||||||||||
| the effects of callable bonds on the cost of debt, (4) and when to use | Common Stk | 20.0 | |||||||||||
| YTC vs. YTM as the component cost of debt. | RE | 59.3 | |||||||||||
| ------ | ------ | ||||||||||||
| Investment opportunities are also evaluated, and the marginal cost of | Tot Assets | $218.5 | Tot Claims | $218.5 | |||||||||
| 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 | Sales | $400.5 | |||||||||||
| Schedules, click on the 'MCC-IOS' tab at the bottom of the workbook. | Operating costs excluding depreciation | 364.6 | |||||||||||
| To return to the model, click on the tab labelled 'CASE 54M.' | Depreciation | 4.1 | |||||||||||
| ------- | |||||||||||||
| The model is long, because it does a lot of little calculations, | Total Operating Costs | $368.7 | |||||||||||
| 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 | Earnings before interest and taxes (EBIT) | $31.8 | |||||||||||
| model, and it would be easy to mess it up. It would be easier to not | Less Interest | 6.6 | |||||||||||
| use the model unless you plan to do sensitivity analysis. | ------- | ||||||||||||
| Earnings Before Taxes (EBT) | $25.2 | ||||||||||||
| If you are using the student version of the model, the following cells | Taxes | 10.1 | |||||||||||
| 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 | Net Income before preferred dividends | $15.1 | |||||||||||
| model, it is necessary to fill in the empty cells with the appropriate | Preferred dividends | 0.8 | |||||||||||
| formulas. Once this is done, the model is ready for use. | ------- | ||||||||||||
| Net income available to common stockholders | $14.3 | ||||||||||||
| ======= | |||||||||||||
| Common dividends | $2.9 | ||||||||||||
| Addition to retained earnings | $11.5 | ||||||||||||
| ======= | |||||||||||||
| EPS | $2.30 | ||||||||||||
| This model is quite large, but is broken down into easy to find sections. | DPS | $0.46 | |||||||||||
| 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 Ratio | 1.41 | Profit Margin on Sales | 3.58% | |||||||||
| detailed below, that you wish to go to. | Quick Ratio | 0.58 | Basic earning pwr ratio | 14.55% | |||||||||
| Section | Range Name | Inv. Turnover | 5.40 | Return on Capital | 13.18% | ||||||||
| - | - | TA Turnover | 1.83 | Return on Assets | 6.55% | ||||||||
| 1. Model Instructions | Instructions | D/A ratio | 59.1% | Book Value per Share | $12.74 | ||||||||
| 2. Variable Input/Key Output | Input/output | P/E ratio | 13.3 | Market/Book Ratio | 2.39 | ||||||||
| 3. Balance Sheet & Income Statement | Finstmt | E/P ratio | 7.54% | TIE Ratio | 4.82 | ||||||||
| 4. Calculate Historical Growth Rate | Growth | PO Ratio | 20.0% | ROE | 18.1% | ||||||||
| 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 $ | BV % | MV $ | MV % | ||||||||
| 8. Graph Data for MCC and IOS graphs | GraphData | 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): | KEY OUTPUT: | Regression Method for Determining the Historical Growth Rate: | |||||||||||
| Stock price (Po) | $30.50 | Debt: | |||||||||||
| Earnings per share (EPSo) | $2.30 | YTM | 3.89% | (1) Convert DPS to @ln(DPS) and then run a regression between years | |||||||||
| Last dividend (Do) | $0.46 | YTC | -73.53% | (the x variable) and the natural log of dividends (the Y variable). | |||||||||
| Analysts "L-R" growth rate | 16.00% | (2) The regression coefficient is the exponent in the expression e^x. | |||||||||||
| Common Equity: | (3) Find the growth rate as @exp(x) - 1. | ||||||||||||
| Constant DCF k | 17.75% | ||||||||||||
| CAPM k | 16.34% | DPS | Ln(DPS) | ||||||||||
| Payout ratio (PO) | 20.00% | k=kd+RP | 12.00% | 1991 | $0.12 | -2.1203 | |||||||
| No. shares outstanding (N) | 6.2261 | 1992 | 0.30 | -1.2040 | |||||||||
| Return on market (km) | 12.57% | ks (Avg CAPM + RP) | 14.17% | 1993 | 0.30 | -1.2040 | |||||||
| Risk-free rate (krf) | = | 1994 | 0.33 | -1.1087 | |||||||||
| Beta (b) | 1.30 | 1995 | 0.46 | -0.7765 | |||||||||
| Preferred Stock: | |||||||||||||
| Risk Prem over own bond, RP | 4.00% | kps | 0.00% | Regression Output: | |||||||||
| kd for avg. A-rated firm | 8.00% | Constant | -555.89 | ||||||||||
| WACC1 | 10.65% | Std Err of Y Est | 0.27448 | ||||||||||
| Price of Preferred Stock | WACC2 | 0.00% | R Squared | 0.77407 | |||||||||
| Preferred dividend (Dp) | $8.00 | RE Breakpt | $4.5000 | No. of Observations | 5 | ||||||||
| No. prf shrs out. (Million) | 0.10 | Degrees of Freedom | 3 | ||||||||||
| Accepted Projects: | Project's | ||||||||||||
| Par value of bonds | $1,000.00 | Num./rank | ROR | Cost | X Coefficient(s) | 0.2783 | |||||||
| Coupon rate (semiannual) | 10.00% | 1 | 13.00% | $25.00 | Std Err of Coef. | 0.0868 | |||||||
| Current price | $1,209.00 | 2 | 12.00% | 15.00 | |||||||||
| Call price | $1,100.00 | 3 | 11.00% | 30.00 | |||||||||
| Years to maturity (2012) | 17.00 | 4 | 10.00% | 10.00 | @EXP(X coeficient) = | 1.3209 | |||||||
| Years to call (1998) | 3.00 | - | Growth rate = | 32.1% | |||||||||
| Total Capital Budget = | $80.00 | ||||||||||||
| = | Point-to-Point Growth Rate based on @RATE: | ||||||||||||
| Tax rate (T) | 40.00% | Target cap structure: | |||||||||||
| For'cst comm earnings, 1996 | $17.127 | % long-term debt | 30.00% | g = | 39.9% | ||||||||
| Depreciation | 4.5 | % preferred stock | 5.00% | ||||||||||
| Flot. cost, common | 30.00% | % common equity | 65.00% | ||||||||||
| Flot. cost, preferred | |||||||||||||
| Investment Projects (Cost in Millions): | (OUTPUT): | ||||||||||||
| Number/rank | Cost | Life | Ann. CF | IRR: | |||||||||
| 1 | $25.00 | 5 | $7.10786 | 13.00% | |||||||||
| 2 | $15.00 | 7 | 3.28677 | 12.00% | |||||||||
| 3 | $30.00 | 5 | 8.11711 | 11.00% | |||||||||
| 4 | $10.00 | 6 | 2.29607 | 10.00% | |||||||||
| =================================================================== | |||||||||||||
| |:: | |||||||||||||
| Cost of debt calculations (17 years remaining, semiannual compounding) | |||||||||||||
| Cash flows | Cash flows | ||||||||||||
| if bond is | if bond is | ||||||||||||
| 6-mo 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 3-year bonds? | |||||||||||||
| What interest rate would the company probably have to pay | |||||||||||||
| if it issued new 20-year bonds? | |||||||||||||
| Component cost of retained earnings: | |||||||||||||
| DCF (based on "L-R" g) | 1.75% | 16.00% | 17.75% | ||||||||||
| CAPM | 0.00% | 16.34% | 16.34% | ||||||||||
| Risk premium method: kd+RP | 8.00% | 4.00% | 12.00% | ||||||||||
| ------ | |||||||||||||
| Average to use for ks (disregard DCF number): | 14.17% | ||||||||||||
| ====== | |||||||||||||
| Component cost of ext. equity: | |||||||||||||
| Ke as estimated with Constant Growth Model with Po = 30.5(1-F) | |||||||||||||
| = $21.35: | |||||||||||||
| Flotation adjustment: | -17.75% | ||||||||||||
| Flotation-adj ke based on avg. ks: | 0.00% | ||||||||||||
| Cost of preferred stock (Dps/Pn): | |||||||||||||
| WACC calculations (In Millions of Dollars, BOOK VALUES) | |||||||||||||
| RE = Forecasted earnings * (1-Payout ratio) | |||||||||||||
| BP RE = RE Breakpoint= RE/(target % common equity) + Depr'n | |||||||||||||
| Retained Earnings (RE, in millions): | |||||||||||||
| Depreciation (DEP, in millions): | $4.50 | ||||||||||||
| BP RE (incl Deprn): | (BP = 21.08 w/o deprn) | $4.50 | |||||||||||
| WACC = wd*Kd(1-T) + wps*Kps + wce*(Ks or Ke) | |||||||||||||
| WACC1, when Equity is from RE: | 10.65% | ||||||||||||
| WACC2, when Equity is from New Common Stk: | |||||||||||||
| 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: | $4.5000 | 10.7000% | |||||||||||
| Over: | $4.5001 | 0.0000% | |||||||||||
| Optimal Capital Budget: | |||||||||||||
| Project | |||||||||||||
| Number/Rank | ROR | Project Cost | |||||||||||
| - | ----- | - | |||||||||||
| 1 | 13.00% | $25.00 | |||||||||||
| 2 | 12.00% | $15.00 | |||||||||||
| 3 | 11.00% | $30.00 | |||||||||||
| 4 | 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. | |||||||||||||
-
Rating:
/5
Solution: C68, C74, C90, B140, C112, F161, F166, E172, E178 are cells