11/21/18
2019 2019
Cash $90 Sales $9,000.9
Accts. rec. 1,260 Op. costs (excl. depr.) 8,100.9
Inventories 1,440 Depreciation 360.0
Total CA $2,790 EBIT $540.0
Selected Ratios, Calculations, and Other Data, 2019
Operating Ratios and Data Hatfield Industry Other Ratios Hatfield Industry
(Op. costs)/Sales 90% 88% Profit margin (M) 3.30% 5.60%
Depr./FA 10% 12% Return on assets (ROA) 4.6% 9.5%
Cash/Sales 1% 1% Return on equity (ROE) 10.0% 15.1%
Receivables/Sales 14% 11% Sales/Assets 1.41 1.69
The debt/TA ratio and the TL/TA ratio indicate that Hatfield has more leverage than its industry competitors. The combination of higher
interest payments and lower operating profitability cause Hatfield’s times interest earned ratio to be much lower than the industry
Data for AFN Method
Growth rate in sales (g) 11.1%
Sales (S0)$9,001
Forecasted sales (S1)$10,000
Hatfield Medical Supply’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.
b. Use the AFN equation to estimate Hatfield’s required new external capital for 2020 if the sale growth rate is 11.1%.
Assume that the firm’s 2019 ratios will remain the same in 2020. (Hint: Hatfield was operating at full capacity in 2019.)
Hatfield Medical Supply: Balance Sheet (Millions of
Dollars), December 31
Hatfield Medical Supply: Income Statement
(Millions of Dollars Except per Share)
Hatfield has lower operating profitability as shown by operating profitability (OP) ratio: 4.5% vs. 6.1%. Hatfield utilizes operating capital
less efficiently, as shown by capital requirement (CR) ratio: 53% vs. 47%. As a consequence, Hatfield has a lower ROIC: 8.5% vs. 13%. In
fact, Hatfield’s ROIC is less than its 10% WACC.
Chapter 12 Mini Case
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 3) as one part of your analysis.
Spont. Liab./Sales (L0*/S0)18.0%
Self-Supporting g =
Self-Supporting g = ──────── = 4.3%
A0* – L0* – M(1 – POR)S0
Scenario:
No Change
Actual Forecast For inputs:
Inputs 2019 2020 2021 2022 2023 Error Check
Sales growth rate: 11.1% 8% 5% 5% Ok
(Op. costs)/Sales: 90.00% 90.0% 90% 90% 90% Ok
Depr./FA 10.00% 10% 10% 10% 10% Ok
Tax rate: 25.00% 25% 25% 25% 25% Ok
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.
Increase in
spontaneous
−
AFNHatfield =
Increase in
retained earnings
Required increase
in 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 with the AFN (B96) 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.
e. Use the following assumptions to answer the questions below: (1) Operating ratios remain unchanged. (2) Sales will
grow by 11.1%, 8%, 5%, and 5% for the next four years. (3) The target weighted average cost of capital (WACC) is 10%.
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 .
= ───────────────────────
−
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. Answer: See PowerPoint Show
M(1 – POR)(S0)
A0* – L0* – M(1 – POR)S0
Rate on all debt 8% 8% 8% 8%
Div. growth rate: 5.00% 10% 8% 5% 5%
Target WACC 10% 10% 10% 10%
Scenario:
No Change
Actual Forecast
2019 2020 2021 2022 2023
Net sales $9,000.9 $10,000 $10,800 $11,340 $11,907
Scenario: Actual Forecast
Scenario:
No Change
No Change No Change
Net fixed assets 3,600 40.00% 4,000 Net fixed assets 3,600.0 40.00% #########
After forecasting the 2020 financial statements, answer the following questions.
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 2023? 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 2020 through 2023). What is the current value of operations? Using information
from the 2019 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 2020 (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.
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?
Liabilities and equity Liabilities and equity
Accts. pay. & accruals 1,620$ 18.00% 1,800$ Accts. pay. & accruals $1,620.0 18.00% #########
Line of credit – Draw on LOC if financing deficit 298 Line of credit 0.0 Draw on LOC if financing deficit $298.00
Total CL 1,620$ 2,098$ Total CL $1,620.0 #########
Long-term debt 1,800 Carry over from previous year 1,800 Long-term debt 1,800.0 Carry over from previous year #########
Total liabilities 3,420$ 3,898$ Total liabilities $3,420.0 #########
Common stock 2,100$ Carry over from previous year 2,100 Common stock 2,100.0 Carry over from previous year #########
Retained earnings 870 1,102 Retained earnings 870.0 $1,102
Total common equity 2,970$ 3,202$ Total common equity $2,970.0 $3,202
Total liabs. & equity 6,390$ 7,100$ Total liabs. & equity $6,390.0 $7,100
3. Elimination of the Financial Deficit or Surplus 3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals)
If deficit in financing (negative), draw on line of credit
If deficit in financing (negative), draw on line of credit
If surplus in financing (positive), pay special dividend
$180 Increase in spontaneous liabilities (accounts payable and accruals) $180.00
+ Increase in long-term debt and common stock $0 Note: + Increase in long-term debt and common stock $0.00
− Previous line of credit $0 − Previous line of credit $0.00
+ Net income minus regular common dividends $232 + Net income minus regular common dividends $232.00
Increase in financing $412 Increase in financing $412.00
− Increase in total assets $710 − Increase in total assets $710.00
Values, Not Live
Improvements
1. Balance Sheets Most Recent Forecast
2019 Input 2020
Assets
Cash $90 1.00% $100
Accts. rec. $1,260 14.00% $1,400
Inventories $1,440 14.00% $1,400
Net fixed assets $3,600 38.00% $3,800
Total assets $6,390 $6,700
Line of credit $0 Draw on LOC if financing deficit $0
Long-term debt $1,800 Carry over from previous year $1,800
Retained earnings $870 $1,000
Total common equity $2,970 $3,100
Total liabs. & equity $6,390 $6,700
Old RE + Add. to RE
× 2020 Sales
× 2020 Sales
2. Income Statement Most Recent Forecast
2019 Input $2,020
Sales $9,000.9 111.1% $10,000
Op. costs (excl. depr.) 8,100.9 89.40% $8,940
Depreciation 360.0 10.00% $380
Taxes (25%) 99.0 25.00% $134
Net income $297.0 $402
Special dividends $0.0 Pay if financing surplus $162
Addition to RE $197.0 Net income – Dividends $130
× Pretax earnings
× 2019 Dividends
3. Elimination of the Financial Deficit or Surplus
× 2020 Sales
Old RE + Add. to RE
× 2020 Sales
Old RE + Add. to RE
Basis for 2020 Forecast
If there is a LOC in the previous year, then it is necessary to subtract the
previous year’s line of credit. In other words, this is like paying off the old line
of credit on the last day of the year and then drawing on a new line of credit.
g. Repeat the analysis performed in the previous question, but now assume that Hatfield is able to improve the following
inputs: (1) Reduce operating costs (excluding depreciation) to sales to 89.4% at a cost of $40 million. (2) Reduce
inventories/sales to 14% at a cost of $10 million. (3) Reduce net fixed assets/sales to 38% at a cost of $20 million. This is
the Improve scenario.
× 2020 Net fixed assets
Go to Scenario Manager and choose the Improve Scenario. This will update the financial statements shown above. They
are copied below as values.
Basis for 2020 Forecast
× 2019 Sales
× 2020 Sales
× 2020 Sales
× 2020 Sales
× 2020 Sales
2019 Input 2020 2019 Input 2020
Taxes (25%) 99 25.00% 114 Taxes (25%) 99.0 25.00% $114.00
Net income 297.0$ 342$ Net income $297.0 $342.00
Special dividends $0 Pay if financing surplus $0 Special dividends $0.0 Pay if financing surplus $0.00
Addition to RE $197 Net income – Dividends $232 Addition to RE $197.0 Net income – Dividends $232.00
Basis for 2020 Forecast
× 2019 Sales
× 2020 Sales
× 2019 Sales
× 2020 Sales
× 2020 Net fixed assets
× Pretax earnings
× 2019 Dividends
× 2019 Dividends
If there is an initial balance on the on the LOC, the assumption is that the
balance will not change until the last day of the year. Therefore, the interest
for the year is the based only on the beginning balance.
× Pretax earnings
× 2020 Net fixed assets
Increase in spontaneous liabilities (accounts payable and accruals)
$180
+ Increase in long-term debt and common stock $0
− Previous line of credit $0
g. (1)
Should Hatfield implement the plans? How much value would they add to the company?
Improve No Change Net Change in Value
Value of operations $5,662 $3,683 $1,980
Cost of Improvement -$70 -$70
Total value $5,592 $3,683 $1,910
g. (2)
If deficit in financing (negative), draw on line of credit
If surplus in financing (positive), pay special dividend