finance mini case
4/11/10
Chapter 9 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 $40 Sales $2,000
Accounts receivable 290 Total operating costs 1,900
Inventories 390 EBIT $100
Total current assets $720 Interest 60
Net fixed assets 500 EBT $40
Total assets $1,220 Taxes (40%) 16
Net income $24
Accounts pay. + accruals $120 Dividends $9
Notes payable 80 Add. to retain. earnings $15
Total current liabilities $200 Shares outstanding 10
Long-term debt 520 EPS $2.40
Total liabilities $720 DPS $0.90
Common stock 300 Year-end stock price $24.00
Retained earnings 200
Total common equity $500
Total liab. & equity $1,220
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): 61.0% 50.0%
Payout ratio (POR): 37.5% 35.0%
Equity multiplier (Assets/Equity): 2.44 2.13
Total liability/Total assets 59.0% 53.0%
Times interest earned (EBIT/Interest): 1.67 5.20
Increase in sales (ΔS = gS0): $300 $300
(Payables + Accruals)/Sales (L0*/S0): 6.0% 4.0%
Operating costs/Sales: 95.0% 93.0%
Cash/Sales: 2.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.64 2.44 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)
= – –
AFNHatfield = 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. Find g 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)
Self-Supporting g = = =
A0* – L0* – PM(1 – POR)S0
Therefore, if the firm’s ratios remain constant, the company can grow at about g 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 the self-
supporting g, which will cause Cell B56 to change to $0.00. You should click OK to leave the new growth rate in Cell D25.
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 Second Section 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
Sales growth rate 15.0% 15.0% 15.0%
Operating costs/Sales 95.0% 95.0% 93.0%
Cash/Sales 2.0% 2.0% 1.0%
Receivables/Sales 14.5% 14.5% 11.0%
Inventories/Sales 19.5% 19.5% 15.0%
Fixed assets/Sales 25.0% 25.0% 23.0%
Payables and accruals/ Sales 6.0% 6.0% 4.0%
Growth rate in regular dividends 15.0% 15.0% 15.0%
Interest rate on all debt 10.0% 10.0% 10.0%
Tax rate 40.0% 40.0% 40.0%
Scenario: Hatfield Forecast w/o AFN With AFN
Balance Sheet 2010 Factor Basis for 2011 Forecast 2011 2011
Assets
Cash $40 Factor × Forecasted Sales
Accounts receivable 290 Factor × Forecasted Sales
Inventories 390 Factor × Forecasted Sales
Total current assets $720
Net fixed assets 500 Factor × Forecasted Sales
Total assets $1,220
Liabilities & equity
Accts pay. and accruals $120 Factor × Forecasted Sales
Notes payable: Planned 80 Carry over 2010 amount
Line of credit (LOC) 0 New LOC if AFN > 0
Total current liabs $200
LT debt: Planned 520 Carry over 2010 amount
Total liabilities $720
Common stock 300 Carry over 2010 amount
Retained earnings 200 2010 + Add’n to RE from Income St.
Total common equity $500
Total liab. & equity $1,220
AFN = TA – (Planned Liab & Equity)
New line of credit (if AFN > 0) =
Special dividend (if AFN ≤ 0) =
Scenario: Hatfield Forecast w/o AFN With AFN
Income Statement 2010 Factor Basis for 2011 Forecast 2011 2011
Sales $2,000.0 (1 + Factor) × 2010 Sales
Total operating costs 1,900.0 Factor × Forecasted Sales
EBIT $100.0
Interest: NP planned 8.0 Rate x Avg Balance
Interest: LT debt planned 52.0 Rate x Avg Balance
Interest: Line of credit 0.0 Rate x Beginning Balance
Earnings before taxes (EBT) $40.0
Taxes 16.0 Tax rate × EBT
Net inc. for common (NI) $24.0
Dividends- regular (DIVs) $9.0 (1 + g) × 2010 Dividends
Special dividends Special dividend if AFN ≤ 0
Add. to ret. earnings $15.0 NI − all dividends
Hatfield 2010 Forecast Scenarios Active is
Performance Steady Target
Net operating profits after taxes $100
Net operating working capital $600
Total operating capital $1,100
Free cash flow NA
Return on invested capital 9.1%
AFN NA
EPS $2.40
DPS (regular dividends) $0.90
Payout ratio (all dividends) 37.5%
Profit margin 1.2%
Sales/Assets (Assets turnover) 1.64
Assets/Equity 2.44
ROE 4.8%
Operating costs/Sales 95.0%
Total liability/Total assets 59.0%
TIE ratio 1.67
See Below as Section 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 Forecast Scenarios Active
Steady Target
Growth rate 15.0% 15.0% 15.0%
Operating costs/sales 95.0% 95.0% 93.0%
Cash/Sales 2.0% 2.0% 1.0%
Receivables/Sales 14.5% 14.5% 11.0%
Inventories/Sales 19.5% 19.5% 15.0%
Fixed assets/Sales 25.0% 25.0% 23.0%
Payables and Accruals/ Sales 6.0% 6.0% 4.0%
Interest rate on notes payable 10.0% 10.0% 9.5%
Payout ratio 37.5% 37.5% 35.0%
Tax rate 40% 40% 40%
P/E ratio 10.0 10.0 12.0
Shares outstanding (millions) 10.000 10.000 10.000
Adjusted for New Interest Hatfield Forecast Scenario 2011
Balance Sheet 2010 Factor Procedure for 2011 Forecast Forecast
Assets
Cash $40 Factor × Forecasted Sales
Accounts receivable 290 Factor × Forecasted Sales
Inventories 390 Factor × Forecasted Sales
Total current assets $720
Net fixed assets 500 Factor × Forecasted Sales
Total assets $1,220
Claims on Assets
Accts payable and accruals $120 Factor × Forecasted Sales
Notes payable 80 Carry over 2010 amount
Add’ notes to balance 0 New notes (+/-) to balance
Total current liabs $200
Long Term Debt 520 Carry over 2010 amount
Total liabilities $720
Common stock 300 Carry over 2010 amount
Retained earnings 200 2010 + Add’n to RE from Income St.
Total common equity $500
Total liabs and equity $1,220
Shares outstanding 10.000 Difference between Assets and Liab+Equity:
Year-end stock price $24.00
Adjusted for New Interest
Adjusted for New Interest 2010 Actual % of Sales Factors Scenario: Forecast 2011
2010 Income Statement
Sales $2,000.0 (1 + Factor) × 2010 Sales
Total operating costs 1,900.0 Factor × Forecasted Sales
EBIT $100.0
Interest on initial debt 60.0 Carry over 2010 amount
Interest on 1/2 of new debt 0.0 Interest rate × (0.5 × Δ notes)
Total interest $60.0
Earnings before taxes (EBT) $40.0
Taxes 16.0 Tax rate × 2011 EBT
Net income for common (NI) $24.0
Dividends (DIVs) $9.0
Add. to ret. earns (NI – DIVs) $15.0
Shares outstanding 10.000
EPS $2.40
DPS $0.90
Stock Price $24.00
Adjusted for New Interest Adjusted for New Interest
Performance Forecast Scenarios Active is
2010 Actual Steady Target
EPS $2.40
Year-end stock price $24.00 `
Profit margin (PM) 1.2%
Sales/Assets (Assets turnover) 1.64
ROE 4.8%
Debt/Assets 59.0%
Assets/Equity 2.44
TIE ratio 1.67
Payout ratio 37.5%
Final comment: Different problems require somewhat different models–one size does not fit all. For example, a firm’s growth rate might be low, and if that resulted
in a negative AFN, then a model would have to be programmed to do something with the excess funds. The model on Tab 2 is an example.
1. Based on the data in Figure MC-1, how well run does Hatfield appear to be in comparison to other firms in its industry? What are its primary strengths and
weaknesses? Be specific in your answer, and point to various ratios that support your position. Also, use the DuPont equation as one part of your analysis.
