excel case 2- build a model

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

financial statements of Zieber Corporation. Forecast Zeiber’s 2024 income statement and balance sheets. Use the following assumptions: (1)

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

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

rate. (7) The tax rate is

. 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

. 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.

25%

11%

12%

(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.)

2023

Sales

Growth

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)

% of sales

Mike Ehrhardt: Percent of forecasted sales.

% of sales

Zero in preliminary forecast

$345,914

a. What are the forecasted levels of the line of credit and special dividends?

Mike Ehrhardt: Percent of forecasted sales.

Mike Ehrhardt: In final forecast, the line of credit is equal to the amount specified in the section below the balance sheets.

Special dividends

Build a Model Problem

3/13/23

Chapter:

Problem:

Start with the partial model in the file Ch12 P10 Build a Model.xlsx on the textbook’s Web site, which contains the

2023

Sales

11%

25%

12%

Key Input Data:

Used in the

forecast

Tax rate

Dividend growth rate

Rate on notes payable-term debt, rstd

Rate on long-term debt, rd

Rate on line of credit, rLOC

a. What are the forecasted levels of the line of credit and special dividends?

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 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)

$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

EBT

$41,828

Taxes (25%)

$10,457

Net Income

$31,371

Common dividends (regular dividends)

$12,554

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

Assets:

Cash

$18,206

Mike Ehrhardt: Percent of forecasted sales.

Accounts Receivable

$100,133

Inventories

$45,515

Total current assets

$163,854

Fixed assets

$182,060

Total assets

$345,914

Liabilities and equity

Accounts payable

$31,861

Accruals

$27,309

Line of credit

Total current liabilities

$59,170

Long-term debt

$120,000

Total liabilities

$179,170

Common stock

$60,000

Retained Earnings

$106,745

Previous + Addition to retained earnings

Total common equity

$166,745

Total liabilities and equity

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

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: Line of credit is zero in preliminary forecast.

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.

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

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

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

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%
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%

(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%

(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%
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%
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%

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%

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

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

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

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

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,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,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

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,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

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

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

Assets

0
Input
0

Assets

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

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

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
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

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

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

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

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

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

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.

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

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.

− 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
$142.5
$210.5
$248.4
$213.7
$169.3

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
$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

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

Minimum LOC in forecast
$0
$143
$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

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

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

Actual
Actual
Forecast

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%

(COGS excl. depr.)/Sales
76%
78%
78%
78%
78%
78%
78%

(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%

(Net PP&E)/Sales
33%
40%
40%
40%
40%
40%
40%

(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

Industry
MicroDrive

Panel B: Key Output
Actual
Actual
Forecast

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%

Free cash flow
NA
−$210
$30
$92
$132
$210
$220

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

Special dividends
NA
$0
$0
$0
$0
$0
$47

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%

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

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 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%

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

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%

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

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

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.

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%

$5,000
million

A0*/ S0

72.000%

L0*/ S0

12.000%

Profit margin (M)

4.960%

Payout ratio

24.194%

Δ Sales

$750.00
million

$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.

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

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

Total CL

$1,620

Dividends

$100

Long-term debt

1,800

Add. to RE

$197

Total liabilities

$3,420

Common shares

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

image2

Turn in your highest-quality paper
Get a qualified writer to help you with

“ excel case 2- build a model ”

Get high-quality paper

Guarantee! All work is written by expert writers!

Still stressed from student homework?

Get quality assistance from academic writers!

Order now