Scenario Summary

Current Values: Final

Changing Cells:
$A$60 Status Quo Final
$E$63 10% 10%
$F$63 8% 8%
$G$63 7% 7%
$H$63 5% 5%
$E$64 76% 76%
$F$64 76% 75%
$E$65 10% 10%
$E$66 10% 10%
$E$67 1% 1%
$E$68 10% 10%
$E$69 20% 17%
$E$70 40% 39%
$F$70 40% 36%
$E$71 4% 4%
$E$72 6% 6%
$E$73 40% 40%
$E$75 28% 28%
$E$76 2% 2%
$E$77 3% 3%
$E$78 67% 67%
$E$80 9.0% 9.0%
$E$81 10.0% 10.0%
$E$82 8.0% 8.0%
$E$83 13.58% 13.58%
$E$85 5% 5%
Result Cells:
$C$157 $3,814 $5,260
$H$366 9.8% 12.7%
$E$384 $2,719 $4,140
$E$385 $22.78 $51.20
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
 Status Quo Status Quo Except Higher Sales Growth Final Except Higher Sales Growth

Status Quo High Sales Growth Final & Hi Sales

10% 12% 12%
8% 10% 10%
7% 8% 8%
5% 6% 6%
76% 76% 76%
76% 76% 75%
10% 10% 10%
10% 10% 10%
1% 1% 1%
10% 10% 10%
20% 20% 17%
40% 40% 39%
40% 40% 36%
4% 4% 4%
6% 6% 6%
40% 40% 40%
28% 28% 28%
2% 2% 2%
3% 3% 3%
67% 67% 67%
9.0% 9.0% 9.0%
10.0% 10.0% 10.0%
8.0% 8.0% 8.0%
13.58% 13.58% 13.58%
5% 5% 5%

$3,814 $4,060 $6,031

9.8% 9.8% 12.7%
$2,719 $2,712 $4,477
$22.78 $22.65 $57.94
 A B C D E F
1 Tool Kit Chapter 12
2
3 Corporate Valuation and Financial Planning
4
5 12-2 Financial Planning at MicroDrive, Inc.
6
7 The process used by MicroDrive to forecast the free cash flows from its operating plan is described in the sections below.
8
9 Setting Up the Model to Forecast Operations
10
11 We begin with MicroDrive's most recent financial statements and selected additional data.
12
13 Figure 12-1
14 MicroDrive’s Most Recent Financial Statements (Millions, Except for Per Share Data)
15 INCOME STATEMENTS BALANCE SHEETS
16 2012 2013 Assets 2012
17 Net sales $ 4,760 $ 5,000 Cash $ 60
18 COGS (excl. depr.) 3,560 3,800 ST Investments 40
19 Depreciation 170 200 Accounts receivable 380
20 Other operating expenses 480 500 Inventories 820
21 EBIT $ 550 $ 500 Total CA $ 1,300
22 Interest expense 100 120 Net PP&E 1,700
23 Pre-tax earnings $ 450 $ 380 Total assets $ 3,000
24 Taxes (40%) 180 152
25 NI before pref. div. $ 270 $ 228 Liabilities and equity
26 Preferred div. 8 8 Accounts payable $ 190
27 Net income $ 262 $ 220 Accruals 280
28 Notes payable 130
29 Other Data Total CL $ 600
30 Common dividends $48 $50 Long-term bonds 1,000
31 Addition to RE $214 $170 Total liabilities $ 1,600
32 Tax rate 40% 40% Preferred stock 100
33 Shares of common stock 50 50 Common stock 500
34 Earnings per share $5.24 $4.40 Retained earnings 800
35 Dividends per share $0.96 $1.00 Total common equity $ 1,300
36 Price per share $40.00 $27.00 Total liabs. & equity $ 3,000
37
38
39
40 The figure below shows all the inputs required to project the financial statements for the scenario that has been selected
41 with the Scenario Manager: Data, What-If Analysis, Scenario Manager. There are two scenarios. The first is named Status
42 Quo because all operating ratios except the sales growth rate are assumed to remain unchanged. The initial sales growth
43 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
44 MicroDrive's management team.
45
46
47 Section 1 shows the inputs required to estimate the items in an operating plan. For each of these inputs, Section 1 shows
48 the industry averages, the actual values for the past two years for MicroDrive, and the forecasted values for the next five
49 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.
 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
A
years. The managers B for future
assumed the inputs C years (except
D the sales growth
E F be equal to the inputs in the
rate) would
first projected year.
50
51
52 MicroDrive's managers assume that sales will eventually level off at a sustaniable constant rate.
53
54 Sections 2 and 3 show the data required to estimate the weighted average cost of capital. Section 4 shows the forecasted
55 growth rate in dividends.
56
57 Note: These inputs are linked throughout the model. If you want to change an input, do it here and not other plac
58 Figure 12-2
59 MicroDrive's Forecast: Inputs for the Selected Scenario
 A B C D E F
60 Status Quo Industry MicroDrive MicroDrive
61 Inputs Actual Actual Forecast
62 1. Operating Ratios 2013 2012 2013 2014 2015
63 Sales growth rate 5% 15% 5% 10% 8%
64 COGS (excl. depr.) / Sales 76% 75% 76% 76% 76%
65 Depreciation / Net PP&E 9% 10% 10% 10% 10%
66 Other op. exp. / Sales 10% 10% 10% 10% 10%
67 Cash / Sales 1% 1% 1% 1% 1%
68 Acc. rec. / Sales 8% 8% 10% 10% 10%
69 Inventory / Sales 15% 17% 20% 20% 20%
70 Net PP&E / Sales 33% 36% 40% 40% 40%
71 Acc. pay. / Sales 4% 4% 4% 4% 4%
72 Accruals / Sales 7% 6% 6% 6% 6%
73 Tax rate 40% 40% 40% 40% 40%
74 2. Capital Structure Actual Market Weights Target Market Weights
75 % Long-term debt 22% 31% 41% 28% 28%
76 % Short-term debt 3% 4% 10% 2% 2%
77 % Preferred stock 0% 3% 3% 3% 3%
78 % Common stock 75% 62% 46% 67% 67%
79 3. Costs of Capital Forecast
80 Rate on LT debt 9.0% 9%
81 Rate on ST debt 10.0% 10%
82 Rate on preferred stock (ignoring flotation costs) 8.0% 8%
83 Cost of equity 13.58% 14%
145 Net operating profit after taxes $300 $330 $356
146 B4. Free Cash Flows
147 Net operating working capital $1,050 $1,155 $1,247
148 Total operating capital $3,050 $3,355 $3,623
149 FCF = NOPAT – Δ op capital −$260 $25 $88
150 B5. Estimated Intrinsic Value
151 Target WACC 11.0% 11.0%
152 Return on invested capital 9.8% 9.8% 9.8%
153 Growth in FCF 252%
154
155 Horizon Value:
156
157 〖𝐇𝐕〗 _𝟐𝟎𝟏𝟖= ( 〖𝐅𝐂 = $3,814 Estimated total intrinsic value
158 𝐅〗 _𝟐𝟎𝟏𝟖
159 Value
(𝟏+𝐠_𝐋))/((𝐖𝐀𝐂𝐂
of Operations: −
160 𝐠_𝐋)) Present value of HV $2,267 Estimated intrinsic value of equity
161 + Present value of FCF $453

