need the highlighted parts and the excel formulas

$39.0 ACE REPAIR, INC. Coat of Capital (Easy) Cash & See AR Inventory $5.0 46.3 74.1 A/P recruala N/P 35.5 CA Net Fixed Assets $125.4 93.1 IT Debt Pref. stk Common stk RE $89.2 40.0 10.0 20.0 59.) his case 11lustrates the cost of capital estimation process. It Lemonstrates (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 MACC, (3) che effects of callable bonds on the cost of debt, (6) and when to use HTC VS. YIM as the component cost of debt. Investment opportunities are also avaluated, and the marginal cost of capital (MC) schedule is combined with an investment opportunity schedule (108) to illustrate the role of the cost of capital in the capital budgeting process. To view a graph of the MCC and TOS Schedules, click on the MCC-IOS' tab at the bottom of the workbook. To return to the model, click on the tab labelled 'CASE 54. $218.5 $218.5 Tot Merete Tot claim $400.5 Sales Operating costs excluding depreciation Depreciation Total Operating conte Earnings before interest and taxes (EBIT) Loss Teterest $368.7 $31.8 6.6 The model is long, because it does a lot of little calculations, and also because ve copied values to use the IRR functions to tind bond yielde. 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 068, 071, 074, 090, 014010141, 112, 7161, 163, 7166, 1172, 174, 177.8178, 183, 187, and 1201. Before using the model, it essary to fill in the empty calls with the appropriate formules. Once this is done, the model is ready for we Earnings Before Taxes (BBT) Taxes $25.2 10.1 $15.1 Net Income before preferred dividende Preferred dividende $14.3 Net Income available to common stockholdere Common dividende Addition to retained earnings $2.9 $11.9 $2.30 50.46 3 4 5 16 # This model is quite large, but is broken down into easy to find sections These stions can be reached by looking for the Named Range of the 39 area. To access the area, press 15 and the type the name of the area 40 cetailed below, that you wish to go to: 41 Section 42 Model Instructions Instructions 442 Variable Trut/Key Output Input/output 1.41 Cur Ratio Otok Rat TV. Tar TA Turnov D/N ratio WH ratio 5.40 1.03 Profit Kargin on sales ante earning pur ratio Return on Capital Return on Assets Book Value per Share Market/book Ratio 3.500 14.551 13.185 6.550 $12.74 2.39 13.3 F c. H J K TA Trnov D/A ratio F/C ratio E/P ratio PO Ratio 1.83 59.10 13.) 7.545 20.04 Return on Aspete Book Value per Share Market/Book Ratio TIE Ratio ROE 6.550 $12.74 2.39 4.82 18.10 Instructions Input/output Pinstat Growth Debt MACC Capbud Graphibata 42 431 Model Instructions Variable Input/Key Outpat 45 3. Balance sheet 6 Income Statement 46. Calculate Historical Growth rate 47 5. Cost of Debt Calculations calculation of weighted avg. cost of capital 49 7. Selection of Optimal Capital Budget 50 8. Graph Data for MOC and los graphs 51 52 53 54 55 56 57 INPUT DATA (Totale in Milliona) Se stock peice (Po) $10.50 59 Earning per where IP50) 52.30 60 Last dividend (D) $0.46 61 Analyst "LR" growth rate 16.00 Capital Structures BV $ LT Debt 40.00 Preferred 10.00 Conton 79.30 BV MIS 30.94 48.36 7.78 0.00 61.31 109.90 MVH 20.30 0.00 79.70 Total 129.30 100.0 238.26 100.00 DPS 20.000 6.2261 12.570 1.30 KEY OUTPUT Regression Method for Determining the storical Growth Rate Debti YTM (1) Convert Ps to Pin (DPS) and then run & regression between years YIC [the x variable) and the natural log of dividends (the T variable). (2) The regression coefficient is the exponent in the expression e'x. Common Equity (3) Pind the growth rate as texp(x) - 1. Constant DCT * 17.750 CAP 16.340 LA(DPS) 8.000 1991 $0.12 -2.1203 1992 0.30 -1.2040 e av CAP. 1) 12.17 0.30 -1.2000 0.33 -1.1007 -0.7765 Preferred to 0.001 Regression Output Constant -555.09 WAOC 0.001 Std ter of Eat 0.27440 NACE2 0.006 R Squared 0.77407 Breakt 50.000 No. of Observation 5 Degrees of Freedom Accepted Projects Projects a./rank RO Coat I coefficienta) 0.2783 0.00 $25.00 StdTre of Conf. 0.0868 2 12.000 15.00 11.000 30.00 4 10.000 10.00 EXP(x coeficienty- 1.3309 Growth rate 32.10 Total Capital Bet- point-to-point Growth rate based on SE 61 64 65 Payout ratio (PO) Moshares outstanding - Return on market () 68 Max-free rate (kr teta 70 1 Kink Prenaver en bond, 12 for we. A rated tim 73 74 Price of Preferred Stock 75 Proferred dividend (p) 76 No. pet shes out. (11) 31 70 Par value of bonds 79 Coupon rate seniowoal) 90 Current price 91 Call price B2 You to maturity (2012) 83 Years to coll (1998) 86 05 TEXT IT 3.000 $8.00 0.10 $1,000.00 10.000 $1,200.00 $1,100.00 19.00 3.00 3 F G H I 30.001 g 39.98 5.000 65.000 86 Tax rate (2) 40.000 Target cap structure: 87 For'cat com earnings, 1996 $17.127 long-term debt B8 Depreciation preferred stock 89 Flot. cost, common 30.000 #common equity 90 zlot. cost, preferred 91 Investment Projects (Cost in Millions): (OUTPUT); 92 Number/rank Cost Ann. CF IRR: 93 1 $25.00 5 $7.10786 0.000 94 2 $15.00 7 3.28677 12.000 95 3 $30.00 8.11711 11.000 96 4 $10.00 2.29607 10.000 97 98 99 :: 100 cost of debt calculations (17 years renaining, semiannual compounding) 101 102 Cash flow Cash flows 103 if bond is if bond is 104 6-mo period not called called 105 106 ($1,209.00) 207 ($1,209.00) $50.00 $50.00 100 $50.00 109 $50.00 $50.00 $50.00 110 $50.00 111 $50.00 $50.00 112 $50.00 $50.00 113 $50.00 $50.00 115 $50.00 $50.00 117 550.00 110 350.00 23 $50.00 120 14 $50.00 1217 $50.00 1227 16 $50.00 123 17 1247 $50.00 125 $50.00 19 $50.00 126 20 $50.00 127 21 $50.00 120 22 129 $50.00 23 130 $50.00 550000 P G T 134 135 1367 137 138 D 23 350.00 130 24 $50.00 1917 25 550.00 26 550.00 27 $50.00 28 $50.00 29 550.00 30 $50.00 31 $50.00 32 $50.00 33 $50.00 140 34 141 142 YTC -73.53 143 1.44 which yield would investore probably earn on the bonda? 145 146 what interest rate would the company probably have to pay If it lored new 3-year bonds? 140 149 What interest rate would the company probably have to pay 150 L It Loved nov 20-yeat benda? 151 152 Component cost of retained earninger 153 DCY (based on "LR" 9) 16.000 154 CAPM 0.000 16.340 155 Rak premium methodi do 8.000 0.000 157 Average to use for ks (disregard DCF number) 150 159 Component cost of ext. equity! 160 Xoss satinated with Constant Growth Model with 70 - 30.511-7) 161 - $21.35 162 163 Teration adjustsenti Ylotation-adj ke based on avg. kat 165 166 cost of preferred stock (pa) 167 160 MACC calculations (in Millions of dollars, BOOK VALUES) 169 - Forecasted earning 11-ayout ratio) 170 BD - Breakpoint-/target common equity) - Dege's 171 172 Betained earnings (RE, in silla) 123 17.754 16.341 0.000 12.170 12.174 Q11 X & fx G YTJ B c D E Decor TT ***** 174 BP RE (incl Deprn): (BP - 21.08 w/o deprn) 175 176 WACC = wdKd(1-T) + wp8*Kps + wce*(Ks or Ke) 177 WACC), when Equity is from RE: 178 WACC2, when Equity is from New Common Stk: 179 180 Based on MARKET VALUES, the results would be different: 181 Total MV capitalization: LT debt $48.36 100.008 182 Preferred 0.00 0.008 183 Common 0.000 184 185 $48.36 100.000 186 Increase: 187 WACCI, when Equity is from RE: 0.008 188 WACC2, when Equity is from New Common Stk: 4.80% 4.808 189 This would substantially change the accepted projects and the cap. bud. 190 191 Capital Budgeting Projects (In Millions of dollars) 192 Period Project 1 Project 2 Project 3 Project 4 193 0 -25.00000 - 15.00000 -30.00000 - 10.00000 194 1 7.10786 3.28677 8.11711 2.29607 195 2 7.10786 3.28677 8.11711 2.29607 196 3 7.10786 3.28677 8.11711 2.29607 197 4 7.10786 3.28677 8.11711 2.29607 198 5 7.10786 3.28677 8.11711 2.29607 199 6 3.28677 2.29607 200 7 3.28677 201 IRR: 12.009 11.000 202 10.008 203 Range of financing Capital Cost: 204 $0.0000 0.00003 205 Up to: $0.0000 0.00000 206 Overt $0.0001 0.00005 207 208 Optimal Capital Budget: 209 Project 210 Number/Rank ROR 211 Project Coat 212 1 0.008 213 $25.00 2 12.008 214 $15.00 3 11.000 215 $30.00 4 216 10.00% $10.00 217 210 $80.00 D E E 205 Up tot $0.0000 0.00008 206 Over: $0.0001 0.00000 207 208 Optimal Capital Budget 209 Project 210 Number/Rank ROR Project Cost 211 212 1 0.008 $25.00 213 2 12.008 $15.00 214 3 11.008 $30.00 215 4 10.000 $10.00 216 217 $80.00 218 219 MCC and IOS Data for Graph 220 IOS 221 $ Amount HACC (Investment Return) 222 223 0.000 0.000 0.008 224 5.000 0.000 0.000 225 10.000 0.000 0.008 226 15.000 0.000 0.00% 227 20.000 0.008 0.008 25.000 0.000 0.000 229 25.001 0.008 12.008 230 0.000 Set manually 0.000 0.00 231 0.001 0.000 Set manually 232 30.000 0.000 Set manually 0.000 233 12.000 35.000 0.000 12.000 40.000 0.000 235 40.001 12.009 0.001 236 11.00% 45.000 0.000 Set manually 232 50.000 11.000 0.008 Set manually 230 55.000 11.005 0.000 239 11.000 60.000 0.008 240 11.000 65.000 0.000 241 70.000 11.000 0.000 242 11.000 70.001 0.000 243 75.000 10.000 Set manually 244 80.000 10.000 0.000 Set manually 245 85.000 10.000 0.000 246 10.000 247 248 END 249 220 234 0.000 $39.0 ACE REPAIR, INC. Coat of Capital (Easy) Cash & See AR Inventory $5.0 46.3 74.1 A/P recruala N/P 35.5 CA Net Fixed Assets $125.4 93.1 IT Debt Pref. stk Common stk RE $89.2 40.0 10.0 20.0 59.) his case 11lustrates the cost of capital estimation process. It Lemonstrates (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 MACC, (3) che effects of callable bonds on the cost of debt, (6) and when to use HTC VS. YIM as the component cost of debt. Investment opportunities are also avaluated, and the marginal cost of capital (MC) schedule is combined with an investment opportunity schedule (108) to illustrate the role of the cost of capital in the capital budgeting process. To view a graph of the MCC and TOS Schedules, click on the MCC-IOS' tab at the bottom of the workbook. To return to the model, click on the tab labelled 'CASE 54. $218.5 $218.5 Tot Merete Tot claim $400.5 Sales Operating costs excluding depreciation Depreciation Total Operating conte Earnings before interest and taxes (EBIT) Loss Teterest $368.7 $31.8 6.6 The model is long, because it does a lot of little calculations, and also because ve copied values to use the IRR functions to tind bond yielde. 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 068, 071, 074, 090, 014010141, 112, 7161, 163, 7166, 1172, 174, 177.8178, 183, 187, and 1201. Before using the model, it essary to fill in the empty calls with the appropriate formules. Once this is done, the model is ready for we Earnings Before Taxes (BBT) Taxes $25.2 10.1 $15.1 Net Income before preferred dividende Preferred dividende $14.3 Net Income available to common stockholdere Common dividende Addition to retained earnings $2.9 $11.9 $2.30 50.46 3 4 5 16 # This model is quite large, but is broken down into easy to find sections These stions can be reached by looking for the Named Range of the 39 area. To access the area, press 15 and the type the name of the area 40 cetailed below, that you wish to go to: 41 Section 42 Model Instructions Instructions 442 Variable Trut/Key Output Input/output 1.41 Cur Ratio Otok Rat TV. Tar TA Turnov D/N ratio WH ratio 5.40 1.03 Profit Kargin on sales ante earning pur ratio Return on Capital Return on Assets Book Value per Share Market/book Ratio 3.500 14.551 13.185 6.550 $12.74 2.39 13.3 F c. H J K TA Trnov D/A ratio F/C ratio E/P ratio PO Ratio 1.83 59.10 13.) 7.545 20.04 Return on Aspete Book Value per Share Market/Book Ratio TIE Ratio ROE 6.550 $12.74 2.39 4.82 18.10 Instructions Input/output Pinstat Growth Debt MACC Capbud Graphibata 42 431 Model Instructions Variable Input/Key Outpat 45 3. Balance sheet 6 Income Statement 46. Calculate Historical Growth rate 47 5. Cost of Debt Calculations calculation of weighted avg. cost of capital 49 7. Selection of Optimal Capital Budget 50 8. Graph Data for MOC and los graphs 51 52 53 54 55 56 57 INPUT DATA (Totale in Milliona) Se stock peice (Po) $10.50 59 Earning per where IP50) 52.30 60 Last dividend (D) $0.46 61 Analyst "LR" growth rate 16.00 Capital Structures BV $ LT Debt 40.00 Preferred 10.00 Conton 79.30 BV MIS 30.94 48.36 7.78 0.00 61.31 109.90 MVH 20.30 0.00 79.70 Total 129.30 100.0 238.26 100.00 DPS 20.000 6.2261 12.570 1.30 KEY OUTPUT Regression Method for Determining the storical Growth Rate Debti YTM (1) Convert Ps to Pin (DPS) and then run & regression between years YIC [the x variable) and the natural log of dividends (the T variable). (2) The regression coefficient is the exponent in the expression e'x. Common Equity (3) Pind the growth rate as texp(x) - 1. Constant DCT * 17.750 CAP 16.340 LA(DPS) 8.000 1991 $0.12 -2.1203 1992 0.30 -1.2040 e av CAP. 1) 12.17 0.30 -1.2000 0.33 -1.1007 -0.7765 Preferred to 0.001 Regression Output Constant -555.09 WAOC 0.001 Std ter of Eat 0.27440 NACE2 0.006 R Squared 0.77407 Breakt 50.000 No. of Observation 5 Degrees of Freedom Accepted Projects Projects a./rank RO Coat I coefficienta) 0.2783 0.00 $25.00 StdTre of Conf. 0.0868 2 12.000 15.00 11.000 30.00 4 10.000 10.00 EXP(x coeficienty- 1.3309 Growth rate 32.10 Total Capital Bet- point-to-point Growth rate based on SE 61 64 65 Payout ratio (PO) Moshares outstanding - Return on market () 68 Max-free rate (kr teta 70 1 Kink Prenaver en bond, 12 for we. A rated tim 73 74 Price of Preferred Stock 75 Proferred dividend (p) 76 No. pet shes out. (11) 31 70 Par value of bonds 79 Coupon rate seniowoal) 90 Current price 91 Call price B2 You to maturity (2012) 83 Years to coll (1998) 86 05 TEXT IT 3.000 $8.00 0.10 $1,000.00 10.000 $1,200.00 $1,100.00 19.00 3.00 3 F G H I 30.001 g 39.98 5.000 65.000 86 Tax rate (2) 40.000 Target cap structure: 87 For'cat com earnings, 1996 $17.127 long-term debt B8 Depreciation preferred stock 89 Flot. cost, common 30.000 #common equity 90 zlot. cost, preferred 91 Investment Projects (Cost in Millions): (OUTPUT); 92 Number/rank Cost Ann. CF IRR: 93 1 $25.00 5 $7.10786 0.000 94 2 $15.00 7 3.28677 12.000 95 3 $30.00 8.11711 11.000 96 4 $10.00 2.29607 10.000 97 98 99 :: 100 cost of debt calculations (17 years renaining, semiannual compounding) 101 102 Cash flow Cash flows 103 if bond is if bond is 104 6-mo period not called called 105 106 ($1,209.00) 207 ($1,209.00) $50.00 $50.00 100 $50.00 109 $50.00 $50.00 $50.00 110 $50.00 111 $50.00 $50.00 112 $50.00 $50.00 113 $50.00 $50.00 115 $50.00 $50.00 117 550.00 110 350.00 23 $50.00 120 14 $50.00 1217 $50.00 1227 16 $50.00 123 17 1247 $50.00 125 $50.00 19 $50.00 126 20 $50.00 127 21 $50.00 120 22 129 $50.00 23 130 $50.00 550000 P G T 134 135 1367 137 138 D 23 350.00 130 24 $50.00 1917 25 550.00 26 550.00 27 $50.00 28 $50.00 29 550.00 30 $50.00 31 $50.00 32 $50.00 33 $50.00 140 34 141 142 YTC -73.53 143 1.44 which yield would investore probably earn on the bonda? 145 146 what interest rate would the company probably have to pay If it lored new 3-year bonds? 140 149 What interest rate would the company probably have to pay 150 L It Loved nov 20-yeat benda? 151 152 Component cost of retained earninger 153 DCY (based on "LR" 9) 16.000 154 CAPM 0.000 16.340 155 Rak premium methodi do 8.000 0.000 157 Average to use for ks (disregard DCF number) 150 159 Component cost of ext. equity! 160 Xoss satinated with Constant Growth Model with 70 - 30.511-7) 161 - $21.35 162 163 Teration adjustsenti Ylotation-adj ke based on avg. kat 165 166 cost of preferred stock (pa) 167 160 MACC calculations (in Millions of dollars, BOOK VALUES) 169 - Forecasted earning 11-ayout ratio) 170 BD - Breakpoint-/target common equity) - Dege's 171 172 Betained earnings (RE, in silla) 123 17.754 16.341 0.000 12.170 12.174 Q11 X & fx G YTJ B c D E Decor TT ***** 174 BP RE (incl Deprn): (BP - 21.08 w/o deprn) 175 176 WACC = wdKd(1-T) + wp8*Kps + wce*(Ks or Ke) 177 WACC), when Equity is from RE: 178 WACC2, when Equity is from New Common Stk: 179 180 Based on MARKET VALUES, the results would be different: 181 Total MV capitalization: LT debt $48.36 100.008 182 Preferred 0.00 0.008 183 Common 0.000 184 185 $48.36 100.000 186 Increase: 187 WACCI, when Equity is from RE: 0.008 188 WACC2, when Equity is from New Common Stk: 4.80% 4.808 189 This would substantially change the accepted projects and the cap. bud. 190 191 Capital Budgeting Projects (In Millions of dollars) 192 Period Project 1 Project 2 Project 3 Project 4 193 0 -25.00000 - 15.00000 -30.00000 - 10.00000 194 1 7.10786 3.28677 8.11711 2.29607 195 2 7.10786 3.28677 8.11711 2.29607 196 3 7.10786 3.28677 8.11711 2.29607 197 4 7.10786 3.28677 8.11711 2.29607 198 5 7.10786 3.28677 8.11711 2.29607 199 6 3.28677 2.29607 200 7 3.28677 201 IRR: 12.009 11.000 202 10.008 203 Range of financing Capital Cost: 204 $0.0000 0.00003 205 Up to: $0.0000 0.00000 206 Overt $0.0001 0.00005 207 208 Optimal Capital Budget: 209 Project 210 Number/Rank ROR 211 Project Coat 212 1 0.008 213 $25.00 2 12.008 214 $15.00 3 11.000 215 $30.00 4 216 10.00% $10.00 217 210 $80.00 D E E 205 Up tot $0.0000 0.00008 206 Over: $0.0001 0.00000 207 208 Optimal Capital Budget 209 Project 210 Number/Rank ROR Project Cost 211 212 1 0.008 $25.00 213 2 12.008 $15.00 214 3 11.008 $30.00 215 4 10.000 $10.00 216 217 $80.00 218 219 MCC and IOS Data for Graph 220 IOS 221 $ Amount HACC (Investment Return) 222 223 0.000 0.000 0.008 224 5.000 0.000 0.000 225 10.000 0.000 0.008 226 15.000 0.000 0.00% 227 20.000 0.008 0.008 25.000 0.000 0.000 229 25.001 0.008 12.008 230 0.000 Set manually 0.000 0.00 231 0.001 0.000 Set manually 232 30.000 0.000 Set manually 0.000 233 12.000 35.000 0.000 12.000 40.000 0.000 235 40.001 12.009 0.001 236 11.00% 45.000 0.000 Set manually 232 50.000 11.000 0.008 Set manually 230 55.000 11.005 0.000 239 11.000 60.000 0.008 240 11.000 65.000 0.000 241 70.000 11.000 0.000 242 11.000 70.001 0.000 243 75.000 10.000 Set manually 244 80.000 10.000 0.000 Set manually 245 85.000 10.000 0.000 246 10.000 247 248 END 249 220 234 0.000