## Transcribed Text

Mini Case_1
Figure MC-1. Financial Statements and Other Data (Millions except per share data)
Calculations need to be cell referenced
Balance Sheet, Hatfield, 12/31/10
Cash and securities
Accounts receivable
Inventories 390
Income Statement, Hatfield, 2010
Sales $3,000 Total operating costs 2,800 EBIT $200 Interest 54 EBT $146 Taxes (40%) 58 Net income $88 Dividends $28 Add. to retain. earnings $60 Shares outstanding 10 EPS $8.76 DPS $2.80
Total current assets Net fixed assets Total assets
Accounts pay. + accruals Notes payable
Total current liabilities Long-term debt
$720 500 $1,220
$120 80
Total common equity $500 Total liab. & equity $1,220
Selected Ratios and Other Data, 2010
Sales, 2010 (S0):
Expected growth in sales: Profit margin (M): Assets/Sales (A0*/S0): Payout ratio (POR): Equity multiplier (Assets/Equity): Total liability/Total assets Times interest earned (EBIT/Interest): Increase in sales (ΔS = gS0): (Payables + Accruals)/Sales (L0*/S0): Operating costs/Sales: Cash/Sales: Receivables/Sales: Inventories/Sales: Fixed assets/Sales: Tax rate: Interest rate on all debt: Price/Earning (P/E): ROE (Net income/Common equity):
$200 520 $720 300 Retained earnings 200
Total liabilities Common stock
Year-end stock price
Industry $3,000 20.0% 3.67% 37.0% 35.0% 2 50.0% 5.20 $600 3.0% 90.0% 1.0% 8.2% 11.5% 14.8% 40.0% 9.0% 12.0 19.84%
Assets/Equity =
2.44
2.00
$87.60
$40 290
DuPont ROE
Hatfield
Industry
PM
2.92%
3.67%
x
Sales/Assets
2.46
2.70
Hatfield $3,000 18.0% 2.9% 40.7% 32.0% 2.44 59.0% 3.70 $540 4.0% 93.3% 1.3% 9.7% 13.0% 16.7% 40.0% 9.00% 10.0 17.52%
x
ROE
17.52%
19.84%
AFNHatfield = =
=
Add'l Req'd Assets (A0*/S0)∆S
(A0*/S0)(gS0)
- Spontaneous liabi. - (L0*/S0)∆S
- (L0*/S0)(gS0)
- Add'n to RE
- S1 × M × (1–POR)
- 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.
1. Using algebra. The self-supporting growth rate can also be found by solving the equation below 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.
Self-Supporting g =
PM(1 – POR)(S0)
A0* – L0* – PM(1 – POR)S0
= =
2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, first highlight cell B56. Then, on the Main Menu bar click Data>What-If-Analysis>Goal Seek. When you click OK, Cell D25 will change to the answer, 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 18% in that cell to get back to the base case. In this assignement, please Leave the new value for D25 to receive credits for goal seek.
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 9%. (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 11.91%. (8) Sales will grow by 18%. 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
Scenario:
Total current assets Net fixed assets Total assets
Accts pay. and accruals Notes payable: Planned Line of credit (LOC) Total current liabs
LT debt: Planned Total liabilities Common stock
Scenario:
Income Statement
EBIT
Earnings before taxes (EBT) Taxes
Net inc. for common (NI) Dividends- regular (DIVs) Special dividends
Add. to ret. earnings
New line of credit (if AFN > 0) = Special dividend (if AFN ≤ 0) =
Balance Sheet
Assets
Hatfield
2010
$120 80 0
18.00% 93.30% 1.30% 9.70% 13.00% 16.70% 4.00% 11.91% 9.00% 40.00%
Forecast
18.00% 93.30% 1.30% 9.70% 13.00% 16.70% 4.00% 11.91% 9.00% 40.00%
Factor × Forecasted Sales
Forecast Scenarios
Target
20.00% 90.00% 1.00% 8.20% 11.50% 14.80% 3.00% 12.90% 9.00% 40.00%
Active is
Sales growth rate
Operating costs/Sales Cash/Sales
Receivables/Sales Inventories/Sales
Fixed assets/Sales
Payables and accruals/ Sales Growth rate in regular dividends Interest rate on all debt
Tax rate
Hatfield 2010
Steady
w/o AFN
With AFN
Factor Basis for 2011 Forecast
2011
2011
Cash
Accounts receivable Inventories
$40 290 390
Factor × Forecasted Sales Factor × Forecasted Sales Factor × Forecasted Sales
$720 500
$1,220
Liabilities & equity
Factor × Forecasted Sales Carry over 2010 amount New LOC if AFN > 0
Carry over 2010 amount Carry over 2010 amount
$200 520
$720 300
Retained earnings 2010 + Add'n to RE from Income St. Total common equity
Total liab. & equity
AFN = TA – (Planned Liab & Equity)
200
$500
$1,220
Hatfield 2010
Forecast
Factor Basis for 2011 Forecast
w/o AFN 2011
With AFN 2011
Sales
Total operating costs
$3,000.0 2,800.0
(1 + Factor) × 2010 Sales Factor × Forecasted Sales
Interest: NP planned Interest: LT debt planned Interest: Line of credit
$200.0
7.2 46.8 0.0
$146.0 58.4
Rate x NP
Rate x L-T Debt
Rate x Beginning Balance
Tax rate × EBT
(1 + g) × 2010 Dividends Special dividend if AFN ≤ 0
NI − all dividends
$87.6
Performance
Net operating profits after taxes Net operating working capital Total operating capital
Free cash flow
Return on invested capital AFN
EPS
DPS (regular dividends) Payout ratio (all dividends) Profit margin
Sales/Assets (Assets turnover) Assets/Equity
ROE
Operating costs/Sales
Total liability/Total assets TIE ratio
$28.0 $0.0
$59.6
Hatfield 2010
Steady
Forecast Scenarios
Target
Active is
$120
$600 $1,100 NA 10.9% NA $8.76 $2.80 32.0% 2.9% 2.46 2.44 17.5% 93.3% 59.0% 3.70
ADJUSTED FOR INTEREST ON ADDED NOTES PAYABLE
Adjusted for New Interest
Data Used in the Scenarios
Inputs for Forecasts Hatfield Steady State Target Active
Growth rate
Operating costs/sales Cash/Sales Receivables/Sales Inventories/Sales
Fixed assets/Sales
Payables and Accruals/ Sales Interest rate on notes payable Payout ratio
Tax rate
P/E ratio
Shares outstanding (millions)
2010 Steady 18.0% 18.0% 93.3% 93.3% 1.3% 1.3% 9.7% 9.7% 13.0% 13.0% 16.7% 16.7% 4.0% 4.0% 9.0% 9.0% 32.0% 32.0% 40% 40% 10.0 10.0 10.000 10.000
Target 20.0% 90.0% 1.0% 8.2% 11.5% 14.8% 3.0% 9.0% 35.0% 40% 12.0 10.000
Adjusted for New Interest Hatfield
Total current assets $720 Net fixed assets 500 Total assets $1,220
Claims on Assets
Accts payable and accruals $120 Notes payable 80
Shares outstanding 10.000
Forecast
This data is for:
Factor × Forecasted Sales
Factor × Forecasted Sales Carry over 2010 amount
Difference between Assets and Liab+Equity:
2011
Balance Sheet 2010 Factor Procedure for 2011 Forecast Forecast Assets
Cash $40 Accounts receivable 290 Inventories 390
Factor × Forecasted Sales Factor × Forecasted Sales Factor × Forecasted Sales
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 Statement Total common equity $500
Total liabs and equity $1,220
Year-end stock price $87.60
Adjusted for New Interest
Adjusted for New Interest 2010 Income Statement
Hatfield 2010
Forecast Factors
Scenario:
Interest rate × (NP+L-T Debt) Interest rate × (0.5 × Δ notes)
Tax rate × 2011 EBT Payout Ratio × NI
Forecast 2011
Sales $3,000.0 Total operating costs 2,800.0
(1 + Factor) × 2010 Sales Factor × Forecasted Sales
EBIT $200.0
Interest on initial debt Interest on 1/2 of new debt
54.0 0.0 Total interest $54.0 Earnings before taxes (EBT) $146.0 Taxes 58.4
Net income for common (NI)
Dividends (DIVs)
Add. to ret. earns (NI – DIVs)
$87.6
$28.0 $59.6
Shares outstanding EPS
DPS
Stock Price
10.000 $8.76 $2.80
$87.60
Adjusted for New Interest
Adjusted for New Interest
Performance
EPS
Year-end stock price
Profit margin (PM) Sales/Assets (Assets turnover) ROE
Debt/Assets
Assets/Equity
TIE ratio
Payout ratio
Hatfield 2010
$8.76 $87.60 2.9% 2.46 17.5% 59.0% 2.44 3.70 32.0%
Steady State Steady
Final Target Target
Active
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.

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.