Mini cases

profilepadma25
Ch12ToolKit.xlsx
Home>Business & Finance homework help>Management homework help>Mini cases

Chapter

Tool Kit Chapter 12 10/27/15
Corporate Valuation and Financial Planning
12-2 Financial Planning at MicroDrive, Inc.
The process used by MicroDrive to forecast the free cash flows from its operating plan is described in the sections below.
We begin with MicroDrive's most recent financial statements and selected additional data.
Figure 12-1
MicroDrive’s Most Recent Financial Statements (Millions, Except for Per Share Data)
INCOME STATEMENTS BALANCE SHEETS
2015 2016 Assets 2015 2016
Net sales $ 4,760 $ 5,000 Cash $ 60 $ 50
COGS (excl. depr.) 3,560 3,800 ST Investments 40 -
Depreciation 170 200 Accounts receivable 380 500
Other operating expenses 480 500 Inventories 820 1,000
EBIT $ 550 $ 500 Total CA $ 1,300 $ 1,550
Interest expense 100 120 Net PP&E 1,700 2,000
Pre-tax earnings $ 450 $ 380 Total assets $ 3,000 $ 3,550
Taxes (40%) 180 152
NI before pref. div. $ 270 $ 228 Liabilities and equity
Preferred div. 8 8 Accounts payable $ 190 $ 200
Net income $ 262 $ 220 Accruals 280 300
Notes payable 130 280
Other Data Total CL $ 600 $ 780
Common dividends $48 $50 Long-term bonds 1,000 1,200
Addition to RE $214 $170 Total liabilities $ 1,600 $ 1,980
Tax rate 40% 40% Preferred stock 100 100
Shares of common stock 50 50 Common stock 500 500
Earnings per share $5.24 $4.40 Retained earnings 800 970
Dividends per share $0.96 $1.00 Total common equity $ 1,300 $ 1,470
Price per share $40.00 $27.00 Total liabs. & equity $ 3,000 $ 3,550
The figure below shows all the inputs required to project the financial statements for the scenario that has been selected with the Scenario Manager: Data, What-If Analysis, Scenario Manager. There are four scenarios. The first is named Status Quo because all operating ratios except the sales growth rate are assumed to remain unchanged. The initial sales growth rate was chosen by MicroDrive's managers based on the existing product lines. The growth rate declines over time until it eventually levels off at a sustainable rate. The second scenario is one with higher sales growth but no improvements in operations. The third scenario improves operations but does not increase sales growth rates higher than those in the Status Quo scenario. The fourth scenario improves operations and sales growth rates.
Section 1 shows the inputs required to estimate the items in an operating plan. For each of these inputs, Section 1 shows the industry averages, the actual values for the past two years for MicroDrive, and the forecasted values for the next five years. The managers assumed the inputs for future years (except the sales growth rate) would be equal to the inputs in the first projected year.
MicroDrive's managers assume that sales will eventually level off at a sustaniable constant rate.
Sections 2 and 3 show the data required to estimate the weighted average cost of capital. Section 4 shows the forecasted growth rate in dividends.
LOC 117 182 214 173 121
PPE 2,200 2,376 2,542 2,669 2,803
Note: These inputs are linked throughout the model. If you want to change an input, do it here and not other places in the model.
Figure 12-2
MicroDrive's Forecast: Inputs for the Selected Scenario (Millions of Dollars) 22.79 9.8%
Status Quo Industry MicroDrive MicroDrive 1000.00 51.20
Inputs Actual Actual Forecast 6.000%
1. Operations 2016 2015 2016 2017 2018 2019 2020 2021 61.00% 18.0% 16.0%
Sales growth rate 5% 15% 5% 10.0% 8.0% 7.0% 5.0% 5.0% 37.0% 35.0%
(COGS excl. depr.)/Sales 76% 75% 76% 76.0% 76.0% 76.0% 76.0% 76.0% 74.70
Depreciation/(Net PP&E) 9% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0%
(Other op. exp.)/Sales 10% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% 0.0984
Cash/Sales 1% 1% 1% 1.0% 1.0% 1.0% 1.0% 1.0%
(Acc. rec.)/Sales 8% 8% 10% 10.0% 10.0% 10.0% 10.0% 10.0% Actual Historical Financing
Inventory/Sales 15% 17% 20% 20.0% 20.0% 20.0% 20.0% 20.0% 2015 2016
(Net PP&E)/Sales 33% 36% 40% 40.0% 40.0% 40.0% 40.0% 40.0% Long-term debt $1,000 $1,200
(Acc. pay.)/Sales 4% 4% 4% 4.0% 4.0% 4.0% 4.0% 4.0% Short-term debt $130 $280
Accruals/Sales 7% 6% 6% 6.0% 6.0% 6.0% 6.0% 6.0% Preferred stock $100 $100
Tax rate 40% 40% 40% 40.0% 40.0% 40.0% 40.0% 40.0% Market value of equity = (Price x # shares) $2,000 $1,350
Cost of strategic initiatives NA NA NA $0 $0 $0 $0 $0 Total $3,230 $2,930
2. Capital Structure Actual Market Weights Target Market Weights
% Long-term debt 22% 31% 41% 28% 28% 28% 28% 28% See the box to the right for calculations of the actual capital structures, based on market values, for the past two years. Percent long-term debt 31% 41%
% Short-term debt 3% 4% 10% 2% 2% 2% 2% 2% Percent short-term debt 4% 10%
% Preferred stock 0% 3% 3% 3% 3% 3% 3% 3% Percent preferred stock 3% 3%
% Common stock 75% 62% 46% 67% 67% 67% 67% 67% Percent market value of equity 62% 46%
3. Costs of Capital Forecast Total 100% 100%
Rate on LT debt 9.0% 9% 9% 9% 9%
Rate on ST debt 10.0% 10% 10% 10% 10%
Rate on preferred stock (ignoring flotation costs) 8.0% 8% 8% 8% 8%
Cost of equity 13.58% 13.58% 13.58% 13.58% 13.58%
4. Target Dividend Policy Actual Forecast
Growth rate of dividends 11% 4.2% 5% 5% 5% 5% 5% 1.00
12-3 Forecasting Operations
The figure below shows the forecasted items for the operating plan. For convenience, we repeat the inputs of operating ratios.
Section B1 shows the sales forecast. Each year's sales is equal to the previous year's sales multiplied by the forecasted sales growth rate.
Section B2 shows the projections of operating assets and operating liabilities. The operating asset for a particular year is equal to the product of that asset's ratio in Section A1 and that particular year's projected sales. The operating liabilities are projected in a similar manner.
Section B3 shows the projections of operating income. The COGS and other operating expenses are equal to the product of the ratio in Section A1 and that particular year's projected sales. Depreciation is equal to the product of the ratio in Section A1 and that particular year's projected net PP&E. EBIT is net sales minus COGS, depreciation, and other operating expenses (including the cost to implement the strategic initiatives in the other scenarios). NOPAT is EBIT(1-T), where T is the tax rate.
Section B4 shows the projections of free cash flows. NOWC is equal to operating CA (i.e., cash, accounts receivable, and inventories from Section B2) minus operating CL (i.e., accounts payable and accruals from Section 4). Total capital is equal to the sum of NOWC and net PP&E (from Section B2).
Section B5 shows the results of the operating plan. The first rows in Section B5 report the target WACC (calculated as shown in Chapter 9), the return on invested capital, and the growth rate in FCF.
The horizon value, value of operations, and estimated intrinsic stock price are calculated using the FCF valuation model as present in Chapter 7.
The forecast of operations for the current scenario is shown below. To look at a different scenario, look in the menu bar and choose Data, What-If Analysis, Scenario Manager, select a scenario and click Show. The information for each scenario is shown to the right, but the values are fixed.
Note: Do not change inputs here! The "input" cells here are linked to the ones in Figure 12-2. If you want to change inputs, do so in 12-2 or use Scenario Manager.
Figure 12-3 Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below
MicroDrive's Forecast of Operations for the Selected Scenario (Millions of Dollars, Except for Per Share Data)
Status Quo Industry MicroDrive MicroDrive Fixed values for each scenario are shown to the right. Status Quo Industry MicroDrive MicroDrive Higher Growth Only Industry MicroDrive MicroDrive Improve Operations Only Industry MicroDrive MicroDrive Operations and Growth Industry MicroDrive MicroDrive
Panel A: Inputs Actual Actual Forecast Panel A: Inputs Actual Actual Forecast Panel A: Inputs Actual Actual Forecast Panel A: Inputs Actual Actual Forecast Panel A: Inputs Actual Actual Forecast
A1. Operations 2016 2015 2016 2017 2018 2019 2020 2021 A1. Operations 2016 2015 2016 2017 2018 2019 2020 2021 A1. Operations 2016 2015 2016 2017 2018 2019 2020 2021 A1. Operations 2016 2015 2016 2017 2018 2019 2020 2021 A1. Operations 2016 2015 2016 2017 2018 2019 2020 2021
Sales growth rate 5% 15% 5% 10.0% 9.0% 8.0% 5.0% 5.0% Sales growth rate 5% 15% 5% 10.0% 9.0% 8.0% 5.0% 5.0% Sales growth rate 5% 15% 5% 10.0% 9.0% 8.0% 6.0% 6.0% Sales growth rate 5% 15% 5% 10.0% 9.0% 8.0% 5.0% 5.0% Sales growth rate 5% 15% 5% 10.0% 9.0% 8.0% 6.0% 6.0%
(COGS excl. depr.)/Sales 76% 75% 76% 76.0% 76.0% 76.0% 76.0% 76.0% (COGS excl. depr.)/Sales 76% 75% 76% 76.0% 76.0% 76.0% 76.0% 76.0% (COGS excl. depr.)/Sales 76% 75% 76% 76.0% 76.0% 76.0% 76.0% 76.0% (COGS excl. depr.)/Sales 76% 75% 76% 76.0% 74.8% 74.8% 74.8% 74.8% (COGS excl. depr.)/Sales 76% 75% 76% 76.0% 74.8% 74.8% 74.8% 74.8%
Depreciation/(Net PP&E) 9% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% Depreciation/(Net PP&E) 9% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% Depreciation/(Net PP&E) 9% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% Depreciation/(Net PP&E) 9% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% Depreciation/(Net PP&E) 9% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0%
(Other op. exp.)/Sales 10% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Other op. exp./Sales) 10% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Other op. exp./Sales) 10% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Other op. exp./Sales) 10% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Other op. exp./Sales) 10% 10% 10% 10.0% 10.0% 10.0% 10.0% 10.0%
Cash/Sales 1% 1% 1% 1.0% 1.0% 1.0% 1.0% 1.0% Cash/Sales 1% 1% 1% 1.0% 1.0% 1.0% 1.0% 1.0% Cash/Sales 1% 1% 1% 1.0% 1.0% 1.0% 1.0% 1.0% Cash/Sales 1% 1% 1% 1.0% 1.0% 1.0% 1.0% 1.0% Cash/Sales 1% 1% 1% 1.0% 1.0% 1.0% 1.0% 1.0%
(Acc. rec.)/Sales 8% 8% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Acc. rec.)/Sales 8% 8% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Acc. rec.)/Sales 8% 8% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Acc. rec.)/Sales 8% 8% 10% 10.0% 10.0% 10.0% 10.0% 10.0% (Acc. rec.)/Sales 8% 8% 10% 10.0% 10.0% 10.0% 10.0% 10.0%
Inventory/Sales 15% 17% 20% 20.0% 20.0% 20.0% 20.0% 20.0% Inventory/Sales 15% 17% 20% 20.0% 20.0% 20.0% 20.0% 20.0% Inventory/Sales 15% 17% 20% 20.0% 20.0% 20.0% 20.0% 20.0% Inventory/Sales 15% 17% 20% 18.0% 16.0% 16.0% 16.0% 16.0% Inventory/Sales 15% 17% 20% 18.0% 16.0% 16.0% 16.0% 16.0%
(Net PP&E)/Sales 33% 36% 40% 40.0% 40.0% 40.0% 40.0% 40.0% (Net PP&E)/Sales 33% 36% 40% 40.0% 40.0% 40.0% 40.0% 40.0% (Net PP&E)/Sales 33% 36% 40% 40.0% 40.0% 40.0% 40.0% 40.0% (Net PP&E)/Sales 33% 36% 40% 37.0% 35.0% 35.0% 35.0% 35.0% (Net PP&E)/Sales 33% 36% 40% 37.0% 35.0% 35.0% 35.0% 35.0%
(Acc. pay.)/Sales 4% 4% 4% 4.0% 4.0% 4.0% 4.0% 4.0% (Acc. pay.)/Sales 4% 4% 4% 4.0% 4.0% 4.0% 4.0% 4.0% (Acc. pay.)/Sales 4% 4% 4% 4.0% 4.0% 4.0% 4.0% 4.0% (Acc. pay.)/Sales 4% 4% 4% 4.0% 4.0% 4.0% 4.0% 4.0% (Acc. pay.)/Sales 4% 4% 4% 4.0% 4.0% 4.0% 4.0% 4.0%
Accruals/Sales 7% 6% 6% 6.0% 6.0% 6.0% 6.0% 6.0% Accruals/Sales 7% 6% 6% 6.0% 6.0% 6.0% 6.0% 6.0% Accruals/Sales 7% 6% 6% 6.0% 6.0% 6.0% 6.0% 6.0% Accruals/Sales 7% 6% 6% 6.0% 6.0% 6.0% 6.0% 6.0% Accruals/Sales 7% 6% 6% 6.0% 6.0% 6.0% 6.0% 6.0%
Tax rate 40% 40% 40% 40.0% 40.0% 40.0% 40.0% 40.0% Tax rate 40% 40% 40% 40.0% 40.0% 40.0% 40.0% 40.0% Tax rate 40% 40% 40% 40.0% 40.0% 40.0% 40.0% 40.0% Tax rate 40% 40% 40% 40.0% 40.0% 40.0% 40.0% 40.0% Tax rate 40% 40% 40% 40.0% 40.0% 40.0% 40.0% 40.0%
Cost of strategic initiatives NA NA NA $0 $0 $0 $0 $0 Cost of strategic initiatives NA NA NA $0 $0 $0 $0 $0 Cost of strategic initiatives NA NA NA $110 $0 $0 $0 $0 Cost of strategic initiatives NA NA NA $165 $0 $0 $0 $0 Cost of strategic initiatives NA NA NA $275 $0 $0 $0 $0
Panel B: Results Actual Forecast Panel B: Results Actual Forecast Panel B: Results Actual Forecast Panel B: Results Actual Forecast Panel B: Results Actual Forecast
B1. Sales Revenues 2016 2017 2018 2019 2020 2021 B1. Sales Revenues 2016 2017 2018 2019 2020 2021 B1. Sales Revenues 2016 2017 2018 2019 2020 2021 B1. Sales Revenues 2016 2017 2018 2019 2020 2021 B1. Sales Revenues 2016 2017 2018 2019 2020 2021
Net sales $5,000 $5,500 $5,940 $6,356 $6,674 $7,007 Net sales $5,000 $5,500 $5,940 $6,356 $6,674 $7,007 Net sales $5,000 $5,500 $5,995 $6,475 $6,863 $7,275 Net sales $5,000 $5,500 $5,940 $6,356 $6,674 $7,007 Net sales $5,000 $5,500 $5,995 $6,475 $6,863 $7,275
B2. Operating Assets and Operating Liabilities B2. Operating Assets and Operating Liabilities B2. Operating Assets and Operating Liabilities B2. Operating Assets and Operating Liabilities B2. Operating Assets and Operating Liabilities
Cash $50 $55 $59 $64 $67 $70 Cash $50 $55 $59 $64 $67 $70 Cash $50 $55 $60 $65 $69 $73 Cash $50 $55 $59 $64 $67 $70 Cash $50 $55 $60 $65 $69 $73
Accounts receivable $500 $550 $594 $636 $667 $701 Accounts receivable $500 $550 $594 $636 $667 $701 Accounts receivable $500 $550 $600 $647 $686 $727 Accounts receivable $500 $550 $594 $636 $667 $701 Accounts receivable $500 $550 $600 $647 $686 $727
Inventories $1,000 $1,100 $1,188 $1,271 $1,335 $1,401 Inventories $1,000 $1,100 $1,188 $1,271 $1,335 $1,401 Inventories $1,000 $1,100 $1,199 $1,295 $1,373 $1,455 Inventories $1,000 $990 $950 $1,017 $1,068 $1,121 Inventories $1,000 $990 $959 $1,036 $1,098 $1,164
Net PP&E $2,000 $2,200 $2,376 $2,542 $2,669 $2,803 Net PP&E $2,000 $2,200 $2,376 $2,542 $2,669 $2,803 Net PP&E $2,000 $2,200 $2,398 $2,590 $2,745 $2,910 Net PP&E $2,000 $2,035 $2,079 $2,225 $2,336 $2,453 Net PP&E $2,000 $2,035 $2,098 $2,266 $2,402 $2,546
Accounts payable $200 $220 $238 $254 $267 $280 Accounts payable $200 $220 $238 $254 $267 $280 Accounts payable $200 $220 $240 $259 $275 $291 Accounts payable $200 $220 $238 $254 $267 $280 Accounts payable $200 $220 $240 $259 $275 $291
Accruals $300 $330 $356 $381 $400 $420 Accruals $300 $330 $356 $381 $400 $420 Accruals $300 $330 $360 $388 $412 $436 Accruals $300 $330 $356 $381 $400 $420 Accruals $300 $330 $360 $388 $412 $436
B3. Operating Income B3. Operating Income B3. Operating Income B3. Operating Income B3. Operating Income
COGS (excl. depr.) $3,800 $4,180 $4,514 $4,830 $5,072 $5,326 COGS (excl. depr.) $3,800 $4,180 $4,514 $4,830 $5,072 $5,326 COGS (excl. depr.) $3,800 $4,180 $4,556 $4,921 $5,216 $5,529 COGS (excl. depr.) $3,800 $4,180 $4,443 $4,754 $4,992 $5,241 COGS (excl. depr.) $3,800 $4,180 $4,484 $4,843 $5,134 $5,442
Depreciation $200 $220 $238 $254 $267 $280 Depreciation $200 $220 $238 $254 $267 $280 Depreciation $200 $220 $240 $259 $275 $291 Depreciation $200 $204 $208 $222 $234 $245 Depreciation $200 $204 $210 $227 $240 $255
Other operating expenses $500 $550 $594 $636 $667 $701 Other operating expenses $500 $550 $594 $636 $667 $701 Other operating expenses $500 $550 $600 $647 $686 $727 Other operating expenses $500 $550 $594 $636 $667 $701 Other operating expenses $500 $550 $600 $647 $686 $727
Cost of strategic initiatives NA $0 $0 $0 $0 $0 Cost of strategic initiatives NA $0 $0 $0 $0 $0 Cost of strategic initiatives NA $110 $0 $0 $0 $0 Cost of strategic initiatives NA $165 $0 $0 $0 $0 Cost of strategic initiatives NA $275 $0 $0 $0 $0
EBIT $500 $550 $594 $636 $667 $701 EBIT $500 $550 $594 $636 $667 $701 EBIT $500 $440 $600 $647 $686 $727 EBIT $500 $402 $695 $744 $781 $820 EBIT $500 $292 $701 $758 $803 $851
Net operating profit after taxes $300 $330 $356 $381 $400 $420 Net operating profit after taxes $300 $330 $356 $381 $400 $420 Net operating profit after taxes $300 $264 $360 $388 $412 $436 Net operating profit after taxes $300 $241 $417 $446 $468 $492 Net operating profit after taxes $300 $175 $421 $455 $482 $511
B4. Free Cash Flows B4. Free Cash Flows B4. Free Cash Flows B4. Free Cash Flows B4. Free Cash Flows
Net operating working capital $1,050 $1,155 $1,247 $1,335 $1,401 $1,472 Net operating working capital $1,050 $1,155 $1,247 $1,335 $1,401 $1,472 Net operating working capital $1,050 $1,155 $1,259 $1,360 $1,441 $1,528 Net operating working capital $1,050 $1,045 $1,010 $1,080 $1,135 $1,191 Net operating working capital $1,050 $1,045 $1,019 $1,101 $1,167 $1,237
Total net operating capital $3,050 $3,355 $3,623 $3,877 $4,071 $4,274 Total operating capital $3,050 $3,355 $3,623 $3,877 $4,071 $4,274 Total operating capital $3,050 $3,355 $3,657 $3,950 $4,186 $4,438 Total operating capital $3,050 $3,080 $3,089 $3,305 $3,470 $3,644 Total operating capital $3,050 $3,080 $3,117 $3,367 $3,569 $3,783
FCF = NOPAT – Δ net op capital −$260 $25 $88 $128 $207 $216.89 FCF = NOPAT – Δ op capital −$260 $25 $88 $128 $207 $217 FCF = NOPAT – Δ op capital −$260 −$41 $58 $96 $175 $185 FCF = NOPAT – Δ op capital −$260 $211 $408 $230 $303 $318 FCF = NOPAT – Δ op capital −$260 $145 $383 $205 $280 $297
B5. Estimated Intrinsic Value Note: the WACC is rounded to 4 decimal places. B5. Estimated Intrinsic Value B5. Estimated Intrinsic Value B5. Estimated Intrinsic Value B5. Estimated Intrinsic Value
Target WACC 10.97% 10.97% 10.97% 10.97% 10.97% Target WACC 11.0% 11.0% 11.0% 11.0% 11.0% Target WACC 11.0% 11.0% 11.0% 11.0% 11.0% Target WACC 11.0% 11.0% 11.0% 11.0% 11.0% Target WACC 11.0% 11.0% 11.0% 11.0% 11.0%
OP ratio: NOPAT/Sales 6% 6.0% 6.0% 6.0% 6.0% 6.0% OP ratio: NOPAT/Sales 6% 6.0% 6.0% 6.0% 6.0% 6.0% OP ratio: NOPAT/Sales 6% 4.8% 6.0% 6.0% 6.0% 6.0% OP ratio: NOPAT/Sales 6% 4.4% 7.0% 7.0% 7.0% 7.0% OP ratio: NOPAT/Sales 6% 3.2% 7.0% 7.0% 7.0% 7.0%
CR ratio: (Total op. cap.)/Sales 61% 61.0% 61.0% 61.0% 61.0% 61.0% CR ratio: (Total op. cap.)/Sales 61% 61.0% 61.0% 61.0% 61.0% 61.0% CR ratio: (Total op. cap.)/Sales 61% 61.0% 61.0% 61.0% 61.0% 61.0% CR ratio: (Total op. cap.)/Sales 61% 56.0% 52.0% 52.0% 52.0% 52.0% CR ratio: (Total op. cap.)/Sales 61% 56.0% 52.0% 52.0% 52.0% 52.0%
ROIC: NOPAT/(Total op. cap.) 9.8% 9.8% 9.8% 9.8% 9.8% 9.8% ROIC: NOPAT/(Total op. cap.) 9.8% 9.8% 9.8% 9.8% 9.8% 9.8% ROIC: NOPAT/(Total op. cap.) 9.8% 7.9% 9.8% 9.8% 9.8% 9.8% ROIC: NOPAT/(Total op. cap.) 9.8% 7.8% 13.5% 13.5% 13.5% 13.5% ROIC: NOPAT/(Total op. cap.) 9.8% 5.7% 13.5% 13.5% 13.5% 13.5%
Growth in FCF 252.0% 45.1% 61.7% 5.0% Growth in FCF 252% 45.1% 61.7% 5.0% Growth in FCF -241% 66.1% 82.3% 6.0% Growth in FCF 94% -43.7% 31.9% 5.0% Growth in FCF 165% -46.5% 36.4% 6.0%
Value of Operations (12/31/2021) Estimated intrinsic stock price (12/31/2016) Value of Operations (12/31/2021) Estimated intrinsic stock price (12/31/2016) Value of Operations (12/31/2021) Estimated intrinsic stock price (12/31/2016) Value of Operations (12/31/2021) Estimated intrinsic stock price (12/31/2016) Value of Operations (12/31/2021) Estimated intrinsic stock price (12/31/2016)
Value of operations $2,719 Value of operations $2,719 Value of operations $2,654 Value of operations $4,407 Value of operations $4,712
= $3,815 + ST investments $0 = $3,815 + ST investments $0 = $3,952 + ST investments $0 = $5,600 + ST investments $0 = $6,325 + ST investments $0
Estimated total intrinsic value $2,719 Estimated total intrinsic value $2,719 Estimated total intrinsic value $2,654 Estimated total intrinsic value $4,407 Estimated total intrinsic value $4,712
Value of Operations (12/31/2016) − All debt $1,480 Value of Operations (12/31/2016) − All debt $1,480 Value of Operations (12/31/2016) − All debt $1,480 Value of Operations (12/31/2016) − All debt $1,480 Value of Operations (12/31/2016) − All debt $1,480
Present value of HV $2,267 − Preferred stock $100 Present value of HV $2,267 − Preferred stock $100 Present value of HV $2,349 − Preferred stock $100 Present value of HV $3,328 − Preferred stock $100 Present value of HV $3,759 − Preferred stock $100
+ Present value of FCF $453 Estimated intrinsic value of equity $1,139 + Present value of FCF $453 Estimated intrinsic value of equity $1,139 + Present value of FCF $306 Estimated intrinsic value of equity $1,074 + Present value of FCF $1,079 Estimated intrinsic value of equity $2,827 + Present value of FCF $953 Estimated intrinsic value of equity $3,132
Value of operations = $2,719 ÷ Number of shares $50.00 Value of operations = $2,719 ÷ Number of shares $50 Value of operations = $2,654 ÷ Number of shares $50 Value of operations = $4,407 ÷ Number of shares $50 Value of operations = $4,712 ÷ Number of shares $50
Estimate intrinsic stock price = $22.79 Estimate intrinsic stock price = $22.79 Estimate intrinsic stock price = $21.48 Estimate intrinsic stock price = $56.54 Estimate intrinsic stock price = $62.63
12-4 Evaluating MicroDrive's Strategic Initiatives
The figure below shows key inputs and results for the four scenarios: Status Quo, Higher Sales Growth (Only), Improve Operations (Only), and Growth and Operations. The values for each scenaro can be generated using Scenario Manager's Summary feature: Data, What-If Analysis, Scenario Manager, Summary. We show them in the worksheet "Scenario Summary" as well as in the figure below. Note: we generated the values in the Figure by manually linking them to the fixed value output for the scenarios shown above and to the right so that the reported values in the figure below would not change if you decide to try the Scenario Summary feature yourself, which we encourgage.
Figure 12-4
Key Inputs and Operating Results for Possible Strategic Initiatives (Millions, Except for Per Share Data)
Scenario
(1) Status Quo (2) Higher Sales Growth (Only) (3) Improve Operations (Only) (4) Operations and Growth
Panel A: Key Inputs
Sales growth (Year 1) 10.0% 10.0% 10.0% 10.0%
Sales growth Year 2) 9.0% 9.0% 9.0% 9.0%
Sales growth (Year 3) 8.0% 8.0% 8.0% 8.0%
Long-term sales growth (gL) 5.0% 6.0% 5.0% 6.0%
(COGS excl. depr.)/Sales (Year 1) 76.0% 76.0% 76.0% 76.0%
(COGS excl. depr.)/Sales (Year 2) 76.0% 76.0% 74.8% 74.8%
Inventory/Sales for (Year 1) 20.0% 20.0% 18.0% 18.0%
Inventory/Sales for (Year 2) 20.0% 20.0% 16.0% 16.0%
(Net PP&E)/Sales (Year 1) 40.0% 40.0% 37.0% 37.0%
(Net PP&E)/Sales (Year 2) 40.0% 40.0% 35.0% 35.0%
Cost of strategic initiatives $0 $110 $165 $275
Weighted average cost of capital (WACC) 10.97% 10.97% 10.97% 10.97%
Panel B: Key Operating Plan Results
OP, operating profitability (Year 5) 6.0% 6.0% 7.0% 7.0%
CR, capital requirement (Year 5) 61.0% 61.0% 52.0% 52.0%
ROIC, return on invested capital (Year 5) 9.84% 9.84% 13.50% 13.50%
Sales (Year 5) $7,007 $7,275 $7,007 $7,275
NOPAT (Year 5) $420 $436 $492 $511
Total net operating capital ((Year 5) $4,274 $4,438 $3,644 $3,783
FCF (Year 5) $217 $185 $318 $297
Value of operations (Year 0) $2,719 $2,654 $4,407 $4,712
Intrinsic stock price (Year 0) $22.79 $21.48 $56.54 $62.63
Note: Operating improvements will be sustained at the Year 2 values.
12-5 Projecting MicroDrive's Financial Statements
Projecting Financial Statements for One Year
Figure 12-5, shown below, projects MicroDrive's financial statements for the upcoming year for the Status Quo scenario.
Operating items are projected in the identical manner as previously projected for the operating plan.
The preliminary short-term financial policy calls for no changes in notes payable, long-term bonds, preferred stock, and common stock, so their values from the previous year are carried over.
The interest on notes payable and long-term bonds is based on the average amount of debt during the year, defined as the average of the beginning debt (i.e., the debt at the end of the previous year) and the ending debt. An identical process is applied to preferred dividends.
The preliminary short-term financial policy calls for dividends to grow at the same rate as the long-term sustainable growth rate in earnings (which is the same as sales in the long-term).
Section 3 in the figure below calculates the additional financing provided by spontaneous liabilities, external sources, and internal sources. The sum of these three sources of financing is the total amount of additional preliminary financing.
Section 3 also calculates the total amount of additional assets required by the operating plan.
The difference between the total additional financing and the total additional assets is defined as the financing deficit (if the difference is negative) or the financing surplus (if the difference is positive).
If there is a financing deficit, MicroDrive will draw on a line of credit. MicroDrive assumes that the LOC will be accessed on the last day of the year, so the new line of credit (reflected in the end-of-year balance) will not accrue enough interest to matter. Therefore, the interest on the LOC will be equal to the balance at the beginning of the year (which is the same as the balance at the end of the previous year).
If there is a financing surplus, MicroDrive will pay a special dividend.
Note: Do not change inputs here! The "input" cells here are linked to the ones in Figure 12-2. If you want to change inputs, do so in 12-2 or use Scenario Manager.
Figure 12-5
Projected Financial Statements (Millions of Dollars) Fixed values for each scenario are shown to the right. Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below
Status Quo Status Quo Higher Growth Only Improve Operations Only Operations and Growth
1. Balance Sheets Most Recent Forecast 1. Balance Sheets Most Recent Forecast 1. Balance Sheets Most Recent Forecast 1. Balance Sheets Most Recent Forecast 1. Balance Sheets Most Recent Forecast
Assets 2016 Input Basis for 2017 Forecast 2017 Assets 2016 Input Basis for 2017 Forecast 2017 Assets 2016 Input Basis for 2017 Forecast 2017 Assets 2016 Input Basis for 2017 Forecast 2017 Assets 2016 Input Basis for 2017 Forecast 2017
Cash $50.0 1.00% × 2017 Sales $55.00 Cash $50.0 1.00% × 2017 Sales $55.00 Cash $50.0 1.00% × 2017 Sales $55.00 Cash $50.0 1.00% × 2017 Sales $55.00 Cash $50.0 1.00% × 2017 Sales $55.00
Accounts receivable 500.0 10.00% × 2017 Sales $550.00 Accounts receivable 500.0 10.00% × 2017 Sales $550.00 Accounts receivable 500.0 10.00% × 2017 Sales $550.00 Accounts receivable 500.0 10.00% × 2017 Sales $550.00 Accounts receivable 500.0 10.00% × 2017 Sales $550.00
Inventories 1,000.0 20.00% × 2017 Sales $1,100.00 Inventories 1,000.0 20.00% × 2017 Sales $1,100.00 Inventories 1,000.0 20.00% × 2017 Sales $1,100.00 Inventories 1,000.0 18.00% × 2017 Sales $990.00 Inventories 1,000.0 18.00% × 2017 Sales $990.00
Total current assets $1,550.0 $1,705.00 Total current assets $1,550.0 $1,705.00 Total current assets $1,550.0 $1,705.00 Total current assets $1,550.0 $1,595.00 Total current assets $1,550.0 $1,595.00
Net PP&E 2,000.0 40.00% × 2017 Sales $2,200.00 Net PP&E 2,000.0 40.00% × 2017 Sales $2,200.00 Net PP&E 2,000.0 40.00% × 2017 Sales $2,200.00 Net PP&E 2,000.0 37.00% × 2017 Sales $2,035.00 Net PP&E 2,000.0 37.00% × 2017 Sales $2,035.00
Total assets (TA) $3,550.0 $3,905.00 Total assets (TA) $3,550.0 $3,905.00 Total assets (TA) $3,550.0 $3,905.00 Total assets (TA) $3,550.0 $3,630.00 Total assets (TA) $3,550.0 $3,630.00
Liabilities and equity Liabilities and equity Liabilities and equity Liabilities and equity Liabilities and equity
Accounts payable $200.0 4.00% × 2017 Sales $220.00 Accounts payable $200.0 4.00% × 2017 Sales $220.00 Accounts payable $200.0 4.00% × 2017 Sales $220.00 Accounts payable $200.0 4.00% × 2017 Sales $220.00 Accounts payable $200.0 4.00% × 2017 Sales $220.00
Accruals 300.0 6.00% × 2017 Sales $330.00 Accruals 300.0 6.00% × 2017 Sales $330.00 Accruals 300.0 6.00% × 2017 Sales $330.00 Accruals 300.0 6.00% × 2017 Sales $330.00 Accruals 300.0 6.00% × 2017 Sales $330.00
Notes payable 280.0 Carry over from previous year $280.00 Notes payable 280.0 Carry over from previous year $280.00 Notes payable 280.0 Carry over from previous year $280.00 Notes payable 280.0 Carry over from previous year $280.00 Notes payable 280.0 Carry over from previous year $280.00
Line of credit 0.0 Draw on LOC if financing deficit $117.10 Line of credit 0.0 Draw on LOC if financing deficit $117.10 Line of credit 0.0 Draw on LOC if financing deficit $183.10 Line of credit 0.0 Draw on LOC if financing deficit $0.00 Line of credit 0.0 Draw on LOC if financing deficit $0.00
Total CL $780.0 $947.10 Total CL $780.0 $947.10 Total CL $780.0 $1,013.10 Total CL $780.0 $830.00 Total CL $780.0 $830.00
Long-term bonds 1,200.0 Carry over from previous year $1,200.00 Long-term bonds 1,200.0 Carry over from previous year $1,200.00 Long-term bonds 1,200.0 Carry over from previous year $1,200.00 Long-term bonds 1,200.0 Carry over from previous year $1,200.00 Long-term bonds 1,200.0 Carry over from previous year $1,200.00
Total liabilities $1,980.0 $2,147.10 Total liabilities $1,980.0 $2,147.10 Total liabilities $1,980.0 $2,213.10 Total liabilities $1,980.0 $2,030.00 Total liabilities $1,980.0 $2,030.00
Preferred stock $100.0 Carry over from previous year $100.00 Preferred stock $100.0 Carry over from previous year $100.00 Preferred stock $100.0 Carry over from previous year $100.00 Preferred stock $100.0 Carry over from previous year $100.00 Preferred stock $100.0 Carry over from previous year $100.00
Common stock 500.0 Carry over from previous year $500.00 Common stock 500.0 Carry over from previous year $500.00 Common stock 500.0 Carry over from previous year $500.00 Common stock 500.0 Carry over from previous year $500.00 Common stock 500.0 Carry over from previous year $500.00
Retained earnings 970.0 Previous RE + Add. to RE $1,158 Retained earnings 970.0 Previous RE + Add. to RE $1,158 Retained earnings 970.0 Previous RE + Add. to RE $1,092 Retained earnings 970.0 Previous RE + Add. to RE $1,000 Retained earnings 970.0 Previous RE + Add. to RE $1,000
Total common equity $1,470.0 $1,658 Total common equity $1,470.0 $1,658 Total common equity $1,470.0 $1,592 Total common equity $1,470.0 $1,500 Total common equity $1,470.0 $1,500
Total liabs. & equity $3,550.0 $3,905 Total liabs. & equity $3,550.0 $3,905 Total liabs. & equity $3,550.0 $3,905 Total liabs. & equity $3,550.0 $3,630 Total liabs. & equity $3,550.0 $3,630
Check: TA − Total Liab. & Eq. = $0.00 Check: TA − Total Liab. & Eq. = $0.00 Check: TA − Total Liab. & Eq. = $0.00 Check: TA − Total Liab. & Eq. = $0.00 Check: TA − Total Liab. & Eq. = $0.00
2. Income Statement Most Recent Forecast 2. Income Statement Most Recent Forecast 2. Income Statement Most Recent Forecast 2. Income Statement Most Recent Forecast 2. Income Statement Most Recent Forecast
2016 Input Basis for 2017 Forecast 2017 2016 Input Basis for 2017 Forecast 2017 2016 Input Basis for 2017 Forecast 2017 2016 Input Basis for 2017 Forecast 2017 2016 Input Basis for 2017 Forecast 2017
Net sales $5,000.0 110% × 2016 Sales $5,500.00 Net sales $5,000.0 110% × 2016 Sales $5,500.00 Net sales $5,000.0 110% × 2016 Sales $5,500.00 Net sales $5,000.0 110% × 2016 Sales $5,500.00 Net sales $5,000.0 110% × 2016 Sales $5,500.00
COGS (excl. depr.) 3,800.0 76.00% × 2017 Sales 4,180.00 COGS (excl. depr.) 3,800.0 76.00% × 2017 Sales 4,180.00 COGS (excl. depr.) 3,800.0 76.00% × 2017 Sales 4,180.00 COGS (excl. depr.) 3,800.0 76.00% × 2017 Sales 4,180.00 COGS (excl. depr.) 3,800.0 76.00% × 2017 Sales 4,180.00
Depreciation 200.0 10.00% × 2017 Net PP&E 220.00 Depreciation 200.0 10.00% × 2017 Net PP&E 220.00 Depreciation 200.0 10.00% × 2017 Net PP&E 220.00 Depreciation 200.0 10.00% × 2017 Net PP&E 203.50 Depreciation 200.0 10.00% × 2017 Net PP&E 203.50
Other operating expenses 500.0 10.00% × 2017 Sales 550.00 Other operating expenses 500.0 10.00% × 2017 Sales 550.00 Other operating expenses 500.0 10.00% × 2017 Sales 550.00 Other operating expenses 500.0 10.00% × 2017 Sales 550.00 Other operating expenses 500.0 10.00% × 2017 Sales 550.00
Cost of strategic initiatives 0.0 Cost of implementation 0.00 Cost of strategic initiatives 0.0 Cost of implementation 0.00 Cost of strategic initiatives 0.0 Cost of implementation 110.00 Cost of strategic initiatives 0.0 Cost of implementation 165.00 Cost of strategic initiatives 0.0 Cost of implementation 275.00
EBIT $500.0 $550.00 EBIT $500.0 $550.00 EBIT $500.0 $440.00 EBIT $500.0 $401.50 EBIT $500.0 $291.50
Less: Interest on notes 20.0 10.00% × Avg notes $28.00 Less: Interest on notes 20.0 10.00% × Avg notes $28.00 Less: Interest on notes 20.0 10.00% × Avg notes $28.00 Less: Interest on notes 20.0 10.00% × Avg notes $28.00 Less: Interest on notes 20.0 10.00% × Avg notes $28.00
Interest on bonds 100.0 9.00% × Avg bonds $108.00 Interest on bonds 100.0 9.00% × Avg bonds $108.00 Interest on bonds 100.0 9.00% × Avg bonds $108.00 Interest on bonds 100.0 9.00% × Avg bonds $108.00 Interest on bonds 100.0 9.00% × Avg bonds $108.00
Interest on LOC 0.0 11.50% × Beginning LOC $0.00 Note: See comment.
Mike Ehrhardt: 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. 		Interest on LOC 0.0 11.50% × Beginning LOC $0.00 Interest on LOC 0.0 11.50% × Beginning LOC $0.00 Interest on LOC 0.0 11.50% × Beginning LOC $0.00 Interest on LOC 0.0 11.50% × Beginning LOC $0.00
Pre-tax earnings $380.0 $414.00 Pre-tax earnings $380.0 $414.00 Pre-tax earnings $380.0 $304.00 Pre-tax earnings $380.0 $265.50 Pre-tax earnings $380.0 $155.50
Taxes (40%) 152.0 40.00% × Pre-tax earnings $165.60 Taxes (40%) 152.0 40.00% × Pre-tax earnings $165.60 Taxes (40%) 152.0 40.00% × Pre-tax earnings $121.60 Taxes (40%) 152.0 40.00% × Pre-tax earnings $106.20 Taxes (40%) 152.0 40.00% × Pre-tax earnings $62.20
NI before pref. div. $228.0 $248.40 NI before pref. div. $228.0 $248.40 NI before pref. div. $228.0 $182.40 NI before pref. div. $228.0 $159.30 NI before pref. div. $228.0 $93.30
Preferred dividend 8.0 8.00% × Avg pref. stock $8.00 Preferred dividend 8.0 8.00% × Avg pref. stock $8.00 Preferred dividend 8.0 8.00% × Avg pref. stock $8.00 Preferred dividend 8.0 8.00% × Avg pref. stock $8.00 Preferred dividend 8.0 8.00% × Avg pref. stock $8.00
Net income $220.0 $240.40 Net income $220.0 $240.40 Net income $220.0 $174.40 Net income $220.0 $151.30 Net income $220.0 $85.30
Regular common dividends $50.0 105% × 2016 Dividend $52.50 Regular common dividends $50.0 105% × 2016 Dividend $52.50 Regular common dividends $50.0 105% × 2016 Dividend $52.50 Regular common dividends $50.0 105% × 2016 Dividend $52.50 Regular common dividends $50.0 105% × 2016 Dividend $52.50
Special dividends $0.0 Pay if financing surplus $0.00 Special dividends $0.0 Pay if financing surplus $0.00 Special dividends $0.0 Pay if financing surplus $0.00 Special dividends $0.0 Pay if financing surplus $68.80 Special dividends $0.0 Pay if financing surplus $2.80
Addition to RE $170.0 Net income – Dividends $187.90 Addition to RE $170.0 Net income – Dividends $187.90 Addition to RE $170.0 Net income – Dividends $121.90 Addition to RE $170.0 Net income – Dividends $30.00 Addition to RE $170.0 Net income – Dividends $30.00
3. Elimination of the Financial Deficit or Surplus 3. Elimination of the Financial Deficit or Surplus 3. Elimination of the Financial Deficit or Surplus 3. Elimination of the Financial Deficit or Surplus 3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals) $50.00 Increase in spontaneous liabilities (accounts payable and accruals) $50.00 Increase in spontaneous liabilities (accounts payable and accruals) $50.00 Increase in spontaneous liabilities (accounts payable and accruals) $50.00 Increase in spontaneous liabilities (accounts payable and accruals) $50.00
+ Increase in notes payable, long-term bonds, preferred stock, and common stock $0.00 + Increase in notes payable, long-term bonds, preferred stock, and common stock $0.00 + Increase in notes payable, long-term bonds, preferred stock, and common stock $0.00 + Increase in notes payable, long-term bonds, preferred stock, and common stock $0.00 + Increase in notes payable, long-term bonds, preferred stock, and common stock $0.00
+ Net income minus regular common dividends $187.90 Note: See comment.
Mike Ehrhardt: This is the planned increase in the retained earnings account.		 + Net income minus regular common dividends $187.90 + Net income minus regular common dividends $121.90 + Net income minus regular common dividends $98.80 + Net income minus regular common dividends $32.80
− Previous line of credit $0.00 Note: See comment.
Mike Ehrhardt: 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.		 − Previous line of credit $0.00 − Previous line of credit $0.00 − Previous line of credit $0.00 − Previous line of credit $0.00
Increase in financing $237.90 Increase in financing $237.90 Increase in financing $171.90 Increase in financing $148.80 Increase in financing $82.80
− Increase in total assets $355.00 − Increase in total assets $355.00 − Increase in total assets $355.00 − Increase in total assets $80.00 − Increase in total assets $80.00
Amount of deficit or surplus financing: −$117.10 Amount of deficit or surplus financing: −$117.10 Amount of deficit or surplus financing: −$183.10 Amount of deficit or surplus financing: $68.80 Amount of deficit or surplus financing: $2.80
If deficit in financing (negative), draw on line of credit Line of credit $117.10 If deficit in financing (negative), draw on line of credit Line of credit $117.10 If deficit in financing (negative), draw on line of credit Line of credit $183.10 If deficit in financing (negative), draw on line of credit Line of credit $0.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 $0.00 If surplus in financing (positive), pay special dividend Special dividend $0.00 If surplus in financing (positive), pay special dividend Special dividend $0.00 If surplus in financing (positive), pay special dividend Special dividend $68.80 If surplus in financing (positive), pay special dividend Special dividend $2.80
Projecting Financial Statements for Five Years
The following tables are shown only here and not in the textbook. They provide an example of how to apply the concepts used in the 1-year projection to now project 5 years of statements.
Projected Financial Statements (Millions of Dollars) Fixed values for each scenario are shown to the right. Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below Fixed Values for the Scenario Shown Below
Status Quo Status Quo Higher Growth Only Improve Operations Only Operations and Growth
1. Balance Sheets Actual Forecast 1. Balance Sheets Actual Forecast 1. Balance Sheets Actual Forecast 1. Balance Sheets Actual Forecast 1. Balance Sheets Actual Forecast
2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021
Assets Assets Assets Assets Assets
Cash $50.0 $55.0 $59.4 $63.6 $66.7 $70.1 Cash $50.0 $55.0 $59.4 $63.6 $66.7 $70.1 Cash $50.0 $55.0 $60.0 $64.7 $68.6 $72.7 Cash $50.0 $55.0 $59.4 $63.6 $66.7 $70.1 Cash $50.0 $55.0 $60.0 $64.7 $68.6 $72.7
Accounts receivable 500.0 550.0 594.0 635.6 667.4 700.7 Accounts receivable 500.0 550.0 594.0 635.6 667.4 700.7 Accounts receivable 500.0 550.0 599.5 647.5 686.3 727.5 Accounts receivable 500.0 550.0 594.0 635.6 667.4 700.7 Accounts receivable 500.0 550.0 599.5 647.5 686.3 727.5
Inventories 1,000.0 1,100.0 1,188.0 1,271.2 1,334.7 1,401.5 Inventories 1,000.0 1,100.0 1,188.0 1,271.2 1,334.7 1,401.5 Inventories 1,000.0 1,100.0 1,199.0 1,294.9 1,372.6 1,455.0 Inventories 1,000.0 990.0 950.4 1,016.9 1,067.8 1,121.2 Inventories 1,000.0 990.0 959.2 1,035.9 1,098.1 1,164.0
Total current assets $1,550.0 $1,705.0 $1,841.4 $1,970.3 $2,068.8 $2,172.3 Total current assets $1,550.0 $1,705.0 $1,841.4 $1,970.3 $2,068.8 $2,172.3 Total current assets $1,550.0 $1,705.0 $1,858.5 $2,007.1 $2,127.6 $2,255.2 Total current assets $1,550.0 $1,595.0 $1,603.8 $1,716.1 $1,801.9 $1,892.0 Total current assets $1,550.0 $1,595.0 $1,618.7 $1,748.1 $1,853.0 $1,964.2
Net PP&E 2,000.0 2,200.0 2,376.0 2,542.3 2,669.4 2,802.9 Net PP&E 2,000.0 2,200.0 2,376.0 2,542.3 2,669.4 2,802.9 Net PP&E 2,000.0 2,200.0 2,398.0 2,589.8 2,745.2 2,909.9 Net PP&E 2,000.0 2,035.0 2,079.0 2,224.5 2,335.8 2,452.5 Net PP&E 2,000.0 2,035.0 2,098.3 2,266.1 2,402.1 2,546.2
Total assets (TA) $3,550.0 $3,905.0 $4,217.4 $4,512.6 $4,738.2 $4,975.2 Total assets (TA) $3,550.0 $3,905.0 $4,217.4 $4,512.6 $4,738.2 $4,975.2 Total assets (TA) $3,550.0 $3,905.0 $4,256.5 $4,597.0 $4,872.8 $5,165.2 Total assets (TA) $3,550.0 $3,630.0 $3,682.8 $3,940.6 $4,137.6 $4,344.5 Total assets (TA) $3,550.0 $3,630.0 $3,716.9 $4,014.3 $4,255.1 $4,510.4
Liabilities and equity Liabilities and equity Liabilities and equity Liabilities and equity Liabilities and equity
Accounts payable $200.0 $220.0 $237.6 $254.2 $266.9 $280.3 Accounts payable $200.0 $220.0 $237.6 $254.2 $266.9 $280.3 Accounts payable $200.0 $220.0 $239.8 $259.0 $274.5 $291.0 Accounts payable $200.0 $220.0 $237.6 $254.2 $266.9 $280.3 Accounts payable $200.0 $220.0 $239.8 $259.0 $274.5 $291.0
Accruals 300.0 330.0 356.4 381.3 400.4 420.4 Accruals 300.0 330.0 356.4 381.3 400.4 420.4 Accruals 300.0 330.0 359.7 388.5 411.8 436.5 Accruals 300.0 330.0 356.4 381.3 400.4 420.4 Accruals 300.0 330.0 359.7 388.5 411.8 436.5
Notes payable 280.0 280.0 280.0 280.0 280.0 280.0 Notes payable 280.0 280.0 280.0 280.0 280.0 280.0 Notes payable 280.0 280.0 280.0 280.0 280.0 280.0 Notes payable 280.0 280.0 280.0 280.0 280.0 280.0 Notes payable 280.0 280.0 280.0 280.0 280.0 280.0
Line of credit 0.0 117.1 181.9 214.2 172.8 121.3 Line of credit 0.0 117.1 181.9 214.2 172.8 121.3 Line of credit 0.0 183.1 282.7 353.8 353.7 346.3 Line of credit 0.0 0.0 0.0 0.0 0.0 0.0 Line of credit 0.0 0.0 0.0 0.0 0.0 0.0
Total CL $780.0 $947.1 $1,055.9 $1,129.8 $1,120.2 $1,102.0 Total CL $780.0 $947.1 $1,055.9 $1,129.8 $1,120.2 $1,102.0 Total CL $780.0 $1,013.1 $1,162.2 $1,281.2 $1,320.1 $1,353.8 Total CL $780.0 $830.0 $874.0 $915.6 $947.4 $980.7 Total CL $780.0 $830.0 $879.5 $927.5 $966.3 $1,007.5
Long-term bonds 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 Long-term bonds 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 Long-term bonds 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 Long-term bonds 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 Long-term bonds 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0 1,200.0
Total liabilities $1,980.0 $2,147.1 $2,255.9 $2,329.8 $2,320.2 $2,302.0 Total liabilities $1,980.0 $2,147.1 $2,255.9 $2,329.8 $2,320.2 $2,302.0 Total liabilities $1,980.0 $2,213.1 $2,362.2 $2,481.2 $2,520.1 $2,553.8 Total liabilities $1,980.0 $2,030.0 $2,074.0 $2,115.6 $2,147.4 $2,180.7 Total liabilities $1,980.0 $2,030.0 $2,079.5 $2,127.5 $2,166.3 $2,207.5
Preferred stock $100.0 $100.0 $100.0 $100.0 $100.0 $100.0 Preferred stock $100.0 $100.0 $100.0 $100.0 $100.0 $100.0 Preferred stock $100.0 $100.0 $100.0 $100.0 $100.0 $100.0 Preferred stock $100.0 $100.0 $100.0 $100.0 $100.0 $100.0 Preferred stock $100.0 $100.0 $100.0 $100.0 $100.0 $100.0
Common stock 500.0 500.0 500.0 500.0 500.0 500.0 Common stock 500.0 500.0 500.0 500.0 500.0 500.0 Common stock 500.0 500.0 500.0 500.0 500.0 500.0 Common stock 500.0 500.0 500.0 500.0 500.0 500.0 Common stock 500.0 500.0 500.0 500.0 500.0 500.0
Retained earnings 970.0 1,157.9 1,361.5 1,582.8 1,818.1 2,073.2 Retained earnings 970.0 1,157.9 1,361.5 1,582.8 1,818.1 2,073.2 Retained earnings 970.0 1,091.9 1,294.2 1,515.7 1,752.7 2,011.4 Retained earnings 970.0 1,000.0 1,008.8 1,225.0 1,390.3 1,563.8 Retained earnings 970.0 1,000.0 1,037.4 1,286.8 1,488.8 1,702.9
Total common equity $1,470.0 $1,657.9 $1,861.5 $2,082.8 $2,318.1 $2,573.2 Total common equity $1,470.0 $1,657.9 $1,861.5 $2,082.8 $2,318.1 $2,573.2 Total common equity $1,470.0 $1,591.9 $1,794.2 $2,015.7 $2,252.7 $2,511.4 Total common equity $1,470.0 $1,500.0 $1,508.8 $1,725.0 $1,890.3 $2,063.8 Total common equity $1,470.0 $1,500.0 $1,537.4 $1,786.8 $1,988.8 $2,202.9
Total liabs. & equity $3,550.0 $3,905.0 $4,217.4 $4,512.6 $4,738.2 $4,975.2 Total liabs. & equity $3,550.0 $3,905.0 $4,217.4 $4,512.6 $4,738.2 $4,975.2 Total liabs. & equity $3,550.0 $3,905.0 $4,256.5 $4,597.0 $4,872.8 $5,165.2 Total liabs. & equity $3,550.0 $3,630.0 $3,682.8 $3,940.6 $4,137.6 $4,344.5 Total liabs. & equity $3,550.0 $3,630.0 $3,716.9 $4,014.3 $4,255.1 $4,510.4
Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00 $0.00 $0.00 Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00 $0.00 $0.00 Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00 $0.00 $0.00 Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00 $0.00 $0.00 Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00 $0.00 $0.00
2. Income Statement Actual Forecast 2. Income Statement Actual Forecast 2. Income Statement Actual Forecast 2. Income Statement Actual Forecast 2. Income Statement Actual Forecast
2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021 2016 2017 2018 2019 2020 2021
Net sales $5,000.0 $5,500.0 $5,940.0 $6,355.8 $6,673.6 $7,007.3 Net sales $5,000.0 $5,500.0 $5,940.0 $6,355.8 $6,673.6 $7,007.3 Net sales $5,000.0 $5,500.0 $5,995.0 $6,474.6 $6,863.1 $7,274.9 Net sales $5,000.0 $5,500.0 $5,940.0 $6,355.8 $6,673.6 $7,007.3 Net sales $5,000.0 $5,500.0 $5,995.0 $6,474.6 $6,863.1 $7,274.9
COGS (excl. depr.) 3,800.0 4,180.0 4,514.4 4,830.4 5,071.9 5,325.5 COGS (excl. depr.) 3,800.0 4,180.0 4,514.4 4,830.4 5,071.9 5,325.5 COGS (excl. depr.) 3,800.0 4,180.0 4,556.2 4,920.7 5,215.9 5,528.9 COGS (excl. depr.) 3,800.0 4,180.0 4,443.1 4,754.1 4,991.8 5,241.4 COGS (excl. depr.) 3,800.0 4,180.0 4,484.3 4,843.0 5,133.6 5,441.6
Depreciation 200.0 220.0 237.6 254.2 266.9 280.3 Depreciation 200.0 220.0 237.6 254.2 266.9 280.3 Depreciation 200.0 220.0 239.8 259.0 274.5 291.0 Depreciation 200.0 203.5 207.9 222.5 233.6 245.3 Depreciation 200.0 203.5 209.8 226.6 240.2 254.6
Other operating expenses 500.0 550.0 594.0 635.6 667.4 700.7 Other operating expenses 500.0 550.0 594.0 635.6 667.4 700.7 Other operating expenses 500.0 550.0 599.5 647.5 686.3 727.5 Other operating expenses 500.0 550.0 594.0 635.6 667.4 700.7 Other operating expenses 500.0 550.0 599.5 647.5 686.3 727.5
Cost of strategic initiatives $0.0 $0.0 $0.0 $0.0 $0.0 Cost of strategic initiatives $0.0 $0.0 $0.0 $0.0 $0.0 Cost of strategic initiatives $110.0 $0.0 $0.0 $0.0 $0.0 Cost of strategic initiatives $165.0 $0.0 $0.0 $0.0 $0.0 Cost of strategic initiatives $275.0 $0.0 $0.0 $0.0 $0.0
EBIT $500.0 $550.0 $594.0 $635.6 $667.4 $700.7 EBIT $500.0 $550.0 $594.0 $635.6 $667.4 $700.7 EBIT $500.0 $440.0 $599.5 $647.5 $686.3 $727.5 EBIT $500.0 $401.5 $695.0 $743.6 $780.8 $819.9 EBIT $500.0 $291.5 $701.4 $757.5 $803.0 $851.2
Less: Interest on notes 20.0 28.0 28.0 28.0 28.0 28.0 Less: Interest on notes 20.0 28.0 28.0 28.0 28.0 28.0 Less: Interest on notes 20.0 28.0 28.0 28.0 28.0 28.0 Less: Interest on notes 20.0 28.0 28.0 28.0 28.0 28.0 Less: Interest on notes 20.0 28.0 28.0 28.0 28.0 28.0
Interest on bonds 100.0 108.0 108.0 108.0 108.0 108.0 Interest on bonds 100.0 108.0 108.0 108.0 108.0 108.0 Interest on bonds 100.0 108.0 108.0 108.0 108.0 108.0 Interest on bonds 100.0 108.0 108.0 108.0 108.0 108.0 Interest on bonds 100.0 108.0 108.0 108.0 108.0 108.0
Interest on LOC 0.0 0.0 13.5 20.9 24.6 19.9 Note: See comment.
Mike Ehrhardt: 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. 		Interest on LOC 0.0 0.0 13.5 20.9 24.6 19.9 Interest on LOC 0.0 0.0 21.1 32.5 40.7 40.7 Interest on LOC 0.0 0.0 0.0 0.0 0.0 0.0 Interest on LOC 0.0 0.0 0.0 0.0 0.0 0.0
Pre-tax earnings $380.0 $414.0 $444.5 $478.7 $506.7 $544.9 Pre-tax earnings $380.0 $414.0 $444.5 $478.7 $506.7 $544.9 Pre-tax earnings $380.0 $304.0 $442.4 $478.9 $509.6 $550.8 Pre-tax earnings $380.0 $265.5 $559.0 $607.6 $644.8 $683.9 Pre-tax earnings $380.0 $155.5 $565.4 $621.5 $667.0 $715.2
Taxes (40%) 152.0 165.6 177.8 191.5 202.7 217.9 Taxes (40%) 152.0 165.6 177.8 191.5 202.7 217.9 Taxes (40%) 152.0 121.6 177.0 191.6 203.8 220.3 Taxes (40%) 152.0 106.2 223.6 243.1 257.9 273.5 Taxes (40%) 152.0 62.2 226.2 248.6 266.8 286.1
NI before pref. div. $228.0 $248.4 $266.7 $287.2 $304.0 $326.9 NI before pref. div. $228.0 $248.4 $266.7 $287.2 $304.0 $326.9 NI before pref. div. $228.0 $182.4 $265.5 $287.4 $305.8 $330.5 NI before pref. div. $228.0 $159.3 $335.4 $364.6 $386.9 $410.3 NI before pref. div. $228.0 $93.3 $339.2 $372.9 $400.2 $429.1
Preferred div. 8.0 8.0 8.0 8.0 8.0 8.0 Preferred div. 8.0 8.0 8.0 8.0 8.0 8.0 Preferred div. 8.0 8.0 8.0 8.0 8.0 8.0 Preferred div. 8.0 8.0 8.0 8.0 8.0 8.0 Preferred div. 8.0 8.0 8.0 8.0 8.0 8.0
Net income $220.0 $240.4 $258.7 $279.2 $296.0 $318.9 Net income $220.0 $240.4 $258.7 $279.2 $296.0 $318.9 Net income $220.0 $174.4 $257.5 $279.4 $297.8 $322.5 Net income $220.0 $151.3 $327.4 $356.6 $378.9 $402.3 Net income $220.0 $85.3 $331.2 $364.9 $392.2 $421.1
Regular common dividends $50.0 $52.5 $55.1 $57.9 $60.8 $63.8 Regular common dividends $50.0 $52.5 $55.1 $57.9 $60.8 $63.8 Regular common dividends $50.0 $52.5 $55.1 $57.9 $60.8 $63.8 Regular common dividends $50.0 $52.5 $55.1 $57.9 $60.8 $63.8 Regular common dividends $50.0 $52.5 $55.1 $57.9 $60.8 $63.8
Special dividends $0.0 $0.0 $0.0 $0.0 $0.0 $0.0 Special dividends $0.0 $0.0 $0.0 $0.0 $0.0 $0.0 Special dividends $0.0 $0.0 $0.0 $0.0 $0.0 $0.0 Special dividends $0.0 $68.8 $263.5 $82.5 $152.9 $165.0 Special dividends $0.0 $2.8 $238.7 $57.6 $129.4 $143.2
Addition to RE $170.0 $187.9 $203.6 $221.3 $235.3 $255.1 Addition to RE $170.0 $187.9 $203.6 $221.3 $235.3 $255.1 Addition to RE $170.0 $121.9 $202.3 $221.5 $237.0 $258.7 Addition to RE $170.0 $30.0 $8.8 $216.2 $165.3 $173.5 Addition to RE $170.0 $30.0 $37.4 $249.4 $202.0 $214.1
3. Incorporating the Financial Deficit or Surplus 3. Incorporating the Financial Deficit or Surplus 3. Incorporating the Financial Deficit or Surplus 3. Incorporating the Financial Deficit or Surplus 3. Incorporating the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals) $50.0 $44.0 $41.6 $31.8 $33.4 Increase in spontaneous liabilities (accounts payable and accruals) $50.0 $44.0 $41.6 $31.8 $33.4 Increase in spontaneous liabilities (accounts payable and accruals) $50.0 $49.5 $48.0 $38.8 $41.2 Increase in spontaneous liabilities (accounts payable and accruals) $50.0 $44.0 $41.6 $31.8 $33.4 Increase in spontaneous liabilities (accounts payable and accruals) $50.0 $49.5 $48.0 $38.8 $41.2
+ Increase in notes payable, long-term bonds, preferred stock and common stock $0.0 $0.0 $0.0 $0.0 $0.0 + Increase in notes payable, long-term bonds, preferred stock and common stock $0.0 $0.0 $0.0 $0.0 $0.0 + Increase in notes payable, long-term bonds, preferred stock and common stock $0.0 $0.0 $0.0 $0.0 $0.0 + Increase in notes payable, long-term bonds, preferred stock and common stock $0.0 $0.0 $0.0 $0.0 $0.0 + Increase in notes payable, long-term bonds, preferred stock and common stock $0.0 $0.0 $0.0 $0.0 $0.0
+ Net income minus regular common dividends $187.9 $203.6 $221.3 $235.3 $255.1 Note: See comment.
Mike Ehrhardt: This is the planned increase in the retained earnings account.
Mike Ehrhardt: 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.		 + Net income minus regular common dividends $187.9 $203.6 $221.3 $235.3 $255.1 + Net income minus regular common dividends $121.9 $202.3 $221.5 $237.0 $258.7 + Net income minus regular common dividends $98.8 $272.3 $298.7 $318.1 $338.5 + Net income minus regular common dividends $32.8 $276.1 $307.0 $331.4 $357.3
− Previous line of credit $0.0 $117.1 $181.9 $214.2 $172.8 Note: See comment.
Mike Ehrhardt: 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.
Mike Ehrhardt: 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.
Mike Ehrhardt: 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. 		− Previous line of credit $0.0 $117.1 $181.9 $214.2 $172.8 − Previous line of credit $0.0 $183.1 $282.7 $353.8 $353.7 − Previous line of credit $0.0 $0.0 $0.0 $0.0 $0.0 − Previous line of credit $0.0 $0.0 $0.0 $0.0 $0.0
Increase in financing $237.9 $130.5 $81.0 $52.8 $115.6 Increase in financing $237.9 $130.5 $81.0 $52.8 $115.6 Increase in financing $171.9 $68.7 −$13.3 −$77.9 −$53.9 Increase in financing $148.8 $316.3 $340.3 $349.9 $371.9 Increase in financing $82.8 $325.6 $355.0 $370.3 $398.5
− Increase in total assets $355.0 $312.4 $295.2 $225.6 $236.9 − Increase in total assets $355.0 $312.4 $295.2 $225.6 $236.9 − Increase in total assets $355.0 $351.5 $340.5 $275.8 $292.4 − Increase in total assets $80.0 $52.8 $257.8 $197.0 $206.9 − Increase in total assets $80.0 $86.9 $297.4 $240.9 $255.3
Amount of deficit or surplus financing: −$117.1 −$181.9 −$214.2 −$172.8 −$121.3 Amount of deficit or surplus financing: −$117.1 −$181.9 −$214.2 −$172.8 −$121.3 Amount of deficit or surplus financing: −$183.1 −$282.7 −$353.8 −$353.7 −$346.3 Amount of deficit or surplus financing: $68.8 $263.5 $82.5 $152.9 $165.0 Amount of deficit or surplus financing: $2.8 $238.7 $57.6 $129.4 $143.2
Line of credit $117.1 $181.9 $214.2 $172.8 $121.3 Line of credit $117.1 $181.9 $214.2 $172.8 $121.3 Line of credit $183.1 $282.7 $353.8 $353.7 $346.3 Line of credit $0.0 $0.0 $0.0 $0.0 $0.0 Line of credit $0.0 $0.0 $0.0 $0.0 $0.0
Special dividend $0.0 $0.0 $0.0 $0.0 $0.0 Special dividend $0.0 $0.0 $0.0 $0.0 $0.0 Special dividend $0.0 $0.0 $0.0 $0.0 $0.0 Special dividend $68.8 $263.5 $82.5 $152.9 $165.0 Special dividend $2.8 $238.7 $57.6 $129.4 $143.2
Statement of Cash Flows (Millions of Dollars)
Status Quo Actual Forecast Forecast Forecast Forecast Forecast
2016 2017 2018 2019 2020 2021
Operating Activities
Net Income before preferred dividends $228.0 $248.4 $266.7 $287.2 $304.0 $326.9
Noncash adjustments
Depreciation $200.0 $220.0 $237.6 $254.2 $266.9 $280.3
Working capital adjustments
Increase(-)/Decrease(+) in accounts receivable ($120.0) ($50.0) ($44.0) ($41.6) ($31.8) ($33.4)
Increase(-)/Decrease(+) in inventories ($180.0) ($100.0) ($88.0) ($83.2) ($63.6) ($66.7)
Increase(-)/Decrease(+) in payables $10.0 $20.0 $17.6 $16.6 $12.7 $13.3
Increase(-)/Decrease(+) in accruals $20.0 $30.0 $26.4 $24.9 $19.1 $20.0
Net cash provided (used) by operating activities $158.0 $368.4 $416.3 $458.3 $507.4 $540.5
Investing Activities
Cash used to acquire fixed assets ($500.0) ($420.0) ($413.6) ($420.6) ($394.1) ($413.8)
Sale of short-term investments $40.0 $0.0 $0.0 $0.0 $0.0 $0.0
Net cash provided (used) by investing activities ($460.0) ($420.0) ($413.6) ($420.6) ($394.1) ($413.8)
Financing Activities
Increase(+)/Decrease(-) in notes payable $150.0 $0.0 $0.0 $0.0 $0.0 $0.0
Increase(+)/Decrease(-) in line of credit $0.0 $117.1 $64.8 $32.3 ($41.4) ($51.6)
Increase(+)/Decrease(-) in bonds $200.0 $0.0 $0.0 $0.0 $0.0 $0.0
Preferred stock issue(+)/repurchase(-) $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
Payment of common and preferred dividends ($58.0) ($60.5) ($63.1) ($65.9) ($68.8) ($71.8)
Common stock issue(+)/repurchase(-) $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
Net cash provided by financing activities $292.0 $56.6 $1.7 ($33.6) ($110.2) ($123.4)
Summary
Net change in cash and equivalents ($10.0) $5.0 $4.4 $4.2 $3.2 $3.3
Cash and securities at beginning of the year $60.0 $50.0 $55.0 $59.4 $63.6 $66.7
Cash and securities at end of the year $50.0 $55.0 $59.4 $63.6 $66.7 $70.1
12-6 Analysis and Selection of a Strategic Plan
The figure below shows key inputs and outcomes for the financial plan for each of the the four scenarios: Status Quo, Higher Sales Growth (Only), Improve Operations (Only), and Growth and Operations. The values for each scenaro can be generated using Scenario Manager's Summary feature: Data, What-If Analysis, Scenario Manager, Summary. Note: we generated the values in the Figure by manually linking them to the fixed value output for the scenarios' 5-year projections shown above and to the right so that the reported values in the figure below would not change if you decide to try the Scenario Summary feature yourself, which we encourgage.
Figure 12-6
Key Inputs and Financial Plan Outcomes for Possible Strategic Initiatives (Millions, Except for Per Share Data)
Scenario
(1) Status Quo (2) Higher Sales Growth (Only) (3) Improve Operations (Only) (4) Operations and Growth
Panel A: Key Inputs
Sales growth (Year 1) 10.0% 10.0% 10.0% 10.0%
Sales growth Year 2) 9.0% 9.0% 9.0% 9.0%
Sales growth (Year 3) 8.0% 8.0% 8.0% 8.0%
Long-term sales growth (gL) 5.0% 6.0% 5.0% 6.0%
(COGS excl. depr.)/Sales (Year 1) 76.0% 76.0% 76.0% 76.0%
(COGS excl. depr.)/Sales (Year 2) 76.0% 76.0% 74.8% 74.8%
Inventory/Sales for (Year 1) 20.0% 20.0% 18.0% 18.0%
Inventory/Sales for (Year 2) 20.0% 20.0% 16.0% 16.0%
(Net PP&E)/Sales (Year 1) 40.0% 40.0% 37.0% 37.0%
(Net PP&E)/Sales (Year 2) 40.0% 40.0% 35.0% 35.0%
Cost of strategic initiatives $0 $0 $165 $275
Weighted average cost of capital (WACC) 10.97% 10.97% 10.97% 10.97%
Panel B: Key Financial Plan Results
Line of credit (Year 1) $117.1 $183.1 $0.0 $0.0
Line of credit (5-Year average) $161.5 $303.9 $0.0 $0.0
Regular dividends (Year 1) $52.5 $52.5 $52.5 $52.5
Regular dividends (5-Year average) $58.0 $58.0 $58.0 $58.0
Special dividends (Year 1) $0.0 $0.0 $68.8 $2.8
Special dividends (5-Year average) $0.0 $0.0 $146.5 $114.3
Value of operations (Year 0) $2,719 $2,654 $4,407 $4,712
Intrinsic stock price (Year 0) $22.79 $21.48 $56.54 $62.63
The Figure shown below is "live" and shows the current values of the scenario that is chosen. The Figure to its right is fixed for the Operations and Growth scenario, which is shown in the textbook.
Note: Do not change inputs here! The "input" cells here are linked to the ones in Figure 12-2. If you want to change inputs, do so in Figure 12-2 or use Scenario Manager.
Figure 12-7 Fixed values for the Operations and Growth scenario are shown to the right. Figure 12-7
Operating Results and Financial Outcomes for the Selected Scenario (Millions Except Percentages and Per Share Data) Operating Results and Financial Outcomes for the Operations an Growth Plan (Millions Except Percentages and Per Share Data)
Status Quo Industry MicroDrive Operations and Growth Industry MicroDrive
Actual Actual Forecast Actual Actual Forecast
Panel A: Inputs 2016 2016 2017 2018 2019 2020 2021 Panel A: Inputs 2016 2016 2017 2018 2019 2020 2021
Sales growth rate 5% 5% 10% 8% 7% 5% 5% Sales growth rate 5% 5% 10% 9% 8% 6% 6%
(COGS excl. depr.)/Sales 76% 76% 76% 76% 76% 76% 76% (COGS excl. depr.)/Sales 76% 76% 76% 75% 75% 75% 75%
Inventory/Sales 15% 20% 20% 20% 20% 20% 20% Inventory/Sales 15% 20% 18% 16% 16% 16% 16%
(Net PP&E)/Sales 33% 40% 40% 40% 40% 40% 40% (Net PP&E)/Sales 33% 40% 37% 35% 35% 35% 35%
Cost of strategic initiatives NA NA $0 $0 $0 $0 $0 Cost of strategic initiatives NA NA $275 $0 $0 $0 $0
Industry MicroDrive Industry MicroDrive
Panel B: Key Output Actual Actual Forecast Panel B: Key Financial Results Actual Actual Forecast
2016 2016 2017 2018 2019 2020 2021 2016 2016 2017 2018 2019 2020 2021
Return on invested capital 15.0% 9.84% 9.84% 9.84% 9.84% 9.84% 9.84% Return on invested capital 15.0% 9.84% 5.68% 13.50% 13.50% 13.50% 13.50%
Free cash flow NA −$260 $25 $88 $128 $207 $217 Free cash flow NA −$260 $145 $383 $205 $280 $297
Line of credit NA $0 $117 $182 $214 $173 $121 Line of credit NA $0 $0 $0 $0 $0 $0
Special dividends NA $0 $0 $0 $0 $0 $0 Special dividends NA $0 $3 $239 $58 $129 $143
(Total debt)/TA 25.0% 41.7% 40.9% 39.4% 37.5% 34.9% 32.2% (Total debt)/TA 25.0% 41.7% 40.8% 39.8% 36.9% 34.8% 32.8%
Times interest earned 10.0 4.2 4.0 4.0 4.1 4.2 4.5 Times interest earned 10.0 4.2 2.1 5.2 5.6 5.9 6.3
Return on assets (ROA) 11.0% 6.2% 6.2% 6.1% 6.2% 6.2% 6.4% Return on assets (ROA) 11.0% 6.2% 2.3% 8.9% 9.1% 9.2% 9.3%
Return on equity (ROE) 19.0% 15.0% 14.5% 13.9% 13.4% 12.8% 12.4% Return on equity (ROE) 19.0% 15.0% 5.7% 21.5% 20.4% 19.7% 19.1%
Earnings per share NA $4.40 $4.81 $5.17 $5.58 $5.92 $6.38 Earnings per share NA $4.40 $1.71 $6.62 $7.30 $7.84 $8.42
Payout ratio 35.0% 22.7% 21.8% 21.3% 20.7% 20.5% 20.0% Payout ratio 35.0% 22.7% 64.8% 88.7% 31.7% 48.5% 49.1%
Regular dividends per share NA $1.00 $1.05 $1.10 $1.16 $1.22 $1.28 Regular dividends per share NA $1.00 $1.11 $5.88 $2.31 $3.80 $4.14
Special dividends per share NA $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Special dividends per share NA $0.00 $0.06 $4.77 $1.15 $2.59 $2.86
Panel C: Valuation Panel C: Valuation
Weighted average cost of capital = 10.97% Weighted average cost of capital = 10.97%
12/31/2016 Estimated value of operations = $2,719 12/31/2016 Estimated value of operations = $4,712
12/31/2016 Estimated intrinsic stock price = $22.79 12/31/2016 Estimated intrinsic stock price = $62.63
12-7 The CFO’s Model
The CFO’s final model, shown in the worksheet named CFO Model, has several refinements to the basic model presented in the previous sections, including the incorporation of financing feedback and implementation of the target capital structure.
12-8 Additional Funds Needed (AFN) Equation Method
The AFN model forecasts MicroDrive's need for external funds to support its forecasted next year's sales.
Figure 12-8
Additional Funds Needed (AFN) (Millions of Dollars)
Panel A. Inputs and Definitions
S0: Most recent year's sales = $5,000
g: Forecasted growth rate in sales = 10.00%
S1: Next year's sales: S0 × (1 + g) = $5,500
gS0: Forecasted change in sales = S1 – S0 = ΔS = $500
A0*: Most recent year's operating assets = $3,550
A0*/S0: Required assets per dollar of sales = 71.00%
L0*: Most recent year's spontaneous liabilities (payables + accruals) = $500
L0*/S0: Spontaneous liabilities per dollar of sales = 10.00%
Profit margin (M): Most recent profit margin = NI/sales = 4.40%
Payout ratio (POR): Most recent year's dividends/NI = % of income paid out = 22.73%
Panel B. Additional Funds Needed (AFN) to Support Growth
Additional      funds = needed         Required increase in assets − Increase in spontaneous liabilities − Increase in retained earnings
AFN = (A0*/S0)∆S − (L0*/S0)∆S − S1 × M × (1 – POR)
= (A0*/S0)(gS0) − (L0*/S0)(gS0) − (1+g)S0 × M × (1 – POR)
= (0.710)($500) − (0.10)($500) − $5,500(0.044)(1 – 0.2273)
= $355 − $50.00 − $187.00
AFN = $118.00
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 sustainable growth rate can also be found by solving the equation as shown on the 3rd row above g, then finding the value of g that causes AFN to equal zero.
Sustainable g = PM(1 – POR)(S0) = $170.00 = 5.90%
A0* – L0* – PM(1 – POR)S0 $2,880.00
2. Using Goal Seek. The sustainable growth rate can also be found by using Goal Seek. In the figure above, set the AFN in the orange cell to zero by changing the growth rate in the blue cell.
12-8 Forecasting When the Ratios Change
Excess Capacity Adjustments
We assumed that all operating assets grow at the same rate of sales, but this is not necessarily correct. For instance, suppose the firm is using its fixed assets at only partial capacity. This means that it could achieve a greater level of production with its fixed assets. Here are the steps to determine the AFN if there is excess capacity. 1. Calculate the AFN ignoring the excess capacity. 2. Calculate the required new fixed assets ignoring the excess capacity. 3. Calculate the firm's full capacity sales. 4. Calculate a revised target fixed assets-to-sales ratio. 5. Calculate the required fixed assets given the excess capacity. 6. Calculate the increase in required fixed assets given excess capacity. 7. Calculate the reduction in required fixed assets from the result when excess capacity is ignored versus the required fixed assets when excess capacity is considered. 8. Subtract this difference in required fixed assets from the previously calculated AFN.
Inputs
2016 Percent utilization of fixed assets capacity = 96%
2016 Sales = $5,000
2016 Fixed assets = $2,000
2016 Fixed assets/Sales= 40.00%
2017 Sales = $5,500
Required increase in FA if no excess capacity = (2016 FA/Sales) (Change in Sales)
Required increase in FA if no excess capacity = $200
Full capacity sales = Actual sales/capacity utilization
Full capacity sales = $5,208
Actual fixed assets/Full capacity sales
Target fixed assets/Sales = 38.40%
Required fixed assets = (Target FA/Sales) (Forecast sales)
Required fixed assets = $2,112
Required increase in fixed assets = $112
Difference between required increase assuming no excess capacity and required increase if there is excess capacity =
$88
AFN if no excess capacity = $118
AFN if there is excess capacity = $30

