11/21/18
Inventories 1,440 Depreciation 360.0
Total CA $2,790 EBIT $540.0
Net fixed assets 3,600 Interest 144.0
Total assets $6,390 Pretax earnings $396.0
Taxes (25%) 99.0
Accts. pay. & accruals $1,620 Net income $297.0
Line of credit 0
Total CL $1,620 Dividends $100
Long-term debt 1,800 Add. to RE $197
Total liabilities $3,420 Common shares 50
Common stock 2,100 EPS $5.94
Retained earnings 870 DPS $2.00
Total common equ. $2,970 Ending stock price $41.00
Total liab. & equity $6,390
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%
ROIC 8.5% 13.0%
Du Pont ROE M x Sales/Assets x Assets/Equity =ROE
Hatfield 3.30% 1.41 2.15 =10.0%
Industry 5.60% 1.69 1.59 =15.1%
The DuPont analysis confirms the conclusions.
Data for AFN Method
Growth rate in sales (g) 11.1%
Sales (S0)$9,001
Required assets (A0*) $6,390
Spontaneous liabilities (L0*) $1,620
Forecasted sales (S1)$10,000
Increase in sales (ΔS = gS0)$999
Profit margin (M) 3.30%
Assets/Sales (A0*/S0)71.0%
Payout ratio (POR) 33.7%
Spont. Liab./Sales (L0*/S0)18.0%
=−
(L0*/S0)∆S −
=−(0.18)(999.1) −
=−$179.8 −
AFNHatfield = $310.60 million
Self-Supporting g =
M = 3.30%
33.300%
Self-Supporting g = ──────── = 4.3%
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.
Increase in
spontaneous
liabilities
−
Hatfield Medical Supply’s stock price had been lagging its industry averages, so its board of directors brought in a new
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.)
AFNHatfield =
Increase in
retained earnings
Required increase
in assets
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.
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
average.
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.
(A0*/S0)∆S
= ───────────────────────
−
(0.7099)(999.1)
M ×S1 × (1–POR)
(0.033)(10000)(0.6633)
$218.9
$709.3
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? Answer: See PowerPoint Show
M(1 – POR)(S0)
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
Scenario:
No Change
Actual Forecast
2019 2020 2021 2022 2023
Net sales $9,000.9 ######## $10,800 $11,340 #########
Op. costs (excl. depr.) $8,100.9 $9,000 $9,720 $10,206 #########
Depreciation $360.0 $400 $432 $454 $476
EBIT $540.0 $600 $648 $680 $714
Scenario: Actual Forecast
No Change 2019 2020 2021 2022 2023 Definitions:
NOPAT $405 $450 $486 $510 $536 NOPAT = EBIT(1-T)
NOWC $1,170 $1,300 $1,404 $1,474 $1,548 NOWC = (Cash + accounts receivable + inventories) − (Accounts payable & accruals)
Total op. capital $4,770 $5,300 $5,724 $6,010 $6,311 Total operating capital = NOWC + Net fixed assets
FCF −$80 $62 $224 $235 FCF = NOPAT − Change in total operating capital
Growth in FCF -177.5% 261.5% 5.0%
ROIC 8.5% 8.5% 8.5% 8.5% 8.5% ROIC = NOPAT/Total operating capital
Scenario:
No Change
Horizon Value: Value of operations $3,683
+ ST investments $0
$4,941 Estimated total intrinsic value $3,683
− All debt $1,800
Value of Operations: − Preferred stock $0
Present value of HV $3,375 Estimated intrinsic value of equity $1,883
+ Present value of FCF $308 ÷ Number of shares $50
Value of operations = $3,683 Estimated intrinsic stock price = $37.65
Values, Not Live
No Change No Change
1. Balance Sheets Most Recent Forecast 1. Balance Sheets Most Recent Forecast
2019 Input 2020 2019 Input 2020
Assets Assets
Cash 90$ 1.00% 100$ Cash $90.0 1.00% #########
Accts. rec. 1,260 ######## 1,400 Accts. rec. 1,260.0 14.00% #########
Inventories 1,440 ######## 1,600 Inventories 1,440.0 16.00% #########
Total CA 2,790$ ###### # Total CA $2,790.0 #########
Net fixed assets 3,600 ######## 4,000 Net fixed assets 3,600.0 40.00% #########
Total assets 6,390$ ###### # Total assets $6,390.0 #########
Liabilities and equity Liabilities and equity
Accts. pay. & accruals 1,620$ ######## ###### # Accts. pay. & accruals $1,620.0 18.00% #########
2. Income Statement Most Recent Forecast 2. Income Statement Most Recent Forecast
2019 Input 2020 2019 Input 2020
Sales 9,000.9$ 1.111 ####### Sales $9,000.9 111.1% #########
Op. costs (excl. depr.) 8,101 ######## 9,000 Op. costs (excl. depr.) 8,100.9 90.00% #########
Depreciation 360 ######## 400 Depreciation 360.0 10.00% #########
EBIT 540.0$ 600$ EBIT $540.0 #########
Less: Interest on LTD 144 8.00% × Avg bonds 144 Less: Interest on LTD 144.0 8.00% × Avg bonds #########
Interest on LOC – 8.00% × Beginning LOC – Note: Interest on LOC 0.0 8.00% × Beginning LOC $0.00
Pretax earnings 396.0$ 456$ Pretax earnings $396.0 #########
Taxes (25%) 99 ######## 114 Taxes (25%) 99.0 25.00% #########
Net income 297.0$ 342$ Net income $297.0 #########
Regular common dividends $100 110% $110 Regular common dividends $100.0 110% #########
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 #########
× 2020 Sales
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 .
× 2020 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 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?
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 2020 financial statements, answer the following questions.
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 2020 Forecast
× 2020 Sales
× 2020 Sales
Basis for 2020 Forecast
× 2019 Sales
× 2020 Sales
× 2020 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?
Basis for 2020 Forecast
Note: see to right for the “No Change” financial statements with fixed values and not variables.
× 2020 Sales
× 2020 Sales
× 2020 Sales
× 2020 Sales
× 2020 Sales
Basis for 2020 Forecast
× 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 total assets $710 − Increase in total assets #########
Amount of deficit or surplus financing: −$298 Amount of deficit or surplus financing: #########
If deficit in financing (negative), draw on line of credit Line of credit $298
If deficit in financing (negative), draw on line of credit
Line of credit #########
If surplus in financing (positive), pay special dividend Special dividend $0
If surplus in financing (positive), pay special dividend
Special dividend $0.00
Values, Not Live
Improvements
1. Balance Sheets Most Recent Forecast
2019 Input 2020
Assets
Cash $90 1.00% $100
Accts. rec. $1,260 ######## $1,400
Inventories $1,440 ######## $1,400
Total CA $2,790 $2,900
Net fixed assets $3,600 ######## $3,800
Total assets $6,390 $6,700
Check: TA − Total Liab. & Eq. = $0
2. Income Statement Most Recent Forecast
× 2020 Sales
2019 Input $2,020
Sales $9,000.9 ######## #########
Op. costs (excl. depr.) 8,100.9 ######## $8,940
Depreciation 360.0 ######## $380
EBIT $540.0 $680
Less: Interest on LTD 144.0 8.00% × Avg bonds $144
Interest on LOC 0.0 8.00% × Beginning LOC $0
Pretax earnings $396.0 $536
Taxes (25%) 99.0 ######## $134
Net income $297.0 $402
Regular common dividends $100.0 110% $110
Special dividends $0.0 Pay if financing surplus $162
Addition to RE $197.0 Net income – Dividends $130
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)
Special dividend = $162
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.
Go to Scenario Manager and choose the Improve Scenario. This will update the financial statements shown above. They
are copied below as values.
How much can Hatfield pay as a special dividend in the Improve Scenario? What else might Hatfield
do with the financing surplus?
× 2020 Net fixed assets
× Pretax earnings
× 2019 Dividends
Basis for 2020 Forecast
× 2019 Sales
× 2020 Sales
× 2020 Sales
× 2020 Sales
× 2020 Sales
× 2020 Sales
11/21/18
No Change
1. Balance Sheets Most Recent Forecast
2019 Input 2020
Assets
Cash $90.0 1.00% $100.00
Accts. rec. 1,260.0 14.00% $1,400.00
Inventories 1,440.0 16.00% $1,600.00
Total CA $2,790.0 $3,100.00
Net fixed assets 3,600.0 40.00% $4,000.00
Total assets $6,390.0 $7,100.00
Accts. pay. & accruals $1,620.0 18.00% $1,800.00
Line of credit 0.0 0.00% Draw on LOC if financing deficit $307.22
Total CL $1,620.0 $2,107.22
Long-term debt 1,800.0 0.00 Carry over from previous year $1,800.00
Total liabilities $3,420.0 $3,907.22
Common stock 2,100.0 Carry over from previous year $2,100.00
Retained earnings 870.0 $1,093
Total common equity $2,970.0 $3,193
Total liabs. & equity $6,390.0 $7,100
× 2020 Sales
2. Income Statement Most Recent Forecast
2019 Input 2020
Sales $9,000.9 111% $10,000.00
Op. costs (excl. depr.) 8,100.9 90.00% $9,000.00
Depreciation 360.0 10.00% $400.00
EBIT $540.0 $600.00
Less: Interest on LTD 144.0 8.00% × Avg bonds $144.00
Interest on LOC 0.0 8.00% × Avg LOC $12.29 Note:
Pretax earnings $396.0 $443.71
Taxes (25%) 99.0 25.00% $110.93
Net income $297.0 $332.78
Special dividends $0.0 Pay if financing surplus $0.00
Addition to RE $197.0 $0.00 Net income – Dividends $222.78
× 2019 Dividends
3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals)
$180.0
+ Increase in long-term debt and common stock $0.0
− Previous line of credit $0.0 Note:
+ Planned increase in retained earnings
+ After-tax operating income: EBIT (1-T)
$450.0
− After-tax interest on LT debt: (INT LTD x (1-T) $108.0
− After-tax interest on previous LOC: (rLOC x 0.5 x LOCt-1 x (1-T)
$0.0 Note:
− Regular common dividends $110.0
Total planned increase in the retained earnings account
$232.0
Increase in financing $412.0 Note:
− Increase in total assets $710.0
Amount of unadjusted deficit or surplus financing: −$298.0
If there is a surplus (the financing need is positive), pay a special dividend: $0.0
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
× 2020 Sales
Basis for 2020 Forecast
× 2020 Sales
× 2020 Sales
× 2020 Sales
The increase in financing is equal to the sum of
spontaneous liabilities, planned external financing,
and the planned addition to the retained earnings
Note: interest expense is incurred on the planned LOC. Because the plan does not call for any LOC, the average balance
is equal to
(LOCt-1 + 0)/2 = 0.5*LOCt-1.
We subtract the previous LOC because the plan does not call for any projected LOC unless necessary.
Basis for 2020 Forecast
× 2019 Sales
× 2020 Sales
× 2020 Net fixed assets