Please help answer just part f. (in red) for this problem. I will be re-working it before I submit, but I need full guidance to better learn the concepts and how you got to them through Excel. Attached is the Excel document and the Excel Toolkit associated with it for use as a guide. Thank you for the help!

"Hatfield Medical Supplies?s stock price had been lagging its industry averages, so its board of directors brought in a new CEO, Jaiden Lee. Lee had brought in Ashley Novak, a finance MBA who had been working for a consulting company, to replace the old CFO, and Lee asked Ashley to develop the financial planning section of the strategic plan. In her previous job, Novak?s primary task had been to help clients develop financial forecasts, and that was one reason Lee hired her. "

"Novak began as she always did, by comparing Hatfield?s financial ratios to the industry averages. If any ratio was substandard, she discussed it with the responsible manager to see what could be done to improve the situation. The following data shows Hatfield?s latest financial statements plus some ratios and other data that Novak plans to use in her analysis."

f.)Continue with the same assumptions for the No Change scenario from the previous question, but now forecast the balance sheet and income statements for 2014 (but not for the following three years) using the following preliminary financial policy. (1) Regular dividends will grow by 10%. (2) No additional long-term debt or common stock will be issued. (3) The interest rate on all debt is 8%. (4) Interest expense for long-term debt is based on the average balance during the year. (5) If the operating results and the preliminary financing plan cause a financing deficit, eliminate the deficit by drawing on a line of credit. The line of credit would be tapped on the last day of the year, so it would create no additional interest expenses for that year. (6) If there is a financing surplus, eliminate it by paying a special dividend. After forecasting the 2014 financial statements, answer the following questions.

(1) How much will Hatfield need to draw on the line of credit?

(2) What are some alternative ways than those in the preliminary financial policy that Hatfield might choose to eliminate the financing deficit?