12-8

SECTION 12-8
SOLUTIONS TO SELF-TEST
Suppose MicroDrive's growth rate in sales is forecast as 15% rather than 10%. If all ratios stay the same, what is the AFN?
Sales growth rate 15%
S0 $3,000 million
A0*/ S0 66.666%
L0*/ S0 6.667%
Profit margin (M) 3.783%
Payout ratio 50.670%
Δ Sales $450.00 million
S1 $3,450.00 million
AFN $205.62 million

CFO model

10/27/15
Financing Feedback and Specifying the Capital Structure
12-7 The CFO’s Model
The CFO's model incorporates financing feedback caused by the new interest incurred by new debt. The model also ensures that the actual capital structure will match the target capital structure.
For the user's convenience, we repeat the basic information for MicroDrive.
The following data are linked to the Chapter worksheet--do not change here! To change a scenario, go to the worksheet named "Chapter" and choose a scenario using Scenario Manager.
Figure 12-1. Repeated for convenience.
MicroDrive’s Most Recent Financial Statements (Millions, Except for Per Share Data)
INCOME STATEMENTS BALANCE SHEETS
2015 2016 Assets 2015 2016
Net sales $ 4,760 $ 5,000 Cash $ 60 $ 50
COGS (excl. depr.) 3,560 3,800 ST Investments 40 -
Depreciation 170 200 Accounts receivable 380 500
Other operating expenses 480 500 Inventories 820 1,000
EBIT $ 550 $ 500 Total CA $ 1,300 $ 1,550
Interest expense 100 120 Net PP&E 1,700 2,000
Pretax earnings $ 450 $ 380 Total assets $ 3,000 $ 3,550
Taxes (40%) 180 152
NI before pref. div. $ 270 $ 228 Liabilities and equity
Preferred div. 8 8 Accounts payable $ 190 $ 200
Net income $ 262 $ 220 Accruals 280 300
Notes payable 130 280
Other Data Total CL $ 600 $ 780
Common dividends $48 $50 Long-term bonds 1,000 1,200
Addition to RE $214 $170 Total liabilities $ 1,600 $ 1,980
Tax rate 40% 40% Preferred stock 100 100
Shares of common stock 50 50 Common stock 500 500
Earnings per share $5.24 $4.40 Retained earnings 800 970
Dividends per share $0.96 $1.00 Total common equity $ 1,300 $ 1,470
Price per share $40.00 $27.00 Total liabs. & equity $ 3,000 $ 3,550
The figure below shows all the inputs required to project the financial statements for the scenario that has been selected in the worksheet "Chapter" with the Scenario Manager: Data, What-If Analysis, Scenario Manager. There are two scenarios. The first is named Status Quo because all operating ratios except the sales growth rate are assumed to remain unchanged. The initial sales growth rate was chosen by MicroDrive's managers based on the existing product lines. The growth rate declines over time until it eventually levels off at a sustainable rate. The other scenario is named Final because it is the set of inputs chosen by MicroDrive's management team.
Section 1 shows the inputs required to estimate the items in an operating plan. For each of these inputs, Section 1 shows the industry averages, the actual values for the past two years for MicroDrive, and the forecasted values for the next five years. The managers assumed the inputs for future years (except the sales growth rate) would be equal to the inputs in the first projected year.
MicroDrive's managers assume that sales will eventually level off at a sustaniable constant rate.
Sections 2 and 3 show the data required to estimate the weighted average cost of capital. Section 4 shows the forecasted growth rate in dividends.
The following data are linked to the Chapter worksheet--do not change here! To change a scenario, go to the worksheet named "Chapter" and choose a scenario using Scenario Manager.
Figure 12-2. Repeated here for convenience.
MicroDrive's Forecast: Inputs for the Selected Scenario
Status Quo Industry MicroDrive MicroDrive
Inputs Actual Actual Forecast
1. Operations 2016 2015 2016 2017 2018 2019 2020 2021
Sales growth rate 5% 15% 5% 10% 8% 7% 5% 5%
(COGS excl. depr.)/Sales 76% 75% 76% 76% 76% 76% 76% 76%
Depreciation/(Net PP&E) 9% 10% 10% 10% 10% 10% 10% 10%
(Other op. exp.)/Sales 10% 10% 10% 10% 10% 10% 10% 10%
Cash/Sales 1% 1% 1% 1% 1% 1% 1% 1% Actual Historical Financing
(Acc. rec.)/Sales 8% 8% 10% 10% 10% 10% 10% 10% 2015 2016
Inventory/Sales 15% 17% 20% 20% 20% 20% 20% 20% Long-term debt $1,000 $1,200
(Net PP&E)/Sales 33% 36% 40% 40% 40% 40% 40% 40% Short-term debt $130 $280
(Acc. pay.)/Sales 4% 4% 4% 4% 4% 4% 4% 4% Preferred stock $100 $100
Accruals/Sales 7% 6% 6% 6% 6% 6% 6% 6% Market value of equity = (Price x # shares) $2,000 $1,350
Tax rate 40% 40% 40% 40% 40% 40% 40% 40% Total $3,230 $2,930
Cost of strategic initiatives NA NA NA $0 $0 $0 $0 $0
2. Capital Structure Actual Market Weights Target Market Weights
% Long-term debt 22% 31% 41% 28% 28% 28% 28% 28% See the box to the right for calculations of the actual capital structures, based on market values, for the past two years. Percent long-term debt 31% 41%
% Short-term debt 3% 4% 10% 2% 2% 2% 2% 2% Percent short-term debt 4% 10%
% Preferred stock 0% 3% 3% 3% 3% 3% 3% 3% Percent preferred stock 3% 3%
% Common stock 75% 62% 46% 67% 67% 67% 67% 67% Percent market value of equity 62% 46%
3. Costs of Capital Forecast Total 100% 100%
Rate on LT bonds, rLTD 9.0% 9% 9% 9% 9%
Rate on ST debt, rSTD 10.0% 10% 10% 10% 10%
Rate on preferred stock (ignoring flotation costs), rps 8.0% 8% 8% 8% 8%
Cost of equity, rs 13.58% 14% 14% 14% 14%
4. Target Dividend Policy Actual
Growth rate of dividends 11% 4.2% 5% 5% 5% 5% 5%
5. Capital Structure Choices
% Long-term debt, wLTD 41.0% 38.4% 35.8% 33.2% 30.6% 28%
% Short-term debt, wSTD 9.6% 8.0% 6.5% 5.0% 3.5% 2%
% Preferred stock, wps 3.4% 3.3% 3.2% 3.2% 3.1% 3%
% Common stock, ws 46.1% 50.3% 54.4% 58.6% 62.8% 67%
The following projections incorporate the impact of financing feedback. They also ensure that the actual capital structure matches the target capital structure. Following are explanations of these two issues, beginning with the capital structure.
Implementing the Target Capital Structure
The preliminary financial policy held external financing constant—with no additional borrowing or repayment of debt (other than the line of credit) and no new issues or repurchases of preferred stock or common stock. However, this ignores the target capital structure. Fortunately, there is a simple way to implement the target capital structure in the projected statements. Notice that the WACC depends on the target weights, not the actual weights. This means the value of operations does not depend on the actual amounts of debt and preferred stock. Therefore, it is easy to estimate the value of operations for each year of the forecast (starting at the horizon and working backward) before specifying the dollar amounts of debt and preferred stock. Given the yearly value of operations, the yearly values of debt and preferred stock can be found by multiplying their target weights by the value of operations.
We implement this approach in the figure below.
Incorporating Financing Feedback
The basic model assumed that no interest would accrue on the line of credit because the LOC would be added at the end of the year. However, if interest is calculated on the LOC’s average balance during the year, which is more realistic, here is what happens:
1. The line of credit required to make the balance sheets balance is added to the balance sheet.
2. Interest expense increases due to the LOC.
3. Net income decreases because interest expenses are higher.
4. Internally generated financing decreases because net income decreases.
5. The financing deficit increases because internally generated financing decreases.
6. An additional amount of the LOC is added to the balance sheets to make them balance.
7. Go to step 2 and repeat the loop.
If you were to go through these steps manually, then each time you add some additional LOC in Step 6, the amount would be less than the previous amount because the additional LOC is just large enough to cover the additional interest estimated in Step 2. If you repeated this process manually enough times, then the change in the additional LOC would become so small that it would be neglible. In fact, sometimes it is possible to set Excel to Iterate automatically and determine the correct amount of debt. However, in complicated models it is possible for this automatic iteration to cause Excel to "freeze." Fortunately, there is a simple solution.
As noted above, the additional LOC required by each additional iteration becomes smaller and smaller. In fact, the additional LOC eventually converges to zero. Because the LOC converges to a value, it is possible to use a relatively simple formula to calculate the final LOC needed when there is financing feeback. This formula is based on the amount of LOC needed if feedback is ignored and on the interest rates (and preferred dividend yield). We explain this formula below at the point where we specify the final LOC.
The silver rows in the tables indicate the rows that differ from those in the basic model in the worksheet named "Chapter".
Projected Financial Statements (Millions of Dollars)
Status Quo
1. Balance Sheets Actual Forecast
2016 2017 2018 2019 2020 2021
Assets
Cash $50.0 $55.0 $59.4 $63.6 $66.7 $70.1
Accounts receivable 500.0 550.0 594.0 635.6 667.4 700.7
Inventories 1,000.0 1,100.0 1,188.0 1,271.2 1,334.7 1,401.5
Total current assets $1,550.0 $1,705.0 $1,841.4 $1,970.3 $2,068.8 $2,172.3
Net PP&E 2,000.0 2,200.0 2,376.0 2,542.3 2,669.4 2,802.9
Total assets (TA) $3,550.0 $3,905.0 $4,217.4 $4,512.6 $4,738.2 $4,975.2
Liabilities and equity
Accounts payable $200.0 $220.0 $237.6 $254.2 $266.9 $280.3
Accruals 300.0 330.0 356.4 381.3 400.4 420.4
Notes payable (wSTD x Vop) 280.0 240.8 211.2 173.8 127.6 76.3
Line of credit (After adjustment for feedback effects) 0.0 213.3 297.8 374.9 415.8 459.0
Total CL $780.0 $1,004.0 $1,103.0 $1,184.3 $1,210.7 $1,236.0
Long-term bonds (wLTD x Vop) 1,200.0 1,148.2 1,156.6 1,148.1 1,111.4 1,068.1
Total liabilities $1,980.0 $2,152.2 $2,259.6 $2,332.4 $2,322.1 $2,304.1
Preferred stock (wps x Vop) $100.0 $99.7 $105.0 $109.5 $112.0 $114.4
Common stock 500.0 500.0 500.0 500.0 500.0 500.0
Retained earnings 970.0 1,153.1 1,352.8 1,570.7 1,804.2 2,056.6
Total common equity $1,470.0 $1,653.1 $1,852.8 $2,070.7 $2,304.2 $2,556.6
Total liabs. & equity $3,550.0 $3,905.0 $4,217.4 $4,512.6 $4,738.2 $4,975.2
Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00 $0.00 $0.00
2. Income Statement Actual Forecast
2016 2017 2018 2019 2020 2021
Net sales $5,000.0 $5,500.0 $5,940.0 $6,355.8 $6,673.6 $7,007.3
COGS (excl. depr.) 3,800.0 4,180.0 4,514.4 4,830.4 5,071.9 5,325.5
Depreciation 200.0 220.0 237.6 254.2 266.9 280.3
Other operating expenses 500.0 550.0 594.0 635.6 667.4 700.7
Cost of strategic initiatives 0.0 0.0 0.0 0.0 0.0
EBIT $500.0 $550.0 $594.0 $635.6 $667.4 $700.7
Less: Interest on notes payable, based on average NP and rSTD 20.0 26.0 22.6 19.3 15.1 10.2
Interest on bonds, based on average LT bonds and rLTD 100.0 105.7 103.7 103.7 101.7 98.1
Interest on LOC, based on average LOC and rLOC = rSTD +1.5% 0.0 12.3 29.4 38.7 45.5 50.3
Pre-tax earnings $380.0 $406.0 $438.3 $473.9 $505.1 $542.2
Taxes (40%) 152.0 162.4 175.3 189.6 202.1 216.9
NI before pref. div. $228.0 $243.6 $263.0 $284.4 $303.1 $325.3
Preferred dividend, based on average preferred stock and rps 8.0 8.0 8.2 8.6 8.9 9.1
Net income $220.0 $235.6 $254.8 $275.8 $294.2 $316.2
Regular common dividends $50.0 $52.5 $55.1 $57.9 $60.8 $63.8
Special dividends $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
Addition to RE $170.0 $183.1 $199.7 $217.9 $233.5 $252.4
3. Eliminating the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals) $50.0 $44.0 $41.6 $31.8 $33.4
+ Increase in notes payable, long-term bonds, preferred stock and common stock −$91.4 −$15.8 −$41.4 −$80.5 −$92.1
− Previous line of credit $0.0 $213.3 $297.8 $374.9 $415.8 Note: We subtract the previous LOC because the plan does not call for any projected LOC unless necessary.
+ Planned increase in retained earnings
+ After-tax operating income: EBIT (1-T) $330.0 $356.4 $381.3 $400.4 $420.4
− After-tax interest on notes payable (INTSTD x (1-T) $15.6 $13.6 $11.6 $9.0 $6.1
− After-tax interest on bonds (INTLTD x (1-T) $63.4 $62.2 $62.2 $61.0 $58.8
− After-tax interest on previous LOC: (rLOC x 0.5 x LOCt-1 x (1-T) $0.0 $7.4 $10.3 $12.9 $14.3 Note: 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.
− Preferred dividends $8.0 $8.2 $8.6 $8.9 $9.1
− Regular common dividends $52.5 $55.1 $57.9 $60.8 $63.8
Total planned increase in the retained earnings account $190.5 $209.9 $230.8 $247.8 $268.3
Increase in financing $149.1 $24.9 −$66.8 −$175.8 −$206.3 Note: The increase in financing is equal to the sum of spontaneous liabilities, planned external financing, and the planned addition to the retained earnings account.
− Increase in total assets $355.0 $312.4 $295.2 $225.6 $236.9
Amount of unadjusted deficit or surplus financing: −$205.9 −$287.5 −$362.0 −$401.5 −$443.2
If there is a surplus (the financing need is positive), pay a special dividend: $0.0 $0.0 $0.0 $0.0 $0.0
If there is a deficit (the financing need is positive), draw on the LOC:
Unadjusted line of credit = $205.9 $287.5 $362.0 $401.5 $443.2
Adjustment factor (see note below) = 0.97 0.97 0.97 0.97 0.97
Adjusted line of credit = Unadjusted LOC / Adjustment factor = $213.3 $297.8 $374.9 $415.8 $459.0
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 purposes, so it is only the after-tax impact that determines the adjusted LOC.
The following section shows how to determine capital structure components that are consistent with the target capital structure.
The value of operations for the last year in the forecast is equal to the horizon value, which is the present value of all free cash flows beyond the horizon, discounted back to the horizon using the target WACC. The value of operations in the year prior to the horizon is equal to the value of all free cash flows beyond the year prior to the horizon, discounted back to the year prior to the horizon at the target WACC. But this present value is equivalent to the present value of the value of operations one year ahead plus the free cash flow one year ahead, discounted back one period at the target WACC. Thus, we can estimate the annual values of operations by starting at the horizon and working backward one year at a time.
Here is the procedure. The value of operations at the horizon, Year t, is equal to:
VHV = Vop,t = [FCFt (1+g)]/(WACC-g).
The value of operations at any year prior to the horizon is:
Vop,t-1 = [FCFt +Vop,t]/(1+WACC).
The choices for the yearly values of the capital components are equal to weights in the target capital structure multiplied by the value of operations.
4. Determining Consistent Capital Structure Components Actual Forecast
Status Quo 2016 2017 2018 2019 2020 2021
Net operating working capital $790 $1,050 $1,155 $1,247 $1,335 $1,401 $1,472
Total net operating capital $2,490 $3,050 $3,355 $3,623 $3,877 $4,071 $4,274
NOPAT $330 $300 $330 $356 $381 $400 $420
FCF -$260 $25 $88 $128 $207 $217
Growth rate in FCF 252.0% 45.1% 61.7% 5.0%
Target WACC (rounded to 4 decimal places) 10.97% 10.97% 10.97% 10.97% 10.97%
Horizon value: VHV = Vop,2021 = [FCF2020 (1+g)]/(WACC-g). $3,815
Value of operations: Vop,t-1 = [FCFt +Vop,t]/(1+WACC). $2,719 $2,993 $3,233 $3,460 $3,633 $3,815 Note: The value of operations at the horizon is equal to the horizon value.
Choice of long-term bonds (wLTD x Vop) $1,200 $1,148 $1,157 $1,148 $1,111 $1,068
Choice of notes payable (wSTD x Vop) $280 $241 $211 $174 $128 $76
Choice of preferred stock (wps x Vop) $100 $100 $105 $110 $112 $114
5. Estimating the Intrinsic Stock Price
Status Quo 12/31/16
Value of operations $2,719
+ ST investments $0
Estimated total intrinsic value $2,719
− All debt $1,480
− Preferred stock $100
Estimated intrinsic value of equity $1,139
÷ Number of shares $50
Estimated intrinsic stock price = $22.79
Statement of Cash Flows (Millions of Dollars)
Status Quo Actual Forecast Forecast Forecast Forecast Forecast
2016 2017 2018 2019 2020 2021
Operating Activities
Net Income before preferred dividends $228.0 $243.6 $263.0 $284.4 $303.1 $325.3
Noncash adjustments
Depreciation $200.0 $220.0 $237.6 $254.2 $266.9 $280.3
Working capital adjustments
Increase(-)/Decrease(+) in accounts receivable ($120.0) ($50.0) ($44.0) ($41.6) ($31.8) ($33.4)
Increase(-)/Decrease(+) in inventories ($180.0) ($100.0) ($88.0) ($83.2) ($63.6) ($66.7)
Increase(-)/Decrease(+) in payables $10.0 $20.0 $17.6 $16.6 $12.7 $13.3
Increase(-)/Decrease(+) in accruals $20.0 $30.0 $26.4 $24.9 $19.1 $20.0
Net cash provided (used) by operating activities $158.0 $363.6 $412.6 $455.4 $506.5 $538.8
Investing Activities
Cash used to acquire fixed assets ($500.0) ($420.0) ($413.6) ($420.6) ($394.1) ($413.8)
Sale of short-term investments $40.0 $0.0 $0.0 $0.0 $0.0 $0.0
Net cash provided (used) by investing activities ($460.0) ($420.0) ($413.6) ($420.6) ($394.1) ($413.8)
Financing Activities
Increase(+)/Decrease(-) in notes payable $150.0 ($39.2) ($29.5) ($37.5) ($46.2) ($51.3)
Increase(+)/Decrease(-) in line of credit $0.0 $213.3 $84.5 $77.1 $40.9 $43.2
Increase(+)/Decrease(-) in bonds $200.0 ($51.8) $8.4 ($8.5) ($36.7) ($43.3)
Preferred stock issue(+)/repurchase(-) $0.0 ($0.3) $5.3 $4.5 $2.5 $2.4
Payment of common and preferred dividends ($58.0) ($60.5) ($63.3) ($66.5) ($69.6) ($72.9)
Common stock issue(+)/repurchase(-) $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
Net cash provided by financing activities $292.0 $61.4 $5.4 ($30.7) ($109.2) ($121.7)
Summary
Net change in cash and equivalents ($10.0) $5.0 $4.4 $4.2 $3.2 $3.3
Cash and securities at beginning of the year $60.0 $50.0 $55.0 $59.4 $63.6 $66.7
Cash and securities at end of the year $50.0 $55.0 $59.4 $63.6 $66.7 $70.1
Summary of Key Results for Forecasted Scenarios (Millions Except Percentages and Per Share Data)
Status Quo Industry MicroDrive
Actual Actual Forecast
1. Operations 2016 2016 2017 2018 2019 2020 2021
Free cash flow NA −$260 $25 $88 $128 $207 $217
Return on invested capital 15.0% 9.8% 9.8% 9.8% 9.8% 9.8% 9.8%
NOPAT/Sales 6.9% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0%
(Total op. capital)/Sales 46.0% 61.0% 61.0% 61.0% 61.0% 61.0% 61.0%
Inventory turnover 5.0 4.0 4.0 4.0 4.0 4.0 4.0
Days sales outstanding 30.0 36.5 36.5 36.5 36.5 36.5 36.5
Fixed asset turnover 3.0 2.5 2.5 2.5 2.5 2.5 2.5
2. Financing
(Total liabilities)/TA 45.0% 55.8% 55.1% 53.6% 51.7% 49.0% 46.3%
(Net income)/Sales 6.2% 4.4% 4.3% 4.3% 4.3% 4.4% 4.5%
Return on assets (ROA) 11.0% 6.2% 6.0% 6.0% 6.1% 6.2% 6.4%
Return on equity (ROE) 19.0% 15.0% 14.3% 13.8% 13.3% 12.8% 12.4%
Times interest earned 10.0 4.2 3.8 3.8 3.9 4.1 4.4
Line of credit NA $0 $213 $298 $375 $416 $459
Payout ratio 35.0% 22.7% 22.3% 21.6% 21.0% 20.7% 20.2%
Regular dividends/share NA $1.00 $1.05 $1.10 $1.16 $1.22 $1.28
Special dividends/share NA $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Earnings per share NA $4.40 $4.71 $5.10 $5.52 $5.88 $6.32
3. Estimated intrinsic value
12/31/2016 Estimated intrinsic stock price = $22.79

Mini Case Data

Mini Case Data 10/27/15
Figure 12-MC-1. Financial Statements and Other Data (Millions except per share data)
Hatfield Medical Supply: Balance Sheet (Millions of Dollars), 12/31/2016 Hatfield Medical Supply: Income Statement (Millions of Dollars Except per Share)
2016
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
Net fixed assets $500 Interest $40.0
Total assets $1,200 Pretax earnings $110.0
Taxes (40%) $44.0
Accts. pay. & accruals $80 Net income $66.0
Line of credit $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
Total common equ. $620 Ending stock price $52.80
Total liab. & equity $1,200
Selected Ratios and Other Data, 2016
Hatfield Industry Hatfield Industry
Op. costs/Sales 90% 88% (Total liabilities)/(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%
Inventories/Sales 20% 15% Sales/Assets 1.67 2.04
(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
ROIC 8.0% 12.5%
NOPAT/Sales 4.5% 5.6%
(Total op. capital)/Sales 56.0% 45.0%
Additional Data 2017
Exp. Sales growth rate 10%
Interest rate on LT debt 8%
Target WACC 9%