- Calculate NOPAT, Net Operating Working Capital, Total Operating Capital, FCF, ROIC, Asset turnover, Asset/Equity, ROE, Operating costs/Sales, Total liability/Total assets, Times Interest Earned Ratios for the projections discussed in class (2016).
Fill in missing values for cells D26:D42 in the data sheet (posted on Blackboard). This should fill in the missing values for cells D90:D99. Copy and paste [use Paste Special and select Values (1,2,3)] these values to E90:E99 under the Steady scenario. Then copy and paste these values from E89:E99 to G89:G99 under the heading Active is This should fill out forecast factors in column D (relevant cells in the range D104:D140). Use these forecast factors to calculate your projected balance sheet and income statements (Rows 104 to 140). Replicate the calculations done in class on the spreadsheet using these forecast factors.
Enter the relevant formulas in cells G145:G160. Formulas already entered in cells D145:D160 provide a good starting point. Make needed modifications; do not forget to calculate the FCF. Once the formulas are entered, save (copy and paste use paste special) the VALUES in cells E145:E160.
A B C D E F G 1 2 Chapter 9 Mini Case 3 Figure MC-1. Financial Statements and Other Data (Millions except per share data) 4 Balance Sheet. Hatfield. 12/31/2015 Income Statement. Hatfield 2015 5 Cash and securities $20 Sales $2,000 6 Accounts receivable 290 Total operating costs 1,900 7 Inventories 390 EBIT $100 8 Total current assets $700 Interest 60 9 Net fixed assets 500 EBT $40 10 Total assets $1,200 Taxes (40%) 16 11 Net income $24 12 Accounts pay. + accruals $100 Dividends $9 13 Notes payable 80 Add. to retain. earnings $15 14 Total current liabilities $180 Shares outstanding 10 15 Long-term debt 520 EPS $2.40 16 Total liabilities $700 DPS $0.90 17 Common stock 300 Year-end stock price $19.20 18 Retained earnings 200 19 Total common equity $500 20 Total liab. & equity $1,200 21 22 23 Selected Ratios and Other Data 2015 Hatfield Industry 24 2015 Sales (S.): $2,000 $2,000 25 Expected growth in sales: 15.0% 15.0% Sales set equal to Hatfield to make the data 26 Profit margin (M): 0.0% 2.74% comparable. 27 Total Assets/Sales (A,*/S.): 0.0% 50.0% 28 Payout ratio (POR): 0.0% 35.0% 29 Equity multiplier (Assets/Equity): 0.0 2.13 30 Total liability/Total assets 0.0% 53.0% 31 Times interest earned (EBIT/Interest): 0.0 5.20 32 Increase in sales (AS = gS.): $0 $300 33 (Payables + Accruals)/Sales (L.*/S.): 0.0% 4.0% 34 Operating costs/Sales: 0.0% 93.0% 35 Cash/Sales: 0.0% 1.0% 36 Receivables/Sales: 0.0% 11.0% 37 Inventories/Sales: 0.0% 15.0% 38 Fixed assets/Sales: 0.0% 23.0% 39 Tax rate: 0.0% 40.0% 40 Interest rate on all debt: 10.0% 9.5% 41 Price/Earning (P/E): 8.0 12.0 42 ROE (Net income Common equity): 11.64% 43 DuPont ROE PM x Sales/Assets Assets/Equity - ROE 44 Hatfield 0.00% #DIV/0! 0.00 F #DIV/0! 45 Industry 2.74% 2.00 2.13 11.67% 46 47 19 0.0% A B C D E F G H | J 48 = Add'l Req'd Assets Add'n to RE Spontaneous liabilities (L.*/S.)AS (A */S, AS Si M * (1-POR) (A*/S.)(S) (L.*/S.) (gSo) Si * M * (1-POR) AFN Hatfield 49 50 51 52 53 54 55 56 AFN Hatfield 57 = $0 $0.00 $0.00 $0.00 million Self-Supporting Growth Rate. This is the maximum growth rate that can be attained without raising external funds, i.e., the value of g that forces AFN = 0, holding other things constant. We found this rate, g = 1.439%, with Excel's Goal Seek function and also algebraically, as explained below. 58 59 1. Using algebra. The self-supporting growth rate can also be found by solving the equation as shown on the 3rd row above AFN, then finding the value of g that causes AFN to equal zero. This results in the same value as we find with Goal Seek. The algebriac solution is easy if we give you the equation, but if you had to solve the AFN equation for g, you would probably find the Goal Seek solution easier. 60 61 PM(1 - POR)(S.) $0.00 62 Self-Supporting g 0.000% 63 A.* - L.* -PM(1 - POR)S. $1,100.00 64 65 66 67 Therefore if the firm's ratios remain constant, the company can grow at about 1.44% without external financing, 68 Goal Seek ? Set cell: $B$56 2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, first highlight cell B56 (which has the resulting AFN). Then, with Excel 07 or 10, on the Main Menu bar click Data>What-If-Analysis>Goal Seek. With Excel 03 click Tools>Goal Seek. Then complete the dialog box as shown to the right (use $D$25 rather than $E$25 in "By changing cell"). When you click OK, Cell D25 (the sales growth rate) will change to 1.382%, which will cause Cell B56 to change to $0.00. Record the new growth rate and then return to the base case by clicking Cancel. Or, you could click OK to leave the new growth rate in Cell D25 and then over-type it with 15% in that cell to get back to the base 0 To value: By changing cell: SE$25 case. OK Cancel 69 Goal Seek is one of Excel's most useful features. We use it elsewhere in this chapter to find the required amount of new capital. In capital budgeting, we use it to see how high the WACC can go before the NPV becomes negative, how low the WACC must be for the NPV to be positive, how low the initial cost must be to achieve a positive NPV, how long a project must last to achieve a positive NPV, and so forth. We have worked on real world cases dealing with almost every chapter in the text, and we almost always have occasion to use Goal Seek. We can't overemphasize its usefulness. 70 71 A B C D E F G H TI 72 Forecasted Financial Statements 73 74 Forecast the financial statements using the following assumptions. (1) Operating ratios remain unchanged. (2) No 75 additional notes payable, LT bonds, or common stock will be issued. (3) The interest rate on all debt is 10%. (4) If 76 additional financing is needed, then it will be raised through a line of credit. The line of credit will be tapped on the 77 last day of the year, so there will be no additional interest charges due to the line of credit. (On Tab 2 we relax this 78 assumption and assume that the line of credit is accessed smoothly throughout the year.) (5) Interest expenses for 79 notes payable and LT bonds are based on the average balances during the year. (6) If surplus funds are available, the 80 surplus will be paid out as a special dividend payment. (7) Regular dividends will grow by 15%. (8) Sales will grow by 81 15%. This is called the "Steady" scenario because operations remain unchanged. The same assumptions apply to the 82 Target scenario, except there are improvements in several areas of operations. 83 84 85 86 87 88 Inputs for Forecasts Hatfield Forecast Scenarios Active is 89 2015 Steady Target Steady 90 Sales growth rate 15.0% 15.0% 15.0% 25.0% 91 Operating costs/Sales 0.0% 93.0% 100.0% 92 Cash/S. 0.0% 1.0% 93 Receivables/Sales 0.0% 11.0% 94 Inventories/Sales 0.0% 15.0% 95 Fixed assets/Sales 0.0% 23.0% 96 Payables and accruals/ Sales 0.0% 4.0% 97 Growth rate in regular dividends 15.0% 15.0% 98 Interest rate on all debt 10.0% 10.0% 99 Tax rate 40.0% 40.0% 100 101 Scenario: Steady Hatfield Forecast wlo AFN With AFN 102 Balance Sheet 2015 Factor Basis for 2016 Forecast 2016 2016 103 Assets 104 Cash $20 0.00% Factor Forecasted Sales 105 Accounts receivable 290 0.00% Factor x Forecasted Sales 106 Inventories 390 0.00% Factor * Forecasted Sales 107 Total current assets $700 108 Net fixed assets 500 0.00% Factor * Forecasted Sales 109 Total assets $1,200 110 111 Liabilities & equity 112 Accts pay. and accruals $100 0.00% Factor Forecasted Sales 113 Notes payable: Planned 80 tarry over 2015 amount 114 Line of credit (LOC) 0 New LOC if AFN > 0 115 Total current liabs $180 116 LT debt: Planned 520 Carry over 2015 amount 117 Total liabilities $700 118 Common stock 300 tarry over 2015 amount 119 Retained earnings 200 2015 + Add'n to RE from Income St. 120 Total common equity $500 121 Total liab. & equity $1,200 122 AFN = TA - (Planned Liab & Equity) 123 New line of credit (if AFN > 0) = 124 Special dividend (if AFN S0 = C D E G H wlo AFN 2016 With AFN 2016 Forecast Factor 125.0% 100.0% Basis for 2016 Forecast (1 + Factor) * 2015 Sales Factor * Forecasted Sales A B 125 126 127 Scenario: Steady 128 Income Statement 129 Sales 130 Total operating costs 131 EBIT 132 Interest: NP planned 133 Interest: LT debt planned 134 Interest: Line of credit 135 Earnings before taxes (EBT) 136 Taxes 137 Net inc. for common (NI) 138 Dividends- regular (DIVs) 139 Special dividends 140 Add to ret. earnings 141 142 Hatfield 2015 $2,000.0 1,900.0 $100.0 8.0 52.0 0.0 $40.0 16.0 $24.0 $9.0 0.0% 0.0% 0.0% Rate x Avg Balance Rate x Avg Balance Rate x Beginning Balance 0% Tax rate * EBT F 0% (1 + g) * 2015 Dividends Special dividend if AFN SO NI - all dividends $15.0 143 Forecast Scenarios Steady Target Active is Steady 144 Performance 145 Net operating profits after taxes 146 Net operating working capital 147 Total operating capital 148 Free cash flow 149 Return on invested capital 150 AFN 151 EPS 152 DPS (regular dividends) 153 Payout ratio (all dividends) 154 Profit margin 155 Sales/Assets (Assets turnover) 156 Assets/Equity 157 ROE 158 Operating costs/Sales 159 Total liability/Total assets 160 TIE ratio 161 162 Hatfield 2015 $100 $600 $1,100 NA 9.1% NA $2.40 $0.90 0.0% 1.2% 1.67 2.40 4.8% 95.0% 58.3% 0.00 A B C D E F G 1 2 Chapter 9 Mini Case 3 Figure MC-1. Financial Statements and Other Data (Millions except per share data) 4 Balance Sheet. Hatfield. 12/31/2015 Income Statement. Hatfield 2015 5 Cash and securities $20 Sales $2,000 6 Accounts receivable 290 Total operating costs 1,900 7 Inventories 390 EBIT $100 8 Total current assets $700 Interest 60 9 Net fixed assets 500 EBT $40 10 Total assets $1,200 Taxes (40%) 16 11 Net income $24 12 Accounts pay. + accruals $100 Dividends $9 13 Notes payable 80 Add. to retain. earnings $15 14 Total current liabilities $180 Shares outstanding 10 15 Long-term debt 520 EPS $2.40 16 Total liabilities $700 DPS $0.90 17 Common stock 300 Year-end stock price $19.20 18 Retained earnings 200 19 Total common equity $500 20 Total liab. & equity $1,200 21 22 23 Selected Ratios and Other Data 2015 Hatfield Industry 24 2015 Sales (S.): $2,000 $2,000 25 Expected growth in sales: 15.0% 15.0% Sales set equal to Hatfield to make the data 26 Profit margin (M): 0.0% 2.74% comparable. 27 Total Assets/Sales (A,*/S.): 0.0% 50.0% 28 Payout ratio (POR): 0.0% 35.0% 29 Equity multiplier (Assets/Equity): 0.0 2.13 30 Total liability/Total assets 0.0% 53.0% 31 Times interest earned (EBIT/Interest): 0.0 5.20 32 Increase in sales (AS = gS.): $0 $300 33 (Payables + Accruals)/Sales (L.*/S.): 0.0% 4.0% 34 Operating costs/Sales: 0.0% 93.0% 35 Cash/Sales: 0.0% 1.0% 36 Receivables/Sales: 0.0% 11.0% 37 Inventories/Sales: 0.0% 15.0% 38 Fixed assets/Sales: 0.0% 23.0% 39 Tax rate: 0.0% 40.0% 40 Interest rate on all debt: 10.0% 9.5% 41 Price/Earning (P/E): 8.0 12.0 42 ROE (Net income Common equity): 11.64% 43 DuPont ROE PM x Sales/Assets Assets/Equity - ROE 44 Hatfield 0.00% #DIV/0! 0.00 F #DIV/0! 45 Industry 2.74% 2.00 2.13 11.67% 46 47 19 0.0% A B C D E F G H | J 48 = Add'l Req'd Assets Add'n to RE Spontaneous liabilities (L.*/S.)AS (A */S, AS Si M * (1-POR) (A*/S.)(S) (L.*/S.) (gSo) Si * M * (1-POR) AFN Hatfield 49 50 51 52 53 54 55 56 AFN Hatfield 57 = $0 $0.00 $0.00 $0.00 million Self-Supporting Growth Rate. This is the maximum growth rate that can be attained without raising external funds, i.e., the value of g that forces AFN = 0, holding other things constant. We found this rate, g = 1.439%, with Excel's Goal Seek function and also algebraically, as explained below. 58 59 1. Using algebra. The self-supporting growth rate can also be found by solving the equation as shown on the 3rd row above AFN, then finding the value of g that causes AFN to equal zero. This results in the same value as we find with Goal Seek. The algebriac solution is easy if we give you the equation, but if you had to solve the AFN equation for g, you would probably find the Goal Seek solution easier. 60 61 PM(1 - POR)(S.) $0.00 62 Self-Supporting g 0.000% 63 A.* - L.* -PM(1 - POR)S. $1,100.00 64 65 66 67 Therefore if the firm's ratios remain constant, the company can grow at about 1.44% without external financing, 68 Goal Seek ? Set cell: $B$56 2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, first highlight cell B56 (which has the resulting AFN). Then, with Excel 07 or 10, on the Main Menu bar click Data>What-If-Analysis>Goal Seek. With Excel 03 click Tools>Goal Seek. Then complete the dialog box as shown to the right (use $D$25 rather than $E$25 in "By changing cell"). When you click OK, Cell D25 (the sales growth rate) will change to 1.382%, which will cause Cell B56 to change to $0.00. Record the new growth rate and then return to the base case by clicking Cancel. Or, you could click OK to leave the new growth rate in Cell D25 and then over-type it with 15% in that cell to get back to the base 0 To value: By changing cell: SE$25 case. OK Cancel 69 Goal Seek is one of Excel's most useful features. We use it elsewhere in this chapter to find the required amount of new capital. In capital budgeting, we use it to see how high the WACC can go before the NPV becomes negative, how low the WACC must be for the NPV to be positive, how low the initial cost must be to achieve a positive NPV, how long a project must last to achieve a positive NPV, and so forth. We have worked on real world cases dealing with almost every chapter in the text, and we almost always have occasion to use Goal Seek. We can't overemphasize its usefulness. 70 71 A B C D E F G H TI 72 Forecasted Financial Statements 73 74 Forecast the financial statements using the following assumptions. (1) Operating ratios remain unchanged. (2) No 75 additional notes payable, LT bonds, or common stock will be issued. (3) The interest rate on all debt is 10%. (4) If 76 additional financing is needed, then it will be raised through a line of credit. The line of credit will be tapped on the 77 last day of the year, so there will be no additional interest charges due to the line of credit. (On Tab 2 we relax this 78 assumption and assume that the line of credit is accessed smoothly throughout the year.) (5) Interest expenses for 79 notes payable and LT bonds are based on the average balances during the year. (6) If surplus funds are available, the 80 surplus will be paid out as a special dividend payment. (7) Regular dividends will grow by 15%. (8) Sales will grow by 81 15%. This is called the "Steady" scenario because operations remain unchanged. The same assumptions apply to the 82 Target scenario, except there are improvements in several areas of operations. 83 84 85 86 87 88 Inputs for Forecasts Hatfield Forecast Scenarios Active is 89 2015 Steady Target Steady 90 Sales growth rate 15.0% 15.0% 15.0% 25.0% 91 Operating costs/Sales 0.0% 93.0% 100.0% 92 Cash/S. 0.0% 1.0% 93 Receivables/Sales 0.0% 11.0% 94 Inventories/Sales 0.0% 15.0% 95 Fixed assets/Sales 0.0% 23.0% 96 Payables and accruals/ Sales 0.0% 4.0% 97 Growth rate in regular dividends 15.0% 15.0% 98 Interest rate on all debt 10.0% 10.0% 99 Tax rate 40.0% 40.0% 100 101 Scenario: Steady Hatfield Forecast wlo AFN With AFN 102 Balance Sheet 2015 Factor Basis for 2016 Forecast 2016 2016 103 Assets 104 Cash $20 0.00% Factor Forecasted Sales 105 Accounts receivable 290 0.00% Factor x Forecasted Sales 106 Inventories 390 0.00% Factor * Forecasted Sales 107 Total current assets $700 108 Net fixed assets 500 0.00% Factor * Forecasted Sales 109 Total assets $1,200 110 111 Liabilities & equity 112 Accts pay. and accruals $100 0.00% Factor Forecasted Sales 113 Notes payable: Planned 80 tarry over 2015 amount 114 Line of credit (LOC) 0 New LOC if AFN > 0 115 Total current liabs $180 116 LT debt: Planned 520 Carry over 2015 amount 117 Total liabilities $700 118 Common stock 300 tarry over 2015 amount 119 Retained earnings 200 2015 + Add'n to RE from Income St. 120 Total common equity $500 121 Total liab. & equity $1,200 122 AFN = TA - (Planned Liab & Equity) 123 New line of credit (if AFN > 0) = 124 Special dividend (if AFN S0 = C D E G H wlo AFN 2016 With AFN 2016 Forecast Factor 125.0% 100.0% Basis for 2016 Forecast (1 + Factor) * 2015 Sales Factor * Forecasted Sales A B 125 126 127 Scenario: Steady 128 Income Statement 129 Sales 130 Total operating costs 131 EBIT 132 Interest: NP planned 133 Interest: LT debt planned 134 Interest: Line of credit 135 Earnings before taxes (EBT) 136 Taxes 137 Net inc. for common (NI) 138 Dividends- regular (DIVs) 139 Special dividends 140 Add to ret. earnings 141 142 Hatfield 2015 $2,000.0 1,900.0 $100.0 8.0 52.0 0.0 $40.0 16.0 $24.0 $9.0 0.0% 0.0% 0.0% Rate x Avg Balance Rate x Avg Balance Rate x Beginning Balance 0% Tax rate * EBT F 0% (1 + g) * 2015 Dividends Special dividend if AFN SO NI - all dividends $15.0 143 Forecast Scenarios Steady Target Active is Steady 144 Performance 145 Net operating profits after taxes 146 Net operating working capital 147 Total operating capital 148 Free cash flow 149 Return on invested capital 150 AFN 151 EPS 152 DPS (regular dividends) 153 Payout ratio (all dividends) 154 Profit margin 155 Sales/Assets (Assets turnover) 156 Assets/Equity 157 ROE 158 Operating costs/Sales 159 Total liability/Total assets 160 TIE ratio 161 162 Hatfield 2015 $100 $600 $1,100 NA 9.1% NA $2.40 $0.90 0.0% 1.2% 1.67 2.40 4.8% 95.0% 58.3% 0.00