162 Value of operations = $2,719 Estimated intrinsic stock price =

163
164
165 12-4 Projecting MicroDrive's Financial Statements
166 Projecting 1 Year of Financial Statements
167
168
169 Figure 12-4, shown below, projects MicroDrive's financial statements for the upcoming year for the Status Quo scenario.
170
171 Operating items are projected in the identical manner as previously projected for the operating plan.
172
 A B C D E F
173 The preliminary short-term financial policy calls for no changes in notes payable, long-term bonds, preferred stock, and
174 common stock, so their values from the previous year are carried over.
175
176 The interest on notes payable and long-term bonds is based on the average amount of debt during the year, defined as the
177 average of the beginning debt (i.e., the debt at the end of the previous year) and the ending debt. An identical process is
178 applied to preferred dividends.
179
180 The preliminary short-term financial policy calls for dividends to grow at the same rate as the long-term sustainable
181 growth rate in earnings (which is the same as sales in the long-term).
182
183 Section 3 in the figure below calculates the additional financing provided by spontaneous liabilities, external sources, and
184 internal sources. The sum of these three sources of financing is the total amount of additional preliminary financing.
185
186 Section 3 also calculates the total amount of additional assets required by the operating plan.
187
188 The difference between the total additional financing and the total additional assets is defined as the financing deficit (if
189 the difference is negative) or the financing surplus (if the difference is positive).
190
191
192 If there is a financing deficit, MicroDrive will draw on a line of credit. MicroDrive assumes that the LOC will be accessed on
the last day of the year, so the new line of credit (reflected in the end-of-year balance) will not accrue enough interest to
193 matter. Therefore, the interest on the LOC will be equal to the balance at the beginning of the year (which is the same as the
194 balance at the end of the previous year).
195
196 If there is a financing surplus, MicroDrive will pay a special dividend.
197
198 Note: Do not change inputs here because these inputs are linked to the ones in Figure 12-2. If you want to change
199 Figure 12-4
200 Projected Financial Statements (Millions of Dollars)
201 Status Quo
202 1. Balance Sheets Most Recent
203 2013 Input Basis for 2014 Forecast
204 Assets
205 Cash $50.0 1.00% × 2014 Sales
206 Accounts receivable 500.0 10.00% × 2014 Sales
207 Inventories 1,000.0 20.00% × 2014 Sales
208 Total current assets $1,550.0
209 Net PP&E 2,000.0 40.00% × 2014 Sales
210 Total assets (TA) $3,550.0
211 Liabilities and equity
212 Accounts payable $200.0 4.00% × 2014 Sales
213 Accruals 300.0 6.00% × 2014 Sales
214 Notes payable 280.0 Carry over from previous year
215 Line of credit 0.0 Draw on LOC if financing deficit
216 Total CL $780.0
217 Long-term bonds 1,200.0 Carry over from previous year
218 Total liabilities $1,980.0
219 Preferred stock $100.0 Carry over from previous year
220 Common stock 500.0 Carry over from previous year
221 Retained earnings 970.0 Old RE + Add. to RE
222 Total common equity $1,470.0
223 Total liabs. & equity $3,550.0
224 Check: TA − Total Liab. & Eq. =
 A B C D E F
225 2. Income Statement Most Recent
226 2013 Input Basis for 2014 Forecast
227 Net sales $5,000.0 110% × 2013 Sales
228 COGS (excl. depr.) 3,800.0 76.00% × 2014 Sales
229 Depreciation 200.0 10.00% × 2014 Net PP&E
230 Other operating expenses $500.0 10.00% × 2014 Sales
231 EBIT $500.0
232 Less: Interest on notes 20.0 10.00% × Avg notes
233 Interest on bonds 100.0 9.00% × Avg bonds
234 Interest on LOC 0.0 11.50% × Beginning LOC
235 Pre-tax earnings $380.0
236 Taxes (40%) 152.0 40.00% × Pretax earnings
237 NI before pref. div. $228.0
238 Preferred div. 8.0 8.00% × Avg pref. stock
239 Net income $220.0
240 Regular common dividends $50.0 105% × 2013 Dividend
241 Special dividends $0.0 Pay if financing surplus
242 Addition to RE $170.0 Net income – Dividends
243
244 3. Elimination of the Financial Deficit or Surplus
245 Increase in spontaneous liabilities (accounts payable and accruals)
246 + Increase in notes payable, long-term bonds, preferred stock, and common stock
247 + Net income minus regular common dividends
248 Increase in financing
249 − Increase in total assets
250 Amount of deficit or surplus financing:
251 If deficit in financing (negative), draw on line of credit Line of credit
252 If surplus in financing (positive), pay special dividend Special dividend
253
254
255 12-4 Analysis and Revision of the Preliminary Plan
256 Projected 5-Year Statements
257
258 Projected Financial Statements (Millions of Dollars)
259 Status Quo
260 1. Balance Sheets Actual Forecast
261 2013 2014 2015 2016
262 Assets
263 Cash $50.0 $55.0 $59.4 $63.6
264 Accounts receivable 500.0 550.0 594.0 635.6
265 Inventories 1,000.0 1,100.0 1,188.0 1,271.2
266 Total current assets $1,550.0 $1,705.0 $1,841.4 $1,970.3
267 Net PP&E 2,000.0 2,200.0 2,376.0 2,542.3
268 Total assets (TA) $3,550.0 $3,905.0 $4,217.4 $4,512.6
269 Liabilities and equity
270 Accounts payable $200.0 $220.0 $237.6 $254.2
271 Accruals 300.0 330.0 356.4 381.3
272 Notes payable 280.0 280.0 280.0 280.0
273 Line of credit 0.0 117.1 181.9 214.2
274 Total CL $780.0 $947.1 $1,055.9 $1,129.8
275 Long-term bonds 1,200.0 1,200.0 1,200.0 1,200.0
276 Total liabilities $1,980.0 $2,147.1 $2,255.9 $2,329.8
 A B C D E F
