excel case 2- build a model

by | Aug 8, 2025 | Homework Answsers / Business & Finance

 Needed by 12/7

Please complete the Ch12 P10, build a model Excel spreadsheet attached, and upload it to Canvas.

build a model (spreadsheet) attached document-

You can refer to the tool kit below to learn how to complete the assignment. 

Ch. 12 tool kit- (attached document)

Build a Model

Build a Model Problem 3/13/23
Chapter: 12
Problem: 10
Start with the partial model in the file Ch12 P10 Build a Model.xlsx on the textbook’s Web site, which contains the 2023 financial statements of Zieber Corporation. Forecast Zeiber's 2024 income statement and balance sheets. Use the following assumptions: (1) Sales grow by 6%. (2) The ratios of expenses to sales, depreciation to fixed assets, cash to sales, accounts receivable to sales, and inventories to sales will be the same in 2024 as in 2023. (3) Zeiber will not issue any new stock or new long-term bonds. (4) The interest rate is 11% for long-term debt and the interest expense on long-term debt is based on the average balance during the year. (5) No interest is earned on cash. (6) Regular dividends grow at an 8% rate. (7) The tax rate is 25%. Calculate the additional funds needed (AFN). If new financing is required, assume it will be raised by drawing on a line of credit with an interest rate of 12%. Assume that any draw on the line of credit will be made on the last day of the year, so there will be no additional interest expense for the new line of credit. If surplus funds are available, pay a special dividend.
Key Input Data: Used in the
forecast
Tax rate 25%
Dividend growth rate 8%
Rate on notes payable-term debt, rstd 9%
Rate on long-term debt, rd 11%
Rate on line of credit, rLOC 12%
a. What are the forecasted levels of the line of credit and special dividends? (Hints: Create a column showing the ratios for the current year; then create a new column showing the ratios used in the forecast. Also, create a preliminary forecast that doesn't include any new line of credit or special dividends. Identify the financing deficit or surplus in this preliminary forecast and then add a new column that shows the final forecast that includes any new line of credit or special dividend.)
Begin by calculating the appropriate historical ratios in Column E. Then put these ratios and any other input ratios in Column G.
Forecast the preliminary balance sheets and income statements in Column H. Don't include any line of credit or special dividend in the preliminary forecast.
After completing the preliminary forecast of the balance sheets and income statement, go to the area below the preliminary forecast and identify the financing deficit or surplus. Then use Excel's IF statements to specify the amount of any new line of credit OR special dividend (you should not have a new line of credit AND a special dividend, only one or the other).
After specifying the amounts of the special dividend or line of credit, create a second column (I) for the final forecast next to the column for the preliminary forecast (H). In this final forecast, be sure to include the effect of the special dividend or line of credit.
Income Statements: 2023 2023 Historical ratios Forecasting basis 2024 Input ratios 2024 Preliminary forecast (doesn't include special dividend or LOC) 2024 Final forecast (includes special dividend or LOC)
(December 31, in thousands of dollars)
Sales $455,150 Growth
Expenses (excluding depr. & amort.) $386,878 % of sales
Depreciation and Amortization $14,565 % of fixed assets
EBIT $53,708
Interest expense on long-term debt $11,880 Interest rate x average debt during year
Interest expense on line of credit $0
EBT $41,828
Taxes (25%) $10,457
Net Income $31,371
Common dividends (regular dividends) $12,554 Growth 8.00%
Kenneth D. Jackson: Dividends are growing at 8% Special dividends Zero in preliminary forecast
Mike Ehrhardt: Special dividend in preliminary forecast is zero. Mike Ehrhardt: The special dividend in the final forecast should be equal to the amount specified in the area below the balance sheets. Addition to retained earnings $18,817
Mike Ehrhardt: The addition to retained earnings is equal to the net income minus any regular and special dividends. Balance Sheets 2023 2023 Historical ratios Forecasting basis 2024 Input ratios 2024 Preliminary forecast (doesn't include special dividend or LOC) 2024 Final forecast (includes special dividend or LOC)
(December 31, in thousands of dollars)
Assets:
Cash $18,206 % of sales
Mike Ehrhardt: Percent of forecasted sales. Accounts Receivable $100,133 % of sales
Mike Ehrhardt: Percent of forecasted sales. Inventories $45,515 % of sales
Total current assets $163,854
Fixed assets $182,060 % of sales
Total assets $345,914
Liabilities and equity
Accounts payable $31,861 % of sales
Accruals $27,309 % of sales
Line of credit $0 Zero in preliminary forecast
Total current liabilities $59,170
Long-term debt $120,000 Previous
Total liabilities $179,170
Common stock $60,000 Previous
Retained Earnings $106,745 Previous + Addition to retained earnings
Total common equity $166,745
Total liabilities and equity $345,914
Identify Financing Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals)
+ Increase in long-term bonds, preferred stock and common stock
+ Net income (in preliminary forecast) minus regular common dividends
Increase in financing
− Increase in total assets
Amount of financing deficit or surplus:
If deficit in financing (negative), show the amount for the line of credit
If surplus in financing (positive), show the amount of the special dividend
a. What are the forecasted levels of the line of credit and special dividends?
Required line of credit
Mike Ehrhardt: In final forecast, the line of credit is equal to the amount specified in the section below the balance sheets. Mike Ehrhardt: Percent of forecasted sales. Mike Ehrhardt: Percent of forecasted sales. Mike Ehrhardt: Percent of forecasted sales. Mike Ehrhardt: Percent of forecasted sales. Mike Ehrhardt: Line of credit is zero in preliminary forecast. Mike Ehrhardt: In final forecast, the line of credit is equal to the amount specified in the section below the balance sheets. Kenneth D. Jackson: Estimated sales based on the growth rate. Kenneth D. Jackson: Percent of forecasted sales. Kenneth D. Jackson: Percent of forecasted fixed assets. Mike Ehrhardt: The amount shown in retained earnings in the preliminary forecast is equal to the amount from the previous year's balance sheet plus the addition to retained earnings on the preliminary forecasted income statement. Mike Ehrhardt: The amount shown in retained earnings in the final forecast is equal to the amount from the previous year's balance sheet (not the amount from the preliminary forecast) plus the addition to retained earnings on the final forecasted income statement. Special dividends
b. Now assume that the growth in sales is only 3% (do this by changing the growth rate in Cell G51). What are the forecasted levels of line of credit and special dividends?
Required ine of credit
Special dividends

,

Chapter

Tool Kit Chapter 12 3/1/23
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
2022 2023 Assets 2022 2023
Net sales $4,800 $5,000 Cash $102 $100
COGS (excl. depr.) 3,710 3,900 ST Investments 40 10
Depreciation 180 200 Accounts receivable 384 500
Other operating expenses 470 500 Inventories 774 1,000
EBIT $440 $400 Total CA $1,300 $1,610
Interest expense 40 60 Net PP&E 1,780 2,000
Pre-tax earnings $400 $340 Total assets $3,080 $3,610
Taxes (25%) 100 85
NI before pref. div. $300 $255 Liabilities and equity
Preferred div. 7 7 Accounts payable $180 $200
Net income $293 $248 Notes payable 28 150
Accruals 370 400
Other Data Total CL $578 $750
Common dividends $59.4 $60.0 Long-term bonds 350 520
Addition to RE $233.6 $188.0 Total liabilities $928 $1,270
Tax rate 25% 25% Preferred stock 100 100
Shares of common stock 60 60 Common stock 500 500
Earnings per share $4.88 $4.13 Retained earnings 1,552 1,740
Dividends per share $0.99 $1.00 Total common equity $2,052 $2,240
Price per share $45.00 $31.00 Total liabs. & equity $3,080 $3,610
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.
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)
Status Quo Industry MicroDrive MicroDrive
Inputs Actual Actual Forecast
1. Operations 2023 2022 2023 2024 2025 2026 2027 2028
Sales growth rate 5% 10.0% 4.2% 10.0% 8.0% 7.0% 5.0% 5.0%
(COGS excl. depr.)/Sales 76% 77.3% 78.0% 78.0% 78.0% 78.0% 78.0% 78.0%
Depreciation/(Net PP&E) 9% 10.1% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0%
(Other op. exp.)/Sales 10% 9.8% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0%
Cash/Sales 1% 2.1% 2.0% 2.0% 2.0% 2.0% 2.0% 2.0%
(Acc. rec.)/Sales 8% 8.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% Actual Historical Financing
Inventory/Sales 15% 16.1% 20.0% 20.0% 20.0% 20.0% 20.0% 20.0% 2022 2023
(Net PP&E)/Sales 33% 37.1% 40.0% 40.0% 40.0% 40.0% 40.0% 40.0% Short-term debt $28 $150
(Acc. pay.)/Sales 4% 3.8% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% Long-term debt $350 $520
Accruals/Sales 7% 7.7% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% Preferred stock $100 $100
Tax rate 25% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% Market value of equity = (Price x # shares) $2,700 $1,860
Cost of strategic initiatives NA NA NA 0.0% 0.0% 0.0% 0.0% 0.0% Total $3,178 $2,630
2. Capital Structure Actual Market Weights Target Market Weights
% Short-term debt 4% 0.9% 5.7% 4% 4% 4% 4% 4% Percent short-term debt 0.9% 5.7%
% Long-term debt 20% 11.0% 19.8% 20% 20% 20% 20% 20%
% Preferred stock 2% 3.1% 3.8% 2% 2% 2% 2% 2%
% Common stock 74% 85.0% 70.7% 74% 74% 74% 74% 74% 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 11.0% 19.8%
% Line of credit 0% 0.0% 0.0% 0% 0% 0% 0% 0% Percent preferred stock 3.1% 3.8%
3. Costs of Capital Forecast Total 15.0% 29.3%
Rate on ST debt 8.0% 8.0% 8.0% 8.0% 8.0%
Rate on LT debt 10.0% 10.0% 10.0% 10.0% 10.0%
Rate on preferred stock (ignoring flotation costs) 7.0% 7.0% 7.0% 7.0% 7.0%
Flotation cost of new preferred stock 2.1% 2.1% 2.1% 2.1% 2.1%
Cost of equity 13.0% 13.0% 13.0% 13.0% 13.0%
Additional rate on line of credit vs. ST debt 1.5% 1.5% 1.5% 1.5% 1.5%
4. Target Dividend Policy Actual Forecast
Growth rate of dividends 11% 1.0% 5% 5% 5% 5% 5%
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 Figure 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 Sales Growth (Only) Industry MicroDrive MicroDrive Improve Operations (Only) Industry MicroDrive MicroDrive Improve 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 2023 2022 2023 2024 2025 2026 2027 2028 A1. Operations 2023 2022 2023 2024 2025 2026 2027 2028 A1. Operations 2023 2022 2023 2024 2025 2026 2027 2028 A1. Operations 2023 2022 2023 2024 2025 2026 2027 2028 A1. Operations 2023 2022 2023 2024 2025 2026 2027 2028
Sales growth rate 5% 10% 4% 10.0% 8.0% 7.0% 5.0% 5.0% Sales growth rate 5% 10% 4% 10.0% 8.0% 7.0% 5.0% 5.0% Sales growth rate 5% 10% 4% 11.0% 9.0% 8.0% 6.0% 6.0% Sales growth rate 5% 10% 4% 10.0% 8.0% 7.0% 5.0% 5.0% Sales growth rate 5% 10% 4% 11.0% 9.0% 8.0% 6.0% 6.0%
(COGS excl. depr.)/Sales 76% 77% 78% 78.0% 78.0% 78.0% 78.0% 78.0% (COGS excl. depr.)/Sales 76% 77% 78% 78.0% 78.0% 78.0% 78.0% 78.0% (COGS excl. depr.)/Sales 76% 77% 78% 78.0% 78.0% 78.0% 78.0% 78.0% (COGS excl. depr.)/Sales 76% 77% 78% 78.0% 77.1% 77.1% 77.1% 77.1% (COGS excl. depr.)/Sales 76% 77% 78% 78.0% 77.1% 77.1% 77.1% 77.1%
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% 2% 2% 2.0% 2.0% 2.0% 2.0% 2.0% Cash/Sales 1% 2% 2% 2.0% 2.0% 2.0% 2.0% 2.0% Cash/Sales 1% 2% 2% 2.0% 2.0% 2.0% 2.0% 2.0% Cash/Sales 1% 2% 2% 2.0% 2.0% 2.0% 2.0% 2.0% Cash/Sales 1% 2% 2% 2.0% 2.0% 2.0% 2.0% 2.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% 16% 20% 20.0% 20.0% 20.0% 20.0% 20.0% Inventory/Sales 15% 16% 20% 20.0% 20.0% 20.0% 20.0% 20.0% Inventory/Sales 15% 16% 20% 20.0% 20.0% 20.0% 20.0% 20.0% Inventory/Sales 15% 16% 20% 18.0% 15.0% 15.0% 15.0% 15.0% Inventory/Sales 15% 16% 20% 18.0% 15.0% 15.0% 15.0% 15.0%
(Net PP&E)/Sales 33% 37% 40% 40.0% 40.0% 40.0% 40.0% 40.0% (Net PP&E)/Sales 33% 37% 40% 40.0% 40.0% 40.0% 40.0% 40.0% (Net PP&E)/Sales 33% 37% 40% 40.0% 40.0% 40.0% 40.0% 40.0% (Net PP&E)/Sales 33% 37% 40% 38.0% 36.0% 36.0% 36.0% 36.0% (Net PP&E)/Sales 33% 37% 40% 38.0% 36.0% 36.0% 36.0% 36.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% 8% 8% 8.0% 8.0% 8.0% 8.0% 8.0% Accruals/Sales 7% 8% 8% 8.0% 8.0% 8.0% 8.0% 8.0% Accruals/Sales 7% 8% 8% 8.0% 8.0% 8.0% 8.0% 8.0% Accruals/Sales 7% 8% 8% 8.0% 8.0% 8.0% 8.0% 8.0% Accruals/Sales 7% 8% 8% 8.0% 8.0% 8.0% 8.0% 8.0%
Tax rate 25% 25% 25% 25.0% 25.0% 25.0% 25.0% 25.0% Tax rate 25% 25% 25% 25.0% 25.0% 25.0% 25.0% 25.0% Tax rate 25% 25% 25% 25.0% 25.0% 25.0% 25.0% 25.0% Tax rate 25% 25% 25% 25.0% 25.0% 25.0% 25.0% 25.0% Tax rate 25% 25% 25% 25.0% 25.0% 25.0% 25.0% 25.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 $50 $0 $0 $0 $0 Cost of strategic initiatives NA NA NA $100 $0 $0 $0 $0 Cost of strategic initiatives NA NA NA $150 $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 2022 2023 2024 2025 2026 2027 2028 B1. Sales Revenues 2022 2023 2024 2025 2026 2027 2028 B1. Sales Revenues 2022 2023 2024 2025 2026 2027 2028 B1. Sales Revenues 2022 2023 2024 2025 2026 2027 2028 B1. Sales Revenues 2022 2023 2024 2025 2026 2027 2028
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,550 $6,050 $6,533 $6,925 $7,341 Net sales $5,000 $5,500 $5,940 $6,356 $6,674 $7,007 Net sales $5,000 $5,550 $6,050 $6,533 $6,925 $7,341
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 $102 $100 $110 $119 $127 $133 $140 Cash $102 $100 $110 $119 $127 $133 $140 Cash $102 $100 $111 $121 $131 $139 $147 Cash $102 $100 $110 $119 $127 $133 $140 Cash $102 $100 $111 $121 $131 $139 $147
Accounts receivable $384 $500 $550 $594 $636 $667 $701 Accounts receivable $384 $500 $550 $594 $636 $667 $701 Accounts receivable $384 $500 $555 $605 $653 $693 $734 Accounts receivable $384 $500 $550 $594 $636 $667 $701 Accounts receivable $384 $500 $555 $605 $653 $693 $734
Inventories $774 $1,000 $1,100 $1,188 $1,271 $1,335 $1,401 Inventories $774 $1,000 $1,100 $1,188 $1,271 $1,335 $1,401 Inventories $774 $1,000 $1,110 $1,210 $1,307 $1,385 $1,468 Inventories $774 $1,000 $990 $891 $953 $1,001 $1,051 Inventories $774 $1,000 $999 $907 $980 $1,039 $1,101
Net PP&E $1,780 $2,000 $2,200 $2,376 $2,542 $2,669 $2,803 Net PP&E $1,780 $2,000 $2,200 $2,376 $2,542 $2,669 $2,803 Net PP&E $1,780 $2,000 $2,220 $2,420 $2,613 $2,770 $2,936 Net PP&E $1,780 $2,000 $2,090 $2,138 $2,288 $2,402 $2,523 Net PP&E $1,780 $2,000 $2,109 $2,178 $2,352 $2,493 $2,643
Accounts payable $180 $200 $220 $238 $254 $267 $280 Accounts payable $180 $200 $220 $238 $254 $267 $280 Accounts payable $180 $200 $222 $242 $261 $277 $294 Accounts payable $180 $200 $220 $238 $254 $267 $280 Accounts payable $180 $200 $222 $242 $261 $277 $294
Accruals $370 $400 $440 $475 $508 $534 $561 Accruals $370 $400 $440 $475 $508 $534 $561 Accruals $370 $400 $444 $484 $523 $554 $587 Accruals $370 $400 $440 $475 $508 $534 $561 Accruals $370 $400 $444 $484 $523 $554 $587
B3. Operating Income B3. Operating Income B3. Operating Income B3. Operating Income B3. Operating Income
COGS (excl. depr.) $3,900 $4,290 $4,633 $4,958 $5,205 $5,466 COGS (excl. depr.) $3,900 $4,290 $4,633 $4,958 $5,205 $5,466 COGS (excl. depr.) $3,900 $4,329 $4,719 $5,096 $5,402 $5,726 COGS (excl. depr.) $3,900 $4,290 $4,578 $4,898 $5,143 $5,401 COGS (excl. depr.) $3,900 $4,329 $4,662 $5,035 $5,337 $5,658
Depreciation $200 $220 $238 $254 $267 $280 Depreciation $200 $220 $238 $254 $267 $280 Depreciation $200 $222 $242 $261 $277 $294 Depreciation $200 $209 $214 $229 $240 $252 Depreciation $200 $211 $218 $235 $249 $264
Other operating expenses $500 $550 $594 $636 $667 $701 Other operating expenses $500 $550 $594 $636 $667 $701 Other operating expenses $500 $555 $605 $653 $693 $734 Other operating expenses $500 $550 $594 $636 $667 $701 Other operating expenses $500 $555 $605 $653 $693 $734
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 $50 $0 $0 $0 $0 Cost of strategic initiatives NA $100 $0 $0 $0 $0 Cost of strategic initiatives NA $150 $0 $0 $0 $0
EBIT $400 $440 $475 $508 $534 $561 EBIT $400 $440 $475 $508 $534 $561 EBIT $400 $394 $484 $523 $554 $587 EBIT $400 $351 $554 $593 $623 $654 EBIT $400 $305 $564 $610 $646 $685
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 $296 $363 $392 $416 $440 Net operating profit after taxes $300 $263 $416 $445 $467 $490 Net operating profit after taxes $300 $229 $423 $457 $485 $514
B4. Free Cash Flows B4. Free Cash Flows B4. Free Cash Flows B4. Free Cash Flows B4. Free Cash Flows
Net operating working capital $710 $1,000 $1,100 $1,188 $1,271 $1,335 $1,401 Net operating working capital $710 $1,000 $1,100 $1,188 $1,271 $1,335 $1,401 Net operating working capital $710 $1,000 $1,110 $1,210 $1,307 $1,385 $1,468 Net operating working capital $710 $1,000 $990 $891 $953 $1,001 $1,051 Net operating working capital $710 $1,000 $999 $907 $980 $1,039 $1,101
Total net operating capital $2,490 $3,000 $3,300 $3,564 $3,813 $4,004 $4,204 Total net operating capital $2,490 $3,000 $3,300 $3,564 $3,813 $4,004 $4,204 Total net operating capital $2,490 $3,000 $3,330 $3,630 $3,920 $4,155 $4,405 Total net operating capital $2,490 $3,000 $3,080 $3,029 $3,241 $3,404 $3,574 Total net operating capital $2,490 $3,000 $3,108 $3,085 $3,332 $3,532 $3,744
FCF = NOPAT – Δ net op capital −$210 $30 $92 $132 $210 $220 FCF = NOPAT – Δ net op capital −$210 $30 $92 $132 $210 $220 FCF = NOPAT – Δ net op capital −$210 -$35 $63 $102 $180 $191 FCF = NOPAT – Δ net op capital −$210 $183 $466 $233 $305 $320 FCF = NOPAT – Δ net op capital −$210 $121 $446 $210 $285 $302
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 11.50% 11.50% 11.50% 11.50% 11.50% Target WACC 11.50% 11.50% 11.50% 11.50% 11.50% Target WACC 11.50% 11.50% 11.50% 11.50% 11.50% Target WACC 11.50% 11.50% 11.50% 11.50% 11.50% Target WACC 11.50% 11.50% 11.50% 11.50% 11.50%
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% 5.3% 6.0% 6.0% 6.0% 6.0% OP ratio: NOPAT/Sales 6% 4.8% 7.0% 7.0% 7.0% 7.0% OP ratio: NOPAT/Sales 6% 4.1% 7.0% 7.0% 7.0% 7.0%
CR ratio: (Total op. cap.)/Sales 61% 60.0% 60.0% 60.0% 60.0% 60.0% CR ratio: (Total op. cap.)/Sales 61% 60.0% 60.0% 60.0% 60.0% 60.0% CR ratio: (Total op. cap.)/Sales 61% 60.0% 60.0% 60.0% 60.0% 60.0% CR ratio: (Total op. cap.)/Sales 61% 56.0% 51.0% 51.0% 51.0% 51.0% CR ratio: (Total op. cap.)/Sales 61% 56.0% 51.0% 51.0% 51.0% 51.0%
ROIC: NOPAT/(Total op. cap.) 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% ROIC: NOPAT/(Total op. cap.) 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% ROIC: NOPAT/(Total op. cap.) 10.0% 8.9% 10.0% 10.0% 10.0% 10.0% ROIC: NOPAT/(Total op. cap.) 10.0% 8.5% 13.7% 13.7% 13.7% 13.7% ROIC: NOPAT/(Total op. cap.) 10.0% 7.4% 13.7% 13.7% 13.7% 13.7%
Growth in FCF 208.0% 42.7% 59.1% 5.0% Growth in FCF 208.0% 42.7% 59.1% 5.0% Growth in FCF -283.4% 60.6% 77.4% 6.0% Growth in FCF 154.4% -50.1% 31.0% 5.0% Growth in FCF 269.2% -52.8% 35.3% 6.0%
Value of Operations (12/31/2028) Estimated intrinsic stock price (12/31/2023) Value of Operations (12/31/2028) Estimated intrinsic stock price (12/31/2023) Value of Operations (12/31/2028) Estimated intrinsic stock price (12/31/2023) Value of Operations (12/31/2028) Estimated intrinsic stock price (12/31/2023) Value of Operations (12/31/2028) Estimated intrinsic stock price (12/31/2023)
Value of operations $2,524 Value of operations $2,524 Value of operations $2,458 Value of operations $4,091 Value of operations $4,353
= $3,558 + ST investmentsa $10 = $3,558 + ST investmentsa $10 = $3,684 + ST investmentsa $10 = $5,172 + ST investmentsa $10 = $5,816 + ST investmentsa $10
Estimated total intrinsic value $2,534 Estimated total intrinsic value $2,534 Estimated total intrinsic value $2,468 Estimated total intrinsic value $4,101 Estimated total intrinsic value $4,363
= $2,064 − All debta $670 = $2,064 − All debta $670 = $2,138 − All debta $670 = $3,001 − All debta $670 = $3,375 − All debta $670
− Preferred stocka $100 − Preferred stocka $100 − Preferred stocka $100 − Preferred stocka $100 − Preferred stocka $100
Value of Operations (12/31/2023) Estimated intrinsic value of equity $1,764 Value of Operations (12/31/2023) Estimated intrinsic value of equity $1,764 Value of Operations (12/31/2023) Estimated intrinsic value of equity $1,698 Value of Operations (12/31/2023) Estimated intrinsic value of equity $3,331 Value of Operations (12/31/2023) Estimated intrinsic value of equity $3,593
Present value of HV $2,064 ÷ Number of sharesa $60.00 Present value of HV $2,064 ÷ Number of sharesa $60.00 Present value of HV $2,138 ÷ Number of sharesa $60.00 Present value of HV $3,001 ÷ Number of sharesa $60.00 Present value of HV $3,375 ÷ Number of sharesa $60.00
+ Present value of FCF $460 Estimated intrinsic stock price = $29.40 + Present value of FCF $460 Estimated intrinsic stock price = $29.40 + Present value of FCF $321 Estimated intrinsic stock price = $28.31 + Present value of FCF $1,090 Estimated intrinsic stock price = $55.52 + Present value of FCF $978 Estimated intrinsic stock price = $59.88
Value of operations = $2,524 Value of operations = $2,524 Value of operations = $2,458 Value of operations = $4,091 Value of operations = $4,353
Note: aValues are for 2023 and appear in Figure 12-1.
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 Live
(1) Status Quo (2) Higher Sales Growth (Only) (3) Improve Operations (Only) (4) Operations and Growth Status Quo
Panel A: Key Inputs
Sales growth (Year 1) 10.0% 11.0% 10.0% 11.0% 10.0%
Sales growth (Year 2) 8.0% 9.0% 8.0% 9.0% 8.0%
Sales growth (Year 3) 7.0% 8.0% 7.0% 8.0% 7.0%
Long-term sales growth (gL) 5.0% 6.0% 5.0% 6.0% 5.0%
(COGS excl. depr.)/Sales (Year 1) 78.0% 78.0% 78.0% 78.0% 78.0%
(COGS excl. depr.)/Sales (Year 2) 78.0% 78.0% 77.1% 77.1% 78.0%
Inventory/Sales for (Year 1) 20.0% 20.0% 18.0% 18.0% 20.0%
Inventory/Sales for (Year 2) 20.0% 20.0% 15.0% 15.0% 20.0%
(Net PP&E)/Sales (Year 1) 40.0% 40.0% 38.0% 38.0% 40.0%
(Net PP&E)/Sales (Year 2) 40.0% 40.0% 36.0% 36.0% 40.0%
Cost of strategic initiatives $0 $50 $100 $150 $0
Weighted average cost of capital (WACC) 11.50% 11.50% 11.50% 11.50% 11.50%
Panel B: Key Operating Plan Results
OP, operating profitability (Year 5) 6.0% 6.0% 7.0% 7.0% 6.0%
CR, capital requirement (Year 5) 60.0% 60.0% 51.0% 51.0% 60.0%
ROIC, return on invested capital (Year 5) 10.00% 10.00% 13.72% 13.72% 10.00%
Sales (Year 5) $7,007 $7,341 $7,007 $7,341 $7,007
NOPAT (Year 5) $420 $440 $490 $514 $420
Total net operating capital ((Year 5) $4,204 $4,405 $3,574 $3,744 $4,204
FCF (Year 5) $220 $191 $320 $302 $220
Value of operations (Year 0) $2,064 $2,138 $3,001 $3,375 $2,524
Intrinsic stock price (Year 0) $29.40 $28.31 $55.52 $59.88 $29.40
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 9-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 funding provided by spontaneous liabilities, external sources, and internal sources. The sum of these three sources of funding is the total amount of additional preliminary funding.
Section 3 also calculates the total amount of additional assets required by the operating plan.
The difference between the total additional funding and the total additional assets is defined as the funding deficit (if the difference is negative) or the funding surplus (if the difference is positive).
If there is a financing deficit, MicroDrive will draw on a line of credit. The interest rate on the LOC is equal to the rate on short-term debt plus the premium shown in Cell E86. However, 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 LOC balance at the beginning of the year (which is the same as the LOC balance at the end of the previous year) multiplied by the LOC interest rate.
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
Status Quo Higher Sales Growth (Only) Improve Operations (Only) Improve 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
Assets 2023 Input Basis for 2024 Forecast 2024 Assets 0 Input 0 0 Assets 0 Input 0 0 Assets 0 0 0
Cash $100.0 2.00% × 2024 Sales $110.00 Cash $100.0 2.00% 0.00 $111.00 Cash $100.0 2.00% 0.00 $110.00 Cash $100.0 2.00% 0.00 0.00 $111.00
Short-tem investments 10.0 Set to zero $0.00 Short-tem invesments 10.0 Set to zero $0.00 Short-tem invesments 10.0 Set to zero $0.00 Short-tem invesments 10.0 Set to zero $0.00
Accounts receivable 500.0 10.00% × 2024 Sales $550.00 Accounts receivable 500.0 10.00% 0.00 $555.00 Accounts receivable 500.0 10.00% 0.00 $550.00 Accounts receivable 500.0 10.00% 0.00 0.00 $555.00
Inventories 1,000.0 20.00% × 2024 Sales $1,100.00 Inventories 1,000.0 20.00% 0.00 $1,110.00 Inventories 1,000.0 18.00% 0.00 $990.00 Inventories 1,000.0 18.00% 0.00 0.00 $999.00
Total current assets $1,610.0 $1,760.00 Total current assets $1,610.0 $1,776.00 Total current assets $1,610.0 $1,650.00 Total current assets $1,610.0 $1,665.00
Net PP&E 2,000.0 40.00% × 2024 Sales $2,200.00 Net PP&E 2,000.0 40.00% 0.00 $2,220.00 Net PP&E 2,000.0 38.00% 0.00 $2,090.00 Net PP&E 2,000.0 38.00% 0.00 0.00 $2,109.00
Total assets (TA) $3,610.0 $3,960.00 Total assets (TA) $3,610.0 $3,996.00 Total assets (TA) $3,610.0 $3,740.00 Total assets (TA) $3,610.0 $3,774.00
Liabilities and equity Liabilities and equity Liabilities and equity Liabilities and equity
Accounts payable $200.0 4.00% × 2024 Sales $220.00 Accounts payable $200.0 4.00% 0.00 $222.00 Accounts payable $200.0 4.00% 0.00 $220.00 Accounts payable $200.0 4.00% 0.00 0.00 $222.00
Notes payable 150.0 Carry over from previous year $150.00 Notes payable 150.0 Carry over from previous year $150.00 Notes payable 150.0 Carry over from previous year $150.00 Notes payable 150.0 Carry over from previous year $150.00
Accruals 400.0 8.00% × 2024 Sales $440.00 Accruals 400.0 8.00% 0.00 $444.00 Accruals 400.0 8.00% 0.00 $440.00 Accruals 400.0 8.00% 0.00 0.00 $444.00
Line of credit 0.0 Draw on LOC if financing deficit $78.00 Line of credit 0.0 Draw on LOC if financing deficit $142.50 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 $750.0 $888.00 Total CL $750.0 $958.50 Total CL $750.0 $810.00 Total CL $750.0 $816.00
Long-term bonds 520.0 Carry over from previous year $520.00 Long-term bonds 520.0 Carry over from previous year $520.00 Long-term bonds 520.0 Carry over from previous year $520.00 Long-term bonds 520.0 Carry over from previous year $520.00
Total liabilities $1,270.0 $1,408.00 Total liabilities $1,270.0 $1,478.50 Total liabilities $1,270.0 $1,330.00 Total liabilities $1,270.0 $1,336.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
Retained earnings 1,740.0 Previous RE + Add. to RE $1,952 Retained earnings 1,740.0 Previous RE + Add. to RE $1,918 Retained earnings 1,740.0 Previous RE + Add. to RE $1,810 Retained earnings 1,740.0 Previous RE + Add. to RE $1,838
Total common equity $2,240.0 $2,452 Total common equity $2,240.0 $2,418 Total common equity $2,240.0 $2,310 Total common equity $2,240.0 $2,338
Total liabs. & equity $3,610.0 $3,960 Total liabs. & equity $3,610.0 $3,996 Total liabs. & equity $3,610.0 $3,740 Total liabs. & equity $3,610.0 $3,774
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
2023 Input Basis for 2024 Forecast 2024 0 Input 0 0 0 0 0 0 Input 0 0 0
Net sales $5,000.0 110% × 2023 Sales $5,500.00 Net sales $5,000.0 111% 0.00 $5,550.00 Net sales $5,000.0 110% 0.00 $5,500.00 Net sales $5,000.0 111% 0.00 0.00 $5,550.00
COGS (excl. depr.) 3,900.0 78.00% × 2024 Sales 4,290.00 COGS (excl. depr.) 3,900.0 78.00% 0.00 4,329.00 COGS (excl. depr.) 3,900.0 78.00% 0.00 4,290.00 COGS (excl. depr.) 3,900.0 78.00% 0.00 0.00 4,329.00
Depreciation 200.0 10.00% × 2024 Net PP&E 220.00 Depreciation 200.0 10.00% 0.00 222.00 Depreciation 200.0 10.00% 0.00 209.00 Depreciation 200.0 10.00% 0.00 0.00 210.90
Other operating expenses 500.0 10.00% × 2024 Sales 550.00 Other operating expenses 500.0 10.00% 0.00 555.00 Other operating expenses 500.0 10.00% 0.00 550.00 Other operating expenses 500.0 10.00% 0.00 0.00 555.00
Cost of strategic initiatives 0.0 Cost of implementation 0.00 Cost of strategic initiatives 0.0 Cost of implementation 50.00 Cost of strategic initiatives 0.0 Cost of implementation 100.00 Cost of strategic initiatives 0.0 Cost of implementation 150.00
EBIT $400.0 $440.00 EBIT $400.0 $394.00 EBIT $400.0 $351.00 EBIT $400.0 $305.10
Less: Interest on notes 10.0 8.00% × Avg notes $12.00 Less: Interest on notes 10.0 8.00% × Avg notes $12.00 Less: Interest on notes 10.0 8.00% × Avg notes $12.00 Less: Interest on notes 10.0 8.00% × Avg notes $12.00
Interest on bonds 50.0 10.00% × Avg bonds $52.00 Interest on bonds 50.0 10.00% × Avg bonds $52.00 Interest on bonds 50.0 10.00% × Avg bonds $52.00 Interest on bonds 50.0 10.00% × Avg bonds $52.00
Interest on LOC 0.0 9.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. The interest rate on the LOC is equal to the rate on ST debt plus the additional amount shown in Cell E86. 		Interest on LOC 0.0 9.50% × Beginning LOC $0.00 Interest on LOC 0.0 9.50% × Beginning LOC $0.00 Interest on LOC 0.0 9.50% × Beginning LOC $0.00
Pre-tax earnings $340.0 $376.00 Pre-tax earnings $340.0 $330.00 Pre-tax earnings $340.0 $287.00 Pre-tax earnings $340.0 $241.10
Taxes (25%) 85.0 25.00% × Pre-tax earnings $94.00 Taxes (40%) 85.0 25.00% × Pre-tax earnings $82.50 Taxes (40%) 85.0 25.00% × Pre-tax earnings $71.75 Taxes (40%) 85.0 25.00% × Pre-tax earnings $60.27
NI before pref. div. $255.0 $282.00 NI before pref. div. $255.0 $247.50 NI before pref. div. $255.0 $215.25 NI before pref. div. $255.0 $180.83
Preferred dividend 7.0 7.00% × Avg pref. stock $7.00 Preferred dividend 7.0 7.00% × Avg pref. stock $7.00 Preferred dividend 7.0 7.00% × Avg pref. stock $7.00 Preferred dividend 7.0 7.00% × Avg pref. stock $7.00
Net income $248.0 $275.00 Net income $248.0 $240.50 Net income $248.0 $208.25 Net income $248.0 $173.83
Regular common dividends $60.0 105% × 2023 Dividend $63.00 Regular common dividends $60.0 105% 0.00 $63.00 Regular common dividends $60.0 105% 0.00 $63.00 Regular common dividends $60.0 105% 0.00 0.00 $63.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 $75.25 Special dividends $0.0 Pay if financing surplus $12.82
Addition to RE $188.0 Net income – Dividends $212.00 Addition to RE $188.0 Net income – Dividends $177.50 Addition to RE $188.0 Net income – Dividends $70.00 Addition to RE $188.0 Net income – Dividends $98.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
Increase in spontaneous liabilities (accounts payable and accruals) $60.00 Increase in spontaneous liabilities (accounts payable and accruals) $66.00 Increase in spontaneous liabilities (accounts payable and accruals) $60.00 Increase in spontaneous liabilities (accounts payable and accruals) $66.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 $212.00 Note: See comment.
Mike Ehrhardt: This is the planned increase in the retained earnings account. 		+ Net income minus regular common dividends $177.50 + Net income minus regular common dividends $145.25 + Net income minus regular common dividends $110.83
− 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
Increase in financing $272.00 Increase in financing $243.50 Increase in financing $205.25 Increase in financing $176.83
− Increase in total assets $350.00 − Increase in total assets $386.00 − Increase in total assets $130.00 − Increase in total assets $164.00
Amount of deficit or surplus financing: −$78.00 Amount of deficit or surplus financing: −$142.50 Amount of deficit or surplus financing: $75.25 Amount of deficit or surplus financing: $12.82
If deficit in financing (negative), draw on line of credit Line of credit $78.00 If deficit in financing (negative), draw on line of credit Line of credit $142.50 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 $75.25 If surplus in financing (positive), pay special dividend Special dividend $12.82
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 Sales Growth (Only) Improve Operations (Only) Improve 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
2023 2024 2025 2026 2027 2028 2023 2024 2025 2026 2027 2028 2023 2024 2025 2026 2027 2028 2023 2024 2025 2026 2027 2028 2023 2024 2025 2026 2027 2028
Assets Assets Assets Assets Assets
Cash $100.0 $110.0 $118.8 $127.1 $133.5 $140.1 Cash $100.0 $110.0 $118.8 $127.1 $133.5 $140.1 Cash $100.0 $111.0 $121.0 $130.7 $138.5 $146.8 Cash $100.0 $110.0 $118.8 $127.1 $133.5 $140.1 Cash $100.0 $111.0 $121.0 $130.7 $138.5 $146.8
Short-tem investments $10.0 $0.0 $0.0 $0.0 $0.0 $0.0 Short-tem investments $10.0 $0.0 $0.0 $0.0 $0.0 $0.0 Short-tem invesments $10.0 $0.0 $0.0 $0.0 $0.0 $0.0 Short-tem invesments $10.0 $0.0 $0.0 $0.0 $0.0 $0.0 Short-tem invesments $10.0 $0.0 $0.0 $0.0 $0.0 $0.0
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 555.0 605.0 653.3 692.5 734.1 Accounts receivable 500.0 550.0 594.0 635.6 667.4 700.7 Accounts receivable 500.0 555.0 605.0 653.3 692.5 734.1
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,110.0 1,209.9 1,306.7 1,385.1 1,468.2 Inventories 1,000.0 990.0 891.0 953.4 1,001.0 1,051.1 Inventories 1,000.0 999.0 907.4 980.0 1,038.8 1,101.1
Total current assets $1,610.0 $1,760.0 $1,900.8 $2,033.9 $2,135.5 $2,242.3 Total current assets $1,610.0 $1,760.0 $1,900.8 $2,033.9 $2,135.5 $2,242.3 Total current assets $1,610.0 $1,776.0 $1,935.8 $2,090.7 $2,216.1 $2,349.1 Total current assets $1,610.0 $1,650.0 $1,603.8 $1,716.1 $1,801.9 $1,892.0 Total current assets $1,610.0 $1,665.0 $1,633.4 $1,764.0 $1,869.9 $1,982.1
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,220.0 2,419.8 2,613.4 2,770.2 2,936.4 Net PP&E 2,000.0 2,090.0 2,138.4 2,288.1 2,402.5 2,522.6 Net PP&E 2,000.0 2,109.0 2,177.8 2,352.0 2,493.2 2,642.8
Total assets (TA) $3,610.0 $3,960.0 $4,276.8 $4,576.2 $4,805.0 $5,045.2 Total assets (TA) $3,610.0 $3,960.0 $4,276.8 $4,576.2 $4,805.0 $5,045.2 Total assets (TA) $3,610.0 $3,996.0 $4,355.6 $4,704.1 $4,986.3 $5,285.5 Total assets (TA) $3,610.0 $3,740.0 $3,742.2 $4,004.2 $4,204.4 $4,414.6 Total assets (TA) $3,610.0 $3,774.0 $3,811.2 $4,116.1 $4,363.0 $4,624.8
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 $222.0 $242.0 $261.3 $277.0 $293.6 Accounts payable $200.0 $220.0 $237.6 $254.2 $266.9 $280.3 Accounts payable $200.0 $222.0 $242.0 $261.3 $277.0 $293.6
Notes payable $150.0 $150.0 $150.0 $150.0 $150.0 $150.0 Notes payable $150.0 $150.0 $150.0 $150.0 $150.0 $150.0 Notes payable $150.0 $150.0 $150.0 $150.0 $150.0 $150.0 Notes payable $150.0 $150.0 $150.0 $150.0 $150.0 $150.0 Notes payable $150.0 $150.0 $150.0 $150.0 $150.0 $150.0
Accruals 400.0 440.0 475.2 508.5 533.9 560.6 Accruals 400.0 440.0 475.2 508.5 533.9 560.6 Accruals 400.0 444.0 484.0 522.7 554.0 587.3 Accruals 400.0 440.0 475.2 508.5 533.9 560.6 Accruals 400.0 444.0 484.0 522.7 554.0 587.3
Line of credit 0.0 78.0 112.3 112.9 39.1 0.0 Line of credit 0.0 78.0 112.3 112.9 39.1 0.0 Line of credit 0.0 142.5 210.5 248.4 213.7 169.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 $750.0 $888.0 $975.1 $1,025.6 $990.0 $990.9 Total CL $750.0 $888.0 $975.1 $1,025.6 $990.0 $990.9 Total CL $750.0 $958.5 $1,086.5 $1,182.4 $1,194.7 $1,200.2 Total CL $750.0 $810.0 $862.8 $912.7 $950.8 $990.9 Total CL $750.0 $816.0 $875.9 $934.0 $981.1 $1,030.9
Long-term bonds 520.0 520.0 520.0 520.0 520.0 520.0 Long-term bonds 520.0 520.0 520.0 520.0 520.0 520.0 Long-term bonds 520.0 520.0 520.0 520.0 520.0 520.0 Long-term bonds 520.0 520.0 520.0 520.0 520.0 520.0 Long-term bonds 520.0 520.0 520.0 520.0 520.0 520.0
Total liabilities $1,270.0 $1,408.0 $1,495.1 $1,545.6 $1,510.0 $1,510.9 Total liabilities $1,270.0 $1,408.0 $1,495.1 $1,545.6 $1,510.0 $1,510.9 Total liabilities $1,270.0 $1,478.5 $1,606.5 $1,702.4 $1,714.7 $1,720.2 Total liabilities $1,270.0 $1,330.0 $1,382.8 $1,432.7 $1,470.8 $1,510.9 Total liabilities $1,270.0 $1,336.0 $1,395.9 $1,454.0 $1,501.1 $1,550.9
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 1,740.0 1,952.0 2,181.7 2,430.6 2,695.0 2,934.4 Retained earnings 1,740.0 1,952.0 2,181.7 2,430.6 2,695.0 2,934.4 Retained earnings 1,740.0 1,917.5 2,149.2 2,401.7 2,671.6 2,965.3 Retained earnings 1,740.0 1,810.0 1,759.4 1,971.5 2,133.5 2,303.7 Retained earnings 1,740.0 1,838.0 1,815.2 2,062.1 2,262.0 2,473.9
Total common equity $2,240.0 $2,452.0 $2,681.7 $2,930.6 $3,195.0 $3,434.4 Total common equity $2,240.0 $2,452.0 $2,681.7 $2,930.6 $3,195.0 $3,434.4 Total common equity $2,240.0 $2,417.5 $2,649.2 $2,901.7 $3,171.6 $3,465.3 Total common equity $2,240.0 $2,310.0 $2,259.4 $2,471.5 $2,633.5 $2,803.7 Total common equity $2,240.0 $2,338.0 $2,315.2 $2,562.1 $2,762.0 $2,973.9
Total liabs. & equity $3,610.0 $3,960.0 $4,276.8 $4,576.2 $4,805.0 $5,045.2 Total liabs. & equity $3,610.0 $3,960.0 $4,276.8 $4,576.2 $4,805.0 $5,045.2 Total liabs. & equity $3,610.0 $3,996.0 $4,355.6 $4,704.1 $4,986.3 $5,285.5 Total liabs. & equity $3,610.0 $3,740.0 $3,742.2 $4,004.2 $4,204.4 $4,414.6 Total liabs. & equity $3,610.0 $3,774.0 $3,811.2 $4,116.1 $4,363.0 $4,624.8
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
2023 2024 2025 2026 2027 2028 2023 2024 2025 2026 2027 2028 2019 2020 2021 2022 2023 2024 2019 2020 2021 2022 2023 2024 2019 2020 2021 2022 2023 2024
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,550.0 $6,049.5 $6,533.5 $6,925.5 $7,341.0 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,550.0 $6,049.5 $6,533.5 $6,925.5 $7,341.0
COGS (excl. depr.) 3,900.0 4,290.0 4,633.2 4,957.5 5,205.4 5,465.7 COGS (excl. depr.) 3,900.0 4,290.0 4,633.2 4,957.5 5,205.4 5,465.7 COGS (excl. depr.) 3,900.0 4,329.0 4,718.6 5,096.1 5,401.9 5,726.0 COGS (excl. depr.) 3,900.0 4,290.0 4,578.0 4,898.4 5,143.3 5,400.5 COGS (excl. depr.) 3,900.0 4,329.0 4,662.3 5,035.3 5,337.5 5,657.7
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 222.0 242.0 261.3 277.0 293.6 Depreciation 200.0 209.0 213.8 228.8 240.2 252.3 Depreciation 200.0 210.9 217.8 235.2 249.3 264.3
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 555.0 605.0 653.3 692.5 734.1 Other operating expenses 500.0 550.0 594.0 635.6 667.4 700.7 Other operating expenses 500.0 555.0 605.0 653.3 692.5 734.1
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 $50.0 $0.0 $0.0 $0.0 $0.0 Cost of strategic initiatives $100.0 $0.0 $0.0 $0.0 $0.0 Cost of strategic initiatives $150.0 $0.0 $0.0 $0.0 $0.0
EBIT $400.0 $440.0 $475.2 $508.5 $533.9 $560.6 EBIT $400.0 $440.0 $475.2 $508.5 $533.9 $560.6 EBIT $400.0 $394.0 $484.0 $522.7 $554.0 $587.3 EBIT $400.0 $351.0 $554.2 $593.0 $622.6 $653.8 EBIT $400.0 $305.1 $564.4 $609.6 $646.1 $684.9
Less: Interest on notes 10.0 12.0 12.0 12.0 12.0 12.0 Less: Interest on notes 10.0 12.0 12.0 12.0 12.0 12.0 Less: Interest on notes 20.0 12.0 12.0 12.0 12.0 12.0 Less: Interest on notes 20.0 12.0 12.0 12.0 12.0 12.0 Less: Interest on notes 20.0 12.0 12.0 12.0 12.0 12.0
Interest on bonds 50.0 52.0 52.0 52.0 52.0 52.0 Interest on bonds 50.0 52.0 52.0 52.0 52.0 52.0 Interest on bonds 100.0 52.0 52.0 52.0 52.0 52.0 Interest on bonds 100.0 52.0 52.0 52.0 52.0 52.0 Interest on bonds 100.0 52.0 52.0 52.0 52.0 52.0
Interest on LOC 0.0 0.0 7.4 10.7 10.7 3.7 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 7.4 10.7 10.7 3.7 Interest on LOC 0.0 0.0 13.5 20.0 23.6 20.3 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 $340.0 $376.0 $403.8 $433.8 $459.2 $492.9 Pre-tax earnings $340.0 $376.0 $403.8 $433.8 $459.2 $492.9 Pre-tax earnings $280.0 $330.0 $406.4 $438.7 $466.4 $503.0 Pre-tax earnings $280.0 $287.0 $490.2 $529.0 $558.6 $589.8 Pre-tax earnings $280.0 $241.1 $500.4 $545.6 $582.1 $620.9
Taxes (40%) 85.0 94.0 100.9 108.4 114.8 123.2 Taxes (40%) 85.0 94.0 100.9 108.4 114.8 123.2 Taxes (40%) 85.0 82.5 101.6 109.7 116.6 125.7 Taxes (40%) 85.0 71.8 122.6 132.2 139.7 147.4 Taxes (40%) 85.0 60.3 125.1 136.4 145.5 155.2
NI before pref. div. $255.0 $282.0 $302.8 $325.3 $344.4 $369.6 NI before pref. div. $255.0 $282.0 $302.8 $325.3 $344.4 $369.6 NI before pref. div. $195.0 $247.5 $304.8 $329.0 $349.8 $377.2 NI before pref. div. $195.0 $215.3 $367.7 $396.7 $419.0 $442.3 NI before pref. div. $195.0 $180.8 $375.3 $409.2 $436.6 $465.7
Preferred div. 7.0 7.0 7.0 7.0 7.0 7.0 Preferred div. 7.0 7.0 7.0 7.0 7.0 7.0 Preferred div. 7.0 7.0 7.0 7.0 7.0 7.0 Preferred div. 7.0 7.0 7.0 7.0 7.0 7.0 Preferred div. 7.0 7.0 7.0 7.0 7.0 7.0
Net income $248.0 $275.0 $295.8 $318.3 $337.4 $362.6 Net income $248.0 $275.0 $295.8 $318.3 $337.4 $362.6 Net income $188.0 $240.5 $297.8 $322.0 $342.8 $370.2 Net income $188.0 $208.3 $360.7 $389.7 $412.0 $435.3 Net income $188.0 $173.8 $368.3 $402.2 $429.6 $458.7
Regular common dividends $60.0 $63.0 $66.2 $69.5 $72.9 $76.6 Regular common dividends $60.0 $63.0 $66.2 $69.5 $72.9 $76.6 Regular common dividends $60.0 $63.0 $66.2 $69.5 $72.9 $76.6 Regular common dividends $60.0 $63.0 $66.2 $69.5 $72.9 $76.6 Regular common dividends $60.0 $63.0 $66.2 $69.5 $72.9 $76.6
Special dividends $0.0 $0.0 $0.0 $0.0 $0.0 $46.7 Special dividends $0.0 $0.0 $0.0 $0.0 $0.0 $46.7 Special dividends $0.0 $0.0 $0.0 $0.0 $0.0 $0.0 Special dividends $0.0 $75.3 $345.1 $108.2 $177.0 $188.6 Special dividends $0.0 $12.8 $324.9 $85.9 $156.8 $170.2
Addition to RE $188.0 $212.0 $229.7 $248.9 $264.4 $239.3 Addition to RE $188.0 $212.0 $229.7 $248.9 $264.4 $239.3 Addition to RE $128.0 $177.5 $231.7 $252.5 $269.9 $293.7 Addition to RE $128.0 $70.0 -$50.6 $212.1 $162.1 $170.2 Addition to RE $128.0 $98.0 -$22.8 $246.8 $199.9 $211.9
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) $60.0 $52.8 $49.9 $38.1 $40.0 Increase in spontaneous liabilities (accounts payable and accruals) $60.0 $52.8 $49.9 $38.1 $40.0 Increase in spontaneous liabilities (accounts payable and accruals) $66.0 $59.9 $58.1 $47.0 $49.9 Increase in spontaneous liabilities (accounts payable and accruals) $60.0 $52.8 $49.9 $38.1 $40.0 Increase in spontaneous liabilities (accounts payable and accruals) $66.0 $59.9 $58.1 $47.0 $49.9
+ 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 $212.0 $229.7 $248.9 $264.4 $286.1 Note: See comment.
Mike Ehrhardt: This is the planned increase in the retained earnings account. 		+ Net income minus regular common dividends $212.0 $229.7 $248.9 $264.4 $286.1 + Net income minus regular common dividends $177.5 $231.7 $252.5 $269.9 $293.7 + Net income minus regular common dividends $145.3 $294.5 $320.3 $339.1 $358.8 + Net income minus regular common dividends $110.8 $302.2 $332.7 $356.7 $382.1
− Previous line of credit $0.0 $78.0 $112.3 $112.9 $39.1 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.0 $78.0 $112.3 $112.9 $39.1 − Previous line of credit $0.0 $142.5 $210.5 $248.4 $213.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 $272.0 $204.5 $186.5 $189.7 $287.0 Increase in financing $272.0 $204.5 $186.5 $189.7 $287.0 Increase in financing $243.5 $149.1 $100.1 $68.6 $129.9 Increase in financing $205.3 $347.3 $370.2 $377.2 $398.8 Increase in financing $176.8 $362.1 $390.8 $403.7 $432.0
− Increase in total assets $350.0 $316.8 $299.4 $228.8 $240.2 − Increase in total assets $350.0 $316.8 $299.4 $228.8 $240.2 − Increase in total assets $386.0 $359.6 $348.5 $282.2 $299.2 − Increase in total assets $130.0 $2.2 $262.0 $200.2 $210.2 − Increase in total assets $164.0 $37.2 $304.9 $247.0 $261.8
Amount of deficit or surplus financing: −$78.0 −$112.3 −$112.9 −$39.1 $46.7 Amount of deficit or surplus financing: −$78.0 −$112.3 −$112.9 −$39.1 $46.7 Amount of deficit or surplus financing: −$142.5 −$210.5 −$248.4 −$213.7 −$169.3 Amount of deficit or surplus financing: $75.3 $345.1 $108.2 $177.0 $188.6 Amount of deficit or surplus financing: $12.8 $324.9 $85.9 $156.8 $170.2
Line of credit $78.0 $112.3 $112.9 $39.1 $0.0 Line of credit $78.0 $112.3 $112.9 $39.1 $0.0 Line of credit $142.5 $210.5 $248.4 $213.7 $169.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 $46.7 Special dividend $0.0 $0.0 $0.0 $0.0 $46.7 Special dividend $0.0 $0.0 $0.0 $0.0 $0.0 Special dividend $75.3 $345.1 $108.2 $177.0 $188.6 Special dividend $12.8 $324.9 $85.9 $156.8 $170.2
Statement of Cash Flows (Millions of Dollars)
Status Quo Actual Forecast Forecast Forecast Forecast Forecast
2023 2024 2025 2026 2027 2028
Operating Activities
Net Income before preferred dividends $255.0 $282.0 $302.8 $325.3 $344.4 $369.6
Noncash adjustments
Depreciation $200.0 $220.0 $237.6 $254.2 $266.9 $280.3
Working capital adjustments
Increase(-)/Decrease(+) in accounts receivable ($116.0) ($50.0) ($44.0) ($41.6) ($31.8) ($33.4)
Increase(-)/Decrease(+) in inventories ($226.0) ($100.0) ($88.0) ($83.2) ($63.6) ($66.7)
Increase(-)/Decrease(+) in payables $20.0 $20.0 $17.6 $16.6 $12.7 $13.3
Increase(-)/Decrease(+) in accruals $122.0 $40.0 $35.2 $33.3 $25.4 $26.7
Net cash provided (used) by operating activities $255.0 $412.0 $461.2 $504.7 $554.1 $589.9
Investing Activities
Cash used to acquire fixed assets ($420.0) ($420.0) ($413.6) ($420.6) ($394.1) ($413.8)
Sale of short-term investments $30.0 $0.0 $0.0 $0.0 $0.0 $0.0
Net cash provided (used) by investing activities ($390.0) ($420.0) ($413.6) ($420.6) ($394.1) ($413.8)
Financing Activities
Increase(+)/Decrease(-) in notes payable $30.0 $0.0 $0.0 $0.0 $0.0 $0.0
Increase(+)/Decrease(-) in line of credit $0.0 $78.0 $34.3 $0.6 ($73.8) ($39.1)
Increase(+)/Decrease(-) in bonds $170.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 ($67.0) ($70.0) ($73.2) ($76.5) ($79.9) ($130.3)
Common stock issue(+)/repurchase(-) $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
Net cash provided by financing activities $133.0 $8.0 ($38.8) ($75.9) ($153.7) ($169.4)
Summary
Net change in cash and equivalents ($2.0) $0.0 $8.8 $8.3 $6.4 $6.7
Cash and securities at beginning of the year $102.0 $100.0 $110.0 $118.8 $127.1 $133.5
Cash and securities at end of the year $100.0 $100.0 $118.8 $127.1 $133.5 $140.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 Live
(1) Status Quo (2) Higher Sales Growth (Only) (3) Improve Operations (Only) (4) Operations and Growth Status Quo
Panel A: Key Inputs
Sales growth (Year 1) 10.0% 11.0% 10.0% 11.0% 10.0%
Sales growth (Year 2) 8.0% 9.0% 8.0% 9.0% 8.0%
Sales growth (Year 3) 7.0% 8.0% 7.0% 8.0% 7.0%
Long-term sales growth (gL) 5.0% 6.0% 5.0% 6.0% 5.0%
(COGS excl. depr.)/Sales (Year 1) 78.0% 78.0% 78.0% 78.0% 78.0%
(COGS excl. depr.)/Sales (Year 2) 78.0% 78.0% 77.1% 77.1% 78.0%
Inventory/Sales for (Year 1) 20.0% 20.0% 18.0% 18.0% 20.0%
Inventory/Sales for (Year 2) 20.0% 20.0% 15.0% 15.0% 20.0%
(Net PP&E)/Sales (Year 1) 40.0% 40.0% 38.0% 38.0% 40.0%
(Net PP&E)/Sales (Year 2) 40.0% 40.0% 36.0% 36.0% 40.0%
Cost of strategic initiatives $0 $50 $100 $150 $0
Weighted average cost of capital (WACC) 11.5% 11.5% 11.5% 11.5% 11.5%
Panel B: Key Financial Plan Results
Line of credit (Year 1) $78 $143 $0 $0 $78
Line of credit (Year 5) $0 $169 $0 $0 $0
Minimum LOC in forecast $0 $143 $0 $0 $0
Maximum LOC in forecast $113 $248 $0 $0 $113
Regular dividends (Year 1) $63 $63 $63 $63 $63
Regular dividends (Year 5) $77 $77 $77 $77 $77
Special dividends (Year 1) $0 $0 $75 $13 $0
Special dividends (Year 5) $47 $0 $189 $170 $47
Total special dividends in forecast $47 $0 $894 $751 $47
Value of operations (Year 0) $2,064 $2,138 $3,001 $3,375 $2,524
Intrinsic stock price (Year 0) $29.40 $28.31 $55.52 $59.88 $29.40
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 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 Selected Scenario (Millions Except Percentages and per Share Data) Operating Results and Financial Outcomes for the Selected Scenario (Millions Except Percentages and per Share Data)
Status Quo Industry MicroDrive Improve Operations and Growth Industry MicroDrive Improve Operations and Growth Industry MicroDrive
Actual Actual Forecast Actual Actual Forecast Actual Actual Forecast
Panel A: Inputs 2023 2023 2024 2025 2026 2027 2028 Panel A: Inputs 2023 2023 2024 2025 2026 2027 2028 Panel A: Inputs 2023 2023 2024 2025 2026 2027 2028
Sales growth rate 5% 4% 10% 8% 7% 5% 5% Sales growth rate 5% 4% 11% 9% 8% 6% 6% Sales growth rate 5% 4% 11% 9% 8% 6% 6%
(COGS excl. depr.)/Sales 76% 78% 78% 78% 78% 78% 78% (COGS excl. depr.)/Sales 76% 78% 78% 77% 77% 77% 77% (COGS excl. depr.)/Sales 76% 78% 78% 77% 77% 77% 77%
Inventory/Sales 15% 20% 20% 20% 20% 20% 20% Inventory/Sales 15% 20% 18% 15% 15% 15% 15% Inventory/Sales 15% 20% 18% 15% 15% 15% 15%
(Net PP&E)/Sales 33% 40% 40% 40% 40% 40% 40% (Net PP&E)/Sales 33% 40% 38% 36% 36% 36% 36% (Net PP&E)/Sales 33% 40% 38% 36% 36% 36% 36%
Cost of strategic initiatives NA NA $0 $0 $0 $0 $0 Cost of strategic initiatives NA NA $150 $0 $0 $0 $0 Cost of strategic initiatives NA NA $150 $0 $0 $0 $0
Industry MicroDrive Industry MicroDrive Industry MicroDrive
Panel B: Key Output Actual Actual Forecast Panel B: Key Output Actual Actual Forecast Panel B: Key Output Actual Actual Forecast
2023 2023 2024 2025 2026 2027 2028 2023 2023 2024 2025 2026 2027 2028 2023 2023 2024 2025 2026 2027 2028
Return on invested capital 15.0% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% Return on invested capital 15.0% 10.00% 7.36% 13.72% 13.72% 13.72% 13.72% Return on invested capital 15.0% 10.00% 7.36% 13.72% 13.72% 13.72% 13.72%
Free cash flow NA −$210 $30 $92 $132 $210 $220 Free cash flow NA −$210 $121 $446 $210 $285 $302 Free cash flow NA −$210 $121 $446 $210 $285 $302
Line of credit NA $0 $78 $112 $113 $39 $0 Line of credit NA $0 $0 $0 $0 $0 $0 Line of credit NA $0 $0 $0 $0 $0 $0
Special dividends NA $0 $0 $0 $0 $0 $47 Special dividends NA $0 $13 $325 $86 $157 $170 Special dividends NA $0 $13 $325 $86 $157 $170
(Total debt)/TA 25.0% 18.6% 18.9% 18.3% 17.1% 14.8% 13.3% (Total debt)/TA 25.0% 18.6% 17.8% 17.6% 16.3% 15.4% 14.5% (Total debt)/TA 25.0% 18.6% 17.8% 17.6% 16.3% 15.4% 14.5%
Times interest earned 10.0 6.7 6.9 6.7 6.8 7.1 8.3 Times interest earned 10.0 3.3 4.8 8.8 9.5 10.1 10.7 Times interest earned 10.0 3.3 4.8 8.8 9.5 10.1 10.7
Return on assets (ROA) 11.0% 6.9% 6.9% 6.9% 7.0% 7.0% 7.2% Return on assets (ROA) 11.0% 5.2% 4.6% 9.7% 9.8% 9.8% 9.9% Return on assets (ROA) 11.0% 5.2% 4.6% 9.7% 9.8% 9.8% 9.9%
Return on equity (ROE) 19.0% 11.1% 11.2% 11.0% 10.9% 10.6% 10.6% Return on equity (ROE) 19.0% 8.4% 7.4% 15.9% 15.7% 15.6% 15.4% Return on equity (ROE) 19.0% 8.4% 7.4% 15.9% 15.7% 15.6% 15.4%
Earnings per share NA $4.13 $4.58 $4.93 $5.31 $5.62 $6.04 Earnings per share NA $3.13 $2.90 $6.14 $6.70 $7.16 $7.64 Earnings per share NA $3.13 $2.90 $6.14 $6.70 $7.16 $7.64
Payout ratio 35.0% 24.2% 22.9% 22.4% 21.8% 21.6% 34.0% Payout ratio 35.0% 31.9% 43.6% 106.2% 38.6% 53.5% 53.8% Payout ratio 35.0% 31.9% 43.6% 106.2% 38.6% 53.5% 53.8%
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.05 $1.10 $1.16 $1.22 $1.28 Regular dividends per share NA $1.00 $1.05 $1.10 $1.16 $1.22 $1.28
Special dividends per share NA $0.00 $0.00 $0.00 $0.00 $0.00 $0.78 Special dividends per share NA $0.00 $0.21 $5.42 $1.43 $2.61 $2.84 Special dividends per share NA $0.00 $0.21 $5.42 $1.43 $2.61 $2.84
Panel C: Valuation Weighted average cost of capital = 11.50% Panel C: Valuation Weighted average cost of capital = 11.50% Panel C: Valuation Weighted average cost of capital = 11.50%
12/31/2023 Estimated value of operations = $2,524 12/31/2023 Estimated value of operations = $4,353 12/31/2023 Estimated value of operations = $4,353
12/31/2023 Estimated intrinsic stock price = $29.40 12/31/2023 Estimated intrinsic stock price = $59.88 12/31/2023 Estimated intrinsic stock price = $59.88
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.0% See comment.
Michael Ehrhardt: Note: this is a fixed value so that Goal Seek may be used to determine the self sustaining growth rate as shown below this figure.
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. 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. 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. The interest rate on the LOC is equal to the rate on ST debt plus the additional amount shown in Cell E86. 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. 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,600
A0*/S0: Required operating assets per dollar of sales = 72.00%
L0*: Most recent year's spontaneous liabilities = $600
L0*/S0: Spontaneous liabilities per dollar of sales = 12.00%
Profit margin (M): Most recent profit margin = NI/sales = 4.96%
Payout ratio (POR): Most recent payout ratio = Dividends/NI = 24.19%
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.720)($500) − (0.12)($500) − $5,500(0.0496)(1 – 0.2419)
= $360 − $60.00 − $206.80 Additional      funds = needed         Required increase in assets − Increase in spontaneous liabilities − Increase in retained earnings
AFN = $93.20
Additional funds = needed Required increase – in assets Increase in spontaneous – liabilities Increase in retained earnings.
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) = $188.00 = 6.7%
A0* – L0* – PM(1 – POR)S0 $2,812.00
To print a window, use CTRL + ALT + PrintScreen. This puts it into memory. After exiting the window, use paste to show it.
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
2023 Percent utilization of fixed assets capacity = 96%
2023 Sales = $5,000
2023 Fixed assets = $2,000
2023 Fixed assets/Sales= 40.00%
2024 Sales = $5,500
Required increase in FA if no excess capacity = (2023 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% This is the FA/Sales figure MicroDrive could use if it were always operating at full capacity.
Required fixed assets = (Target FA/Sales) (Forecast sales)
Required fixed assets = $2,112 This is how much fixed assets MicroDrive would need next year if it could operate at full capacity.
Required increase in fixed assets = $112 This is how much new fixed assets MicroDrive would need next year if it could use up its current excess capacity and also if the new assets it added was also used at full capacity.
Difference between required increase assuming no excess capacity and required increase if there is excess capacity =
$88
AFN if no excess capacity = $93
AFN if there is excess capacity = $5 This is the AFN if MicroDrive had excess capacity and plans to use it up to support growth before buying new assets.

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 $5,000 million
A0*/ S0 72.000%
L0*/ S0 12.000%
Profit margin (M) 4.960%
Payout ratio 24.194%
Δ Sales $750.00 million
S1 $5,750.00 million
AFN $233.80 million

CFO Model

3/1/23
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
2022 2023 Assets 2022 2023
Net sales $ 4,800 $ 5,000 Cash $ 102 $ 100
COGS (excl. depr.) 3,710 3,900 ST Investments 40 10
Depreciation 180 200 Accounts receivable 384 500
Other operating expenses 470 500 Inventories 774 1,000
EBIT $ 440 $ 400 Total CA $ 1,300 $ 1,610
Interest expense 40 60 Net PP&E 1,780 2,000
Pretax earnings $ 400 $ 340 Total assets $ 3,080 $ 3,610
Taxes (40%) 100 85
NI before pref. div. $ 300 $ 255 Liabilities and equity
Preferred div. 7 7 Accounts payable $ 180 $ 200
Net income $ 293 $ 248 Notes payable 28 150
Accruals 370 400
Other Data Total CL $ 578 $ 750
Common dividends $59 $60 Long-term bonds 350 520
Addition to RE $234 $188 Total liabilities $ 928 $ 1,270
Tax rate 25% 25% Preferred stock 100 100
Shares of common stock 60 60 Common stock 500 500
Earnings per share $4.88 $4.13 Retained earnings 1,552 1,740
Dividends per share $0.99 $1.00 Total common equity $ 2,052 $ 2,240
Price per share $45.00 $31.00 Total liabs. & equity $ 3,080 $ 3,610
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 2023 2022 2023 2024 2025 2026 2027 2028
Sales growth rate 5% 10% 4% 10% 8% 7% 5% 5%
(COGS excl. depr.)/Sales 76% 77% 78% 78% 78% 78% 78% 78%
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% 2% 2% 2% 2% 2% 2% 2% Actual Historical Financing
(Acc. rec.)/Sales 8% 8% 10% 10% 10% 10% 10% 10% 2022 2023
Inventory/Sales 15% 16% 20% 20% 20% 20% 20% 20% Long-term debt $350 $520
(Net PP&E)/Sales 33% 37% 40% 40% 40% 40% 40% 40% Short-term debt $370 $400
(Acc. pay.)/Sales 4% 4% 4% 4% 4% 4% 4% 4% Preferred stock $100 $100
Accruals/Sales 7% 8% 8% 8% 8% 8% 8% 8% Market value of equity = (Price x # shares) $2,700 $1,860
Tax rate 25% 25% 25% 25% 25% 25% 25% 25% Total $3,520 $2,880
Cost of strategic initiatives NA NA NA $0 $0 $0 $0 $0
2. Capital Structure Actual Market Weights Target Market Weights
% Long-term debt 20% 11% 20% 20% 20% 20% 20% 20% 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 10% 18%
% Short-term debt 4% 1% 6% 4% 4% 4% 4% 4% Percent short-term debt 11% 14%
% Preferred stock 2% 3% 4% 2% 2% 2% 2% 2% Percent preferred stock 3% 3%
% Common stock 74% 85% 71% 74% 74% 74% 74% 74% Percent market value of equity 77% 65%
3. Costs of Capital Forecast Total 100% 100%
Rate on LT bonds, rLTD 10.0% 10% 10% 10% 10%
Rate on ST debt, rSTD 8.0% 8% 8% 8% 8%
Rate on preferred stock (ignoring flotation costs), rps 7.0% 7% 7% 7% 7%
Cost of equity, rs 13.00% 13% 13% 13% 13%
4. Target Dividend Policy Actual
Growth rate of dividends 11% 1.0% 5% 5% 5% 5% 5%
5. Capital Structure Choices
% Long-term debt, wLTD 19.8% 19.817% 19.863% 19.909% 19.954% 20.000%
% Short-term debt, wSTD 5.7% 5.4% 5.0% 4.7% 4.3% 4%
% Preferred stock, wps 3.8% 3.4% 3.1% 2.7% 2.4% 2%
% Common stock, ws 70.7% 71.4% 72.0% 72.7% 73.3% 74%
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. Note that there are no short-term invesments in the target capital structure.
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
2023 2024 2025 2026 2027 2028
Assets
Cash $100.0 $110.0 $118.8 $127.1 $133.5 $140.1
Short-term investments $10.0 $0.0
Michael Ehrhardt: The optimal capital structure does not have short-term investments. 		$0.0 $0.0 $0.0 $0.0
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,610.0 $1,760.0 $1,900.8 $2,033.9 $2,135.5 $2,242.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,610.0 $3,960.0 $4,276.8 $4,576.2 $4,805.0 $5,045.2
Liabilities and equity
Accounts payable $200.0 $220.0 $237.6 $254.2 $266.9 $280.3
Accruals 400.0 440.0 475.2 508.5 533.9 560.6
Notes payable (wSTD x Vop) 150.0 149.3 151.3 151.1 147.1 142.3
Line of credit (After adjustment for feedback effects) 0.0 54.0 44.5 7.0 0.0 0.0
Total CL $750.0 $863.3 $908.6 $920.8 $947.9 $983.2
Long-term bonds (wLTD x Vop) 520.0 551.8 598.3 642.4 676.1 711.5
Total liabilities $1,270.0 $1,415.1 $1,506.9 $1,563.2 $1,624.0 $1,694.7
Preferred stock (wps x Vop) $100.0 $95.8 $92.8 $87.8 $80.0 $71.2
Common stock 500.0 500.0 500.0 500.0 500.0 500.0
Retained earnings 1,740.0 1,949.1 2,177.0 2,425.2 2,601.0 2,779.4
Total common equity $2,240.0 $2,449.1 $2,677.0 $2,925.2 $3,101.0 $3,279.4
Total liabs. & equity $3,610.0 $3,960.0 $4,276.8 $4,576.2 $4,805.0 $5,045.2
Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00 $0.00 $0.00
2. Income Statement Actual Forecast
2023 2024 2025 2026 2027 2028
Net sales $5,000.0 $5,500.0 $5,940.0 $6,355.8 $6,673.6 $7,007.3
COGS (excl. depr.) 3,900.0 4,290.0 4,633.2 4,957.5 5,205.4 5,465.7
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 $400.0 $440.0 $475.2 $508.5 $533.9 $560.6
Less: Interest on notes payable, based on average NP and rSTD 20.0 12.0 12.0 12.1 11.9 11.6
Interest on bonds, based on average LT bonds and rLTD 100.0 53.6 57.5 62.0 65.9 69.4
Interest on LOC, based on average LOC and rLOC = rSTD +1.5% 0.0 2.6 4.7 2.4 0.3 0.0
Pre-tax earnings $280.0 $371.9 $401.0 $431.9 $455.7 $479.6
Taxes (25%) 85.0 93.0 100.2 108.0 113.9 119.9
NI before pref. div. $195.0 $278.9 $300.7 $323.9 $341.8 $359.7
Preferred dividend, based on average preferred stock and rps 7.0 6.9 6.6 6.3 5.9 5.3
Net income $188.0 $272.1 $294.1 $317.6 $335.9 $354.4
Regular common dividends $60.0 $63.0 $66.2 $69.5 $72.9 $76.6
Special dividends $0.0 $0.0 $0.0 $0.0 $87.1 $99.5
Addition to RE $128.0 $209.1 $228.0 $248.1 $175.9 $178.4
3. Eliminating the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals) $60.0 $52.8 $49.9 $38.1 $40.0
+ Increase in notes payable, long-term bonds, preferred stock and common stock $27.0 $45.5 $38.8 $21.9 $21.8
− Previous line of credit $0.0 $54.0 $44.5 $7.0 $0.0 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) $9.0 $9.0 $9.1 $8.9 $8.7
− After-tax interest on bonds (INTLTD x (1-T) $40.2 $43.1 $46.5 $49.4 $52.0
− After-tax interest on previous LOC: (rLOC x 0.5 x LOCt-1 x (1-T) $0.0 $1.9 $1.6 $0.3 $0.0 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 $6.9 $6.6 $6.3 $5.9 $5.3
− Regular common dividends $63.0 $66.2 $69.5 $72.9 $76.6
Total planned increase in the retained earnings account $211.0 $229.6 $248.4 $263.0 $277.9
Increase in financing $297.9 $273.8 $292.6 $315.9 $339.7 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 $350.0 $316.8 $299.4 $228.8 $240.2
Amount of unadjusted deficit or surplus financing: −$52.1 −$43.0 −$6.8 $87.1 $99.5
If there is a surplus (the financing need is positive), pay a special dividend: $0.0 $0.0 $0.0 $87.1 $99.5
If there is a deficit (the financing need is positive), draw on the LOC:
Unadjusted line of credit = $52.1 $43.0 $6.8 $0.0 $0.0
Adjustment factor (see note below) = 0.96 0.96 0.96 0.96 0.96
Adjusted line of credit = Unadjusted LOC / Adjustment factor = $54.0 $44.5 $7.0 $0.0 $0.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 2023 2024 2025 2026 2027 2028
Net operating working capital $710 $1,000 $1,100 $1,188 $1,271 $1,335 $1,401
Total net operating capital $2,490 $3,000 $3,300 $3,564 $3,813 $4,004 $4,204
NOPAT $300 $330 $356 $381 $400 $420
FCF -$210 $30 $92 $132 $210 $220
Growth rate in FCF 208.0% 42.7% 59.1% 5.0%
Target WACC (rounded to 4 decimal places) 11.50% 11.50% 11.50% 11.50% 11.50%
Horizon value: VHV = Vop,2026 = [FCF2026 (1+g)]/(WACC-g). $3,557.537
Value of operations: Vop,t-1 = [FCFt +Vop,t]/(1+WACC). $2,524 $2,784 $3,012 $3,227 $3,388.130 $3,557.537 Note: The value of operations at the horizon is equal to the horizon value.
Choice of long-term bonds (wLTD x Vop) $520 $552 $598 $642 $676.080 $711.507
Choice of notes payable (wSTD x Vop) $150 $149 $151 $151 $147.068 $142.301
Choice of preferred stock (wps x Vop) $100 $96 $93 $88 $79.975 $71.151
5. Estimating the Intrinsic Stock Price
Status Quo 12/31/23
Value of operations $2,524
+ ST investments $10
Estimated total intrinsic value $2,534
− All debt $920
− Preferred stock $100
Estimated intrinsic value of equity $1,514
÷ Number of shares $60
Estimated intrinsic stock price = $25.24
Statement of Cash Flows (Millions of Dollars)
Status Quo Actual Forecast Forecast Forecast Forecast Forecast
2023 2024 2025 2026 2027 2028
Operating Activities
Net Income before preferred dividends $255.0 $278.9 $300.7 $323.9 $341.8 $359.7
Noncash adjustments
Depreciation $200.0 $220.0 $237.6 $254.2 $266.9 $280.3
Working capital adjustments
Increase(-)/Decrease(+) in accounts receivable ($116.0) ($50.0) ($44.0) ($41.6) ($31.8) ($33.4)
Increase(-)/Decrease(+) in inventories ($226.0) ($100.0) ($88.0) ($83.2) ($63.6) ($66.7)
Increase(-)/Decrease(+) in payables $20.0 $20.0 $17.6 $16.6 $12.7 $13.3
Increase(-)/Decrease(+) in accruals $122.0 $40.0 $35.2 $33.3 $25.4 $26.7
Net cash provided (used) by operating activities $255.0 $408.9 $459.1 $503.3 $551.5 $579.9
Investing Activities
Cash used to acquire fixed assets ($420.0) ($420.0) ($413.6) ($420.6) ($394.1) ($413.8)
Sale of short-term investments $30.0 $10.0 $0.0 $0.0 $0.0 $0.0
Net cash provided (used) by investing activities ($390.0) ($410.0) ($413.6) ($420.6) ($394.1) ($413.8)
Financing Activities
Increase(+)/Decrease(-) in notes payable $30.0 ($0.7) $2.0 ($0.2) ($4.0) ($4.8)
Increase(+)/Decrease(-) in line of credit $0.0 $54.0 ($9.4) ($37.5) ($7.0) $0.0
Increase(+)/Decrease(-) in bonds $170.0 $31.8 $46.5 $44.1 $33.7 $35.4
Preferred stock issue(+)/repurchase(-) $0.0 ($4.2) ($3.0) ($5.0) ($7.8) ($8.8)
Payment of common and preferred dividends ($67.0) ($69.9) ($72.8) ($75.8) ($165.9) ($181.3)
Common stock issue(+)/repurchase(-) $0.0 $0.0 $0.0 $0.0 $0.0 $0.0
Net cash provided by financing activities $133.0 $11.1 ($36.7) ($74.4) ($151.1) ($159.5)
Summary
Net change in cash and equivalents ($2.0) $10.0 $8.8 $8.3 $6.4 $6.7
Cash and securities at beginning of the year $102.0 $100.0 $110.0 $118.8 $127.1 $133.5
Cash and securities at end of the year $100.0 $110.0 $118.8 $127.1 $133.5 $140.1
Summary of Key Results for Forecasted Scenarios (Millions Except Percentages and Per Share Data)
Status Quo Industry MicroDrive
Actual Actual Forecast
1. Operations 2023 2023 2024 2025 2026 2027 2028
Free cash flow NA −$210 $30 $92 $132 $210 $220
Return on invested capital 15.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0%
NOPAT/Sales 6.9% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0%
(Total op. capital)/Sales 46.0% 60.0% 60.0% 60.0% 60.0% 60.0% 60.0%
Inventory turnover 5.0 4.1 4.1 4.1 4.1 4.1 4.1
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% 35.2% 35.7% 35.2% 34.2% 33.8% 33.6%
(Net income)/Sales 6.2% 3.8% 4.9% 5.0% 5.0% 5.0% 5.1%
Return on assets (ROA) 11.0% 5.2% 6.9% 6.9% 6.9% 7.0% 7.0%
Return on equity (ROE) 19.0% 8.4% 11.1% 11.0% 10.9% 10.8% 10.8%
Times interest earned 10.0 3.3 6.5 6.4 6.6 6.8 6.9
Line of credit NA $0 $54 $45 $7 $0 $0
Payout ratio 35.0% 31.9% 23.2% 22.5% 21.9% 47.6% 49.7%
Regular dividends/share NA $1.00 $1.05 $1.10 $1.16 $2.67 $2.93
Special dividends/share NA $0.00 $0.00 $0.00 $0.00 $1.45 $1.66
Earnings per share NA $3.13 $4.53 $4.90 $5.29 $5.60 $5.91
3. Estimated intrinsic value
12/31/2023 Estimated intrinsic stock price = $25.24

Mini Case Data

3/1/23
Chapter 12 Mini Case
Hatfield Medical Supply’s stock price had been lagging its industry averages, so its board of directors brought in a new CEO, Jaiden Lee. Lee had brought in Ashley Novak, a finance MBA who had been working for a consulting company, to replace the old CFO, and Lee asked Ashley to develop the financial planning section of the strategic plan. In her previous job, Novak’s primary task had been to help clients develop financial forecasts, and that was one reason Lee hired her.
Novak began as she always did, by comparing Hatfield’s financial ratios to the industry averages. If any ratio was substandard, she discussed it with the responsible manager to see what could be done to improve the situation. The following data show Hatfield’s latest financial statements plus some ratios and other data that Novak plans to use in her analysis.
Hatfield Medical Supply: Balance Sheet (Millions of Dollars), December 31 Hatfield Medical Supply: Income Statement (Millions of Dollars Except per Share)
2023 2023
Cash $90 Sales $9,000.9
Accts. rec. 1,260 Op. costs (excl. depr.) 8,100.9
Inventories 1,440 Depreciation 360.0
Total CA $2,790 EBIT $540.0
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, 2023
Operating Ratios and Data Hatfield Industry Other Ratios Hatfield Industry
(Op. costs)/Sales 90% 88% Profit margin (M) 3.30% 5.60%
Depr./FA 10% 12% Return on assets (ROA) 4.6% 9.5%
Cash/Sales 1% 1% Return on equity (ROE) 10.0% 15.1%
Receivables/Sales 14% 11% Sales/Assets 1.41 1.69
Inventories/Sales 16% 15% Asset/Equity 2.15 1.59
Fixed assets/Sales 40% 32% Debt/TA 28.2% 16.9%
(Acc. pay. & accr.)/Sales 18% 12% (Total liabilities)/(Total assets) 53.5% 37.3%
Tax rate 25% 25% Times interest earned 3.8 11.7
Target WACC 10% 11% P/E ratio 6.9 16.0
Interest rate on debt 8% 7% OP ratio: NOPAT/Sales 4.5% 6.1%
CR ratio: (Total op. capital)/Sales 53.0% 47.0%
ROIC 8.5% 13.0%

image1.png

image2.png

Order a similar assignment, and have writers from our team of experts write it for you, guaranteeing you an A

Order Solution Now