Complete the following problems from Chapter 12 in Financial Management: Theory and Practice:

Chapter 12:

Topics: Additional Funds Needed, Forecasted Statements and Ratios, Financing Deficits

Problems: 12-2, 12-4, 12-5, 12-7, 12-8, 12-9

General Instructions:

Use the Topic 4 Excel Resource (if needed).

Please show all work for each problem.

You are not required to submit this assignment to Turnitin.

A 1 Tool Kit B C D E F Chapter 12 2 Corporate Valuation and Financial Planning 3 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) BALANCE SHEETS 15 INCOME STATEMENTS 2012 2013 Assets 2012 16 $ 4,760 $ 5,000 Cash $ 60 17 Net sales 3,560 3,800 ST Investments 40 18 COGS (excl. depr.) 170 200 Accounts receivable 380 19 Depreciation 480 500 Inventories 820 20 Other operating expenses EBIT $ 550 $ 500 Total CA $ 1,300 21 100 120 Net PP&E 1,700 22 Interest expense Pre-tax earnings $ 450 $ 380 Total assets $ 3,000 23 180 152 24 Taxes (40%) NI before pref. div. $ 270 $ 228 Liabilities and equity 25 8 8 Accounts payable $ 190 26 Preferred div. Net income $ 262 $ 220 Accruals 280 27 Notes payable 130 28 Total CL $ 600 29 Other Data $48 $50 Long-term bonds 1,000 30 Common dividends $214 $170 Total liabilities $ 1,600 31 Addition to RE 40% 40% Preferred stock 100 32 Tax rate 50 50 Common stock 500 33 Shares of common stock $5.24 $4.40 Retained earnings 800 34 Earnings per share $0.96 $1.00 Total common equity $ 1,300 35 Dividends per share $40.00 $27.00 Total liabs. & equity $ 3,000 36 Price per share 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 44 eventually levels off at a sustainable rate. The other scenario is named Final because it is the set of inputs chosen by 45 MicroDrive's management team. 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 years. The managers assumed the inputs rate) would A B for future Cyears (except D the sales growth E F be equal to the inputs in the 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 Industry MicroDrive MicroDrive Status Quo 60 Actual Actual Forecast 61 Inputs 2013 2012 2013 2014 2015 62 1. Operating Ratios Sales growth rate 5% 15% 5% 10% 8% 63 COGS (excl. depr.) / Sales 76% 75% 76% 76% 0.76 64 Depreciation / Net PP&E 9% 10% 10% 10% 0.10 65 Other op. exp. / Sales 10% 10% 10% 10% 0.10 66 Cash / Sales 1% 1% 1% 1% 0.01 67 Acc. rec. / Sales 8% 8% 10% 10% 0.10 68 Inventory / Sales 15% 17% 20% 20% 0.20 69 Net PP&E / Sales 33% 36% 40% 40% 0.40 70 Acc. pay. / Sales 4% 4% 4% 4% 0.04 71 Accruals / Sales 7% 6% 6% 6% 0.06 72 Tax rate 40% 40% 40% 40% 0.40 73 Actual Market Weights Target Market Weights 74 2. Capital Structure % Long-term debt 22% 31% 41% 28% 28% 75 % Short-term debt 3% 4% 10% 2% 2% 76 % Preferred stock 0% 3% 3% 3% 3% 77 % Common stock 75% 62% 46% 67% 67% 78 Forecast 79 3. Costs of Capital Rate on LT debt 9.0% 9% 80 Rate on ST debt 10.0% 10% 81 Rate on preferred stock (ignoring flotation costs) 8.0% 8% 82 Cost of equity 13.58% 14% 83 Actual 84 4. Target Dividend Policy Growth rate of dividends 11% 4.2% 5% 5% 85 86 87 12-3 Forecasting Operations 88 The figure below shows the forecasted items for the operating plan. For convenience, we repeat the inputs of operating 89 ratios. 90 91 Section B1 shows the sales forecast. Each year's sales is equal to the previous year's sales multiplied by the forecasted sales 92 growth rate. 93 94 Section B2 shows the projections of operating assets and operating liabilities. The operating asset for a particular year is 95 equal to the product of that asset's ratio in Section A1 and that particular year's projected sales. The operating liabilities 96 are projected in a similar manner. 97 98 Section B3 shows the projections of operating income. The COGS and other operating expenses are equal to the product of 99 the ratio in Section A1 and that particular year's projected sales. Depreciation is equal to the product of the ratio in Section 100 A1 and that particular year's projected net PP&E. EBIT is net sales minus COGS, depreciation, and other operating expenses. 101 NOPAT is EBIT(1-T), where T is the tax rate. 102 103 Section B4 shows the projections of free cash flows. NOWC is equal to operating CA (i.e., cash, accounts receivable, and 104 inventories from Section B2) minus operating CL (i.e., accounts payable and accruals from Section 4). Total capital is equal 105 to the sum of NOWC and net PP&E (from Section B2). 106 107 Section B5 shows the results of the operating plan. The first rows in Section B5 report the target WACC (calculated as shown 108 in Chapter 9), the return on invested capital, and the growth rate in FCF. 109 110 The horizon value, value of operations, and estimated intrinsic stock price are calculated using the FCF valuation model as 111 present in Chapter 7. A B C D E F 112 113 Note: Do not change inputs here because these inputs are linked to the ones in Figure 12-2. If you want to change 114 Figure 12-3 115 MicroDrive's Forecast of Operations for the Selected Scenario (Millions of Dollars, Except for Per Share Data) Industry MicroDrive MicroDrive Status Quo 116 Actual Actual Forecast 117 Panel A: Inputs 2013 2012 2013 2014 2015 118 A1. Operating Ratios Sales growth rate 5% 15% 5% 10% 8% 119 COGS (excl. depr.) / Sales 76% 75% 76% 76% 0.76 120 Depreciation / Net PP&E 9% 10% 10% 10% 0.10 121 Other op. exp. / Sales 10% 10% 10% 10% 0.10 122 Cash / Sales 1% 1% 1% 1% 0.01 123 Acc. rec. / Sales 8% 8% 10% 10% 0.10 124 Inventory / Sales 15% 17% 20% 20% 0.20 125 Net PP&E / Sales 33% 36% 40% 40% 0.40 126 Acc. pay. / Sales 4% 4% 4% 4% 0.04 127 Accruals / Sales 7% 6% 6% 6% 0.06 128 Tax rate 40% 40% 40% 40% 0.40 129 Actual Forecast 130 Panel B: Results 2013 2014 2015 131 B1. Sales Revenues Net sales $5,000 $5,500 $5,940 132 133 B2. Operating Assets and Operating Liabilities Cash $50 $55 $59 134 Accounts receivable $500 $550 $594 135 Inventories $1,000 $1,100 $1,188 136 Net PP&E $2,000 $2,200 $2,376 137 Accounts payable $200 $220 $238 138 Accruals $300 $330 $356 139 140 B3. Operating Income COGS (excl. depr.) $3,800 $4,180 $4,514 141 Depreciation $200 $220 $238 142 Other operating expenses $500 $550 $594 143 EBIT $500 $550 $594 144 Net operating profit after taxes $300 $330 $356 145 146 B4. Free Cash Flows Net operating working capital $1,050 $1,155 $1,247 147 Total operating capital $3,050 $3,355 $3,623 148 FCF = NOPAT - op capital $260 $25 $88 149 150 B5. Estimated Intrinsic Value Target WACC 11.0% 11.0% 151 Return on invested capital 9.8% 9.8% 9.8% 152 Growth in FCF 252% 153 154 155 Horizon Value: 156 _= ( = $3,814 Estimated total intrinsic value 157 _ (+_))/ 158 (( _)) of Operations: 159 Value Present value of HV $2,267 Estimated intrinsic value of equity 160 + Present value of FCF $453 161 Value of operations = $2,719 Estimated intrinsic stock price = 162 163 A B C D E F 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 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 growth 181 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 If there is a financing deficit, MicroDrive will draw on a line of credit. MicroDrive assumes that the LOC will be accessed on 192 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) Status Quo 201 Most Recent 202 1. Balance Sheets 2013 Input Basis for 2014 Forecast 203 204 Assets $50.0 1.00% 2014 Sales 205 Cash 500.0 10.00% 2014 Sales 206 Accounts receivable 1,000.0 20.00% 2014 Sales 207 Inventories Total current assets $1,550.0 208 2,000.0 40.00% 2014 Sales 209 Net PP&E Total assets (TA) $3,550.0 210 211 Liabilities and equity $200.0 4.00% 2014 Sales 212 Accounts payable 300.0 6.00% 2014 Sales 213 Accruals 280.0 Carry over from previous year 214 Notes payable 0.0 Draw on LOC if financing deficit 215 Line of credit A B C D E F Total CL $780.0 216 1,200.0 Carry over from previous year 217 Long-term bonds Total liabilities $1,980.0 218 $100.0 Carry over from previous year 219 Preferred stock 500.0 Carry over from previous year 220 Common stock 970.0 Old RE + Add. to RE 221 Retained earnings Total common equity $1,470.0 222 Total liabs. & equity $3,550.0 223 224 Check: TA Total Liab. & Eq. = Most Recent 225 2. Income Statement 2013 Input Basis for 2014 Forecast 226 $5,000.0 110% 2013 Sales 227 Net sales 3,800.0 76.00% 2014 Sales 228 COGS (excl. depr.) 200.0 10.00% 2014 Net PP&E 229 Depreciation $500.0 10.00% 2014 Sales 230 Other operating expenses EBIT $500.0 231 20.0 10.00% Avg notes 232 Less: Interest on notes Interest on bonds 100.0 9.00% Avg bonds 233 Interest on LOC 0.0 11.50% Beginning LOC 234 Pre-tax earnings $380.0 235 152.0 40.00% Pretax earnings 236 Taxes (40%) NI before pref. div. $228.0 237 8.0 8.00% Avg pref. stock 238 Preferred div. Net income $220.0 239 $50.0 105% 2013 Dividend 240 Regular common dividends $0.0 Pay if financing surplus 241 Special dividends $170.0 Net income - Dividends 242 Addition to RE 243 244 3. Elimination of the Financial Deficit or Surplus Increase in spontaneous liabilities (accounts payable and accruals) 245 246 + Increase in notes payable, long-term bonds, preferred stock, and common stock 247 + Net income minus regular common dividends Increase in financing 248 249 Increase in total assets 250 Amount of deficit or surplus financing: If deficit in financing (negative), draw on line of credit Line of credit 251 If surplus in financing (positive), pay special dividend Special dividend 252 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) Status Quo 259 Actual Forecast 260 1. Balance Sheets 2014 2015 2016 2013 261 262 Assets $50.0 $55.0 $59.4 $63.6 263 Cash 500.0 550.0 594.0 635.6 264 Accounts receivable 1,000.0 1,100.0 1,188.0 1,271.2 265 Inventories Total current assets $1,550.0 $1,705.0 $1,841.4 $1,970.3 266 2,000.0 2,200.0 2,376.0 2,542.3 267 Net PP&E A 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 B Total assets (TA) Liabilities and equity Accounts payable Accruals Notes payable Line of credit Total CL Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liabs. & equity C D E F $3,550.0 $3,905.0 $4,217.4 $4,512.6 $200.0 300.0 280.0 0.0 $780.0 1,200.0 $1,980.0 $100.0 500.0 970.0 $1,470.0 $3,550.0 $220.0 330.0 280.0 117.1 $947.1 1,200.0 $2,147.1 $100.0 500.0 1,157.9 $1,657.9 $3,905.0 $237.6 356.4 280.0 181.9 $1,055.9 1,200.0 $2,255.9 $100.0 500.0 1,361.5 $1,861.5 $4,217.4 $254.2 381.3 280.0 214.2 $1,129.8 1,200.0 $2,329.8 $100.0 500.0 1,582.8 $2,082.8 $4,512.6 Check: TA Total Liab. & Eq. = Actual 2013 Net sales $5,000.0 COGS (excl. depr.) 3,800.0 Depreciation 200.0 Other operating expenses $500.0 EBIT $500.0 Less: Interest on notes 20.0 Interest on bonds 100.0 Interest on LOC 0.0 Pre-tax earnings $380.0 Taxes (40%) 152.0 NI before pref. div. $228.0 Preferred div. 8.0 Net income $220.0 Regular common dividends $50.0 Special dividends $0.0 Addition to RE $170.0 $0.00 $0.00 2014 $5,500.0 4,180.0 220.0 $550.0 $550.0 28.0 108.0 0.0 $414.0 165.6 $248.4 8.0 $240.4 $52.5 $0.0 $187.9 2015 $5,940.0 4,514.4 237.6 $594.0 $594.0 28.0 108.0 13.5 $444.5 177.8 $266.7 8.0 $258.7 $55.1 $0.0 $203.6 2. Income Statement 3. Incorporating the Financial Deficit or Surplus Increase in spontaneous liabilities (accounts payable $50.0 + Increase in notes payable, long-term bonds, preferred $0.0 Previous line of credit $0.0 + Net income minus regular common dividends $187.9 Increase in financing $237.9 Increase in total assets $355.0 Amount of deficit or surplus financing: $117.1 Line of credit $117.1 Special dividend $0.0 Statement of Cash Flows (Millions of Dollars) Status Quo $0.00 Forecast 2016 $6,355.8 4,830.4 254.2 $635.6 $635.6 28.0 108.0 20.9 $478.7 191.5 $287.2 8.0 $279.2 $57.9 $0.0 $221.3 $44.0 $41.6 $0.0 $0.0 $117.1 $181.9 $203.6 $221.3 $130.5 $81.0 $312.4 $295.2 $181.9 $214.2 $181.9 $214.2 $0.0 $0.0 Actual 2013 Forecast 2014 A B C D E F 320 Operating Activities Net Income before preferred dividends $228.0 $248.4 321 Noncash adjustments 322 Depreciation $200.0 $220.0 323 Working capital adjustments 324 ($120.0) ($50.0) 325 Increase(-)/Decrease(+) in accounts receivable ($180.0) ($100.0) 326 Increase(-)/Decrease(+) in inventories $10.0 $20.0 327 Increase(-)/Decrease(+) in payables $20.0 $30.0 328 Increase(-)/Decrease(+) in accruals $158.0 $368.4 329 Net cash provided (used) by operating activities 330 331 Investing Activities Cash used to acquire fixed assets ($500.0) ($420.0) 332 Sale of short-term investments $40.0 $0.0 333 ($460.0) ($420.0) 334 Net cash provided (used) by investing activities 335 336 Financing Activities $150.0 $0.0 337 Increase(+)/Decrease(-) in notes payable $0.0 $117.1 338 Increase(+)/Decrease(-) in line of credit $200.0 $0.0 339 Increase(+)/Decrease(-) in bonds $0.0 $0.0 340 Preferred stock issue(+)/repurchase(-) ($58.0) ($60.5) 341 Payment of common and preferred dividends $0.0 $0.0 342 Common stock issue(+)/repurchase(-) $292.0 $56.6 343 Net cash provided by financing activities 344 Summary Net change in cash and equivalents ($10.0) $5.0 345 Cash and securities at beginning of the year $60.0 $50.0 346 $50.0 $55.0 347 Cash and securities at end of the year 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 Industry MicroDrive Status Quo 355 Actual Actual Forecast 356 Panel A: Inputs 2013 2013 2014 2015 2016 357 A1. Operating Ratios Sales growth rate 5% 5% 10% 8% 7% 358 COGS (excl. depr.) / Sales 76% 76% 76% 0.76 0.76 359 Inventory / Sales 15% 20% 20% 0.20 0.20 360 Net PP&E / Sales 33% 40% 40% 0.40 0.40 361 362 Panel B: Key Results Industry MicroDrive Actual Actual Forecast 363 2013 2013 2014 2015 2016 364 B1. Operations NA $260 $25 $88 $128 365 Free cash flow 15.0% 9.8% 9.8% 9.8% 9.8% 366 Return on invested capital 6.9% 6.0% 6.0% 6.0% 6.0% 367 NOPAT/Sales 46.0% 61.0% 61.0% 61.0% 61.0% 368 Total op. capital / Sales 5.0 4.0 4.0 4.0 4.0 369 Inventory turnover 30.0 36.5 36.5 36.5 36.5 370 Days sales outstanding 3.0 2.5 2.5 2.5 2.5 371 Fixed asset turnover A 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 B C D B2. Financing Total liabilities / TA 45.0% 55.8% 55.0% Net income / Sales 6.2% 4.4% 4.4% Return on assets (ROA) 11.0% 6.2% 6.2% Return on equity (ROE) 19.0% 15.0% 14.5% Times interest earned 10.0 4.2 4.0 Line of credit NA $0 $117 Payout ratio 35.0% 22.7% 21.8% Regular dividends/share NA $1.00 $1.05 Special dividends/share NA $0.00 $0.00 Earnings per share NA $4.40 $4.81 B3. Estimated intrinsic value 12/31/2013 Estimated value of operations = 12/31/2013 Estimated intrinsic stock price = E 53.5% 4.4% 6.1% 13.9% 4.0 $182 21.3% $1.10 $0.00 $5.17 F 51.6% 4.4% 6.2% 13.4% 4.1 $214 20.7% $1.16 $0.00 $5.58 $2,719 $22.78 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 = Forecasted growth rate in sales = 399 g: Next year's sales: S0 (1 + g) = 400 S1: Change in sales = S1 - S0 = S = 401 gS0: A *: Most recent year's operating assets = 402 0 403 A0* / S0: Required assets per dollar of sales = L *: 404 0 Most recent year's spontaneous liabilities i.e., payables + accruals = 405 L0* /S0: Spontaneous liabilities per dollar of sales = Most recent profit margin = net income/sales = 406 Profit margin (M): Most recent year's dividends / net income = % of income paid out = 407 Payout ratio (POR): 408 Part B. Additional Funds Needed (AFN) to Support Growth 409 Required Increase in AFN = Increase in Spon. Liab. 410 Assets (A */S )S (L0*/S0)S = 411 0 0 412 (A0*/S0)(gS0) (L0*/S0)(gS0) = 413 414 = (0.710)($500) (0.10)($500) 415 = $355 $50.00 416 AFN = $118.00 417 418 419 A 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 B C D E F 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. PM(1 - POR)(S0) = A0* - L0* - PM(1 - POR)S0 Sustainable g = $170.00 $2,880.00 = 2. Using Goal Seek. The sustainable growth rate can also be found by using Goal Seek. In the figure above, set the AFN in the orange cell to zero by changing the growth rate in the blue cell. 12.7 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. FA capacity was used only to this percent 2013 Sales 2014 2014 Sales 2014 2014 Fixed Assets 2013 Fixed assets/Sales = 96% $5,000 $5,500 $2,000 40.00% Required increase in FA if no excess capacity = (Old 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 Target fixed assets/Sales = Actual fixed assets / Full capacity sales 38.40% Required fixed assets = (Target FA/Sales) (Forecast sales) Required fixed assets = $2,112 Required increase in fixed assets = Difference between required increase assuming no excess capacity and required increase if there is excess capacity = $112 471 472 473 474 475 476 Abetween required increase B C no Difference assuming excess capacity and required increase if there is excess capacity = D E $88 AFN if no excess capacity = $118 AFN if there is excess capacity = $30 F G 1 H 5/6/2013 2 3 4 5 6 ts operating plan is described in the sections below. 7 8 9 10 ected additional data. 11 12 13 14 15 2013 16 $ 50 17 18 500 19 1,000 20 $ 1,550 21 2,000 22 $ 3,550 23 24 25 $ 200 26 300 27 280 28 $ 780 29 1,200 30 $ 1,980 31 100 32 500 33 970 34 $ 1,470 35 $ 3,550 36 37 38 39 40 l statements for the scenario that has been selected There are two scenarios. 41 The first is named Status umed to remain unchanged. 42 The initial sales growth duct lines. The growth rate 43 declines over time until it d Final because it is the44 set of inputs chosen by 45 46 47 inputs, Section 1 shows ating plan. For each of these roDrive, and the forecasted 48 values for the next five sales growth rate) would 49be equal to the inputs in the I J K L M G H I J K 50 51 sustaniable constant rate. 52 53 rage cost of capital. Section 54 4 shows the forecasted 55 56 you want to change57an input, do it here and not other places in the model. 58 59 L M G H 60 MicroDrive Forecast 61 2016 2017 62 7% 5% 63 0.76 76% 64 0.10 0.10 65 0.10 0.10 66 0.01 0.01 67 0.10 0.10 68 0.20 0.20 69 0.40 0.40 70 0.04 0.04 71 0.06 0.06 72 0.40 0.40 73 Target Market Weights 74 28% 28% 75 2% 2% 76 3% 3% 77 67% 67% 78 Forecast 79 9% 9% 80 10% 10% 81 8% 8% 82 14% 14% 83 84 5% 5% 85 86 87 or convenience, we repeat 88 the inputs of operating 89 90 91 revious year's sales multiplied by the forecasted sales 92 93 abilities. The operating94 asset for a particular year is ular year's projected sales. 95 The operating liabilities 96 97 98 are equal to the product of other operating expenses reciation is equal to the99 product of the ratio in Section 100 nus COGS, depreciation, and other operating expenses. 101 102 103 operating CA (i.e., cash, accounts receivable, and le and accruals from Section 104 4). Total capital is equal 105 106 107 WACC (calculated as shown ection B5 report the target CF. 108 109 price are calculated using 110the FCF valuation model as 111 I J K 2018 0.05 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% for calculations of the 28% actual capital structures, 2% based on market values, 3% for the past two years. 67% 9% 10% 8% 14% 5% L M Actual Historical Financing Long-term debt Short-term debt Preferred stock Market value of equity = (Price x # shares) Percent long-term debt Percent short-term debt Percent preferred stock Percent market value of equity G H I J K L 112 113in Figure 12-2. If you want to change inputs, do so in Figure 12-2. re linked to the ones 114 ns of Dollars, Except for Per Share Data) 115 116 MicroDrive Forecast 117 2016 2017 2018 118 7% 5% 5% 119 0.76 76% 76% 120 0.10 0.10 10% 121 0.10 0.10 10% 122 0.01 0.01 1% 123 0.10 0.10 10% 124 0.20 0.20 20% 125 0.40 0.40 40% 126 0.04 0.04 4% 127 0.06 0.06 6% 128 0.40 0.40 40% 129 Forecast 130 2016 2017 2018 131 $6,356 $6,674 $7,007 132 133 $64 $67 $70 134 $636 $667 $701 135 $1,271 $1,335 $1,401 136 $2,542 $2,669 $2,803 137 $254 $267 $280 138 $381 $400 $420 139 140 $4,830 $5,072 $5,326 141 $254 $267 $280 142 $636 $667 $701 143 $636 $667 $701 144 $381 $400 $420 145 146 $1,335 $1,401 $1,472 147 $3,877 $4,071 $4,274 148 $128 $207 $217 149 150 11.0% 11.0% 11.0% 151 9.8% 9.8% 9.8% 152 45.1% 61.7% 5.0% 153 154 Value of operations $2,719 155 + ST investments $0 156 Estimated total intrinsic value $2,719 157 All debt $1,480 158 Preferred stock $100 159 Estimated intrinsic value of equity $1,139 160 Number of shares $50 161 Estimated intrinsic stock price = $22.78 162 163 M G H I J K L 164 165 166 167 168 for the upcoming year169 for the Status Quo scenario. 170 projected for the operating 171 plan. 172 otes payable, long-term173 bonds, preferred stock, and r. 174 175 erage amount of debt 176 during the year, defined as the year) and the ending 177 debt. An identical process is 178 179 180long-term sustainable growth w at the same rate as the 181 182 183 ded by spontaneous liabilities, external sources, and tal amount of additional 184preliminary financing. 185 ed by the operating plan. 186 187 188as the financing deficit (if ditional assets is defined s positive). 189 190 191the LOC will be accessed on MicroDrive assumes that 192 of-year balance) will not accrue enough interest to 193 at the beginning of the year (which is the same as the 194 195 196 197 198in Figure 12-2. If you want to change inputs, do so in Figure 12-2. re linked to the ones 199 200 201 Forecast 202 2014 203 204 $55.00 205 $550.00 206 $1,100.00 207 $1,705.00 208 $2,200.00 209 $3,905.00 210 211 $220.00 212 $330.00 213 $280.00 214 $117.10 215 M G 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 Forecast 260 261 262 263 264 265 266 267 H I J K L M $947.10 $1,200.00 $2,147.10 $100.00 $500.00 $1,158 $1,658 $3,905 $0.00 Forecast 2014 $5,500.00 $4,180.00 $220.00 $550.00 $550.00 $28.00 $108.00 $0.00 $414.00 $165.60 $248.40 $8.00 $240.40 $52.50 $0.00 $187.90 $50.00 $0.00 $187.90 $237.90 $355.00 $117.10 $117.10 $0.00 Note: 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. 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 Note: This is the planned increase in the retained earnings account. 2017 2018 $66.7 667.4 1,334.7 $2,068.8 2,669.4 $70.1 700.7 1,401.5 $2,172.3 2,802.9 G 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 Forecast 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 H $4,738.2 $4,975.2 $266.9 400.4 280.0 172.8 $1,120.2 1,200.0 $2,320.2 $100.0 500.0 1,818.1 $2,318.1 $4,738.2 $280.3 420.4 280.0 121.3 $1,102.0 1,200.0 $2,302.0 $100.0 500.0 2,073.2 $2,573.2 $4,975.2 $0.00 $0.00 2017 $6,673.6 5,071.9 266.9 $667.4 $667.4 28.0 108.0 24.6 $506.7 202.7 $304.0 8.0 $296.0 $60.8 $0.0 $235.3 2018 $7,007.3 5,325.5 280.3 $700.7 $700.7 28.0 108.0 19.9 $544.9 217.9 $326.9 8.0 $318.9 $63.8 $0.0 $255.1 $31.8 $0.0 $214.2 $235.3 $52.8 $225.6 $172.8 $172.8 $0.0 $33.4 $0.0 $172.8 $255.1 $115.6 $236.9 $121.3 $121.3 $0.0 Forecast 2015 Forecast 2016 I J Note: Note: Forecast 2017 K L M 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. 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. Forecast 2018 G H I J K L 320 $266.7 $287.2 $304.0 $326.9 321 322 $237.6 $254.2 $266.9 $280.3 323 324 ($44.0) ($41.6) ($31.8) ($33.4) 325 ($88.0) ($83.2) ($63.6) ($66.7) 326 $17.6 $16.6 $12.7 $13.3 327 $26.4 $24.9 $19.1 $20.0 328 $416.3 $458.3 $507.4 $540.5 329 330 331 ($413.6) ($420.6) ($394.1) ($413.8) 332 $0.0 $0.0 $0.0 $0.0 333 ($413.6) ($420.6) ($394.1) ($413.8) 334 335 336 $0.0 $0.0 $0.0 $0.0 337 $64.8 $32.3 ($41.4) ($51.6) 338 $0.0 $0.0 $0.0 $0.0 339 $0.0 $0.0 $0.0 $0.0 340 ($63.1) ($65.9) ($68.8) ($71.8) 341 $0.0 $0.0 $0.0 $0.0 342 $1.7 ($33.6) ($110.2) ($123.4) 343 344 $4.4 $4.2 $3.2 $3.3 345 $55.0 $59.4 $63.6 $66.7 346 $59.4 $63.6 $66.7 $70.1 347 348 349 350 351in Figure 12-2. If you want to change inputs, do so in Figure 12-2. re linked to the ones 352 353 Millions Except Percentages and Per Share Data) 354 MicroDrive 355 Status Quo Industry Forecast Panel A: Inp Actual Actual 356 2017 2018 A1. Operatin 2013 2013 357 5% 0.05 Sales grow 5% 5% 358 76% 76% COGS (excl 76% 76% 359 0.20 20% Inventory 15% 20% 360 0.40 40% Net PP&E 33% 40% 361 MicroDrive 362 Panel B: Key Industry Forecast Actual Actual 363 2017 2018 B1. Operatio 2013 2013 364 $207 $217 Free cash fl NA $260 365 9.8% 9.8% Return on in 15.0% 9.8% 366 6.0% 6.0% NOPAT/Sales 6.9% 6.0% 367 61.0% 61.0% Total op. cap 46.0% 61.0% 368 4.0 4.0 Inventory tu 5.0 4.0 369 36.5 36.5 Days sales o 30.0 36.5 370 2.5 2.5 Fixed asset 3.0 2.5 371 M MicroDrive Forecast 2014 10% 76% 20% 40% MicroDrive Forecast 2014 $25 9.8% 6.0% 61.0% 4.0 36.5 2.5 G 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 49.0% 4.4% 6.2% 12.8% 4.2 $173 20.5% $1.22 $0.00 $5.92 H I 46.3% 4.6% 6.4% 12.4% 4.5 $121 20.0% $1.28 $0.00 $6.38 391 port its forecasted next year's sales. 392 393 394 395 396 397 398 $5,000 10.00% 399 400 $5,500 $500 401 402 $3,550 403 71.00% 404 aneous liabilities i.e., payables + accruals = $500 405 10.00% 4.40% 406 ends / net income = %407 of income paid out = 22.73% 408 409 Addition to Retained 410 Earnings. S1 M (1 - POR) 411 412 (1+g)S0 M (1 - POR) 413 414 $5,500(0.044)(1 - 0.2273) 415 $187.00 416 417 418 419 J K L B2. Financing Total liabilit 45.0% 55.8% Net income / 6.2% 4.4% Return on as 11.0% 6.2% Return on eq 19.0% 15.0% Times intere 10.0 4.2 Line of credi NA $0 Payout ratio 35.0% 22.7% Regular div NA $1.00 Special divi NA $0.00 Earnings per NA $4.40 B3. Estimated intrinsic value ### Estimated value of operations = ### Estimated intrinsic stock price = M 55.0% 4.4% 6.2% 14.5% 4.0 $117 21.8% $1.05 $0.00 $4.81 G H I 420 421 g external funds, i.e., the 422 value of g that forces AFN = 0, holding other 423 424 425 lving the equation as shown 426 on the 3rd row above g, then finding the 427 428 429 430 431 5.90% 432 433 434 435 using Goal Seek. In the436 figure above, set the AFN in the orange cell to 437 438 439 440 441 442 443correct. For instance, suppose the firm is ut this is not necessarily 444 achieve a greater level of production with its fixed assets. Here are 445 capacity. 2. Calculate the required new the AFN ignoring the excess pacity sales. 4. Calculate a revised target fixed assets-to-sales ratio. 446 ulate the increase in required 447 fixed assets given excess capacity. 7. excess capacity is ignored 448 versus the required fixed assets when ixed 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 J K L M G 471 472 473 474 475 476 H I J K L M N 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 O P Q R S T U N 50 51 52 53 54 55 56 57 58 59 O P Q R S T U N 60 61 62 63 64 65 66 67 68 Long-term debt 69 Short-term debt 70 Preferred stock 71 ket value of equity = (Price 72 x # shares) 73 Total 74 Percent75 long-term debt Percent short-term debt 76 Percent77 preferred stock Percent market78 value of equity 79 Total 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 O P 2012 $1,000 $130 $100 $2,000 $3,230 2013 $1,200 $280 $100 $1,350 $2,930 31% 4% 3% 62% 100% Q 41% 10% 3% 46% 100% R S T U N 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 O P Q R S T U N 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 O P Q R S T U N O P Q R S T U 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 s necessary to subtract246 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 l 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 N 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 O P Q R S T U N 320 321 322 323 324 325 326 327 328 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 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 O P Q MicroDrive 2015 8% 0.76 0.20 0.40 Forecast 2016 7% 0.76 0.20 0.40 2017 5% 76% 0.20 0.40 2018 5% 76% 20% 40% Forecast 2016 $128 9.8% 6.0% 61.0% 4.0 36.5 2.5 2017 $207 9.8% 6.0% 61.0% 4.0 36.5 2.5 2018 $217 9.8% 6.0% 61.0% 4.0 36.5 2.5 MicroDrive 2015 $88 9.8% 6.0% 61.0% 4.0 36.5 2.5 R S T U N 372 373 53.5% 4.4% 374 6.1% 375 13.9% 376 4.0 377 $182 378 21.3% 379 $1.10 380 $0.00 381 382 $5.17 383 384 $2,719 $22.78 385 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 O 51.6% 4.4% 6.2% 13.4% 4.1 $214 20.7% $1.16 $0.00 $5.58 P Q 49.0% 46.3% 4.4% 4.6% 6.2% 6.4% 12.8% 12.4% 4.2 4.5 $173 $121 20.5% 20.0% $1.22 $1.28 $0.00 $0.00 $5.92 $6.38 R S T U N 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 O P Q R S T U N 471 472 473 474 475 476 O P Q R S T U V 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 W X Y V 50 51 52 53 54 55 56 57 58 59 W X Y V 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 W X Y V 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 W X Y V 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 W X Y V W X Y 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 redit on the last day of246 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 V 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 W X Y V 320 321 322 323 324 325 326 327 328 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 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 W X Y V 372 373 374 375 376 377 378 379 380 381 382 383 384 385 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 W X Y V 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 W X Y V 471 472 473 474 475 476 W X Y 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 S0 A0*/ S0 L0*/ S0 Profit margin (M) Payout ratio Sales S1 AFN 15% $3,000 million 66.666% 6.667% 3.783% 50.670% $450.00 million $3,450.00 million $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 Net sales $ 4,760 $ 5,000 Cash COGS (excl. depr.) 3,560 3,800 ST Investments Depreciation 170 200 Accounts receivable Other operating expenses 480 500 Inventories EBIT $ 550 $ 500 Total CA Interest expense 100 120 Net PP&E Pretax earnings $ 450 $ 380 Total assets Taxes (40%) 180 152 NI before pref. div. $ 270 $ 228 Liabilities and equity Preferred div. 8 8 Accounts payable Net income $ 262 $ 220 Accruals Notes payable Other Data Total CL Common dividends $48 $50 Long-term bonds $214 $170 Total liabilities Addition to RE Tax rate 40% 40% Preferred stock Shares of common stock 50 50 Common stock Earnings per share $5.24 $4.40 Retained earnings Dividends per share $0.96 $1.00 Total common equit Price per share $40.00 $27.00 Total liabs. & equity 2012 60 40 380 820 $ 1,300 1,700 $ 3,000 $ $ $ $ $ $ 190 280 130 600 1,000 1,600 100 500 800 1,300 3,000 $ $ $ $ $ $ $ $ 2013 50 500 1,000 1,550 2,000 3,550 200 300 280 780 1,200 1,980 100 500 970 1,470 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. 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 Industry MicroDrive Actual Actual 2013 2012 2013 5% 15% 5% 76% 75% 76% 9% 10% 10% 10% 10% 10% 1% 1% 1% 8% 8% 10% 15% 17% 20% 33% 36% 40% 4% 4% 4% 7% 6% 6% 40% 40% 40% Actual Market Weights 22% 31% 41% 3% 4% 10% 0% 3% 3% 75% 62% 46% Status Quo Inputs 1. Operating Ratios Sales growth rate COGS (excl. depr.) / Sales Depreciation / Net PP&E Other op. exp. / Sales Cash / Sales Acc. rec. / Sales Inventory / Sales Net plant / Sales Acc. pay. / Sales Accruals / Sales Tax rate 2. Capital Structure % Long-term debt % Short-term debt % Preferred stock % Common stock 3. Costs of Capital Rate on LT bonds, rLTD Rate on ST debt, rSTD Rate on preferred stock (ignoring flotation costs), r ps Cost of equity, rs 4. Target Dividend Policy Growth rate of dividends 5. Capital Structure Choices % Long-term debt, wLTD Actual 11% 4.2% 2014 10% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 28% 2% 3% 67% MicroDrive Forecast 2015 2016 8% 7% 76% 76% 10% 10% 10% 10% 1% 1% 10% 10% 20% 20% 40% 40% 4% 4% 6% 6% 40% 40% Target Market Weights 28% 28% 2% 2% 3% 3% 67% 67% Forecast 9.0% 9% 9% 10.0% 10% 10% 8.0% 8% 8% 13.58% 14% 14% 5% 5% 5% 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% 46.1% 50.3% 54.4% 58.6% % Common stock, ws 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 constantwith 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 amount 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 interes is calculated on the LOC's average balance during the year, which is more realistic, here is what happens: 1. 2. 3. 4. The line of credit required to make the balance sheets balance is added to the balance sheet. Interest expense increases due to the LOC. Net income decreases because interest expenses are higher. Internally generated financing decreases because net income decreases. 5. 6. 7. The financing deficit increases because internally generated financing decreases. An additional amount of the LOC is added to the balance sheets to make them balance. 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 Exc 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 converge 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 explai 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 Assets Cash Accounts receivable Inventories Total current assets Net PP&E Total assets (TA) Liabilities and equity Accounts payable Accruals Notes payable (wSTD Vop) Line of credit (After adjustment for feedback effects) Total CL Long-term bonds (wLTD Vop) Total liabilities Preferred stock (wps Vop) Common stock Retained earnings Total common equity Total liabs. & equity Actual 2013 2014 $50.0 500.0 1,000.0 $1,550.0 2,000.0 $3,550.0 $55.0 550.0 1,100.0 $1,705.0 2,200.0 $3,905.0 $59.4 594.0 1,188.0 $1,841.4 2,376.0 $4,217.4 $63.6 635.6 1,271.2 $1,970.3 2,542.3 $4,512.6 $200.0 300.0 $220.0 330.0 $237.6 356.4 $254.2 381.3 280.0 0.0 $780.0 240.7 213.4 $1,004.2 211.2 297.9 $1,103.2 173.8 375.1 $1,184.4 1,200.0 $1,980.0 1,148.0 $2,152.2 1,156.5 $2,259.6 1,148.0 $2,332.4 $100.0 500.0 970.0 $1,470.0 $3,550.0 $99.7 500.0 1,153.1 $1,653.1 $3,905.0 $105.0 500.0 1,352.8 $1,852.8 $4,217.4 $109.5 500.0 1,570.7 $2,070.7 $4,512.6 Check: TA Total Liab. & Eq. = Actual 2013 Net sales $5,000.0 COGS (excl. depr.) 3,800.0 Depreciation 200.0 Other operating expenses $500.0 EBIT $500.0 Less: Interest on notes payable, based on average NP and rSTD 20.0 Forecast 2016 2015 $0.00 $0.00 2014 $5,500.0 4,180.0 220.0 $550.0 $550.0 2015 $5,940.0 4,514.4 237.6 $594.0 $594.0 $0.00 Forecast 2016 $6,355.8 4,830.4 254.2 $635.6 $635.6 26.0 22.6 19.2 100.0 105.7 103.7 103.7 0.0 $380.0 152.0 $228.0 12.3 $406.0 162.4 $243.6 29.4 $438.3 175.3 $263.0 38.7 $473.9 189.6 $284.4 8.0 $220.0 $50.0 $0.0 $170.0 8.0 $235.6 $52.5 $0.0 $183.1 8.2 $254.8 $55.1 $0.0 $199.7 8.6 $275.8 $57.9 $0.0 $217.9 2. Income Statement Interest on bonds, based on average LT bonds and rLTD Interest on LOC, based on average LOC and rLOC = rSTD +1.5% Pre-tax earnings Taxes (40%) NI before pref. div. Preferred dividend, based on average preferred stock and r ps Net income Regular common dividends Special dividends Addition to RE 3. Eliminating the Financial Deficit or Surplus Increase in spontaneous liabilities (accounts payable and accruals) + Increase in notes payable, long-term bonds, preferred stock and common stock Previous line of credit $50.0 $91.6 $0.0 $44.0 $15.8 $213.4 $41.6 $41.4 $297.9 + Planned increase in retained earnings + After-tax operating income: EBIT (1-T) After-tax interest on notes payable (INTSTD x (1-T) $330.0 $356.4 $381.3 $15.6 $13.6 $11.5 $63.4 $62.2 $62.2 $0.0 $8.0 $52.5 $190.5 $7.4 $8.2 $55.1 $209.9 $10.3 $8.6 $57.9 $230.8 $148.9 $355.0 $206.1 $24.7 $312.4 $287.7 $66.9 $295.2 $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 = Adjustment factor (see note below) = Adjusted line of credit = Unadjusted LOC / Adjustment factor = $206.1 0.97 $213.4 $287.7 0.97 $297.9 $362.1 0.97 $375.1 After-tax interest on bonds (INTLTD x (1-T) After-tax interest on previous LOC: (rLOC x 0.5 x LOCt-1 x (1-T) Preferred dividends Regular common dividends Total planned increase in the retained earnings account Increase in financing Increase in total assets Amount of unadjusted deficit or surplus financing: 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 on 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 horizo 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 flow 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, w 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 Net operating working capital Total net operating capital NOPAT FCF Growth rate in FCF Target WACC Horizon value: VHV = Vop,2018 = [FCFt208 (1+g)]/(WACC-g). Actual 2013 $790 $1,050 $2,490 $3,050 $330 $300 -$260 10.97% 2015 $1,247 $3,623 $356 $88 252.0% 10.97% Forecast 2016 $1,335 $3,877 $381 $128 45.1% 10.97% 2014 $1,155 $3,355 $330 $25 Value of operations: Vop,t-1 = [FCFt +Vop,t]/(1+WACC). $2,719 $2,992 $3,233 $3,460 Choice of long-term bonds (wLTD 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 Value of operations + ST investments Estimated total intrinsic value All debt 2013 $2,719 $0 $2,719 $1,480 Preferred stock Estimated intrinsic value of equity Number of shares Estimated intrinsic stock price = Statement of Cash Flows (Millions of Dollars) Status Quo $100 $1,139 $50 $22.78 Actual 2013 Forecast 2014 Forecast 2015 Forecast 2016 $228.0 $243.6 $263.0 $284.4 Operating Activities Net Income before preferred dividends Noncash adjustments Depreciation Working capital adjustments Increase(-)/Decrease(+) in accounts receivable Increase(-)/Decrease(+) in inventories Increase(-)/Decrease(+) in payables Increase(-)/Decrease(+) in accruals Net cash provided (used) by operating activities $200.0 $220.0 $237.6 $254.2 ($120.0) ($180.0) $10.0 $20.0 $158.0 ($50.0) ($100.0) $20.0 $30.0 $363.6 ($44.0) ($88.0) $17.6 $26.4 $412.6 ($41.6) ($83.2) $16.6 $24.9 $455.4 Investing Activities Cash used to acquire fixed assets Sale of short-term investments Net cash provided (used) by investing activities ($500.0) $40.0 ($460.0) ($420.0) $0.0 ($420.0) ($413.6) $0.0 ($413.6) ($420.6) $0.0 ($420.6) $150.0 $0.0 $200.0 $0.0 ($58.0) $0.0 $292.0 ($39.3) $213.4 ($52.0) ($0.3) ($60.5) $0.0 $61.4 ($29.5) $84.5 $8.4 $5.3 ($63.3) $0.0 $5.4 ($37.5) $77.1 ($8.5) $4.5 ($66.5) $0.0 ($30.7) ($10.0) $60.0 $50.0 $5.0 $50.0 $55.0 $4.4 $55.0 $59.4 $4.2 $59.4 $63.6 Financing Activities Increase(+)/Decrease(-) in notes payable Increase(+)/Decrease(-) in line of credit Increase(+)/Decrease(-) in bonds Preferred stock issue(+)/repurchase(-) Payment of common and preferred dividends Common stock issue(+)/repurchase(-) Net cash provided by financing activities Summary Net change in cash and equivalents Cash and securities at beginning of the year Cash and securities at end of the year Summary of Key Results for Forecasted Scenarios (Millions Except Percentages and Per Share Data) Industry MicroDrive Status Quo Actual Actual Forecast 1. Operations 2013 2013 2014 2015 2016 Free cash flow NA $260 $25 $88 $128 Return on invested capital 15.0% 9.8% 9.8% 9.8% 9.8% NOPAT/Sales 6.9% 6.0% 6.0% 6.0% 6.0% Total op. capital / Sales 46.0% 61.0% 61.0% 61.0% 61.0% Inventory turnover 5.0 4.0 4.0 4.0 4.0 Days sales outstanding 30.0 36.5 36.5 36.5 36.5 Fixed asset turnover 3.0 2.5 2.5 2.5 2.5 2. Financing Total liabilities / TA 45.0% 55.8% 55.1% 53.6% 51.7% Net income / Sales 6.2% 4.4% 4.3% 4.3% 4.3% Return on assets (ROA) 11.0% 6.2% 6.0% 6.0% 6.1% Return on equity (ROE) 19.0% 15.0% 14.3% 13.8% 13.3% Times interest earned 10.0 4.2 3.8 3.8 3.9 Line of credit NA $0 $213 $298 $375 Payout ratio 35.0% 22.7% 22.3% 21.6% 21.0% Regular dividends/share NA $1.00 $1.05 $1.10 $1.16 Special dividends/share NA $0.00 $0.00 $0.00 $0.00 Earnings per share NA $4.40 $4.71 $5.10 $5.52 3. Estimated intrinsic value 2017 $207 9.8% 6.0% 61.0% 4.0 36.5 2.5 49.0% 4.4% 6.2% 12.8% 4.1 $416 20.7% $1.22 $0.00 $5.88 12/31/2013 Estimated intrinsic stock price = $22.78 5/6/2013 so ensures that the actual cenario, go to the cenario, go to the selected in the worksheet t is named Status Quo wth rate was chosen by levels off at a sustainable 1 shows the industry The managers assumed the casted growth rate in cenario, go to the MicroDrive Forecast 2017 5% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 2018 5% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 28% 2% 3% 67% for calculations of the 28% actual capital structures, 2% based on market values, 3% for the past two years. 67% et Market Weights Forecast 9% 9% 10% 10% 8% 8% 14% 14% 5% 5% 30.6% 28% 3.5% 2% 3.1% 3% 62.8% 67% tructure matches the target capital f debt (other than the line of credit) ructure. Fortunately, there is a simple oes not depend on the actual amounts starting at the horizon and working , the yearly values of debt and he end of the year. However, if interest d to the balance sheet. Actual Historical Financing Long-term debt Short-term debt Preferred stock Market value of equity = (Price x # shares) Total Percent long-term debt Percent short-term debt Percent preferred stock Percent market value of equity Total 2012 $1,000 $130 $100 $2,000 $3,230 31% 4% 3% 62% 100% 2013 $1,200 $280 $100 $1,350 $2,930 41% 10% 3% 46% 100% e them balance. t would be less than the previous repeated this process manually metimes it is possible to set Excel to r this automatic iteration to cause Excel e additional LOC eventually converges LOC needed when there is financing preferred dividend yield). We explain Forecast 2017 2018 $66.7 667.4 1,334.7 $2,068.8 2,669.4 $4,738.2 $70.1 700.7 1,401.5 $2,172.3 2,802.9 $4,975.2 $266.9 400.4 $280.3 420.4 127.6 415.9 $1,210.8 76.3 459.2 $1,236.2 1,111.3 $2,322.1 1,068.0 $2,304.2 $112.0 500.0 1,804.1 $2,304.1 $4,738.2 $114.4 500.0 2,056.6 $2,556.6 $4,975.2 $0.00 $0.00 2017 $6,673.6 5,071.9 266.9 $667.4 $667.4 2018 $7,007.3 5,325.5 280.3 $700.7 $700.7 15.1 10.2 101.7 98.1 45.5 $505.1 202.1 $303.1 50.3 $542.2 216.9 $325.3 8.9 $294.2 $60.8 $0.0 $233.5 9.1 $316.2 $63.8 $0.0 $252.4 $31.8 $80.5 $375.1 $33.4 $92.1 $415.9 Forecast 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 $8.9 $60.8 $247.8 $14.3 $9.1 $63.8 $268.3 $176.0 $206.4 $225.6 $236.9 $401.6 $443.3 $0.0 $0.0 $401.6 0.97 $415.9 $443.3 0.97 $459.2 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. 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. e new interest will be incurred on only justed LOC. apital structure. f all free cash flows beyond the horizon, equal to the value of all free cash flows s present value is equivalent to the ne period at the target WACC. Thus, we ultiplied by the value of operations. Forecast 2017 $1,401 $4,071 $400 $207 61.7% 10.97% 2018 $1,472 $4,274 $420 $217 5.0% 10.97% $3,814 $3,633 $3,814 Note: $1,111 $1,068 $128 $76 $112 $114 The value of operations at the horizon is equal to the horizon value. Forecast 2017 Forecast 2018 $303.1 $325.3 $266.9 $280.3 ($31.8) ($63.6) $12.7 $19.1 $506.5 ($33.4) ($66.7) $13.3 $20.0 $538.8 ($394.1) $0.0 ($394.1) ($413.8) $0.0 ($413.8) ($46.2) $40.9 ($36.7) $2.5 ($69.6) $0.0 ($109.2) ($51.3) $43.2 ($43.3) $2.4 ($72.9) $0.0 ($121.7) $3.2 $63.6 $66.7 $3.3 $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 ed LOC unless necessary. 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 (Millio Dollars), 12/31/2013 of Dollars Except per Share) Cash Accts. rec. Inventories Total CA $20 $280 $400 $700 Net fixed assets Total assets $500 $1,200 Accts. pay. & accruals Line of credit Total CL Long-term debt Total liabilities Common stock Retained earnings Total common equ. Total liab. & equity $80 $0 $80 $500 $580 $420 $200 $620 $1,200 Sales Op. costs (excl. depr.) Depreciation EBIT Interest Pretax earnings Taxes (40%) Net income Dividends Add. to RE Common shares EPS DPS Ending stock price Selected Ratios and Other Data, 2013 Op. costs/Sales Depr./FA Cash/Sales Receivables/Sales Inventories/Sales Fixed assets/Sales Acc. pay. & accr. / Sales Tax rate ROIC NOPAT/Sales Total op. capital/Sales Additional Data Exp. Saled growth rate Interest rate on LT debt Target WACC Hatfield Industry 90% 88% 10% 12% 1% 1% 14% 11% 20% 15% 25% 22% 4% 4% 40% 40% 8.0% 12.5% 4.5% 5.6% 56.0% 45.0% 2014 10% 8% 9% Total liability/Total assets Times interest earned Return on assets (ROA) Profit margin (M) Sales/Assets Assets/Equity Return on equity (ROE) P/E ratio 5/6/2013 cal Supplies: Income Statement (Millions of Dollars Except per Share) 2013 $2,000.0 $1,800.0 $50.0 $150.0 y/Total assets $40.0 $110.0 $44.0 $66.0 $20.0 $46.0 10.0 $6.6 $2.0 $52.80 Hatfield 48.3% 3.8 5.5% 3.30% 1.67 1.94 10.6% 8.0 Industry 36.7% 8.9 10.2% 4.99% 2.04 1.58 16.1% 16.0