6/8/14
2015 2015
Cash $20 Sales $2,000.0
Accts. rec. $280 Op. costs (excl. depr.) $1,800.0
Selected Ratios and Other Data, 2015
Hatfield Industry Hatfield
Industry
Op. costs/Sales 90% 88% Total liability/Total assets 48.3% 36.7%
Depr./FA 10% 12% Times interest earned 3.8 8.9
Fixed assets/Sales 25% 22% Assets/Equity 1.94 1.58
Acc. pay. & accr. / Sales 4% 4% Return on equity (ROE) 10.6% 16.1%
Tax rate 40% 40% P/E ratio 8.0 16.0
Total op. capital/Sales 56.0% 45.0%
Additional Data 2016
Exp. Saled growth rate 10%
Interest rate on LT debt 8%
Target WACC 9%
Hatfield is less profitable, uses its assets less efficiently, and has too much leverage.
Du Pont ROE M x Sales/Assets x Assets/Equity =ROE
Data for AFN Method
Growth rate in sales (g) 10%
Sales (S0)$2,000
Forecasted sales (S1)$2,200
Profit margin (M) 3.30%
Payout ratio (POR) 30.3%
AFNHatfield = −
Spontaneous
−
Add’n to RE
Self-Supporting g =
Chapter 9 Mini Case
2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, select Data, What-If Analysis, and Goal Seek;
then choose cell with the AFN (B93) as the value for the “Set Cell” area of the Goal Seek dialog box, choose 0 as the value
for the “To Value” area of the dialog box, and choose the cell with the growth rate (C54) as the value for the “By Changing
Cell” area of the dialog box. Then hit OK.
a. Using Hatfield’s data and its industry averages, how well run would you say Hatfield appears to be in comparison with
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 (see Chapter 7) as one part of your analysis.
Add’l Req’d Assets
Hatfield Medical Supplies’s stock price had been lagging its industry averages, so its board of directors brought in a new
CEO, Jaiden Lee. Lee had brought in Ashley Novak, a finance MBA who had been working for a consulting company, to
replace the old CFO, and Lee asked Ashley to develop the financial planning section of the strategic plan. In her previous
job, Novak’s primary task had been to help clients develop financial forecasts, and that was one reason Lee hired her.
Novak began as she always did, by comparing Hatfield’s financial ratios to the industry averages. If any ratio was
substandard, she discussed it with the responsible manager to see what could be done to improve the situation. The
following data shows Hatfield’s latest financial statements plus some ratios and other data that Novak plans to use in her
analysis.
b. Use the AFN equation to estimate Hatfield’s required new external capital for 2016 if the sale growth rate is 10%.
Assume that the firm’s 2015 ratios will remain the same in 2016. (Hint: Hatfield was operating at full capacity in 2015.)
c. Define the term capital intensity. Explain how a decline in capital intensity would affect the AFN, other things held
constant. Would economies of scale combined with rapid growth affect capital intensity, other things held constant? Also,
explain how changes in each of the following would affect AFN, holding other things constant: the growth rate, the amount
were also subject to economies of scale and/or lumpy assets? Answer: See PowerPoint Show
d. Define the term self-supporting growth rate. What is Hatfield’s self-supporting growth rate? Would the self-supporting
growth rate be affected by a change in the capital intensity ratio or the other factors mentioned in the previous question?
Hatfield Medical Supplies: Income Statement (Millions
of Dollars Except per Share)
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, ith Excel’s Goal Seek function and also
algebraically, as explained below.
1. Using algebra. The self-supporting growth rate can also be found by setting the AFN equation to zero and then solving
for g.
Common stock $420 EPS $6.6
Scenario:
No Change
Actual Forecast For inputs:
Inputs 2015 2016 2017 2018 2019 Error Check
Sales growth rate: 10% 8% 5% 5% Ok
Op. costs/Sales: 90% 90.0% 90% 90% 90% Ok
Scenario:
No Change
Actual Forecast
2015 2016 2017 2018 2019
Net sales $2,000 $2,200 $2,376 $2,495 $2,620
Cash $20 $22 $24 $25 $26
Accounts receivable $280 $308 $333 $349 $367
Net fixed assets $500 $550 $594 $624 $655
Accts. pay. & accruals $80 $88 $95 $100 $105
Op. costs (excl. depr.) $1,800 $1,980 $2,138 $2,245 $2,358
Scenario: Actual Forecast
No Change 2015 2016 2017 2018 2019 Definitions:
Total op. capital $1,120 $1,232 $1,331 $1,397 $1,467 Total operating capital = NOWC + Net fixed assets
FCF −$13 $8 $46 $48 FCF = NOPAT − Change in total operating capital
Growth in FCF -164% 447.1% 5.0%
Scenario:
No Change
Horizon Value: Value of operations $958
Value of Operations: − Preferred stock $0
No Change
1. Balance Sheets Most Recent Forecast No Change
2015 Input 2016 1. Balance Sheets Most Recent Forecast
Assets 2015 Input 2016
Cash $20.0 1.00% $22.00 Assets
Accts. rec. 280.0 14.00% $308.00 Cash $20.0 1.00% $22.00
Inventories 400.0 20.00% $440.00 Accts. rec. 280.0 14.00% $308.00
Total CA $700.0 $770.00 Inventories 400.0 20.00% $440.00
Net fixed assets 500.0 25.00% $550.00 Total CA $700.0 $770.00
Total assets $1,200.0 $1,320.00 Net fixed assets 500.0 25.00% $550.00
Line of credit 0.0 Draw on LOC if financing deficit $59.00 Accts. pay. & accruals $80.0 4.00% $88.00
Total CL $80.0 $147.00 Line of credit 0.0 Draw on LOC if financing deficit $59.00
Long-term debt 500.0 Carry over from previous year $500.00 Total CL $80.0 $147.00
Total liabilities $580.0 $647.00 Long-term debt 500.0 Carry over from previous year $500.00
Common stock 420.0 Carry over from previous year $420.00 Total liabilities $580.0 $647.00
Retained earnings 200.0 $253 Common stock 420.0 Carry over from previous year $420.00
Total common equity $620.0 $673 Retained earnings 200.0 $253
Total liabs. & equity $1,200.0 $1,320
Total common equity
× 2016 Sales
× 2016 Sales
× 2016 Sales
× 2016 Sales
× 2016 Sales
Check: TA − Total Liab. & Eq. = $0.00
Total liabs. & equity
$1,200.0 $1,320
2. Income Statement Most Recent Forecast Check: TA − Total Liab. & Eq. = $0.00
2015 Input 2016 2. Income Statement Most Recent Forecast
Sales $2,000.0 110% $2,200.00 2015 Input 2016
Op. costs (excl. depr.) 1,800.0 90.00% $1,980.00 Sales $2,000.0 110% $2,200.00
Depreciation 50.0 10.00% $55.00 Op. costs (excl. depr.) 1,800.0 90.00% $1,980.00
Less: Interest on LTD 40.0 8.00% × Avg bonds $40.00 EBIT $150.0 $165.00
Pretax earnings $110.0 $125.00 Interest on LOC 0.0 8.00% × Beginning LOC $0.00
Taxes (40%) 44.0 40.00% $50.00 Pretax earnings $110.0 $125.00
× 2016 Sales
× 2016 Net fixed assets
× Pretax earnings
× 2016 Net fixed assets
Note: see to right for the No Change financial statements with fixed
values and not variables.
Basis for 2016 Forecast
× 2015 Sales
Basis for 2016 Forecast
× 2016 Sales
× 2016 Sales
e. Use the following assumptions to answer the questions below: (1) Operating ratios remain unchanged. (2) Sales will
grow by 10%, 8%, 5%, and 5% for the next four years. (3) The target weighted average cost of capital (WACC) is 9%. This
is the No Change scenario because operations remain unchanged.
Inputs for the forecast are shown below. You can change inputs in blue. You can show the original scenario by going to
Data, What-If Analysis, Scenario Manager, and select the scenario named No Change .
× 2016 Sales
e. (3) Assume that FCF will continue to grow at the growth rate for the last year in the forecast horizon (Hint: 5%). What is
the horizon value at 2019? What is the present value of the horizon value? What is the present value of the forecasted FCF?
(Hint: use the free cash flows for 2016 through 2019). What is the current value of operations? Using information from the
2015 financial statements, what is the current estimated intrinsic stock price?
f. Continue with the same assumptions for the No Change scenario from the previous question, but now forecast the
balance sheet and income statements for 2016 (but not for the following three years) using the following preliminary
financial policy. (1) Regular dividends will grow by 10%. (2) No additional long-term debt or common stock will be issued.
(3) The interest rate on all debt is 8%. (4) Interest expense for long-term debt is based on the average balance during the
year. (5) If the operating results and the preliminary financing plan cause a financing deficit, eliminate the deficit by
drawing on a line of credit. The line of credit would be tapped on the last day of the year, so it would create no additional
interest expenses for that year. (6) If there is a financing surplus, eliminate it by paying a special dividend. After
forecasting the 2016 financial statements, answer the following questions.
Basis for 2016 Forecast
× 2015 Sales
× 2016 Sales
e. (1) For each of the next four years, forecast the following items: sales, cash, accounts receivable, inventories, net fixed
assets, accounts payable & accruals, operating costs (excluding depreciation), depreciation, and earnings before interest
and taxes (EBIT).
Basis for 2016 Forecast
× 2016 Sales
× 2016 Sales
e. (2) Using the previously forecasted items, calculate for each of the next four years the net operating profit after taxes
(NOPAT), net operating working capital, total operating capital, free cash flow, (FCF), annual growth rate in FCF, and
return on invested capital. What does the forecasted free cash flow in the first year imply about the need for external
financing? Compare the forecasted ROIC compare with the WACC. What does this imply about how well the company is
performing?
Acct. rec. /Sales 14% 14% 14% 14% 14% Ok
AP & accr. / Sales: 4% 4% 4% 4% 4% Ok
Tax rate: 40% 40% 40% 40% 40% Ok
Rate on all debt 8.0% 8% 8% 8%
Div. growth rate: 5% 10% 10% 10% 10%
Target WACC 9%
Addition to RE $46.0 Net income – Dividends $53.00
3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals) $8.00 3. Elimination of the Financial Deficit or Surplus
+ Net income minus regular common dividends $53.00 − Previous line of credit $0.00
Increase in financing $61.00 + Net income minus regular common dividends $53.00
Increase in financing
If deficit in financing (negative), draw on line of credit Line of credit $59.00 Amount of deficit or surplus financing: −$59.00
If surplus in financing (positive), pay special dividend Special dividend $0.00 If deficit in financing (negative), draw on line of credit Line of credit $59.00
+ Increase in long-term debt and common stock $0.00 Note: Increase in spontaneous liabilities (accounts payable and accruals) $8.00
Go to Scenario Manager and choose the Improve Scenario. This will update the financial statements shown above.
Note: see to right for the Improve Scenario’s financial statements with fixed values and not variables. Improve
1. Balance Sheets Most Recent Forecast
2015 Input 2016
Assets
Cash $20.0 1.00% $22.00
Accts. rec. 280.0 14.00% $308.00
Total CA $700.0 $682.00
Accts. pay. & accruals $80.0 4.00% $88.00
Total CL $80.0 $88.00
Total liabilities $580.0 $588.00
Common stock 420.0 Carry over from previous year $420.00
× 2016 Sales
× 2016 Sales
× 2016 Sales
2. Income Statement Most Recent Forecast
2015 Input 2016
Sales $2,000.0 110% $2,200.00
Op. costs (excl. depr.) 1,800.0 89.50% $1,969.00
Depreciation 50.0 10.00% $55.00
EBIT $150.0 $176.00
Less: Interest on LTD 40.0 8.00% × Avg bonds $40.00
Pretax earnings $110.0 $136.00
Regular common dividends
× Pretax earnings
× 2015 Dividends
3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals) $8.00
+ Increase in long-term debt and common stock $0.00
− Previous line of credit $0.00
+ Net income minus regular common dividends $59.60
Increase in financing
− Increase in total assets $32.00
If deficit in financing (negative), draw on line of credit Line of credit $0.00
If surplus in financing (positive), pay special dividend Special dividend $35.60
Basis for 2016 Forecast
× 2015 Sales
× 2016 Sales
× 2016 Net fixed assets
× 2016 Sales
Basis for 2016 Forecast
× 2016 Sales
If there is an initial balance on the on the LOC, the
assumption is that the balance will not change until
If there is a LOC in the previous year, then it is
g. Repeat the analysis performed the previous question but now assume that Hatfield is able to improve the following
inputs: operating costs (excluding depreciation)/sales = 89.5% and inventories/sales = 16%. This is the Improve
scenario.
× 2015 Dividends
× 2015 Dividend
× Pretax earnings
6/8/14
No Change
1. Balance Sheets Most Recent Forecast
2015 Input 2015
Assets
Cash $20.0 1.00% $22.00
Accts. rec. 280.0 14.00% $308.00
Inventories 400.0 20.00% $440.00
Total CA $700.0 $770.00
2. Income Statement Most Recent Forecast
2015 Input 2015
Sales $2,000.0 110% $2,200.00
Op. costs (excl. depr.) 1,800.0 90.00% $1,980.00
Depreciation 50.0 10.00% $55.00
Less: Interest on LTD 40.0 8.00% × Avg bonds $40.00
Pretax earnings $110.0 $122.58
× Pretax earnings
× 2015 Dividends
The interest on the LOC is based on the LOC’s average value during the year.
3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals) $8.0
+ Increase in long-term debt and common stock $0.0
− Previous line of credit $0.0 Note:
+ Planned increase in retained earnings
Increase in financing $61.0 Note:
Amount of unadjusted deficit or surplus financing:
and the planned addition to the retained earnings
We subtract the previous LOC because the plan does not call for any projected LOC unless necessary.
The adjustment factor takes into account the financing feedback. The formula for the factor is:
Adjustment factor =1-[0.5 x rLOC x (1-T)]
The 0.5 in the formula is based on the assumption that the LOC will be added smoothly throughout the year, so the new
interest will be incurred on only half the new LOC. Interest is deductible for tax pursposes, so it is only the after-tax impact
that determines the adjusted LOC.
Basis for 2016 Forecast
× 2015 Sales
× 2016 Sales
× 2016 Net fixed assets
Note: All inputs are linked to the first worksheet, “1. Mini Case”, so don’t make changes here!
If you want to see a different scenario, go the the first worksheet, “1. Mini Case”, and use the Scenario
Manager there to make changes.
This worksheet shows how to incorporate the impact of financing feedback, which is caused if the LOC is added during the
year and not just at the end of the year. The extra notes below show the changes from this model and the one in the first
worksheet, “1. Mini Case”.
Financing Feeback
Basis for 2016 Forecast
× 2016 Sales
× 2016 Sales
× 2016 Sales
Accts. pay. & accruals $80.0 4.00% $88.00
Total CL $80.0 $148.45
Total liabilities $580.0 $648.45
Common stock 420.0 Carry over from previous year $420.00
× 2016 Sales
× 2016 Sales