277 Preferred stock $100.0 $100.0 $100.0 $100.0
278 Common stock 500.0 500.0 500.0 500.0
279 Retained earnings 970.0 1,157.9 1,361.5 1,582.8
280 Total common equity $1,470.0 $1,657.9 $1,861.5 $2,082.8
281 Total liabs. & equity $3,550.0 $3,905.0 $4,217.4 $4,512.6
282 Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00
283 2. Income Statement Actual Forecast
284 2013 2014 2015 2016
285 Net sales $5,000.0 $5,500.0 $5,940.0 $6,355.8
286 COGS (excl. depr.) 3,800.0 4,180.0 4,514.4 4,830.4
287 Depreciation 200.0 220.0 237.6 254.2
288 Other operating expenses $500.0 $550.0 $594.0 $635.6
289 EBIT $500.0 $550.0 $594.0 $635.6
290 Less: Interest on notes 20.0 28.0 28.0 28.0
291 Interest on bonds 100.0 108.0 108.0 108.0
292 Interest on LOC 0.0 0.0 13.5 20.9
293 Pre-tax earnings $380.0 $414.0 $444.5 $478.7
294 Taxes (40%) 152.0 165.6 177.8 191.5
295 NI before pref. div. $228.0 $248.4 $266.7 $287.2
296 Preferred div. 8.0 8.0 8.0 8.0
297 Net income $220.0 $240.4 $258.7 $279.2
298 Regular common dividends $50.0 $52.5 $55.1 $57.9
299 Special dividends $0.0 $0.0 $0.0 $0.0
300 Addition to RE $170.0 $187.9 $203.6 $221.3
301
302 3. Incorporating the Financial Deficit or Surplus
303 Increase in spontaneous liabilities (accounts payable $50.0 $44.0 $41.6
304 + Increase in notes payable, long-term bonds, preferred $0.0 $0.0 $0.0
305 − Previous line of credit $0.0 $117.1 $181.9
306 + Net income minus regular common dividends $187.9 $203.6 $221.3
307 Increase in financing $237.9 $130.5 $81.0
308 − Increase in total assets $355.0 $312.4 $295.2
309 Amount of deficit or surplus financing: −$117.1 −$181.9 −$214.2
310 Line of credit $117.1 $181.9 $214.2
311 Special dividend $0.0 $0.0 $0.0
312
313
314
315
316
317 Statement of Cash Flows (Millions of Dollars)
318 Status Quo Actual Forecast
319 2013 2014
320 Operating Activities
321 Net Income before preferred dividends $228.0 $248.4
322 Noncash adjustments
323 Depreciation $200.0 $220.0
324 Working capital adjustments
325 Increase(-)/Decrease(+) in accounts receivable ($120.0) ($50.0)
326 Increase(-)/Decrease(+) in inventories ($180.0) ($100.0)
327 Increase(-)/Decrease(+) in payables $10.0 $20.0
328 Increase(-)/Decrease(+) in accruals $20.0 $30.0
 A B C D E F
329 Net cash provided (used) by operating activities $158.0 $368.4
330
331 Investing Activities
332 Cash used to acquire fixed assets ($500.0) ($420.0)
333 Sale of short-term investments $40.0 $0.0
334 Net cash provided (used) by investing activities ($460.0) ($420.0)
335
336 Financing Activities
337 Increase(+)/Decrease(-) in notes payable $150.0 $0.0
338 Increase(+)/Decrease(-) in line of credit $0.0 $117.1
339 Increase(+)/Decrease(-) in bonds $200.0 $0.0
340 Preferred stock issue(+)/repurchase(-) $0.0 $0.0
341 Payment of common and preferred dividends ($58.0) ($60.5)
342 Common stock issue(+)/repurchase(-) $0.0 $0.0
343 Net cash provided by financing activities $292.0 $56.6
344 Summary
345 Net change in cash and equivalents ($10.0) $5.0
346 Cash and securities at beginning of the year $60.0 $50.0
347 Cash and securities at end of the year $50.0 $55.0
348
349
350
351 Note: Do not change inputs here because these inputs are linked to the ones in Figure 12-2. If you want to change
352 Figure 12-5 (Status Quo Scenario) or Figure 12-6 (Final Scenario)
353 Summary of Important Inputs and Key Results for Selected Scenario (Millions Except Percentages and Per Share Data)
354
355 Status Quo Industry MicroDrive
356 Panel A: Inputs Actual Actual Forecast
357 A1. Operating Ratios 2013 2013 2014 2015 2016
358 Sales growth rate 5% 5% 10% 8% 7%
359 COGS (excl. depr.) / Sales 76% 76% 76% 76% 76%
360 Inventory / Sales 15% 20% 20% 20% 20%
361 Net PP&E / Sales 33% 40% 40% 40% 40%
362 Panel B: Key Results Industry MicroDrive
363 Actual Actual Forecast
364 B1. Operations 2013 2013 2014 2015 2016
365 Free cash flow NA −$260 $25 $88 $128
366 Return on invested capital 15.0% 9.8% 9.8% 9.8% 9.8%
367 NOPAT/Sales 6.9% 6.0% 6.0% 6.0% 6.0%
368 Total op. capital / Sales 46.0% 61.0% 61.0% 61.0% 61.0%
369 Inventory turnover 5.0 4.0 4.0 4.0 4.0
370 Days sales outstanding 30.0 36.5 36.5 36.5 36.5
371 Fixed asset turnover 3.0 2.5 2.5 2.5 2.5
372 B2. Financing
373 Total liabilities / TA 45.0% 55.8% 55.0% 53.5% 51.6%
374 Net income / Sales 6.2% 4.4% 4.4% 4.4% 4.4%
375 Return on assets (ROA) 11.0% 6.2% 6.2% 6.1% 6.2%
376 Return on equity (ROE) 19.0% 15.0% 14.5% 13.9% 13.4%
377 Times interest earned 10.0 4.2 4.0 4.0 4.1
378 Line of credit NA $0 $117 $182 $214
379 Payout ratio 35.0% 22.7% 21.8% 21.3% 20.7%
380 Regular dividends/share NA $1.00 $1.05 $1.10 $1.16
 A B C D E F
381 Special dividends/share NA $0.00 $0.00 $0.00 $0.00
382 Earnings per share NA $4.40 $4.81 $5.17 $5.58
383 B3. Estimated intrinsic value
384 12/31/2013 Estimated value of operations = $2,719
385 12/31/2013 Estimated intrinsic stock price = $22.78
386
387
388
389
390 12.6 Additional Funds Needed (AFN) Equation Method
391
392 The AFN model forecasts MicroDrive's need for external funds to support its forecasted next year's sales.
393
394
395 Figure 12-7
396 Additional Funds Needed (AFN) (Millions of Dollars)
397 Part A. Inputs and Definitions
398 S0: Most recent year's sales =
399 g: Forecasted growth rate in sales =
400 S1: Next year's sales: S0 × (1 + g) =
401 gS 0
: Change in sales = S1 – S0 = ΔS =
402 A0*: Most recent year's operating assets =
403 0
A * / S 0
: Required assets per dollar of sales =
404 L0*: Most recent year's spontaneous liabilities i.e., payables + accruals =
405 L0* /S0: Spontaneous liabilities per dollar of sales =
406 Profit margin (M): Most recent profit margin = net income/sales =
407 Payout ratio (POR): Most recent year's dividends / net income = % of income paid out =
408 Part B. Additional Funds Needed (AFN) to Support Growth
409
AFN = Required
Increase in
410 Assets − Increase in Spon. Liab.
411 = (A 0
*/S 0
)∆S − (L0*/S0)∆S
412 −
413 = (A0*/S0)(gS0) − (L0*/S0)(gS0)
414 −
415 = (0.710)($500) − (0.10)($500)
416 = $355 − $50.00
417 AFN = $118.00
418
419 750
420 Self-Supporting Growth Rate
421
422 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
423 things constant.
424
425
426 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
427 the value of g that causes AFN to equal zero.
428
 A B C D E F
429
430
431
432 Sustainable g = PM(1 – POR)(S0) = $170.00 =
433 A0
* – L 0
* – PM(1 – POR)S 0 $2,880.00
434
435
436 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
437 zero by changing the growth rate in the blue cell.
438
439
440 12.7 Forecasting When the Ratios Change
441
442 Excess Capacity Adjustments
443
444 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
445 are the steps to determine the AFN if there is excess capacity. 1. Calculate the AFN ignoring the excess capacity. 2. Calculate the required
446 new fixed assets ignoring the excess capacity. 3. Calculate the firm's full capacity sales. 4. Calculate a revised target fixed assets-to-sales
447 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
448 assets when excess capacity is considered. 8. Subtract this difference in required fixed assets from the previously calculated AFN.
449
450 FA capacity was used only to this percent 96%
451 2013 Sales $5,000
452 2014 2014 Sales $5,500
453 2014 2014 Fixed Assets $2,000
454 2013 Fixed assets/Sales = 40.00%
455
456 Required increase in FA if no excess capacity = (Old FA/Sales) (Change in Sales)
457 Required increase in FA if no excess capacity = $200
458
459 Full capacity sales = Actual sales / capacity utilization
460 Full capacity sales = $5,208
461
462 Actual fixed assets / Full capacity sales
463 Target fixed assets/Sales = 38.40%
464
465 Required fixed assets = (Target FA/Sales) (Forecast sales)
466 Required fixed assets = $2,112
467
468 Required increase in fixed assets = $112
469
470 Difference between required increase assuming no
471 excess capacity and required increase if there is excess
472 capacity = $88
473
474 AFN if no excess capacity = $118
475
476 AFN if there is excess capacity = $30
 G H I J K L M
1 5/6/2013
2
3
4
5
6
7
ts operating plan is described in the sections below.
8
9
10
ected additional data. 11
12
13
14
15
16 2013
17 $ 50
18 -
19 500
20 1,000
21 $ 1,550
22 2,000
23 $ 3,550
24
25
26 $ 200
27 300
28 280
29 $ 780
30 1,200
31 $ 1,980
32 100
33 500
34 970
35 $ 1,470
36 $ 3,550
37
38
39
40
al statements for the scenario that has been selected
41 The first is named Status
. There are two scenarios.
umed to remain unchanged.42 The initial sales growth
duct lines. The growth 43rate declines over time until it
d Final because it is the set of inputs chosen by
44
45
46
ating plan. For each of 47
these inputs, Section 1 shows
48 values for the next five
croDrive, and the forecasted
sales growth rate) would49 be equal to the inputs in the
 G H I J K L M
