| Tool Kit | | | Chapter 12 | | | | 12/9/12 |
| | | | Corporate Valuation and Financial Planning |
| 12-2 Financial Planning at MicroDrive, Inc. |
| The process used by MicroDrive to forecast the free cash flows from its operating plan is described in the sections below. |
| Setting Up the Model to Forecast Operations |
| We begin with MicroDrive's most recent financial statements and selected additional data. |
| Figure 12-1 |
| MicroDrive’s Most Recent Financial Statements (Millions, Except for Per Share Data) |
| INCOME STATEMENTS | | | BALANCE SHEETS |
| | 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 |
| Pre-tax earnings | $ 450 | $ 380 | Total assets | | $ 3,000 | $ 3,550 |
| Taxes (40%) | 180 | 152 |
| NI before pref. div. | $ 270 | $ 228 | Liabilities and equity |
| Preferred div. | 8 | 8 | Accounts payable | | $ 190 | $ 200 |
| Net income | $ 262 | $ 220 | Accruals | | 280 | 300 |
| | | | Notes payable | | 130 | 280 |
| Other Data | | | Total CL | | $ 600 | $ 780 |
| Common dividends | $48 | $50 | Long-term bonds | | 1,000 | 1,200 |
| Addition to RE | $214 | $170 | Total liabilities | | $ 1,600 | $ 1,980 |
| Tax rate | 40% | 40% | Preferred stock | | 100 | 100 |
| Shares of common stock | 50 | 50 | Common stock | | 500 | 500 |
| Earnings per share | $5.24 | $4.40 | Retained earnings | | 800 | 970 |
| Dividends per share | $0.96 | $1.00 | Total common equity | | $ 1,300 | $ 1,470 |
| Price per share | $40.00 | $27.00 | Total liabs. & equity | | $ 3,000 | $ 3,550 |
| The figure below shows all the inputs required to project the financial statements for the scenario that has been selected with the Scenario Manager: Data, What-If Analysis, Scenario Manager. There are 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. |
| Note: These inputs are linked throughout the model. If you want to change an input, do it here and not other places in the model. |
| Figure 12-2 |
| MicroDrive's Forecast: Inputs for the Selected Scenario |
| Status Quo | Industry | MicroDrive | | MicroDrive |
| Inputs | Actual | Actual | | Forecast |
| 1. Operating Ratios | 2013 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 |
| Sales growth rate | 5% | 15% | 5% | 10% | 8% | 7% | 5% | 5% |
|
| COGS (excl. depr.) / Sales | 76% | 75% | 76% | 76% | 76% | 76% | 76% | 76% |
|
| Depreciation / Net PP&E | 9% | 10% | 10% | 10% | 10% | 10% | 10% | 10% |
|
| Other op. exp. / Sales | 10% | 10% | 10% | 10% | 10% | 10% | 10% | 10% |
|
| Cash / Sales | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% | | | Actual Historical Financing |
| Acc. rec. / Sales | 8% | 8% | 10% | 10% | 10% | 10% | 10% | 10% | | | | | | 2012 | 2013 |
| Inventory / Sales | 15% | 17% | 20% | 20% | 20% | 20% | 20% | 20% | | | | | Long-term debt | $1,000 | $1,200 |
| Net PP&E / Sales | 33% | 36% | 40% | 40% | 40% | 40% | 40% | 40% | | | | | Short-term debt | $130 | $280 |
| Acc. pay. / Sales | 4% | 4% | 4% | 4% | 4% | 4% | 4% | 4% | | | | | Preferred stock | $100 | $100 |
| Accruals / Sales | 7% | 6% | 6% | 6% | 6% | 6% | 6% | 6% | | | | | Market value of equity = (Price x # shares) | $2,000 | $1,350 |
| Tax rate | 40% | 40% | 40% | 40% | 40% | 40% | 40% | 40% | | | | | Total | $3,230 | $2,930 |
| 2. Capital Structure | Actual Market Weights | | | Target Market Weights |
| % Long-term debt | 22% | 31% | 41% | 28% | 28% | 28% | 28% | 28% | See the box to the right for calculations of the actual capital structures, based on market values, for the past two years. | | | | Percent long-term debt | 31% | 41% |
| % Short-term debt | 3% | 4% | 10% | 2% | 2% | 2% | 2% | 2% | | | | | Percent short-term debt | 4% | 10% |
| % Preferred stock | 0% | 3% | 3% | 3% | 3% | 3% | 3% | 3% | | | | | Percent preferred stock | 3% | 3% |
| % Common stock | 75% | 62% | 46% | 67% | 67% | 67% | 67% | 67% | | | | | Percent market value of equity | 62% | 46% |
| 3. Costs of Capital | | | | Forecast | | | | | | | | | Total | 100% | 100% |
| Rate on LT debt | | | | 9.0% | 9% | 9% | 9% | 9% |
| Rate on ST debt | | | | 10.0% | 10% | 10% | 10% | 10% |
| Rate on preferred stock (ignoring flotation costs) | | | | 8.0% | 8% | 8% | 8% | 8% |
| Cost of equity | | | | 13.58% | 14% | 14% | 14% | 14% |
| 4. Target Dividend Policy | | Actual |
| Growth rate of dividends | | 11% | 4.2% | 5% | 5% | 5% | 5% | 5% |
| 12-3 Forecasting Operations |
| The figure below shows the forecasted items for the operating plan. For convenience, we repeat the inputs of operating ratios. |
| Section B1 shows the sales forecast. Each year's sales is equal to the previous year's sales multiplied by the forecasted sales growth rate. |
| Section B2 shows the projections of operating assets and operating liabilities. The operating asset for a particular year is equal to the product of that asset's ratio in Section A1 and that particular year's projected sales. The operating liabilities are projected in a similar manner. |
| Section B3 shows the projections of operating income. The COGS and other operating expenses are equal to the product of the ratio in Section A1 and that particular year's projected sales. Depreciation is equal to the product of the ratio in Section A1 and that particular year's projected net PP&E. EBIT is net sales minus COGS, depreciation, and other operating expenses. NOPAT is EBIT(1-T), where T is the tax rate. |
| Section B4 shows the projections of free cash flows. NOWC is equal to operating CA (i.e., cash, accounts receivable, and inventories from Section B2) minus operating CL (i.e., accounts payable and accruals from Section 4). Total capital is equal to the sum of NOWC and net PP&E (from Section B2). |
| Section B5 shows the results of the operating plan. The first rows in Section B5 report the target WACC (calculated as shown in Chapter 9), the return on invested capital, and the growth rate in FCF. |
| The horizon value, value of operations, and estimated intrinsic stock price are calculated using the FCF valuation model as present in Chapter 7. |
| Note: Do not change inputs here because these inputs are linked to the ones in Figure 12-2. If you want to change inputs, do so in Figure 12-2. |
| Figure 12-3 |
| MicroDrive's Forecast of Operations for the Selected Scenario (Millions of Dollars, Except for Per Share Data) |
| Status Quo | Industry | MicroDrive | | MicroDrive |
| Panel A: Inputs | Actual | Actual | | Forecast |
| A1. Operating Ratios | 2013 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 |
| Sales growth rate | 5% | 15% | 5% | 10% | 8% | 7% | 5% | 5% |
| COGS (excl. depr.) / Sales | 76% | 75% | 76% | 76% | 76% | 76% | 76% | 76% |
| Depreciation / Net PP&E | 9% | 10% | 10% | 10% | 10% | 10% | 10% | 10% |
| Other op. exp. / Sales | 10% | 10% | 10% | 10% | 10% | 10% | 10% | 10% |
| Cash / Sales | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% |
| Acc. rec. / Sales | 8% | 8% | 10% | 10% | 10% | 10% | 10% | 10% |
| Inventory / Sales | 15% | 17% | 20% | 20% | 20% | 20% | 20% | 20% |
| Net PP&E / Sales | 33% | 36% | 40% | 40% | 40% | 40% | 40% | 40% |
| Acc. pay. / Sales | 4% | 4% | 4% | 4% | 4% | 4% | 4% | 4% |
| Accruals / Sales | 7% | 6% | 6% | 6% | 6% | 6% | 6% | 6% |
| Tax rate | 40% | 40% | 40% | 40% | 40% | 40% | 40% | 40% |
| Panel B: Results | | | Actual | Forecast |
| B1. Sales Revenues | | | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 |
| Net sales | | | $5,000 | $5,500 | $5,940 | $6,356 | $6,674 | $7,007 |
| B2. Operating Assets and Operating Liabilities |
| Cash | | | $50 | $55 | $59 | $64 | $67 | $70 |
| Accounts receivable | | | $500 | $550 | $594 | $636 | $667 | $701 |
| Inventories | | | $1,000 | $1,100 | $1,188 | $1,271 | $1,335 | $1,401 |
| Net PP&E | | | $2,000 | $2,200 | $2,376 | $2,542 | $2,669 | $2,803 |
| Accounts payable | | | $200 | $220 | $238 | $254 | $267 | $280 |
| Accruals | | | $300 | $330 | $356 | $381 | $400 | $420 |
| B3. Operating Income |
| COGS (excl. depr.) | | | $3,800 | $4,180 | $4,514 | $4,830 | $5,072 | $5,326 |
| Depreciation | | | $200 | $220 | $238 | $254 | $267 | $280 |
| Other operating expenses | | | $500 | $550 | $594 | $636 | $667 | $701 |
| EBIT | | | $500 | $550 | $594 | $636 | $667 | $701 |
| Net operating profit after taxes | | | $300 | $330 | $356 | $381 | $400 | $420 |
| B4. Free Cash Flows |
| Net operating working capital | | | $1,050 | $1,155 | $1,247 | $1,335 | $1,401 | $1,472 |
| Total operating capital | | | $3,050 | $3,355 | $3,623 | $3,877 | $4,071 | $4,274 |
| FCF = NOPAT – Δ op capital | | | −$260 | $25 | $88 | $128 | $207 | $217 |
| B5. Estimated Intrinsic Value |
| Target WACC | | | | 11.0% | 11.0% | 11.0% | 11.0% | 11.0% |
| Return on invested capital | | | 9.8% | 9.8% | 9.8% | 9.8% | 9.8% | 9.8% |
| Growth in FCF | | | | | 252% | 45.1% | 61.7% | 5.0% |
| Horizon Value: | | | | | | | Value of operations | $2,719 |
| | | | | | | | + ST investments | $0 |
| | = | $3,814 | | | | | Estimated total intrinsic value | $2,719 |
| | | | | | | | − All debt | $1,480 |
| Value of Operations: | | | | | | | − Preferred stock | $100 |
| | Present value of HV | $2,267 | | | | | Estimated intrinsic value of equity | $1,139 |
| | + Present value of FCF | $453 | | | | | ÷ Number of shares | $50 |
| | Value of operations = | $2,719 | | | | | Estimated intrinsic stock price = | $22.78 |
Lingyun017
CH12Figure1-3.xlsx
