B C D E F G H 1 5/17/2004 2 Note: You may get a warning from Excel that cannot calculate a formula, because the model in the AUTOMATIC 3 FEEDBACK worksheet intentionally has circular references. If you get this warning, go to the menu bar and click on 4 Tools>Options. Then click the tab for Calculation and check the box for Iterations. This will allow Excel to calculate the 5 formulas in the AUTOMATIC FEEDBACK worksheet. 6 7 8 Chapter 12 Web Extension. Tool Kit for Advanced Issues in Financial Forecasting 9 Here are the financial statements from Chapter 12. 10 11 12 INCOME STATEMENT 13 (in millions of dollars) 2004 14 15 Sales $2,850.0 16 Costs except depreciation $2,497.0 17 Depreciation $90.0 18 Total operating costs $2,587.0 19 EBIT $263.0 20 Less Interest $60.0 21 Earnings before taxes (EBT) $203.0 22 Taxes (40%) $81.2 23 NI before preferred dividends $121.8 24 Preferred dividends $4.0 25 NI available to common $117.8 26 27 Dividends to common $53.0 28 Add. to retained earnings (DRE) $64.8 29 30 Shares of common equity 50 31 Dividends per share $1.06 32 Price per share $26.00 33 34 BALANCE SHEET 35 (in millions of dollars) 36 2004 37 Assets 38 Cash $15.0 39 ST Investments $65.0 40 Accounts receivable $315.0 41 Inventories $415.0 42 Total current assets $810.0 43 Net plant and equipment $870.0 44 Total assets $1,680.0 45 46 2004 47 Liabilities and equity 48 Accounts payable $30.0 49 Accruals $130.0 50 Notes payable $60.0 51 Total current liabilities $220.0 52 Long-term bonds $580.0 53 Total liabilities $800.0 54 Preferred stock $40.0 55 Common stock $130.0 56 Retained earnings $710.0 57 Total common equity $840.0 58 Total liabilities and equity $1,680.0
I
2005 $3,000.0 $2,616.2 $100.0 $2,716.2 $283.8 $88.0 $195.8 $78.3 $117.5 $4.0 $113.5 $57.5 $56.0 50 $1.15 $23.00
2005 $10.0 $0.0 $375.0 $615.0 $1,000.0 $1,000.0 $2,000.0 2005 $60.0 $140.0 $110.0 $310.0 $754.0 $1,064.0 $40.0 $130.0 $766.0 $896.0 $2,000.0
B C D E F G H I 59 Preliminary 60 Pro Forma Ratios Actual Historical Industry Forecast 61 2004 2005 Average Composite 2006 62 63 Costs / Sales 87.61% 87.21% 87.41% 87.1% 87.20% 64 Depreciation / Net plant & equip. 10.34% 10.00% 10.17% 10.2% 10.00% 65 Cash / Sales 0.53% 0.33% 0.43% 1.0% 0.33% 66 Accounts Rec. / Sales 11.05% 12.50% 11.78% 10.0% 12.50% 67 Inventory / Sales 14.56% 20.50% 17.53% 11.1% 20.50% 68 Net plant & equip. / sales 30.53% 33.33% 31.93% 33.3% 33.33% 69 Accounts Pay. / Sales 1.05% 2.00% 1.53% 1.0% 2.00% 70 Accruals / Sales 4.56% 4.67% 4.61% 2.0% 4.67% 71 Long-term bonds/operating assets 35.91% 37.70% 36.81% 30.9% 37.70% 72 73 Other Inputs 74 75 Sales Growth Rate 10% 76 Tax rate 40% 77 Dividend growth rate 8% 78 Interest rate on notes payable and short-term investments 8.5% 79 Interest rate on long-term bonds 10.5% 80 Coupon rate on preferred stock 10% 81 82 Funds raised as notes payable 25% 83 Funds raised as long-term debt 25% 84 Funds raised as preferred stock 0% 85 Funds raised as new common stock 50% 86 87 88 89 Note: we have used the ROUND function to make 90 the calculations consistent with the textbook. Table 12E-1 and 12E-3 MicroDrive, Inc.: Actual and Projected Income Statements (Millions of Dollars) 91 92 First Pass Second Pass 93 Actual Forecast Forecast 94 2005 Forecast basis 2006 Feedback 2006 95 (1) (2) (3) (4) (5) 96 Sales $ 3,000.0 110% x 2005 Sales = $ 3,300.0 $ 3,300.0 97 Costs except depreciation 2,616.2 87.2% x 2006 Sales = 2,877.6 2,877.6 98 Depreciation 100.0 10% x 2006 Net plant = 110.0 110.0 99 Total operating costs $ 2,716.2 $ 2,987.6 $ 2,987.6 100 EBIT $ 283.8 $ 312.4 $ 312.4 101 Less Interest 88.0 Carry over from previous year 88.0 Recalculated 91.2 102 Earnings before taxes (EBT) $ 195.8 $ 224.4 $ 221.2 103 Taxes (40%) 78.3 89.8 88.5 104 NI before preferred dividends $ 117.5 $ 134.6 $ 132.7 105 Preferred dividends 4.0 Carry over from previous year 4.0 4.0 106 NI available to common $ 113.5 $ 130.6 $ 128.7 107 108 Shares of common equity 50.0 50.0 2.430 52.43 109 Dividends per share $ 1.15 108% x 2005 DPS = $ 1.25 $ 1.25 110 Dividends to common $ 57.5 DPS x # shares = $ 62.5 $ 65.5 111 Add. to retained earnings $ 56.0 $ 68.1 $ 63.2 112 113 Table 12E-2 MicroDrive, Inc.: Actual and First Pass Projected Balance Sheets (Millions of Dollars) 114 115 Look to the right for the Third Pass 116 First Pass Second Pass 117 Actual Forecast Forecast 118 2005 Forecast basis 2006 AFN 2006 119 (1) (2) (3) (4) (5) 120 Assets 121 Cash $ 10.0 0.33% x 2006 Sales = $ 11.0 $ 11.0 122 ST investments 0.0 Carry over from previous year 0.0 0.0 0.0 123 Accounts receivable 375.0 12.50% x 2006 Sales = 412.5 412.5 124 Inventories 615.0 20.50% x 2006 Sales = 676.5 676.5 125 Total current assets $ 1,000.0 $ 1,100.0 $ 1,100.0 126 Net plant and equipment 1,000.0 33.33% x 2006 Sales = 1,100.0 1,100.0 127 Total assets $ 2,000.0 $ 2,200.0 $ 2,200.0 128 129 Liabilities and equity 130 Accounts payable $ 60.0 2.00% x 2006 Sales = $ 66.0 $ 66.0 131 Accruals 140.0 4.67% x 2006 Sales = 154.0 154.0 132 Notes payable 110.0 Carry over from previous year 110.0 28.0 138.0 133 Total current liabilities $ 310.0 $ 330.0 $ 358.0 134 Long-term bonds 754.0 Carry over from previous year 754.0 28.0 782.0 135 Total liabilities $ 1,064.0 $ 1,084.0 $ 1,140.0
B C D E F G 136 Preferred stock 40.0 Carry over from previous year 40.0 137 Common stock 130.0 Carry over from previous year 130.0 138 Retained earnings 766.0 2005 RE + $68.10 834.1 139 Total common equity $ 896.0 $ 964.1 140 Total liabilities and equity $ 2,000.0 $ 2,088.1 141 142 Required operating assets $ 2,200.0 143 Specified sources of financing $ 2,088.1 144 Additional funds needed (AFN) $ 111.9 145 146 147 HOW THE AFN WILL BE RAISED: 148 149 150 % of AFN Capital raised Notes payable 151 25.00% $ 28.0 Long-term debt 152 25.00% $ 28.0 Preferred stock 153 0.00% $ Common stock 154 50.00% $ 55.9 155 Total 100.00% $ 111.9 156 Note: if the AFN is negative, then the firm has more than it needs to finance the assets. It could reduce its 157 financing by paying off debt, buying back stock, or paying a larger dividend. Instead, we assume the firm 158 will use any extra funds to purchase additional short-term investments. 159 160 New shares of stock 161 162 New equity issued = $ 55.9 163 Stock price = $23.00 164 # new shares issued = 2.430 165 166
H 0.0 55.9 $ $
I 40.0 185.9 834.1 1,020.0 2,200.0
$
111.9
fa1d589f-6206-468e-99e5-d5ba522ac9e7.xls
AUTOMATIC FEEDBACK
Note: You may get a warning from Excel that cannot calculate a formula, because the model in the AUTOMATIC FEEDBACK worksheet intentionally has circular references. If you get this warning, go to the menu bar and click on Tools>Options. Then click the tab for Calculation and check the box for Iterations. This will allow Excel to calculate the formulas in the AUTOMATIC FEEDBACK worksheet.
Here are the financial statements from Chapter 12. INCOME STATEMENT (in millions of dollars) Sales Costs except depreciation Depreciation Total operating costs EBIT Less Interest Earnings before taxes (EBT) Taxes (40%) NI before preferred dividends Preferred dividends NI available to common Dividends to common Add. to retained earnings (DRE) Shares of common equity Dividends per share Price per share BALANCE SHEET (in millions of dollars) 2004 Assets Cash ST Investments Accounts receivable Inventories Total current assets Net plant and equipment Total assets $15.0 $65.0 $315.0 $415.0 $810.0 $870.0 $1,680.0 2004 Liabilities and equity Accounts payable Accruals Notes payable Total current liabilities $30.0 $130.0 $60.0 $220.0 2005 $10.0 $0.0 $375.0 $615.0 $1,000.0 $1,000.0 $2,000.0 2005 $60.0 $140.0 $110.0 $310.0
2004 $2,850.0 $2,497.0 $90.0 $2,587.0 $263.0 $60.0 $203.0 $81.2 $121.8 $4.0 $117.8 $53.0 $64.8 50 $1.06 $26.00
2005 $3,000.0 $2,616.2 $100.0 $2,716.2 $283.8 $88.0 $195.8 $78.3 $117.5 $4.0 $113.5 $57.5 $56.0 50 $1.15 $23.00
Michael C. Ehrhardt
Page 4
8/10/2008
fa1d589f-6206-468e-99e5-d5ba522ac9e7.xls
AUTOMATIC FEEDBACK
Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liabilities and equity Pro Forma Ratios
$580.0 $800.0 $40.0 $130.0 $710.0 $840.0 $1,680.0 Actual 2004
$754.0 $1,064.0 $40.0 $130.0 $766.0 $896.0 $2,000.0 Historical Average 87.2% 10.0% 0.3% 12.5% 20.5% 33.3% 2.0% 4.7% 37.7% 87.4% 10.2% 0.4% 11.8% 17.5% 31.9% 1.5% 4.6% 36.8% Industry Composite 87.1% 10.2% 1.0% 10.0% 11.1% 33.3% 1.0% 2.0% 30.9%
2005
Costs / Sales Depreciation / Net plant & equip. Cash / Sales Accounts Rec. / Sales Inventory / Sales Net plant & equip. / sales Accounts Pay. / Sales Accruals / Sales Long-term bonds/operating assets Other Inputs
87.6% 10.3% 0.5% 11.1% 14.6% 30.5% 1.1% 4.6% 35.9%
Sales Growth Rate Tax rate Dividend growth rate Interest rate on notes payable and short-term investments Interest rate on long-term bonds Coupon rate on preferred stock Funds raised as notes payable Funds raised as long-term debt Funds raised as preferred stock Funds raised as new common stock
10% 40% 8% 8.5% 10.5% 10% 25% 25% 0% 50%
MicroDrive, Inc.: Actual and Projected Income Statements (Millions of Dollars) Actual 2005 (1) 3,000.0 2,616.2 100.0 2,716.2 283.8 88.0 195.8 78.3 Forecast 2006 (3) $ 3,300.0 2,877.6 110.0 $ 2,987.6 $ 312.4 94.1 $ 218.3 87.3
1. 2. 3. 4. 5. 6. 7. 8.
Sales Costs except depreciation Depreciation Total operating costs EBIT Less Interest Earnings before taxes (EBT) Taxes (40%)
$
Forecast basis (2) 110% x 2005 Sales = 87.2% x 2006 Sales = 10% x 2006 Net plant =
$ $ $
Based on avg. of beg. and end. debt
Michael C. Ehrhardt
Page 5
8/10/2008
fa1d589f-6206-468e-99e5-d5ba522ac9e7.xls
AUTOMATIC FEEDBACK
9. NI before preferred dividends 10. Preferred dividends 11. NI available to common 12. Shares of common equity 13. Dividends per share 14. Dividends to common 15. Add. to retained earnings
$ $
117.5 $ 4.0 Based on avg. of beg. and end. preferred 113.5 $ 50.0 1.15 57.5 56.0
131.0 4.0 127.0 52.6 1.24 65.3 61.7
$ $ $
108% x 2005 DPS = DPS x # shares =
$ $ $
MicroDrive, Inc.: Actual and First Pass Projected Balance Sheets (Millions of Dollars)
Actual 2005 (1) 1. 2. 3. 4. 5. 6. 7. Assets Cash ST investments Accounts receivable Inventories Total current assets Net plant and equipment Total assets Liabilities and equity Accounts payable Accruals Notes payable Total current liabilities Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liabilities and equity $ 10.0 0.0 375.0 615.0 1,000.0 1,000.0 2,000.0
Forecast basis (2) 0.33% x 2006 Sales = Carry over plus "plug" if needed 12.50% x 2006 Sales = 20.50% x 2006 Sales = 33.33% x 2006 Sales =
Forecast 2006 (3) $ 11.0 0.0 412.5 676.5 $ 1,100.0 1,100.0 $ 2,200.0
$ $
8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.
$
$ $
$ $
60.0 140.0 110.0 310.0 754.0 1,064.0 40.0 130.0 766.0 896.0 2,000.0
2.00% x 2006 Sales = 4.67% x 2006 Sales = Carry over plus "plug" if needed Carry over plus "plug" if needed
$
$ $ Carry over plus "plug" if needed Carry over plus "plug" if needed 2005 RE + $61.66 $ $
66.0 154.0 139.6 359.6 783.6 1,143.2 40.0 189.2 827.7 1,016.8 2,200.0
19. Required operating assets 20. Specified sources of financing 21. Additional funds needed (AFN) HOW THE AFN WILL BE RAISED: % of AFN Notes payable 25.00% Long-term debt 25.00% Preferred stock 0.00% Common stock 50.00% Total 100.00%
$ 2,200.0 $ 2,081.7 $ 118.3
Capital raised $ 29.6 $ 29.6 $ $ 59.2 $ 118.3
Note: if the AFN is negative, then the firm has more than it needs to finance the assets. It could reduce its financing by paying off debt, buying back stock, or paying a larger dividend. Instead, we assume the firm will use any extra funds to purchase additional short-term investments. Michael C. Ehrhardt Page 6 8/10/2008
fa1d589f-6206-468e-99e5-d5ba522ac9e7.xls
AUTOMATIC FEEDBACK
Note: if the AFN is negative, then the firm has more than it needs to finance the assets. It could reduce its financing by paying off debt, buying back stock, or paying a larger dividend. Instead, we assume the firm will use any extra funds to purchase additional short-term investments. 0 New short-term investments =
New shares of stock New equity issued = $ Stock price = # new shares issued =
59.2 $23.00 2.572
Michael C. Ehrhardt
Page 7
8/10/2008
fa1d589f-6206-468e-99e5-d5ba522ac9e7.xls
AUTOMATIC FEEDBACK
5/17/2004 cannot calculate a formula, because the model in the AUTOMATIC ular references. If you get this warning, go to the menu bar and click on ation and check the box for Iterations. This will allow Excel to calculate the worksheet.
Michael C. Ehrhardt
Page 8
8/10/2008
fa1d589f-6206-468e-99e5-d5ba522ac9e7.xls
AUTOMATIC FEEDBACK
Preliminary Forecast 2006 87.2% 10.0% 0.3% 12.5% 20.5% 33.3% 2.0% 4.7% 37.7%
Michael C. Ehrhardt
Page 9
8/10/2008