50
51
52
a sustaniable constant rate.
53
54 4 shows the forecasted
erage cost of capital. Section
55
56
you want to change 57an input, do it here and not other places in the model.
58
59
 G H I J K L M
60 MicroDrive
61 Forecast
62 2016 2017 2018
63 7% 5% 5%
64 76% 76% 76%
65 10% 10% 10%
66 10% 10% 10%
67 1% 1% 1% Actual Historical Financing
68 10% 10% 10%
69 20% 20% 20% Long-term debt
70 40% 40% 40% Short-term debt
71 4% 4% 4% Preferred stock
72 6% 6% 6% Market value of equity = (Price x # shares)
73 40% 40% 40%
74
Target Market Weights
75 28% 28% 28% for calculations of the Percent long-term debt
actual capital structures,
76 2% 2% 2% based on market values, Percent short-term debt
77 3% 3% 3% for the past two years. Percent preferred stock
78 67% 67% 67% Percent market value of equity
79 Forecast
80 9% 9% 9%
81 10% 10% 10%
82 8% 8% 8%
83 14% 14% 14%
145 $381 $400 $420 $330
146
147 $1,335 $1,401 $1,472
148 $3,877 $4,071 $4,274
149 $128 $207 $217
150
151 11.0% 11.0% 11.0%
152 9.8% 9.8% 9.8%
153 45.1% 61.7% 5.0%
154
155 Value of operations $2,719
156 + ST investments $0
157Estimated total intrinsic value $2,719
158 − All debt $1,480
159 − Preferred stock $100
160
Estimated intrinsic value of equity $1,139
161 ÷ Number of shares $50

162
Estimated intrinsic stock price = $22.78
163
164
165
166
167
168
169
s for the upcoming year for the Status Quo scenario.
170
171 plan.
projected for the operating
172
 G H I J K L M
173
otes payable, long-term bonds, preferred stock, and
er. 174
175
verage amount of debt176 during the year, defined as the
year) and the ending177
debt. An identical process is
178
179
w at the same rate as 180
the long-term sustainable
181
182
183
ided by spontaneous liabilities, external sources, and
184preliminary financing.
otal amount of additional
185
186
ed by the operating plan.
187
188as the financing deficit (if
dditional assets is defined
s positive). 189
190
191
MicroDrive assumes that the LOC will be accessed on
192accrue
of-year balance) will not enough interest to
193
e at the beginning of the year (which is the same as the
194
195
196
197
re linked to the ones 198 in Figure 12-2. If you want to change inputs, do so in Figure 12-2.
199
200
201
202 Forecast
203 2014
204
205 $55.00
206 $550.00
207 $1,100.00
208 $1,705.00
209 $2,200.00
210 $3,905.00
211
212 $220.00
213 $330.00
214 $280.00
215 $117.10
216 $947.10
217 $1,200.00
218 $2,147.10
219 $100.00
220 $500.00
221 $1,158
222 $1,658
223 $3,905
224 $0.00
 G H I J K L M
225 Forecast
226 2014
227 $5,500.00
228 $4,180.00
229 $220.00
230 $550.00
231 $550.00
232 $28.00
233 $108.00
234 $0.00 Note: If there is an initial balance on the on the LOC, the
235 $414.00 assumption is that the balance will not change until the last
day of the year. Therefore, the interest for the year is the
236 $165.60 based only on the beginning balance.
237 $248.40
238 $8.00
239 $240.40
240 $52.50
241 $0.00
242 $187.90
243
244
245 $50.00
246 $0.00 Note: If there is a LOC in the previous year, then it is necessary to subtract the previous year's line of credit. In other wo
247 $187.90 Note: This is the planned increase in the retained earnings account.
248 $237.90
249 $355.00
250 −$117.10
251 $117.10
252 $0.00
253
254
255
256
257
258
259
260
Forecast
261 2017 2018
262
263 $66.7 $70.1
264 667.4 700.7
265 1,334.7 1,401.5
266 $2,068.8 $2,172.3
267 2,669.4 2,802.9
268 $4,738.2 $4,975.2
269
270 $266.9 $280.3
271 400.4 420.4
272 280.0 280.0
273 172.8 121.3
274 $1,120.2 $1,102.0
275 1,200.0 1,200.0
276 $2,320.2 $2,302.0
 G H I J K L M
277 $100.0 $100.0
278 500.0 500.0
279 1,818.1 2,073.2
280 $2,318.1 $2,573.2
281 $4,738.2 $4,975.2
282 $0.00 $0.00
283
Forecast
284 2017 2018
285 $6,673.6 $7,007.3
286 5,071.9 5,325.5
287 266.9 280.3
288 $667.4 $700.7
289 $667.4 $700.7
290 28.0 28.0
291 108.0 108.0
292 24.6 19.9 Note: If there is an initial balance on the on the LOC, the
293 $506.7 $544.9 assumption is that the balance will not change until the
last day of the year. Therefore, the interest for the year is
294 202.7 217.9 the based only on the beginning balance.
295 $304.0 $326.9
296 8.0 8.0
297 $296.0 $318.9
298 $60.8 $63.8
299 $0.0 $0.0
300 $235.3 $255.1
301
302
303 $31.8 $33.4
304 $0.0 $0.0
305 $214.2 $172.8 Note:
306 $235.3 $255.1 If there is a LOC in the previous year, then it is necessary
to subtract the previous year's line of credit. In other
307 $52.8 $115.6
words, this is like paying off the old line of credit on the
308 $225.6 $236.9 last day of the year and then drawing on a new line of
309 −$172.8 −$121.3 credit.
310 $172.8 $121.3
311 $0.0 $0.0
312
313
314
315
316
317
318 Forecast Forecast Forecast Forecast
319 2015 2016 2017 2018
320
321 $266.7 $287.2 $304.0 $326.9
322
323 $237.6 $254.2 $266.9 $280.3
324
325 ($44.0) ($41.6) ($31.8) ($33.4)
326 ($88.0) ($83.2) ($63.6) ($66.7)
327 $17.6 $16.6 $12.7 $13.3
328 $26.4 $24.9 $19.1 $20.0
 G H I J K L M
329 $416.3 $458.3 $507.4 $540.5
330
331
332 ($413.6) ($420.6) ($394.1) ($413.8)
333 $0.0 $0.0 $0.0 $0.0
334 ($413.6) ($420.6) ($394.1) ($413.8)
335
336
337 $0.0 $0.0 $0.0 $0.0
338 $64.8 $32.3 ($41.4) ($51.6)
339 $0.0 $0.0 $0.0 $0.0
340 $0.0 $0.0 $0.0 $0.0
341 ($63.1) ($65.9) ($68.8) ($71.8)
342 $0.0 $0.0 $0.0 $0.0
343 $1.7 ($33.6) ($110.2) ($123.4)
344
345 $4.4 $4.2 $3.2 $3.3
346 $55.0 $59.4 $63.6 $66.7
347 $59.4 $63.6 $66.7 $70.1
348
349
350
re linked to the ones351 in Figure 12-2. If you want to change inputs, do so in Figure 12-2.
352
353
(Millions Except Percentages and Per Share Data)
354
MicroDrive 355 Status Quo Industry MicroDrive
356
Forecast Panel A: Inp Actual Actual Forecast
357 2017 2018 A1. Operatin 2013 2013 2014
358 5% 5% Sales grow 5% 5% 10%
359 76% 76% COGS (excl 76% 76% 76%
360 20% 20% Inventory 15% 20% 20%
361 40% 40% Net PP&E 33% 40% 40%
MicroDrive 362 Panel B: Key Industry MicroDrive
363
Forecast Actual Actual Forecast
364 2017 2018 B1. Operatio 2013 2013 2014
365 $207 $217 Free cash fl NA −$260 $25
366 9.8% 9.8% Return on in 15.0% 9.8% 9.8%
367 6.0% 6.0% NOPAT/Sale 6.9% 6.0% 6.0%
368 61.0% 61.0% Total op. cap 46.0% 61.0% 61.0%
369 4.0 4.0 Inventory tu 5.0 4.0 4.0
370 36.5 36.5 Days sales o 30.0 36.5 36.5
371 2.5 2.5 Fixed asset 3.0 2.5 2.5
372 B2. Financing
373 49.0% 46.3% Total liabili 45.0% 55.8% 55.0%
374 4.4% 4.6% Net income / 6.2% 4.4% 4.4%
375 6.2% 6.4% Return on as 11.0% 6.2% 6.2%
376 12.8% 12.4% Return on eq 19.0% 15.0% 14.5%
377 4.2 4.5 Times intere 10.0 4.2 4.0
378 $173 $121 Line of credi NA $0 $117
379 20.5% 20.0% Payout ratio 35.0% 22.7% 21.8%
380 $1.22 $1.28 Regular div NA $1.00 $1.05
 G H I J K L M
381 $0.00 $0.00 Special divi NA $0.00 $0.00
382 $5.92 $6.38 Earnings per NA $4.40 $4.81
383 B3. Estimated intrinsic value
384 ### Estimated value of operations =
385 ### Estimated intrinsic stock price =
386
387
388
389
390
391
392
port its forecasted next year's sales.
393
394
395
396
397
398 $5,000
399 10.00%
400 $5,500
401 $500
402 $3,550
403 71.00%
404
aneous liabilities i.e., payables + accruals = $500
405 10.00%
406 4.40%
ends / net income = %407 of income paid out = 22.73%
408
409
Addition to Retained
410 − Earnings.
411 − S1 × M × (1 – POR)
412 −
413 − (1+g)S0 × M × (1 – POR)
414 −
415 − $5,500(0.044)(1 – 0.2273)
416 − $187.00
417
418
419
420
421
422
g external funds, i.e., the value of g that forces AFN = 0, holding other
423
424
425
olving the equation as 426
shown on the 3rd row above g, then finding
427
428
 G H I J K L M
429
430
431
432 5.90%
433
434
435
using Goal Seek. In the436
figure above, set the AFN in the orange cell to
437
438
439
440
441
442
443
but this is not necessarily correct. For instance, suppose the firm is
444
d achieve a greater level of production with its fixed assets. Here
late the AFN ignoring 445
the excess capacity. 2. Calculate the required
446 a revised target fixed assets-to-sales
ull capacity sales. 4. Calculate
6. Calculate the increase
447in required fixed assets given excess
result when excess capacity is ignored versus the required fixed
448
in required fixed assets from the previously calculated AFN.
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
 N O P Q R S T U
60
61
62
63
64
65
66
67
68 2012 2013
69
Long-term debt $1,000 $1,200
70
Short-term debt $130 $280
71
Preferred stock $100 $100
72 x # shares)
ket value of equity = (Price $2,000 $1,350
73 Total $3,230 $2,930
74
Percent75long-term debt 31% 41%
76
Percent short-term debt 4% 10%
Percent 77
preferred stock 3% 3%
Percent market78 value of equity 62% 46%
79 Total 100% 100%
80
81
82
83
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
 N O P Q R S T U
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
s 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
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
 N O P Q R S T U
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355 MicroDrive
356 Forecast
357 2015 2016 2017 2018
358 8% 7% 5% 5%
359 76% 76% 76% 76%
360 20% 20% 20% 20%
361 40% 40% 40% 40%
362 MicroDrive
363 Forecast
364 2015 2016 2017 2018
365 $88 $128 $207 $217
366 9.8% 9.8% 9.8% 9.8%
367 6.0% 6.0% 6.0% 6.0%
368 61.0% 61.0% 61.0% 61.0%
369 4.0 4.0 4.0 4.0
370 36.5 36.5 36.5 36.5
371 2.5 2.5 2.5 2.5
372
373 53.5% 51.6% 49.0% 46.3%
374 4.4% 4.4% 4.4% 4.6%
375 6.1% 6.2% 6.2% 6.4%
376 13.9% 13.4% 12.8% 12.4%
377 4.0 4.1 4.2 4.5
378 $182 $214 $173 $121
379 21.3% 20.7% 20.5% 20.0%
380 $1.10 $1.16 $1.22 $1.28
 N O P Q R S T U
381 $0.00 $0.00 $0.00 $0.00
382 $5.17 $5.58 $5.92 $6.38
383
384 $2,719
385 $22.78
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
 V W X Y
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
redit on the last day of the year and then drawing on a new line of credit.
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
SECTION 12-6
SOLUTIONS TO SELF-TEST

Suppose MicroDrive's growth rate in sales is forecast as 15% rather than 10%. If all ratios
stay the same, what is the AFN?

Sales growth rate 15%

S0 $3,000 million
A0*/ S0 66.666%
L0*/ S0 6.667%
Profit margin (M) 3.783%
Payout ratio 50.670%

Δ Sales $450.00 million

S1 $3,450.00 million

AFN $205.62 million

 Financing Feedback and Specifying the Capital Structure

12-5c 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
2012 2013 Assets 2012 2013
Net sales $ 4,760 $ 5,000 Cash $ 60 $ 50
COGS (excl. depr.) 3,560 3,800 ST Investments 40 -
Depreciation 170 200 Accounts receivable 380 500
Other operating expenses 480 500 Inventories 820 1,000
EBIT $ 550 $ 500 Total CA $ 1,300 $ 1,550
Interest expense 100 120 Net PP&E 1,700 2,000
Pretax earnings $ 450 $ 380 Total assets $ 3,000 $ 3,550
Taxes (40%) 180 152
NI before pref. div. $ 270 $ 228 Liabilities and equity
Preferred div. 8 8 Accounts payable $ 190 $ 200
Net income $ 262 $ 220 Accruals 280 300
Notes payable 130 280
Other Data Total CL $ 600 $ 780
Common dividends $48 $50 Long-term bonds 1,000 1,200
Addition to RE $214 $170 Total liabilities $ 1,600 $ 1,980
Tax rate 40% 40% Preferred stock 100 100
Shares of common stock 50 50 Common stock 500 500
Earnings per share $5.24 $4.40 Retained earnings 800 970
Dividends per share $0.96 $1.00 Total common equit $ 1,300 $ 1,470
Price per share $40.00 $27.00 Total liabs. & equity $ 3,000 $ 3,550

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.

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. Operating Ratios 2013 2012 2013 2014 2015 2016
Sales growth rate 5% 15% 5% 10% 8% 7%
COGS (excl. depr.) / Sales 76% 75% 76% 76% 76% 76%
Depreciation / Net PP&E 9% 10% 10% 10% 10% 10%
Other op. exp. / Sales 10% 10% 10% 10% 10% 10%
Cash / Sales 1% 1% 1% 1% 1% 1%
Acc. rec. / Sales 8% 8% 10% 10% 10% 10%
Inventory / Sales 15% 17% 20% 20% 20% 20%
Net plant / Sales 33% 36% 40% 40% 40% 40%
Acc. pay. / Sales 4% 4% 4% 4% 4% 4%
Accruals / Sales 7% 6% 6% 6% 6% 6%
Tax rate 40% 40% 40% 40% 40% 40%
2. Capital Structure Actual Market Weights Target Market Weights
% Long-term debt 22% 31% 41% 28% 28% 28%
% Short-term debt 3% 4% 10% 2% 2% 2%
% Preferred stock 0% 3% 3% 3% 3% 3%
% Common stock 75% 62% 46% 67% 67% 67%
3. Costs of Capital Forecast
Rate on LT bonds, rLTD 9.0% 9% 9%
Rate on ST debt, rSTD 10.0% 10% 10%
Rate on preferred stock (ignoring flotation costs), r ps 8.0% 8% 8%
Cost of equity, rs 13.58% 14% 14%
4. Target Dividend Policy Actual
Growth rate of dividends 11% 4.2% 5% 5% 5%
5. Capital Structure Choices
% Long-term debt, wLTD 41.0% 38.4% 35.8% 33.2%
% Short-term debt, wSTD 9.6% 8.0% 6.5% 5.0%
% Preferred stock, wps 3.4% 3.3% 3.2% 3.2%
% Common stock, ws 46.1% 50.3% 54.4% 58.6%

The following projections incorporate the impact of financing feedback. They also ensure that the actual capital structure matches the target capital
structure. Following are explanations of these two issues, beginning with the capital structure.

Implementing the Target Capital Structure

The preliminary financial policy held external financing constant—with no additional borrowing or repayment of debt (other than the line of credit)
and no new issues or repurchases of preferred stock or common stock. However, this ignores the target capital structure. Fortunately, there is a simple
way to implement the target capital structure in the projected statements.
Notice that the WACC depends on the target weights, not the actual weights. This means the value of operations does not depend on the actual amounts
of debt and preferred stock. Therefore, it is easy to estimate the value of operations for each year of the forecast (starting at the horizon and working
backward) before specifying the dollar amounts of debt and preferred stock. Given the yearly value of operations, the yearly values of debt and
preferred stock can be found by multiplying their target weights by the value of operations.

We implement this approach in the figure below.

Incorporating Financing Feedback

The basic model assumed that no interest would accrue on the line of credit because the LOC would be added at the end of the year. However, if interest
is calculated on the LOC’s average balance during the year, which is more realistic, here is what happens:

1 The line of credit required to make the balance sheets balance is added to the balance sheet.
2 Interest expense increases due to the LOC.
3 Net income decreases because interest expenses are higher.
4 Internally generated financing decreases because net income decreases.
5 The financing deficit increases because internally generated financing decreases.
6 An additional amount of the LOC is added to the balance sheets to make them balance.
7 Go to step 2 and repeat the loop.

If you were to go through these steps manually, then each time you add some additional LOC in Step 6, the amount would be less than the previous
amount because the additional LOC is just large enough to cover the additional interest estimated in Step 2. If you repeated this process manually
enough times, then the change in the additional LOC would become so small that it would be neglible. In fact, sometimes it is possible to set Excel to
Iterate automatically and determine the correct amount of debt. However, in complicated models it is possible for this automatic iteration to cause
Excel to "freeze." Fortunately, there is a simple solution.

As noted above, the additional LOC required by each additional iteration becomes smaller and smaller. In fact, the additional LOC eventually converges
to zero. Because the LOC converges to a value, it is possible to use a relatively simple formula to calculate the final LOC needed when there is financing
feeback. This formula is based on the amount of LOC needed if feedback is ignored and on the interest rates (and preferred dividend yield). We explain
this formula below at the point where we specify the final LOC.

The silver rows in the tables indicate the rows that differ from those in the basic model in the worksheet named "Chapter".

Projected Financial Statements (Millions of Dollars)

Status Quo
1. Balance Sheets Actual Forecast
2013 2014 2015 2016
Assets
Cash $50.0 $55.0 $59.4 $63.6
Accounts receivable 500.0 550.0 594.0 635.6
Inventories 1,000.0 1,100.0 1,188.0 1,271.2
Total current assets $1,550.0 $1,705.0 $1,841.4 $1,970.3
Net PP&E 2,000.0 2,200.0 2,376.0 2,542.3
Total assets (TA) $3,550.0 $3,905.0 $4,217.4 $4,512.6
Liabilities and equity
Accounts payable $200.0 $220.0 $237.6 $254.2
Accruals 300.0 330.0 356.4 381.3
Notes payable (wSTD ⨯ Vop) 280.0 240.7 211.2 173.8
Line of credit (After adjustment for feedback effects) 0.0 213.4 297.9 375.1
Total CL $780.0 $1,004.2 $1,103.2 $1,184.4
Long-term bonds (wLTD ⨯ Vop) 1,200.0 1,148.0 1,156.5 1,148.0
Total liabilities $1,980.0 $2,152.2 $2,259.6 $2,332.4
Preferred stock (wps ⨯ Vop) $100.0 $99.7 $105.0 $109.5
Common stock 500.0 500.0 500.0 500.0
Retained earnings 970.0 1,153.1 1,352.8 1,570.7
Total common equity $1,470.0 $1,653.1 $1,852.8 $2,070.7
Total liabs. & equity $3,550.0 $3,905.0 $4,217.4 $4,512.6
Check: TA − Total Liab. & Eq. = $0.00 $0.00 $0.00
2. Income Statement Actual Forecast
2013 2014 2015 2016
Net sales $5,000.0 $5,500.0 $5,940.0 $6,355.8
COGS (excl. depr.) 3,800.0 4,180.0 4,514.4 4,830.4
Depreciation 200.0 220.0 237.6 254.2
Other operating expenses $500.0 $550.0 $594.0 $635.6
EBIT $500.0 $550.0 $594.0 $635.6
Less: Interest on notes payable, based on average NP and r STD 20.0 26.0 22.6 19.2
Interest on bonds, based on average LT bonds and r LTD 100.0 105.7 103.7 103.7
 Interest on LOC, based on average LOC and r LOC = rSTD +1.5% 0.0 12.3 29.4 38.7
Pre-tax earnings $380.0 $406.0 $438.3 $473.9
Taxes (40%) 152.0 162.4 175.3 189.6
NI before pref. div. $228.0 $243.6 $263.0 $284.4
Preferred dividend, based on average preferred stock and r ps 8.0 8.0 8.2 8.6
Net income $220.0 $235.6 $254.8 $275.8
Regular common dividends $50.0 $52.5 $55.1 $57.9
Special dividends $0.0 $0.0 $0.0 $0.0
Addition to RE $170.0 $183.1 $199.7 $217.9

3. Eliminating the Financial Deficit or Surplus

Increase in spontaneous liabilities (accounts payable and accruals) $50.0 $44.0 $41.6
+ Increase in notes payable, long-term bonds, preferred stock and common stock −$91.6 −$15.8 −$41.4
− Previous line of credit $0.0 $213.4 $297.9
+ Planned increase in retained earnings
+ After-tax operating income: EBIT (1-T) $330.0 $356.4 $381.3
− After-tax interest on notes payable (INTSTD x (1-T) $15.6 $13.6 $11.5
− After-tax interest on bonds (INTLTD x (1-T) $63.4 $62.2 $62.2
− After-tax interest on previous LOC: (r LOC x 0.5 x LOCt-1 x (1-T) $0.0 $7.4 $10.3
− Preferred dividends $8.0 $8.2 $8.6
− Regular common dividends $52.5 $55.1 $57.9
Total planned increase in the retained earnings account $190.5 $209.9 $230.8

Increase in financing $148.9 $24.7 −$66.9

− Increase in total assets $355.0 $312.4 $295.2
Amount of unadjusted deficit or surplus financing: −$206.1 −$287.7 −$362.1

If there is a surplus (the financing need is positive), pay a special dividend: $0.0 $0.0 $0.0

If there is a deficit (the financing need is positive), draw on the LOC:

Unadjusted line of credit = $206.1 $287.7 $362.1
Adjustment factor (see note below) = 0.97 0.97 0.97
Adjusted line of credit = Unadjusted LOC / Adjustment factor = $213.4 $297.9 $375.1

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

2013 2014 2015 2016
Net operating working capital $790 $1,050 $1,155 $1,247 $1,335
Total net operating capital $2,490 $3,050 $3,355 $3,623 $3,877
NOPAT $330 $300 $330 $356 $381
FCF -$260 $25 $88 $128
Growth rate in FCF 252.0% 45.1%
Target WACC 10.97% 10.97% 10.97%
Horizon value: VHV = Vop,2018 = [FCFt208 (1+g)]/(WACC-g).
Value of operations: Vop,t-1 = [FCFt +Vop,t]/(1+WACC). $2,719 $2,992 $3,233 $3,460
Choice of long-term bonds (w LTD ⨯ Vop) $1,200 $1,148 $1,156 $1,148
Choice of notes payable (wSTD ⨯ Vop) $280 $241 $211 $174
Choice of preferred stock (wps ⨯ Vop) $100 $100 $105 $110

5. Estimating the Intrinsic Stock Price

2013
Value of operations $2,719
+ ST investments $0
Estimated total intrinsic value $2,719
− All debt $1,480
− Preferred stock $100
Estimated intrinsic value of equity $1,139
÷ Number of shares $50
Estimated intrinsic stock price = $22.78

Statement of Cash Flows (Millions of Dollars)

Status Quo Actual Forecast Forecast Forecast
2013 2014 2015 2016
Operating Activities
Net Income before preferred dividends $228.0 $243.6 $263.0 $284.4
Noncash adjustments
Depreciation $200.0 $220.0 $237.6 $254.2
Working capital adjustments
Increase(-)/Decrease(+) in accounts receivable ($120.0) ($50.0) ($44.0) ($41.6)
Increase(-)/Decrease(+) in inventories ($180.0) ($100.0) ($88.0) ($83.2)
Increase(-)/Decrease(+) in payables $10.0 $20.0 $17.6 $16.6
Increase(-)/Decrease(+) in accruals $20.0 $30.0 $26.4 $24.9
Net cash provided (used) by operating activities $158.0 $363.6 $412.6 $455.4

Investing Activities
Cash used to acquire fixed assets ($500.0) ($420.0) ($413.6) ($420.6)
Sale of short-term investments $40.0 $0.0 $0.0 $0.0
Net cash provided (used) by investing activities ($460.0) ($420.0) ($413.6) ($420.6)

Financing Activities
Increase(+)/Decrease(-) in notes payable $150.0 ($39.3) ($29.5) ($37.5)
Increase(+)/Decrease(-) in line of credit $0.0 $213.4 $84.5 $77.1
Increase(+)/Decrease(-) in bonds $200.0 ($52.0) $8.4 ($8.5)
Preferred stock issue(+)/repurchase(-) $0.0 ($0.3) $5.3 $4.5
Payment of common and preferred dividends ($58.0) ($60.5) ($63.3) ($66.5)
Common stock issue(+)/repurchase(-) $0.0 $0.0 $0.0 $0.0
Net cash provided by financing activities $292.0 $61.4 $5.4 ($30.7)
Summary
Net change in cash and equivalents ($10.0) $5.0 $4.4 $4.2
Cash and securities at beginning of the year $60.0 $50.0 $55.0 $59.4
Cash and securities at end of the year $50.0 $55.0 $59.4 $63.6
 Summary of Key Results for Forecasted Scenarios (Millions Except Percentages and Per Share Data)
Status Quo Industry MicroDrive
Actual Actual Forecast
1. Operations 2013 2013 2014 2015 2016 2017
Free cash flow NA −$260 $25 $88 $128 $207
Return on invested capital 15.0% 9.8% 9.8% 9.8% 9.8% 9.8%
NOPAT/Sales 6.9% 6.0% 6.0% 6.0% 6.0% 6.0%
Total op. capital / Sales 46.0% 61.0% 61.0% 61.0% 61.0% 61.0%
Inventory turnover 5.0 4.0 4.0 4.0 4.0 4.0
Days sales outstanding 30.0 36.5 36.5 36.5 36.5 36.5
Fixed asset turnover 3.0 2.5 2.5 2.5 2.5 2.5
2. Financing
Total liabilities / TA 45.0% 55.8% 55.1% 53.6% 51.7% 49.0%
Net income / Sales 6.2% 4.4% 4.3% 4.3% 4.3% 4.4%
Return on assets (ROA) 11.0% 6.2% 6.0% 6.0% 6.1% 6.2%
Return on equity (ROE) 19.0% 15.0% 14.3% 13.8% 13.3% 12.8%
Times interest earned 10.0 4.2 3.8 3.8 3.9 4.1
Line of credit NA $0 $213 $298 $375 $416
Payout ratio 35.0% 22.7% 22.3% 21.6% 21.0% 20.7%
Regular dividends/share NA $1.00 $1.05 $1.10 $1.16 $1.22
Special dividends/share NA $0.00 $0.00 $0.00 $0.00 $0.00
Earnings per share NA $4.40 $4.71 $5.10 $5.52 $5.88
3. Estimated intrinsic value
12/31/2013 Estimated intrinsic stock price = $22.78
 5/6/2013

so ensures that the actual

scenario, go to the

scenario, go to the

selected in the worksheet

st is named Status Quo
wth rate was chosen by
y levels off at a sustainable

1 shows the industry

s. The managers assumed
ecasted growth rate in

scenario, go to the

MicroDrive
Forecast
2017 2018
5% 5%
76% 76%
10% 10%
10% 10%
1% 1% Actual Historical Financing
10% 10% 2012 2013
20% 20% Long-term debt $1,000 $1,200
40% 40% Short-term debt $130 $280
4% 4% Preferred stock $100 $100
6% 6% Market value of equity = (Price x # shares) $2,000 $1,350
40% 40% Total $3,230 $2,930
et Market Weights
28% 28% for calculations of the Percent long-term debt 31% 41%
actual capital structures,
2% 2% based on market values, Percent short-term debt 4% 10%
3% 3% for the past two years. Percent preferred stock 3% 3%
67% 67% Percent market value of equity 62% 46%
Forecast Total 100% 100%
9% 9%
10% 10%
8% 8%
14% 14%

5% 5%

30.6% 28%
3.5% 2%
3.1% 3%
62.8% 67%

structure matches the target capital

of debt (other than the line of credit)

tructure. Fortunately, there is a simple

oes not depend on the actual amounts

(starting at the horizon and working
s, the yearly values of debt and
 he end of the year. However, if interest

d to the balance sheet.

e them balance.

nt would be less than the previous

u repeated this process manually
metimes it is possible to set Excel to
or this automatic iteration to cause

e additional LOC eventually converges

al LOC needed when there is financing
preferred dividend yield). We explain

Forecast
2017 2018

$66.7 $70.1
667.4 700.7
1,334.7 1,401.5
$2,068.8 $2,172.3
2,669.4 2,802.9
$4,738.2 $4,975.2

$266.9 $280.3
400.4 420.4
127.6 76.3
415.9 459.2
$1,210.8 $1,236.2
1,111.3 1,068.0
$2,322.1 $2,304.2
$112.0 $114.4
500.0 500.0
1,804.1 2,056.6
$2,304.1 $2,556.6
$4,738.2 $4,975.2
$0.00 $0.00
Forecast
2017 2018
$6,673.6 $7,007.3
5,071.9 5,325.5
266.9 280.3
$667.4 $700.7
$667.4 $700.7
15.1 10.2
101.7 98.1
 45.5 50.3
$505.1 $542.2
202.1 216.9
$303.1 $325.3
8.9 9.1
$294.2 $316.2
$60.8 $63.8
$0.0 $0.0
$233.5 $252.4

$31.8 $33.4
−$80.5 −$92.1
$375.1 $415.9 Note: We subtract the previous LOC because the plan does not call for any projected LOC unless necessary.

$400.4 $420.4
$9.0 $6.1
$61.0 $58.8
$12.9 $14.3 Note: Note: interest expense is incurred on the planned LOC. Because the plan
$8.9 $9.1 does not call for any LOC, the average balance is equal to
$60.8 $63.8 (LOCt-1 + 0)/2 = 0.5*LOCt-1.
$247.8 $268.3

−$176.0 −$206.4 Note:

The increase in financing is equal to the sum of spontaneous liabilities,
$225.6 $236.9 planned external financing, and the planned addition to the retained
−$401.6 −$443.3 earnings account.

$0.0 $0.0

$401.6 $443.3
0.97 0.97
$415.9 $459.2

he new interest will be incurred on only

djusted LOC.

apital structure.

f all free cash flows beyond the

orizon is equal to the value of all free
ACC. But this present value is
discounted back one period at the
ward one year at a time.

ultiplied by the value of operations.

Forecast
2017 2018
$1,401 $1,472
 $4,071 $4,274
$400 $420
$207 $217
61.7% 5.0%
10.97% 10.97%
$3,814
$3,633 $3,814 Note: ⟹ The value of operations at the horizon is equal to the horizon value.
$1,111 $1,068
$128 $76
$112 $114

Forecast Forecast
2017 2018

$303.1 $325.3

$266.9 $280.3

($31.8) ($33.4)
($63.6) ($66.7)
$12.7 $13.3
$19.1 $20.0
$506.5 $538.8

($394.1) ($413.8)
$0.0 $0.0
($394.1) ($413.8)

($46.2) ($51.3)
$40.9 $43.2
($36.7) ($43.3)
$2.5 $2.4
($69.6) ($72.9)
$0.0 $0.0
($109.2) ($121.7)

$3.2 $3.3
$63.6 $66.7
$66.7 $70.1
 2018
$217
9.8%
6.0%
61.0%
4.0
36.5
2.5

46.3%
4.5%
6.4%
12.4%
4.4
$459
20.2%
$1.28
$0.00
$6.32
Mini Case Data

Figure 12-MC-1. Financial Statements and Other Data (Millions except per share data)
Hatfield Medical Supplies: Balance Sheet (Millions of Hatfield Medical Supplies: Income Statement (Millions
Dollars), 12/31/2013 of Dollars Except per Share)

Cash $20 Sales

Accts. rec. $280 Op. costs (excl. depr.)
Inventories $400 Depreciation
Total CA $700 EBIT

Net fixed assets $500 Interest

Total assets $1,200 Pretax earnings
Taxes (40%)
Accts. pay. & accruals $80 Net income
Line of credit $0
Total CL $80 Dividends
Long-term debt $500 Add. to RE
Total liabilities $580 Common shares
Common stock $420 EPS
Retained earnings $200 DPS
Total common equ. $620 Ending stock price
Total liab. & equity $1,200

Selected Ratios and Other Data, 2013

Hatfield Industry
Op. costs/Sales 90% 88% Total liability/Total assets
Depr./FA 10% 12% Times interest earned
Cash/Sales 1% 1% Return on assets (ROA)
Receivables/Sales 14% 11% Profit margin (M)
Inventories/Sales 20% 15% Sales/Assets
Fixed assets/Sales 25% 22% Assets/Equity
Acc. pay. & accr. / Sales 4% 4% Return on equity (ROE)
Tax rate 40% 40% P/E ratio
ROIC 8.0% 12.5%
NOPAT/Sales 4.5% 5.6%
Total op. capital/Sales 56.0% 45.0%

Additional Data 2014

Exp. Saled growth rate 10%
Interest rate on LT debt 8%
Target WACC 9%
 5/6/2013

ome Statement (Millions

per Share)
2013
$2,000.0
$1,800.0
$50.0
$150.0

$40.0
$110.0
$44.0
$66.0

$20.0
$46.0
10.0
$6.6
$2.0
$52.80

Hatfield Industry
48.3% 36.7%
3.8 8.9
5.5% 10.2%
3.30% 4.99%
1.67 2.04
1.94 1.58
10.6% 16.1%
8.0 16.0