5/6/13
2013
Cash $20 Sales $2,000.0
Accts. rec. $280 Op. costs (excl. depr.) $1,800.0
Inventories $400 Depreciation $50.0
Total CA $700 EBIT $150.0
Total liab. & equity $1,200
Selected Ratios and Other Data, 2013
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
Cash/Sales 1% 1% Return on assets (ROA) 5.5% 10.2%
Receivables/Sales 14% 11% Profit margin (M) 3.30% 4.99%
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%
Chapter 12 Mini Case
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.
Hatfield Medical Supplies: Balance Sheet (Millions of
Dollars), 12/31/2013
Hatfield Medical Supplies: Income Statement
(Millions of Dollars Except per Share)
Accts. pay. & accruals $80 Net income $66.0
Total CL $80 Dividends $20.0
Long-term debt $500 Add. to RE $46.0
Total liabilities $580 Common shares 10.0
Common stock $420 EPS $6.6
Retained earnings $200 DPS $2.0
Additional Data 2014
Exp. Saled growth rate 10%
Interest rate on LT debt 8%
Target WACC 9%
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%
Answer: See PowerPoint Show
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 Du Pont equation (see Chapter 3) as one part of your
analysis.
b. Use the AFN equation to estimate Hatfield’s required new external capital for 2014 if the sale growth rate is 10%.
Assume that the firm’s 2013 ratios will remain the same in 2014. (Hint: Hatfield was operating at full capacity in
2013.)
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 of accounts payable, the profit margin, and the payout ratio.
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? Other things held constant, would the calculated capital intensity ratio change over time if the
company were growing and were also subject to economies of scale and/or lumpy assets
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 B91 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 cell C54 as the value for the “By Changing Cell” area of the dialog
box. Then hit OK.
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 .
= ───────────────────
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? Other things held constant, would the calculated capital intensity ratio change over time if the
company were growing and were also subject to economies of scale and/or lumpy assets
M(1 – POR)(S0)
A0* – L0* – M(1 – POR)S0
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.
A0* – L0* – M(1 – POR)S0
Scenario:
Improve
Actual Forecast For inputs:
Inputs 2013 2014 2015 2016 2017 Error Check
Sales growth rate: 10% 8% 5% 5% Ok
Op. costs/Sales: 90% 89.5% 90% 90% 90% Ok
Scenario:
Improve
Actual Forecast
2013 2014 2015 2016 2017
Net sales $2,000 $2,200 $2,376 $2,495 $2,620
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,969 $2,127 $2,233 $2,344
Scenario: Actual Forecast
Improve 2013 2014 2015 2016 2017 Definitions:
Total op. capital $1,120 $1,144 $1,236 $1,297 $1,362 Total operating capital = NOWC + Net fixed assets
FCF $82 $23 $58 $61 FCF = NOPAT − Change in total operating capital
Growth in FCF -72% 157.3% 5.0%
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).
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%
Scenario:
Improve
Horizon Value: Value of operations $1,314
Improve
1. Balance Sheets Most Recent Forecast
2013 Input 2014
Assets
Accts. rec. 280.0 14.00% $308.00
Accts. pay. & accruals $80.0 4.00% $88.00
Common stock 420.0 Carry over from previous year $420.00
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 2017? 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 2014 through 2017). What is the current value of operations? Using
information from the 2013 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 2014 (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 2014 financial statements, answer the following questions.
Basis for 2014 Forecast
Value of Operations: − Preferred stock $0
Total common equity $620.0 $644
Total liabs. & equity $1,200.0 $1,232
Check: TA − Total Liab. & Eq. = $0.00
2. Income Statement Most Recent Forecast
2013 Input 2014
Sales $2,000.0 110% $2,200.00
Op. costs (excl. depr.) 1,800.0 89.50% $1,969.00
Go to Scenario Manager and choose the Improve Scenario.
Basis for 2014 Forecast
× 2013 Sales
× 2014 Sales
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.
Less: Interest on LTD 40.0 8.00% × Avg bonds $40.00
Net income $66.0 $81.60
+ Net income minus regular common dividends $59.60
5/6/13
Improve
1. Balance Sheets Most Recent Forecast
2013 Input #REF!
Assets
Cash $20.0 1.00% $22.00
Accts. rec. 280.0 14.00% $308.00
Inventories 400.0 16.00% $352.00
Check: TA − Total Liab. & Eq. = $0.00
2. Income Statement Most Recent Forecast
2013 Input #REF!
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
Interest on LOC 0.0 8.00% × Avg LOC $0.00 Note:
Pretax earnings $110.0 $136.00
Taxes (40%) 44.0 40.00% $54.40
Net income $66.0 $81.60
Regular common dividends $20.0 110% × 2013 Dividend $22.00
Special dividends $0.0 Pay if financing surplus $35.60
Addition to RE $46.0 $0.00 Net income – Dividends $24.00
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
+ Planned increase in retained earnings
Increase in financing $67.6 Note:
Amount of unadjusted deficit or surplus financing: $35.6
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.
Basis for 2014 Forecast
× 2013 Sales
× 2014 Sales
× 2014 Net PP&E
× Pretax earnings
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”.
The interest on the LOC is based on the LOC’s average value during the year.
Financing Feeback
Basis for 2014 Forecast
× 2014 Sales
× 2014 Sales
× 2014 Sales
Total CA $700.0 $682.00
Net fixed assets 500.0 25.00% $550.00
Total assets $1,200.0 $1,232.00
Accts. pay. & accruals $80.0 4.00% $88.00
Line of credit 0.0 0.00% Draw on LOC if financing deficit $0.00
Total CL $80.0 $88.00
Long-term debt 500.0 0.00 Carry over from previous year $500.00
Total liabilities $580.0 $588.00
Common stock 420.0 Carry over from previous year $420.00
Retained earnings 200.0 $224
Total common equity $620.0 $644
Total liabs. & equity $1,200.0 $1,232
× 2014 Sales
× 2014 Sales