4/11/10 Chapter 12 Mini Case Figure MC-1. Financial Statements and Other Data (M

Question

4/11/10 Chapter 12 Mini Case Figure MC-1. Financial Statements and Other Data (Millions except per share data) Balance Sheet, Hatfield, 12/31/10 Income Statement, Hatfield, 2010 Cash and securities $20 Sales $2,000 Accounts receivable 290 Total operating costs 1,900 Inventories 390 EBIT $100 Total current assets $700 Interest 60 Net fixed assets 500 EBT $40 Total assets $1,200 Taxes (40%) 16 Net income $24 Accounts pay. + accruals $100 Dividends $9 Notes payable 80 Add. to retain. earnings $15 Total current liabilities $180 Shares outstanding 10 Long-term debt 520 EPS $2.40 Total liabilities $700 DPS $0.90 Common stock 300 Year-end stock price $24.00 Retained earnings 200 Total common equity $500 Total liab. & equity $1,200 Selected Ratios and Other Data, 2010 Hatfield Industry Sales, 2010 (S0): $2,000 $2,000 Expected growth in sales: 15.0% 15.0% Profit margin (M): 1.2% 2.74% Assets/Sales (A0*/S0): 60.0% 50.0% Payout ratio (POR): 37.5% 35.0% Equity multiplier (Assets/Equity): 2.40 2.13 Total liability/Total assets 58.3% 53.0% Times interest earned (EBIT/Interest): 1.67 5.20 Increase in sales (?S = gS0): $300 $300 (Payables + Accruals)/Sales (L0*/S0): 5.0% 4.0% Operating costs/Sales: 95.0% 93.0% Cash/Sales: 1.0% 1.0% Receivables/Sales: 14.5% 11.0% Inventories/Sales: 19.5% 15.0% Fixed assets/Sales: 25.0% 23.0% Tax rate: 40.0% 40.0% Interest rate on all debt: 10.00% 9.5% Price/Earning (P/E): 10.0 12.0 ROE (Net income/Common equity): 4.80% 11.64% DuPont ROE PM x Sales/Assets x Assets/Equity = ROE Hatfield 1.20% 1.67 2.40 4.80% Industry 2.74% 2.00 2.13 11.67% AFNHatfield = Add'l Req'd Assets - Spontaneous liabilities - Add'n to RE = (A0*/S0)?S - (L0*/S0)?S - S1

Explanation / Answer

without excel impossible to view following correctly. DuPont ROE PM x Sales/Assets x Assets/Equity = ROE Hatfield 1.20% 1.67 2.40 4.80% Industry 2.74% 2.00 2.13 11.67% AFNHatfield = Add'l Req'd Assets - Spontaneous liabilities - Add'n to RE = (A0*/S0)?S - (L0*/S0)?S - S1 × M × (1–POR) = (A0*/S0)(gS0) - (L0*/S0)(gS0) - S1 × M × (1–POR) = $180 - $15.00 - $17.25 AFNHatfield = $147.75 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. 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. PM(1 – POR)(S0) $15.00 Self-Supporting g = = = 1.382% A0* – L0* – PM(1 – POR)S0 $1,085.00 Therefore, if the firm's ratios remain constant, the company can grow at about 1.44% without external financing. 2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, first highlight cell B56. Then, with Excel 07, 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. When you click OK, Cell D25 will change to 1.439%, 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 case. 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. Forecasted Financial Statements Forecast the financial statements using the following assumptions. (1) Operating ratios remain unchanged. (2) No additional notes payable, LT bonds, or common stock will be issued. (3) The interest rate on all debt is 10%. (4) If additional financing is needed, then it will be raised through a line of credit. The line of credit will be tapped on the 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 assumption and assume that the line of credit is accessed smoothly throughout the year.) (5) Interest expenses for notes payable and LT bonds are based on the average balances during the year. (6) If surplus funds are available, the surplus will be paid out as a special dividend payment. (7) Regular dividends will grow by 15%. (8) Sales will grow by 15%. This is called the "Steady" scenario because operations remain unchanged. The same assumptions apply to the Target scenario, except there are improvements in several areas of operations. Use the Scenaro Manager to change scenarios. Inputs for Forecasts Hatfield 2010 Forecast Scenarios Active is Steady Target Steady Sales growth rate 15.0% 15.0% 15.0% 15.0% Operating costs/Sales 95.0% 95.0% 93.0% 95.0% Cash/Sales 1.0% 1.0% 1.0% 1.0% Receivables/Sales 14.5% 14.5% 11.0% 14.5% Inventories/Sales 19.5% 19.5% 15.0% 19.5% Fixed assets/Sales 25.0% 25.0% 23.0% 25.0% Payables and accruals/ Sales 5.0% 5.0% 4.0% 5.0% Growth rate in regular dividends 15.0% 15.0% 15.0% 15.0% Interest rate on all debt 10.0% 10.0% 10.0% 10.0% Tax rate 40.0% 40.0% 40.0% 40.0% Scenario: Steady Hatfield Forecast w/o AFN With AFN Balance Sheet 2010 Factor Basis for 2011 Forecast 2011 2011 Assets Cash $20 1.00% Factor × Forecasted Sales $23.0 $23.0 Accounts receivable 290 14.50% Factor × Forecasted Sales 333.5 333.5 Inventories 390 19.50% Factor × Forecasted Sales 448.5 448.5 Total current assets $700 $805.0 $805.0 Net fixed assets 500 25.00% Factor × Forecasted Sales 575.0 575.0 Total assets $1,200 $1,380.0 $1,380.0 Liabilities & equity Accts pay. and accruals $100 5.00% Factor × Forecasted Sales $115.0 $115.0 Notes payable: Planned 80 Carry over 2010 amount 80.0 80.0 Line of credit (LOC) 0 New LOC if AFN > 0 0 142.4 Total current liabs $180 $195.0 $337.4 LT debt: Planned 520 Carry over 2010 amount 520.0 520.0 Total liabilities $700 $715.0 $857.4 Common stock 300 Carry over 2010 amount 300.0 300.0 Retained earnings 200 2010 + Add'n to RE from Income St. 222.7 222.7 Total common equity $500 $522.7 $522.7 Total liab. & equity $1,200 $1,237.7 $1,380.0 AFN = TA – (Planned Liab & Equity) $142.4 $0.00 New line of credit (if AFN > 0) = $142.4 Special dividend (if AFN = 0) = $0.0 Scenario: Steady Hatfield Forecast w/o AFN With AFN Income Statement 2010 Factor Basis for 2011 Forecast 2011 2011 Sales $2,000.0 15.00% (1 + Factor) × 2010 Sales $2,300.0 $2,300.0 Total operating costs 1,900.0 95.0% Factor × Forecasted Sales $2,185.0 $2,185.0 EBIT $100.0 $115.0 $115.0 Interest: NP planned 8.0 10.0% Rate x Avg Balance 8.0 $8.0 Interest: LT debt planned 52.0 10.0% Rate x Avg Balance 52.0 $52.0 Interest: Line of credit 0.0 10.0% Rate x Beginning Balance 0.0 $0.0 Earnings before taxes (EBT) $40.0 $55.0 $55.0 Taxes 16.0 40% Tax rate × EBT $22.0 $22.0 Net inc. for common (NI) $24.0 $33.0 $33.0 Dividends- regular (DIVs) $9.0 15% (1 + g) × 2010 Dividends $10.4 $10.4 Special dividends Special dividend if AFN = 0 $0.0 Add. to ret. earnings $15.0 NI - all dividends $22.7 $22.7 Hatfield 2010 Forecast Scenarios Active is Performance Steady Target Steady Net operating profits after taxes $60 $69 $97 $69 Net operating working capital $600 $690 $529 $690 Total operating capital $1,100 $1,265 $1,058 $1,265 Free cash flow NA -$96 $139 -$96 Return on invested capital 5.5% 5.5% 9.1% 5.5% AFN NA $142.4 -$92.3 $142.4 EPS $2.40 $3.30 $6.06 $3.30 DPS (regular dividends) $0.90 $1.04 $1.04 $1.04 Payout ratio (all dividends) 37.5% 31.4% 169.3% 31.4% Profit margin 1.2% 1.4% 2.6% 1.4% Sales/Assets (Assets turnover) 1.67 1.67 2.00 1.67 Assets/Equity 2.40 2.64 2.51 2.64 ROE 4.8% 6.3% 13.2% 6.3% Operating costs/Sales 95.0% 95.0% 93.0% 95.0% Total liability/Total assets 58.3% 62.1% 60.2% 62.1% TIE ratio 1.67 1.92 2.68 1.92 See Tab 2 for a forecasting model that incorporates feedback effects. ADJUSTED FOR INTEREST ON ADDED NOTES PAYABLE Adjusted for New Interest Data Used in the Scenarios Inputs for Forecasts Hatfield 2010 Steady State Target Active Steady Target Target Growth rate 15.0% 15.0% 15.0% 15.0% Operating costs/sales 95.0% 95.0% 93.0% 93.0% Cash/Sales 1.0% 1.0% 1.0% 1.0% Receivables/Sales 14.5% 14.5% 11.0% 11.0% Inventories/Sales 19.5% 19.5% 15.0% 15.0% Fixed assets/Sales 25.0% 25.0% 23.0% 23.0% Payables and Accruals/ Sales 5.0% 5.0% 4.0% 4.0% Interest rate on notes payable 10.0% 10.0% 9.5% 9.5% Payout ratio 37.5% 37.5% 35.0% 35.0% Tax rate 40% 40% 40% 40% P/E ratio 10.0 10.0 12.0 12.0 Shares outstanding (millions) 10.000 10.000 10.000 10.000 Adjusted for New Interest Hatfield Forecast This data is for: Target 2011 Balance Sheet 2010 Factor Procedure for 2011 Forecast Forecast Assets Cash $20 1.00% Factor × Forecasted Sales $23 Accounts receivable 290 11.00% Factor × Forecasted Sales 253 Inventories 390 15.00% Factor × Forecasted Sales 345 Total current assets $700 $621 Net fixed assets 500 23.00% Factor × Forecasted Sales 529 Total assets $1,200 $1,150 Claims on Assets Accts payable and accruals $100 4.00% Factor × Forecasted Sales $92 Notes payable 80 Carry over 2010 amount 80 Add' notes to balance 0 New notes (+/-) to balance -83 Total current liabs $180 $89 Long Term Debt 520 Carry over 2010 amount 520 Total liabilities $700 $609 Common stock 300 Carry over 2010 amount 300 Retained earnings 200 2010 + Add'n to RE from Income Statement 241 Total common equity $500 $541 Total liabs and equity $1,200 $1,150 Shares outstanding 10.000 Difference between Assets and Liab+Equity: $0 Year-end stock price $24.00 Adjusted for New Interest Adjusted for New Interest 2010 Actual % of Sales Factors Scenario: Target Forecast 2011 2010 Income Statement Sales $2,000.0 15.00% (1 + Factor) × 2010 Sales $2,300.00 Total operating costs 1,900.0 93.0% Factor × Forecasted Sales 2,139.00 EBIT $100.0 $161.00 Interest on initial debt 60.0 9.5% Carry over 2010 amount 60.00 Interest on 1/2 of new debt 0.0 9.5% Interest rate × (0.5 × ? notes) -3.94 Total interest $60.0 $56.06 Earnings before taxes (EBT) $40.0 $104.94 Taxes 16.0 40% Tax rate × 2011 EBT 41.98 Net income for common (NI) $24.0 $62.97 Dividends (DIVs) $9.0 35% $22.04 Add. to ret. earns (NI – DIVs) $15.0 $40.93 Shares outstanding 10.000 10.000 EPS $2.40 $6.30 DPS $0.90 $2.20 Stock Price $24.00 12.0 $75.56 Adjusted for New Interest Adjusted for New Interest 2010 Actual Steady State Final Performance Target Active Steady Target Target EPS $2.40 $2.86 $6.30 $6.30 Year-end stock price $24.00 $28.62 $75.56 $75.56 Profit margin (PM) 1.2% 1.24% 2.74% 2.74% Sales/Assets (Assets turnover) 1.67 1.67 2.00 2.00 ROE 4.8% 5.52% 11.64% 11.64% Debt/Assets 58.3% 62.4% 53.0% 53.0% Assets/Equity 2.40 2.66 2.13 2.13 TIE ratio 1.67 1.71 2.87 2.87 Payout ratio 37.5% 35.0% 35.0% 35.0%

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.