Chapter 12. Mini Case Hatfield Medical Supplies's stock price had been lagging its industry averages, so its board of directors brought in a new CEO, Jaiden Lee. Lee had brought in Ashley Novak, a finance MBA who had been working for a consulting company, to replace the old CFO, and Lee asked Ashley to develop the financial planning section of the strategic plan. In her previous job, Novak's primary task had been to help clients develop financial forecasts, and that was one reason Lee hired her. Novak began as she always did, by comparing Hatfield's financial ratios to the industry averages. If any ratio was substandard, she discussed it with the responsible manager to see what could be done to improve the situation. The following data shows Hatfield's latest financial statements plus some ratios and other data that Novak plans to use in her analysis. Hatfield Medical Supplies: Balance Sheet (Millions of Dollars), 12/31/2013 Hatfield Medical Supplies: Income Statement (Millions of Dollars Except per Share) 2013 Cash Accts. rec. Inventories Total CA Net fixed assets Total assets $20 $280 $400 $700 $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 $2,000.00 $1,800.00 $50.00 $150.00 $40.00 $110.00 $44.00 $66.00 $20.00 $46.00 10 $6.60 $2.00 $52.80 Selected Ratios and Other Data, 2013 Op. costs/Sales Depr./FA Cash/Sales Receivables/Sales Inventories/Sales Fixed assets/Sales Acc. pay. & accr. / Sal Tax rate ROIC NOPAT/Sales Total op. capital/Sale Additional Data Exp. Saled growth rat Interest rate on LT de Target WACC Hatfield Industry 90% 88% Total liability/Total 10% 12% Times interest earne 1% 1% Return on assets (RO 14% 11% Profit margin (M) 20% 15% Sales/Assets 25% 22% Assets/Equity 4% 4% Return on equity (RO 40% 40% P/E ratio 8.00% 12.50% 4.50% 5.60% 56.00% 45.00% Hatfield 48.30% 3.8 5.50% 3.30% 1.67 1.94 10.60% 8 2014 10% 8% 9% a. Using Hatfield's data and its industry averages, how well run would you say Hatfield appears to be in comparison with other firms in its industry? What are its primary strengths and weaknesses? Be specific in your answer, and point to various ratios that support your position. Also, use the Du Pont equation (see Chapter 3) as one part of your analysis. b. Use the AFN equation to estimate Hatfield's required new external capital for 2014 if the sale growth rate is 10%. Assume that the firm's 2013 ratios will remain the same in 2014. (Hint: Hatfield was operating at full capacity in 2013.) c. Define the term capital intensity. Explain how a decline in capital intensity would affect the AFN, other things held constant. Would economies of scale combined with rapid growth affect capital intensity, other things held constant? Also, explain how changes in each of the following would affect AFN, holding other things constant: the growth rate, the amount of accounts payable, the profit margin, and the payout ratio. Industry 36.70% 8.9 10.20% 4.99% 2.04 1.58 16.10% 16 d. Define the term self-supporting growth rate. What is Hatfield's self-supporting growth rate? Would the self-supporting growth rate be affected by a change in the capital intensity ratio or the other factors mentioned in the previous question? Other things held constant, would the calculated capital intensity ratio change over time if the company were growing and were also subject to economies of scale and/or lumpy assets e. Use the following assumptions to answer the questions below: (1) Operating ratios remain unchanged. (2) Sales will grow by 10%, 8%, 5%, and 5% for the next four years. (3) The target weighted average cost of capital (WACC) is 9%. This is the No Change scenario because operations remain unchanged. e. (1) For each of the next four years, forecast the following items: sales, cash, accounts receivable, inventories, net fixed assets, accounts payable & accruals, operating costs (excluding depreciation), depreciation, and earnings before interest and taxes (EBIT). e. (2) Using the previously forecasted items, calculate for each of the next four years the net operating profit after taxes (NOPAT), net operating working capital, total operating capital, free cash flow, (FCF), annual growth rate in FCF, and return on invested capital. What does the forecasted free cash flow in the first year imply about the need for external financing? Compare the forecasted ROIC compare with the WACC. What does this imply about how well the company is performing? e. (3) Assume that FCF will continue to grow at the growth rate for the last year in the forecast horizon (Hint: 5%). What is the horizon value at 2017? What is the present value of the horizon value? What is the present value of the forecasted FCF? (Hint: use the free cash flows for 2014 through 2017). What is the current value of operations? Using information from the 2013 financial statements, what is the current estimated intrinsic stock price? f. Continue with the same assumptions for the No Change scenario from the previous question, but now forecast the balance sheet and income statements for 2014 (but not for the following three years) using the following preliminary financial policy. (1) Regular dividends will grow by 10%. (2) No additional long-term debt or common stock will be issued. (3) The interest rate on all debt is 8%. (4) Interest expense for long-term debt is based on the average balance during the year. (5) If the operating results and the preliminary financing plan cause a financing deficit, eliminate the deficit by drawing on a line of credit. The line of credit would be tapped on the last day of the year, so it would create no additional interest expenses for that year. (6) If there is a financing surplus, eliminate it by paying a special dividend. After forecasting the 2014 financial statements, answer the following questions. (1) How much will Hatfield need to draw on the line of credit? (2) What are some alternative ways than those in the preliminary finacial policy that Hatfield might choose to eliminate the financing deficit? Tool Kit Chapter 12 5/6/2013 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 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 Pre-tax 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 equity Price per share $40.00 $27.00 Total liabs. & equity $ $ $ $ $ $ $ $ 2012 60 $ 40 380 820 1,300 $ 1,700 3,000 $ 2013 50 500 1,000 1,550 2,000 3,550 190 280 130 600 1,000 1,600 100 500 800 1,300 3,000 200 300 280 780 1,200 1,980 100 500 970 1,470 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 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 PP&E / Sales Acc. pay. / Sales Accruals / Sales Tax rate 2. Capital Structure 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 % Long-term debt 22% 31% % Short-term debt 3% 4% % Preferred stock 0% 3% % Common stock 75% 62% 3. Costs of Capital Rate on LT debt Rate on ST debt Rate on preferred stock (ignoring flotation costs) Cost of equity 4. Target Dividend Policy Actual Growth rate of dividends 11% 41% 10% 3% 46% 2014 10% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 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% 2% 3% 67% 9% 10% 8% 14% 28% 2% 3% 67% Forecast 9% 10% 8% 14% 2018 5% 76% 10% 10% 1% Actual Historical Financing 10% 20% Long-term debt 40% Short-term debt 4% Preferred stock 6% Market value of equity = (Price x # shares) 40% See the box to the right Total for calculations of the actual capital 28% structures, based on Percent long-term debt 2% market values, for the Percent short-term debt 3% past two years. Percent preferred stock 67% Percent market value of equity 9% 10% 8% 14% 9% 10% 8% 14% 5% 5% 5% 5% 28% 2% 3% 67% 9.0% 10.0% 8.0% 13.58% 4.2% 28% 2% 3% 67% 5% 2017 5% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 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. Total 2012 $1,000 $130 $100 $2,000 $3,230 2013 $1,200 $280 $100 $1,350 $2,930 31% 4% 3% 62% 100% 41% 10% 3% 46% 100% 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) Industry MicroDrive MicroDrive Status Quo Panel A: Inputs Actual Actual Forecast A1. 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 PP&E / 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% Panel B: Results Actual Forecast B1. Sales Revenues 2013 2014 2015 2016 Net sales $5,000 $5,500 $5,940 $6,356 B2. Operating Assets and Operating Liabilities Cash $50 $55 $59 $64 Accounts receivable $500 $550 $594 $636 Inventories $1,000 $1,100 $1,188 $1,271 Net PP&E $2,000 $2,200 $2,376 $2,542 Accounts payable $200 $220 $238 $254 Accruals $300 $330 $356 $381 B3. Operating Income COGS (excl. depr.) $3,800 $4,180 $4,514 $4,830 Depreciation $200 $220 $238 $254 Other operating expenses $500 $550 $594 $636 EBIT $500 $550 $594 $636 Net operating profit after taxes $300 $330 $356 $381 B4. Free Cash Flows Net operating working capital $1,050 $1,155 $1,247 $1,335 Total operating capital $3,050 $3,355 $3,623 $3,877 FCF = NOPAT - op capital $260 $25 $88 $128 B5. Estimated Intrinsic Value Target WACC 11.0% 11.0% 11.0% Return on invested capital 9.8% 9.8% 9.8% 9.8% Growth in FCF 252% 45.1% 2017 5% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 2018 5% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 2017 $6,674 2018 $7,007 $67 $667 $1,335 $2,669 $267 $400 $70 $701 $1,401 $2,803 $280 $420 $5,072 $267 $667 $667 $400 $5,326 $280 $701 $701 $420 $1,401 $4,071 $207 $1,472 $4,274 $217 11.0% 9.8% 61.7% 11.0% 9.8% 5.0% $2,267 Value of operations + ST investments Estimated total intrinsic value All debt Preferred stock Estimated intrinsic value of equity $2,719 $0 $2,719 $1,480 $100 $1,139 $453 Number of shares $50 $2,719 Estimated intrinsic stock price = $22.78 Horizon Value: _= = ( _ of Operations: Value(+_))/ (( Present value of HV _)) + Present value of FCF Value of operations = $3,814 12-4 Projecting MicroDrive's Financial Statements Projecting 1 Year of Financial Statements Figure 12-4, shown below, projects MicroDrive's financial statements for the upcoming year for the Status Quo scenario. Operating items are projected in the identical manner as previously projected for the operating plan. The preliminary short-term financial policy calls for no changes in notes payable, long-term bonds, preferred stock, and common stock, so their values from the previous year are carried over. The interest on notes payable and long-term bonds is based on the average amount of debt during the year, defined as the average of the beginning debt (i.e., the debt at the end of the previous year) and the ending debt. An identical process is applied to preferred dividends. The preliminary short-term financial policy calls for dividends to grow at the same rate as the long-term sustainable growth rate in earnings (which is the same as sales in the long-term). Section 3 in the figure below calculates the additional financing provided by spontaneous liabilities, external sources, and internal sources. The sum of these three sources of financing is the total amount of additional preliminary financing. Section 3 also calculates the total amount of additional assets required by the operating plan. The difference between the total additional financing and the total additional assets is defined as the financing deficit (if the difference is negative) or the financing surplus (if the difference is positive). 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 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 balance at the end of the previous year). If there is a financing surplus, MicroDrive will pay a special dividend. 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-4 Projected Financial Statements (Millions of Dollars) Status Quo 1. Balance Sheets Most Recent 2013 Assets Cash $50.0 Accounts receivable 500.0 Inventories 1,000.0 Total current assets $1,550.0 Net PP&E 2,000.0 Total assets (TA) $3,550.0 Liabilities and equity Accounts payable $200.0 Accruals 300.0 Notes payable 280.0 Line of credit 0.0 Total CL $780.0 Long-term bonds 1,200.0 Total liabilities $1,980.0 Preferred stock $100.0 Common stock 500.0 Retained earnings 970.0 Total common equity $1,470.0 Total liabs. & equity $3,550.0 Input Basis for 2014 Forecast 1.00% 2014 Sales 10.00% 2014 Sales 20.00% 2014 Sales 40.00% 2014 Sales 4.00% 2014 Sales 6.00% 2014 Sales Carry over from previous year Draw on LOC if financing deficit Carry over from previous year Carry over from previous year Carry over from previous year Old RE + Add. to RE Check: TA Total Liab. & Eq. = 2. Income Statement Net sales COGS (excl. depr.) Most Recent 2013 $5,000.0 3,800.0 Input Basis for 2014 Forecast 110% 2013 Sales 76.00% 2014 Sales Forecast 2014 $55.00 $550.00 $1,100.00 $1,705.00 $2,200.00 $3,905.00 $220.00 $330.00 $280.00 $117.10 $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 Depreciation Other operating expenses EBIT Less: Interest on notes Interest on bonds Interest on LOC Pre-tax earnings Taxes (40%) NI before pref. div. Preferred div. Net income Regular common dividends Special dividends Addition to RE 200.0 $500.0 $500.0 20.0 100.0 0.0 $380.0 152.0 $228.0 8.0 $220.0 $50.0 $0.0 $170.0 10.00% 2014 Net PP&E 10.00% 2014 Sales 105% 2013 Dividend Pay if financing surplus Net income - Dividends $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 3. Elimination of 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 + Net income minus regular common dividends Increase in financing Increase in total assets Amount of deficit or surplus financing: If deficit in financing (negative), draw on line of credit Line of credit If surplus in financing (positive), pay special dividend Special dividend $50.00 $0.00 $187.90 $237.90 $355.00 $117.10 $117.10 $0.00 10.00% Avg notes 9.00% Avg bonds 11.50% Beginning LOC 40.00% Pretax earnings 8.00% Avg pref. stock 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 words, this is Note: This is the planned increase in the retained earnings account. 12-4 Analysis and Revision of the Preliminary Plan Projected 5-Year Statements Projected Financial Statements (Millions of Dollars) Status Quo 1. Balance Sheets Actual 2013 Assets Cash $50.0 Accounts receivable 500.0 Inventories 1,000.0 Total current assets $1,550.0 Net PP&E 2,000.0 Total assets (TA) $3,550.0 Liabilities and equity Accounts payable $200.0 Accruals 300.0 Notes payable 280.0 Line of credit 0.0 Total CL $780.0 Long-term bonds 1,200.0 Total liabilities $1,980.0 Preferred stock $100.0 Common stock 500.0 Retained earnings 970.0 Total common equity $1,470.0 Total liabs. & equity $3,550.0 2014 2015 Forecast 2016 2017 2018 $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 $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 $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 $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 Note: 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 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. 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 $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 3. Incorporating the Financial Deficit or Surplus Increase in spontaneous liabilities (accounts payable an + Increase in notes payable, long-term bonds, preferre Previous line of credit + Net income minus regular common dividends Increase in financing Increase in total assets Amount of deficit or surplus financing: Line of credit Special dividend $50.0 $0.0 $0.0 $187.9 $237.9 $355.0 $117.1 $117.1 $0.0 $44.0 $0.0 $117.1 $203.6 $130.5 $312.4 $181.9 $181.9 $0.0 $41.6 $0.0 $181.9 $221.3 $81.0 $295.2 $214.2 $214.2 $0.0 $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 Actual 2013 Forecast 2014 Forecast 2015 Forecast 2016 Forecast 2017 Forecast 2018 $228.0 $248.4 $266.7 $287.2 $304.0 $326.9 2. Income Statement Statement of Cash Flows (Millions of Dollars) Status Quo 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. 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 $266.9 $280.3 ($120.0) ($180.0) $10.0 $20.0 $158.0 ($50.0) ($100.0) $20.0 $30.0 $368.4 ($44.0) ($88.0) $17.6 $26.4 $416.3 ($41.6) ($83.2) $16.6 $24.9 $458.3 ($31.8) ($63.6) $12.7 $19.1 $507.4 ($33.4) ($66.7) $13.3 $20.0 $540.5 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) ($394.1) $0.0 ($394.1) ($413.8) $0.0 ($413.8) 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 $150.0 $0.0 $200.0 $0.0 ($58.0) $0.0 $292.0 $0.0 $117.1 $0.0 $0.0 ($60.5) $0.0 $56.6 $0.0 $64.8 $0.0 $0.0 ($63.1) $0.0 $1.7 $0.0 $32.3 $0.0 $0.0 ($65.9) $0.0 ($33.6) $0.0 ($41.4) $0.0 $0.0 ($68.8) $0.0 ($110.2) $0.0 ($51.6) $0.0 $0.0 ($71.8) $0.0 ($123.4) Summary Net change in cash and equivalents Cash and securities at beginning of the year ($10.0) $60.0 $5.0 $50.0 $4.4 $55.0 $4.2 $59.4 $3.2 $63.6 $3.3 $66.7 Cash and securities at end of the year $50.0 $55.0 $59.4 $63.6 $66.7 $70.1 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-5 (Status Quo Scenario) or Figure 12-6 (Final Scenario) Summary of Important Inputs and Key Results for Selected Scenario (Millions Except Percentages and Per Share Data) Status Quo Panel A: Inputs A1. Operating Ratios Sales growth rate COGS (excl. depr.) / Sales Inventory / Sales Net PP&E / Sales Industry Actual Actual 2013 2013 5% 5% 76% 76% 15% 20% 33% 40% Panel B: Key Results Industry Actual 2013 NA 15.0% 6.9% 46.0% 5.0 30.0 3.0 2014 10% 76% 20% 40% Actual 2013 $260 9.8% 6.0% 61.0% 4.0 36.5 2.5 B1. Operations 2014 Free cash flow $25 Return on invested capital 9.8% NOPAT/Sales 6.0% Total op. capital / Sales 61.0% Inventory turnover 4.0 Days sales outstanding 36.5 Fixed asset turnover 2.5 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 = MicroDrive Forecast 2015 2016 8% 7% 76% 76% 20% 20% 40% 40% 2017 5% 76% 20% 40% Status Quo Industry Panel A: Inp Actual Actual A1. Operatin 2013 2013 Sales growth 5% COGS (excl. 76% Inventory / 15% Net PP&E / 33% 2018 5% 76% 20% 40% MicroDrive Forecast 2015 2016 $88 $128 9.8% 9.8% 6.0% 6.0% 61.0% 61.0% 4.0 4.0 36.5 36.5 2.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 53.5% 4.4% 6.1% 13.9% 4.0 $182 21.3% $1.10 $0.00 $5.17 49.0% 4.4% 6.2% 12.8% 4.2 $173 20.5% $1.22 $0.00 $5.92 46.3% 4.6% 6.4% 12.4% 4.5 $121 20.0% $1.28 $0.00 $6.38 51.6% 4.4% 6.2% 13.4% 4.1 $214 20.7% $1.16 $0.00 $5.58 12.6 Additional Funds Needed (AFN) Equation Method The AFN model forecasts MicroDrive's need for external funds to support its forecasted next year's sales. Figure 12-7 Additional Funds Needed (AFN) (Millions of Dollars) AFN = = = = = AFN = Required Increase in Assets (A0*/S0)S (A0*/S0)(gS0) (0.710)($500) $355 $118.00 Increase in Spon. Liab. (L0*/S0)S (L0*/S0)(gS0) (0.10)($500) $50.00 $5,000 10.00% $5,500 $500 $3,550 71.00% $500 10.00% 4.40% 22.73% Addition to Retained Earnings. S1 M (1 - POR) (1+g)S0 M (1 - POR) $5,500(0.044)(1 - 0.2273) $187.00 Self-Supporting Growth Rate This is the maximum growth rate that can be attained without raising external funds, i.e., the value of g that forces AFN = 0, holding other things constant. 1. Using algebra. The sustainable growth rate can also be found by solving the equation as shown on the 3rd row above g, then finding the value of g that causes AFN to equal zero. Sustainable g = PM(1 - POR)(S0) = A0* - L0* - PM(1 - POR)S0 $170.00 = 5.90% $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 96% 2013 Sales $5,000 2014 2014 Sales $5,500 2014 2014 Fixed Assets $2,000 2013 Fixed assets/Sales = 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 5% 76% 20% 40% Panel B: Key Industry Actual Actual B1. Operati 2013 2013 Free cash f NA $260 Return on in 15.0% 9.8% NOPAT/Sale 6.9% 6.0% Total op. cap 46.0% 61.0% Inventory t 5.0 4.0 Days sales 30.0 36.5 Fixed asset 3.0 2.5 B2. Financing Total liabili 45.0% 55.8% Net income 6.2% 4.4% Return on a 11.0% 6.2% Return on e 19.0% 15.0% Times inter 10.0 4.2 Line of cred NA $0 Payout ratio 35.0% 22.7% Regular div NA $1.00 Special div NA $0.00 Earnings pe NA $4.40 B3. Estimated intrinsic value ### Estimated value of operations = ### Estimated intrinsic stock price = $2,719 $22.78 Part A. Inputs and Definitions Most recent year's sales = S0: g: Forecasted growth rate in sales = Next year's sales: S0 (1 + g) = S1: gS0: Change in sales = S1 - S0 = S = A0*: Most recent year's operating assets = A0* / S0: Required assets per dollar of sales = L0*: Most recent year's spontaneous liabilities i.e., payables + accruals = L0* /S0: Spontaneous liabilities per dollar of sales = Most recent profit margin = net income/sales = Profit margin (M): Most recent year's dividends / net income = % of income paid out = Payout ratio (POR): Part B. Additional Funds Needed (AFN) to Support Growth MicroDrive 2014 10% 76% 20% 40% 2015 8% 76% 20% 40% Forecast 2016 7% 76% 20% 40% 2017 5% 76% 20% 40% 2018 5% 76% 20% 40% MicroDrive 2014 $25 9.8% 6.0% 61.0% 4.0 36.5 2.5 2015 $88 9.8% 6.0% 61.0% 4.0 36.5 2.5 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 55.0% 4.4% 6.2% 14.5% 4.0 $117 21.8% $1.05 $0.00 $4.81 53.5% 4.4% 6.1% 13.9% 4.0 $182 21.3% $1.10 $0.00 $5.17 51.6% 4.4% 6.2% 13.4% 4.1 $214 20.7% $1.16 $0.00 $5.58 49.0% 4.4% 6.2% 12.8% 4.2 $173 20.5% $1.22 $0.00 $5.92 46.3% 4.6% 6.4% 12.4% 4.5 $121 20.0% $1.28 $0.00 $6.38 $2,719 $22.78 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 = $112 Difference between required increase assuming no excess capacity and required increase if there is excess capacity = $88 AFN if no excess capacity = $118 AFN if there is excess capacity = $30 s like paying off the old line of credit on the last day of the year and then drawing on a new line of credit. 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 15% $3,000 million A0*/ S0 66.666% L0*/ S0 6.667% 3.783% 50.670% Profit margin (M) Payout ratio Sales S1 AFN $450.00 million $3,450.00 million $205.62 million 5/6/2013 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 COGS (excl. depr.) Depreciation Other operating expenses EBIT Interest expense Pretax earnings Taxes (40%) NI before pref. div. Preferred div. Net income Other Data Common dividends Addition to RE Tax rate Shares of common stock Earnings per share Dividends per share Price per share $ $ $ $ $ 4,760 3,560 170 480 550 100 450 180 270 8 262 $ $ $ $ $ $48 $214 40% 50 $5.24 $0.96 $40.00 5,000 3,800 200 500 500 120 380 152 228 8 220 $50 $170 40% 50 $4.40 $1.00 $27.00 2012 Cash ST Investments Accounts receivable Inventories Total CA Net PP&E Total assets $ $ $ Liabilities and equity Accounts payable Accruals Notes payable Total CL Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liabs. & equity $ $ $ $ $ 2013 60 $ 40 380 820 1,300 $ 1,700 3,000 $ 50 500 1,000 1,550 2,000 3,550 190 280 130 600 1,000 1,600 100 500 800 1,300 3,000 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. Figure 12-2. Repeated here for convenience. MicroDrive's Forecast: Inputs for the Selected Scenario 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 Industry Actual 2013 5% 76% 9% 10% 1% 5. Capital Structure Choices % Long-term debt, wLTD % Short-term debt, wSTD % Preferred stock, wps % Common stock, ws 31% 4% 3% 62% Actual 11% 2014 MicroDrive Forecast 2015 2016 5% 76% 10% 10% 1% 10% 76% 10% 10% 1% 8% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 10% 20% 40% 4% 6% 40% 10% 10% 20% 20% 40% 40% 4% 4% 6% 6% 40% 40% Target Market Weights 10% 20% 40% 4% 6% 40% 41% 10% 3% 46% 28% 2% 3% 67% 28% 2% 3% 67% 28% 2% 3% 67% 9% 10% 8% 14% 28% 2% 3% 67% Forecast 9% 10% 8% 14% 10% 20% 40% 4% 6% 40% See the box to the right for calculations of the actual capital 28% structures, based on 2% market values, for the 3% past two years. 67% 9.0% 10.0% 8.0% 13.58% 15% 75% 10% 10% 1% 8% 8% 15% 17% 33% 36% 4% 4% 7% 6% 40% 40% Actual Market Weights % Long-term debt 22% % Short-term debt 3% % Preferred stock 0% % Common stock 75% 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 MicroDrive Actual 2012 2013 9% 10% 8% 14% 9% 10% 8% 14% 4.2% 5% 5% 5% 5% 5% 41.0% 9.6% 3.4% 46.1% 38.4% 8.0% 3.3% 50.3% 35.8% 6.5% 3.2% 54.4% 33.2% 5.0% 3.2% 58.6% 30.6% 3.5% 3.1% 62.8% 28% 2% 3% 67% 7% 76% 10% 10% 1% 2017 2018 5% 76% 10% 10% 1% 5% 76% 10% 10% 1% 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 The following projections incorporate the impact of financing feedback. They also ensure that the actual capital structure matches the target capital 2012 $1,000 $130 $100 $2,000 $3,230 2013 $1,200 $280 $100 $1,350 $2,930 31% 4% 3% 62% 100% 41% 10% 3% 46% 100% 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 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 2 3 4 5 6 7 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. 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 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 2016 2013 Total liabilities Preferred stock (wps Vop) Common stock Retained earnings Total common equity Total liabs. & equity $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 $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 $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 Line of credit (After adjustment for feedback effects) Total CL Long-term bonds (wLTD Vop) 2015 $50.0 500.0 1,000.0 $1,550.0 2,000.0 $3,550.0 Assets Cash Accounts receivable Inventories Total current assets Net PP&E Total assets (TA) Liabilities and equity Accounts payable Accruals Notes payable (wSTD Vop) 2014 $220.0 330.0 240.7 213.4 $1,004.2 1,148.0 $2,152.2 $99.7 500.0 1,153.1 $1,653.1 $3,905.0 $237.6 356.4 211.2 297.9 $1,103.2 1,156.5 $2,259.6 $105.0 500.0 1,352.8 $1,852.8 $4,217.4 $254.2 381.3 173.8 375.1 $1,184.4 1,148.0 $2,332.4 $109.5 500.0 1,570.7 $2,070.7 $4,512.6 $266.9 400.4 127.6 415.9 $1,210.8 1,111.3 $2,322.1 $112.0 500.0 1,804.1 $2,304.1 $4,738.2 $280.3 420.4 76.3 459.2 $1,236.2 1,068.0 $2,304.2 $114.4 500.0 2,056.6 $2,556.6 $4,975.2 Check: TA Total Liab. & Eq. = 2. Income Statement Net sales COGS (excl. depr.) Depreciation Other operating expenses EBIT Less: Interest on notes payable, based on average NP and rSTD 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 Actual 2013 $5,000.0 3,800.0 200.0 $500.0 $500.0 20.0 100.0 0.0 $380.0 152.0 $228.0 8.0 $220.0 $50.0 $0.0 $170.0 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 + Planned increase in retained earnings + After-tax operating income: EBIT (1-T) After-tax interest on notes payable (INTSTD x (1-T) 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: $0.00 2017 $0.00 2018 $0.00 $0.00 $0.00 2014 $5,500.0 4,180.0 220.0 $550.0 $550.0 26.0 105.7 12.3 $406.0 162.4 $243.6 8.0 $235.6 $52.5 $0.0 $183.1 2015 $5,940.0 4,514.4 237.6 $594.0 $594.0 22.6 103.7 29.4 $438.3 175.3 $263.0 8.2 $254.8 $55.1 $0.0 $199.7 Forecast 2016 $6,355.8 4,830.4 254.2 $635.6 $635.6 19.2 103.7 38.7 $473.9 189.6 $284.4 8.6 $275.8 $57.9 $0.0 $217.9 2017 $6,673.6 5,071.9 266.9 $667.4 $667.4 15.1 101.7 45.5 $505.1 202.1 $303.1 8.9 $294.2 $60.8 $0.0 $233.5 2018 $7,007.3 5,325.5 280.3 $700.7 $700.7 10.2 98.1 50.3 $542.2 216.9 $325.3 9.1 $316.2 $63.8 $0.0 $252.4 $50.0 $91.6 $0.0 $44.0 $15.8 $213.4 $41.6 $41.4 $297.9 $31.8 $80.5 $375.1 $33.4 $92.1 $415.9 $330.0 $15.6 $63.4 $0.0 $8.0 $52.5 $190.5 $356.4 $13.6 $62.2 $7.4 $8.2 $55.1 $209.9 $381.3 $11.5 $62.2 $10.3 $8.6 $57.9 $230.8 $400.4 $9.0 $61.0 $12.9 $8.9 $60.8 $247.8 $420.4 $6.1 $58.8 $14.3 $9.1 $63.8 $268.3 $148.9 $355.0 $206.1 $24.7 $312.4 $287.7 $66.9 $295.2 $362.1 $176.0 $225.6 $401.6 $206.4 $236.9 $443.3 Note: We subtract the previous LOC because the plan does not call for any projected LOC unless necessary. 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. If there is a surplus (the financing need is positive), pay a special dividend: $0.0 $0.0 $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 $401.6 0.97 $415.9 $443.3 0.97 $459.2 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 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). Value of operations: Vop,t-1 = [FCFt +Vop,t]/(1+WACC). Choice of long-term bonds (wLTD Vop) Choice of notes payable (wSTD Vop) Choice of preferred stock (wps Vop) 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% 2017 $1,401 $4,071 $400 $207 61.7% 10.97% $2,719 $1,200 $280 $100 $2,992 $1,148 $241 $100 $3,233 $1,156 $211 $105 $3,460 $1,148 $174 $110 $3,633 $1,111 $128 $112 Actual 2013 Forecast 2014 Forecast 2015 Forecast 2016 Forecast 2017 Forecast 2018 $228.0 $243.6 $263.0 $284.4 $303.1 $325.3 2014 $1,155 $3,355 $330 $25 2018 $1,472 $4,274 $420 $217 5.0% 10.97% $3,814 $3,814 Note: $1,068 $76 $114 5. Estimating the Intrinsic Stock Price Value of operations + ST investments Estimated total intrinsic value All debt Preferred stock Estimated intrinsic value of equity 2013 $2,719 $0 $2,719 $1,480 $100 $1,139 Number of shares Estimated intrinsic stock price = $50 $22.78 Statement of Cash Flows (Millions of Dollars) Status Quo 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 $266.9 $280.3 ($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 ($31.8) ($63.6) $12.7 $19.1 $506.5 ($33.4) ($66.7) $13.3 $20.0 $538.8 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) ($394.1) $0.0 ($394.1) ($413.8) $0.0 ($413.8) 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 $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) ($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) Summary Net change in cash and equivalents Cash and securities at beginning of the year Cash and securities at end of the year ($10.0) $60.0 $50.0 $5.0 $50.0 $55.0 $4.4 $55.0 $59.4 $4.2 $59.4 $63.6 $3.2 $63.6 $66.7 $3.3 $66.7 $70.1 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 2017 $207 9.8% 6.0% 61.0% 4.0 36.5 2018 $217 9.8% 6.0% 61.0% 4.0 36.5 The value of operations at the horizon is equal to the horizon value. Fixed asset turnover 2. Financing Total liabilities / TA Net income / Sales Return on assets (ROA) Return on equity (ROE) Times interest earned Line of credit Payout ratio Regular dividends/share Special dividends/share Earnings per share 3. Estimated intrinsic value 3.0 2.5 2.5 2.5 2.5 2.5 2.5 45.0% 6.2% 11.0% 19.0% 10.0 NA 35.0% NA NA 55.8% 4.4% 6.2% 15.0% 4.2 $0 22.7% $1.00 $0.00 55.1% 4.3% 6.0% 14.3% 3.8 $213 22.3% $1.05 $0.00 53.6% 4.3% 6.0% 13.8% 3.8 $298 21.6% $1.10 $0.00 51.7% 4.3% 6.1% 13.3% 3.9 $375 21.0% $1.16 $0.00 49.0% 4.4% 6.2% 12.8% 4.1 $416 20.7% $1.22 $0.00 46.3% 4.5% 6.4% 12.4% 4.4 $459 20.2% $1.28 $0.00 NA $4.40 $4.71 $5.10 $5.52 $5.88 $6.32 12/31/2013 Estimated intrinsic stock price = $22.78 Mini Case Data 5/6/2013 Figure 12-MC-1. Financial Statements and Other Data (Millions except per share data) Hatfield Medical Supplies: Balance Sheet (Millions of Dollars), 12/31/2013 Cash Accts. rec. Inventories Total CA Hatfield Medical Supplies: Income Statement (Millions of Dollars Except per Share) 2013 Sales $2,000.0 Op. costs (excl. depr.) $1,800.0 Depreciation $50.0 EBIT $150.0 $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 Interest Pretax earnings Taxes (40%) Net income $80 $0 $80 $500 $580 $420 $200 $620 $1,200 $40.0 $110.0 $44.0 $66.0 Dividends Add. to RE Common shares EPS DPS Ending stock price $20.0 $46.0 10.0 $6.6 $2.0 $52.80 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 90% 10% 1% 14% 20% 25% 4% 40% 8.0% 4.5% 56.0% 2014 10% 8% 9% Industry 88% 12% 1% 11% 15% 22% 4% 40% 12.5% 5.6% 45.0% 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 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