I need, I am working on GENERAL ELECTRIC AND UNITED TECHNOLOGIES CORP, I need help GE and UTC Financial Statements Forecast for the next 5 year using the attachment.

Throughout this course you will prepare a comprehensive financial analysis and proposal (excluding tables, figures, and addenda) that will demonstrate your understanding of key financial concepts, strategies and practices. After selecting a company to profile, you will construct a comparative financial analysis of your selected company?s financial position with that of a prime competitor, for example, Amazon and Netflix, Microsoft and Oracle, or Bank of America and JPMorgan Chase & Co. Following the nine-step assessment process detailed in Assessing a Company?s Future Financial Health, your financial analysis and proposal will be composed of four separate component assignments in topics 2, 4, 6, and 8.

For this assignment, apply the following two steps of the nine-step assessment process detailed in Assessing a Company?s Future Financial Health (i.e., Step 3: Investments in Assets and Step 4: Economic Performance)to compose further assessment of the company/competitor pairing analysis as below:

1. Review the Management?s Discussion and Review (MDR), aka the Management?s Discussion and Analysis (MDA) section, which can be obtained from the SEC 10-K reports. Develop pro-forma financial statements for your company/competitor for the next 5 years. Pro-forma financial statements require us to make conservative assumptions about future growth; as such, your discussion must justify and support any assumptions you have made in developing the pro-formas.
2. Discuss current strategy (ies) used by the company/competitor and any relevant future investments required to support the business unit (s) strategy(ies) to achieve higher ROI and market position.
3. Perform a ratio analysis on the two pro-forma financial statements you?ve developed (company and competitor), and provide a discussion regarding future profitability and competitive performance as well as any significant changes you observe. To successfully complete this assignment, please show all ratio analysis calculations.

Note: You will be required to re-submit this assignment, revised to incorporate all instructor feedback, along with the other three component assignments as one comprehensive submission in Topic 8. To save time later in the course, consider addressing any feedback soon after this assignment has been graded and returned to you.

Prepare this assignment according to the guidelines found in the APA Style Guide, located in the Student Success Center. An abstract is not required.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are required to submit this assignment to Turnitin. Please refer to the directions in the Student Success Center.

A B C D E F G 1 Tool Kit Chapter 12 2 3 Corporate Valuation and Financial Planning 4 5 12-2 Financial Planning at MicroDrive, Inc. 6 7 The process used by MicroDrive to forecast the free cash flows from its operating plan is described in the sections below. 8 9 Setting Up the Model to Forecast Operations 10 11 We begin with MicroDrive's most recent financial statements and selected additional data. 12 13 Figure 12-1 14 MicroDrive's Most Recent Financial Statements (Millions, Except for Per Share Data) BALANCE SHEETS 15 INCOME STATEMENTS 2012 2013 Assets 2012 2013 16 $ 4,760 $ 5,000 Cash $ 60 $ 50 17 Net sales 3,560 3,800 ST Investments 40 18 COGS (excl. depr.) 170 200 Accounts receivable 380 500 19 Depreciation 480 500 Inventories 820 1,000 20 Other operating expenses EBIT $ 550 $ 500 Total CA $ 1,300 $ 1,550 21 100 120 Net PP&E 1,700 2,000 22 Interest expense Pre-tax earnings $ 450 $ 380 Total assets $ 3,000 $ 3,550 23 180 152 24 Taxes (40%) NI before pref. div. $ 270 $ 228 Liabilities and equity 25 8 8 Accounts payable $ 190 $ 200 26 Preferred div. Net income $ 262 $ 220 Accruals 280 300 27 Notes payable 130 280 28 Total CL $ 600 $ 780 29 Other Data $48 $50 Long-term bonds 1,000 1,200 30 Common dividends $214 $170 Total liabilities $ 1,600 $ 1,980 31 Addition to RE 40% 40% Preferred stock 100 100 32 Tax rate 50 50 Common stock 500 500 33 Shares of common stock $5.24 $4.40 Retained earnings 800 970 34 Earnings per share $0.96 $1.00 Total common equity $ 1,300 $ 1,470 35 Dividends per share $40.00 $27.00 Total liabs. & equity $ 3,000 $ 3,550 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 50 first projected year. 51 A B C D E F G 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 G Industry MicroDrive MicroDrive Status Quo 60 Actual Actual Forecast 61 Inputs 2013 2012 2013 2014 2015 2016 62 1. Operating Ratios Sales growth rate 5% 15% 5% 10% 8% 7% 63 COGS (excl. depr.) / Sales 76% 75% 76% 76% 0.76 0.76 64 Depreciation / Net PP&E 9% 10% 10% 10% 0.10 0.10 65 Other op. exp. / Sales 10% 10% 10% 10% 0.10 0.10 66 Cash / Sales 1% 1% 1% 1% 0.01 0.01 67 Acc. rec. / Sales 8% 8% 10% 10% 0.10 0.10 68 Inventory / Sales 15% 17% 20% 20% 0.20 0.20 69 Net PP&E / Sales 33% 36% 40% 40% 0.40 0.40 70 Acc. pay. / Sales 4% 4% 4% 4% 0.04 0.04 71 Accruals / Sales 7% 6% 6% 6% 0.06 0.06 72 Tax rate 40% 40% 40% 40% 0.40 0.40 73 Actual Market Weights Target Market Weights 74 2. Capital Structure % Long-term debt 22% 31% 41% 28% 28% 28% 75 % Short-term debt 3% 4% 10% 2% 2% 2% 76 % Preferred stock 0% 3% 3% 3% 3% 3% 77 % Common stock 75% 62% 46% 67% 67% 67% 78 Forecast 79 3. Costs of Capital Rate on LT debt 9.0% 9% 9% 80 Rate on ST debt 10.0% 10% 10% 81 Rate on preferred stock (ignoring flotation costs) 8.0% 8% 8% 82 Cost of equity 13.58% 14% 14% 83 Actual 84 4. Target Dividend Policy Growth rate of dividends 11% 4.2% 5% 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 101 expenses. 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 108 shown 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. 112 A B C D E F G 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 2016 118 A1. Operating Ratios Sales growth rate 5% 15% 5% 10% 8% 7% 119 COGS (excl. depr.) / Sales 76% 75% 76% 76% 0.76 0.76 120 Depreciation / Net PP&E 9% 10% 10% 10% 0.10 0.10 121 Other op. exp. / Sales 10% 10% 10% 10% 0.10 0.10 122 Cash / Sales 1% 1% 1% 1% 0.01 0.01 123 Acc. rec. / Sales 8% 8% 10% 10% 0.10 0.10 124 Inventory / Sales 15% 17% 20% 20% 0.20 0.20 125 Net PP&E / Sales 33% 36% 40% 40% 0.40 0.40 126 Acc. pay. / Sales 4% 4% 4% 4% 0.04 0.04 127 Accruals / Sales 7% 6% 6% 6% 0.06 0.06 128 Tax rate 40% 40% 40% 40% 0.40 0.40 129 Actual Forecast 130 Panel B: Results 2013 2014 2015 2016 131 B1. Sales Revenues Net sales $5,000 $5,500 $5,940 $6,356 132 133 B2. Operating Assets and Operating Liabilities Cash $50 $55 $59 $64 134 Accounts receivable $500 $550 $594 $636 135 Inventories $1,000 $1,100 $1,188 $1,271 136 Net PP&E $2,000 $2,200 $2,376 $2,542 137 Accounts payable $200 $220 $238 $254 138 Accruals $300 $330 $356 $381 139 140 B3. Operating Income COGS (excl. depr.) $3,800 $4,180 $4,514 $4,830 141 Depreciation $200 $220 $238 $254 142 Other operating expenses $500 $550 $594 $636 143 EBIT $500 $550 $594 $636 144 Net operating profit after taxes $300 $330 $356 $381 145 146 B4. Free Cash Flows Net operating working capital $1,050 $1,155 $1,247 $1,335 147 Total operating capital $3,050 $3,355 $3,623 $3,877 148 FCF = NOPAT - op capital $260 $25 $88 $128 149 150 B5. Estimated Intrinsic Value Target WACC 11.0% 11.0% 11.0% 151 Return on invested capital 9.8% 9.8% 9.8% 9.8% 152 Growth in FCF 252% 45.1% 153 154 Value of operations 155 Horizon Value: + ST investments 156 _ = = $3,814 Estimated total intrinsic value 157 ( _ 158 of Operations: Preferred stock 159 Value (+_ ))/(( Present value of HV $2,267 Estimated intrinsic value of equity 160 _)) + Present value of FCF $453 Number of shares 161 Value of operations = $2,719 Estimated intrinsic stock price = 162 163 164 165 12-4 Projecting MicroDrive's Financial Statements A B C D E F G 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 181 growth rate in earnings (which is the same as sales in the long-term). 182 183 Section 3 in the figure below calculates the additional financing provided by spontaneous liabilities, external sources, and 184 internal sources. The sum of these three sources of financing is the total amount of additional preliminary financing. 185 186 Section 3 also calculates the total amount of additional assets required by the operating plan. 187 188 The difference between the total additional financing and the total additional assets is defined as the financing deficit (if 189 the difference is negative) or the financing surplus (if the difference is positive). 190 191 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 Forecast 202 1. Balance Sheets 2013 Input Basis for 2014 Forecast 2014 203 204 Assets $50.0 1.00% 2014 Sales $55.00 205 Cash 500.0 10.00% 2014 Sales $550.00 206 Accounts receivable 1,000.0 20.00% 2014 Sales $1,100.00 207 Inventories Total current assets $1,550.0 $1,705.00 208 2,000.0 40.00% 2014 Sales $2,200.00 209 Net PP&E Total assets (TA) $3,550.0 $3,905.00 210 211 Liabilities and equity $200.0 4.00% 2014 Sales $220.00 212 Accounts payable 300.0 6.00% 2014 Sales $330.00 213 Accruals 280.0 Carry over from previous year $280.00 214 Notes payable 0.0 Draw on LOC if financing deficit $117.10 215 Line of credit Total CL $780.0 $947.10 216 1,200.0 Carry over from previous year $1,200.00 217 Long-term bonds Total liabilities $1,980.0 $2,147.10 218 $100.0 Carry over from previous year $100.00 219 Preferred stock A B C D E F 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 Total assets (TA) $3,550.0 $3,905.0 $4,217.4 $4,512.6 268 269 Liabilities and equity $200.0 $220.0 $237.6 $254.2 270 Accounts payable 300.0 330.0 356.4 381.3 271 Accruals 280.0 280.0 280.0 280.0 272 Notes payable 0.0 117.1 181.9 214.2 273 Line of credit G $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 2017 $66.7 667.4 1,334.7 $2,068.8 2,669.4 $4,738.2 $266.9 400.4 280.0 172.8 A 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 320 321 322 323 324 325 326 327 B Total CL Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liabs. & equity C $780.0 1,200.0 $1,980.0 $100.0 500.0 970.0 $1,470.0 $3,550.0 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 D $947.1 1,200.0 $2,147.1 $100.0 500.0 1,157.9 $1,657.9 $3,905.0 E $1,055.9 1,200.0 $2,255.9 $100.0 500.0 1,361.5 $1,861.5 $4,217.4 $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 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 F $1,129.8 1,200.0 $2,329.8 $100.0 500.0 1,582.8 $2,082.8 $4,512.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 $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 G $1,120.2 1,200.0 $2,320.2 $100.0 500.0 1,818.1 $2,318.1 $4,738.2 $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 $31.8 $0.0 $214.2 $235.3 $52.8 $225.6 $172.8 $172.8 $0.0 Actual 2013 Forecast 2014 Forecast 2015 $228.0 $248.4 $266.7 $200.0 $220.0 $237.6 ($120.0) ($180.0) $10.0 ($50.0) ($100.0) $20.0 ($44.0) ($88.0) $17.6 A B C D E F G $20.0 $30.0 $26.4 328 Increase(-)/Decrease(+) in accruals $158.0 $368.4 $416.3 329 Net cash provided (used) by operating activities 330 331 Investing Activities Cash used to acquire fixed assets ($500.0) ($420.0) ($413.6) 332 Sale of short-term investments $40.0 $0.0 $0.0 333 ($460.0) ($420.0) ($413.6) 334 Net cash provided (used) by investing activities 335 336 Financing Activities $150.0 $0.0 $0.0 337 Increase(+)/Decrease(-) in notes payable $0.0 $117.1 $64.8 338 Increase(+)/Decrease(-) in line of credit $200.0 $0.0 $0.0 339 Increase(+)/Decrease(-) in bonds $0.0 $0.0 $0.0 340 Preferred stock issue(+)/repurchase(-) ($58.0) ($60.5) ($63.1) 341 Payment of common and preferred dividends $0.0 $0.0 $0.0 342 Common stock issue(+)/repurchase(-) $292.0 $56.6 $1.7 343 Net cash provided by financing activities 344 Summary Net change in cash and equivalents ($10.0) $5.0 $4.4 345 Cash and securities at beginning of the year $60.0 $50.0 $55.0 346 $50.0 $55.0 $59.4 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 2017 357 A1. Operating Ratios Sales growth rate 5% 5% 10% 8% 7% 5% 358 COGS (excl. depr.) / Sales 76% 76% 76% 0.76 0.76 76% 359 Inventory / Sales 15% 20% 20% 0.20 0.20 0.20 360 Net PP&E / Sales 33% 40% 40% 0.40 0.40 0.40 361 362 Panel B: Key Results Industry MicroDrive Actual Actual Forecast 363 2013 2013 2014 2015 2016 2017 364 B1. Operations NA $260 $25 $88 $128 $207 365 Free cash flow 15.0% 9.8% 9.8% 9.8% 9.8% 9.8% 366 Return on invested capital 6.9% 6.0% 6.0% 6.0% 6.0% 6.0% 367 NOPAT/Sales 46.0% 61.0% 61.0% 61.0% 61.0% 61.0% 368 Total op. capital / Sales 5.0 4.0 4.0 4.0 4.0 4.0 369 Inventory turnover 30.0 36.5 36.5 36.5 36.5 36.5 370 Days sales outstanding 3.0 2.5 2.5 2.5 2.5 2.5 371 Fixed asset turnover 372 B2. Financing 45.0% 55.8% 55.0% 53.5% 51.6% 49.0% 373 Total liabilities / TA 6.2% 4.4% 4.4% 4.4% 4.4% 4.4% 374 Net income / Sales 11.0% 6.2% 6.2% 6.1% 6.2% 6.2% 375 Return on assets (ROA) 19.0% 15.0% 14.5% 13.9% 13.4% 12.8% 376 Return on equity (ROE) 10.0 4.2 4.0 4.0 4.1 4.2 377 Times interest earned NA $0 $117 $182 $214 $173 378 Line of credit 35.0% 22.7% 21.8% 21.3% 20.7% 20.5% 379 Payout ratio NA $1.00 $1.05 $1.10 $1.16 $1.22 380 Regular dividends/share NA $0.00 $0.00 $0.00 $0.00 $0.00 381 Special dividends/share A B C D E F G NA $4.40 $4.81 $5.17 $5.58 $5.92 382 Earnings per share 383 B3. Estimated intrinsic value $2,719 12/31/2013 Estimated value of operations = 384 $22.78 12/31/2013 Estimated intrinsic stock price = 385 386 387 388 389 390 12.6 Additional Funds Needed (AFN) Equation Method 391 392 The AFN model forecasts MicroDrive's need for external funds to support its forecasted next year's sales. 393 394 395 Figure 12-7 396 Additional Funds Needed (AFN) (Millions of Dollars) 397 Part A. Inputs and Definitions Most recent year's sales = 398 S0: Forecasted growth rate in sales = 399 g: Next year's sales: S0 (1 + g) = 400 S1: Change in sales = S1 - S0 = S = 401 gS0: Most recent year's operating assets = 402 A0*: Required assets per dollar of sales = 403 A0* / S0: Most recent year's spontaneous liabilities i.e., payables + accruals = 404 L0*: Spontaneous liabilities per dollar of sales = 405 L0* /S0: 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 (A0*/S0)S (L0*/S0)S = 411 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 420 Self-Supporting Growth Rate 421 422 This is the maximum growth rate that can be attained without raising external funds, i.e., the value of g that forces AFN = 0, holding other 423 things constant. 424 425 426 1. Using algebra. The sustainable growth rate can also be found by solving the equation as shown on the 3rd row above g, then finding 427 the value of g that causes AFN to equal zero. 428 429 430 431 PM(1 - POR)(S0) = = 5.90% $170.00 432 Sustainable g = A0* - L0* - PM(1 - POR)S0 $2,880.00 433 A 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 471 472 473 474 475 476 B C D E F G 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 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 H 1 12/9/2012 2 3 4 5 6 lan is described in the sections below. 7 8 9 10 11 nal data. 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 40been selected for the scenario that has o scenarios. The first is41 named Status in unchanged. The initial 42sales growth e growth rate declines over 43 time until it se it is the set of inputs44 chosen by 45 46 47 r each of these inputs, Section 1 shows the forecasted values for 48the next five rate) would be equal to49 the inputs in the 50 51 I J K L M N H I J K 52 53 apital. Section 4 shows 54 the forecasted 55 56 o change an input, 57 do it here and not other places in the model. 58 59 onstant rate. L M N H MicroDrive 60 Forecast 61 2017 62 5% 63 76% 64 0.10 65 0.10 66 0.01 67 0.10 68 0.20 69 0.40 70 0.04 71 0.06 72 0.40 73 Target Market 74Weights 28% 75 2% 76 3% 77 67% 78 Forecast 79 9% 80 10% 81 8% 82 14% 83 84 5% 85 86 87 ce, we repeat the inputs88 of operating 89 90 s sales multiplied by the 91forecasted sales 92 93 94 operating asset for a particular year is ojected sales. The operating 95 liabilities 96 97 ing expenses are equal98 to the product of 99ratio in Section qual to the product of the preciation, and other operating 100 101 102 103 (i.e., cash, accounts receivable, and ls from Section 4). Total capital is equal 104 105 106 ort the target WACC (calculated as 107 108 109 ulated using the FCF valuation model as 110 111 112 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 N 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 H I J K L 113 o the ones in Figure 12-2. If you want to change inputs, do so in Figure 12-2. 114 115 MicroDrive 116 Forecast 117 2017 2018 118 5% 5% 119 76% 76% 120 0.10 10% 121 0.10 10% 122 0.01 1% 123 0.10 10% 124 0.20 20% 125 0.40 40% 126 0.04 4% 127 0.06 6% 128 0.40 40% 129 Forecast 130 2017 2018 131 $6,674 $7,007 132 133 $67 $70 134 $667 $701 135 $1,335 $1,401 136 $2,669 $2,803 137 $267 $280 138 $400 $420 139 140 $5,072 $5,326 141 $267 $280 142 $667 $701 143 $667 $701 144 $400 $420 145 146 $1,401 $1,472 147 $4,071 $4,274 148 $207 $217 149 150 11.0% 11.0% 151 9.8% 9.8% 152 61.7% 5.0% 153 154 Value of operations $2,719 155 + ST investments $0 156 Estimated total $2,719 157 intrinsic value All debt $1,480 158 Preferred stock $100 159 Estimated intrinsic $1,139 160 value of equity Number of shares $50 161 Estimated intrinsic stock price = $22.78 162 163 164 165 M N H I J K L 166 167 168 ming year for the Status Quo scenario. 169 170 he operating plan. 171 172 ong-term bonds, preferred 173 stock, and 174 175 176 t of debt during the year, defined as the e ending debt. An identical 177 process is 178 179 e rate as the long-term180 sustainable 181 182 aneous liabilities, external 183 sources, and additional preliminary financing. 184 185 ating plan. 186 187 ts is defined as the financing 188 deficit (if 189 190 191 ssumes that the LOC will be accessed on 192 interest to ce) will not accrue enough ning of the year (which193 is the same as the 194 195 196 197 198 o the ones in Figure 12-2. If you want to change inputs, do so in Figure 12-2. 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 M N H Forecast 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 260 261 262 263 264 265 266 267 268 269 270 271 272 273 I J K L Note: this is the planned increase in the retained earnings account. 2018 $70.1 700.7 1,401.5 $2,172.3 2,802.9 $4,975.2 $280.3 420.4 280.0 121.3 M N H Forecast 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 320 321 322 323 324 325 326 327 I J K L M $1,102.0 1,200.0 $2,302.0 $100.0 500.0 2,073.2 $2,573.2 $4,975.2 $0.00 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 $33.4 $0.0 $172.8 $255.1 $115.6 $236.9 $121.3 $121.3 $0.0 When forecasting more than one year, 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 and then drawing on a new line of credit. Forecast 2016 Forecast 2017 Forecast 2018 $287.2 $304.0 $326.9 $254.2 $266.9 $280.3 ($41.6) ($83.2) $16.6 ($31.8) ($63.6) $12.7 ($33.4) ($66.7) $13.3 N H I J K L $24.9 $19.1 $20.0 328 $458.3 $507.4 $540.5 329 330 331 ($420.6) ($394.1) ($413.8) 332 $0.0 $0.0 $0.0 333 ($420.6) ($394.1) ($413.8) 334 335 336 $0.0 $0.0 $0.0 337 $32.3 ($41.4) ($51.6) 338 $0.0 $0.0 $0.0 339 $0.0 $0.0 $0.0 340 ($65.9) ($68.8) ($71.8) 341 $0.0 $0.0 $0.0 342 ($33.6) ($110.2) ($123.4) 343 344 $4.2 $3.2 $3.3 345 $59.4 $63.6 $66.7 346 $63.6 $66.7 $70.1 347 348 349 350 351 o the ones in Figure 12-2. If you want to change inputs, do so in Figure 12-2. 352 353 pt Percentages and Per Share Data) 354 roDrive 355 Forecast 356 2018 357 0.05 358 76% 359 20% 360 40% 361 362 roDrive Forecast 363 2018 364 $217 365 9.8% 366 6.0% 367 61.0% 368 4.0 369 36.5 370 2.5 371 372 46.3% 373 4.6% 374 6.4% 375 12.4% 376 4.5 377 $121 378 20.0% 379 $1.28 380 $0.00 381 M N H I $6.38 382 383 384 385 386 387 388 389 390 391 asted next year's sales.392 393 394 395 396 397 $5,000 398 10.00% 399 $5,500 400 $500 401 $3,550 402 71.00% 403 ties i.e., payables + accruals $500 404 = 10.00% 405 4.40% 406 ome = % of income paid out = 22.73% 407 408 409 Addition to Retained 410 Earnings. S1 M (1 - POR) 411 412 413 (1+g)S0 M (1 - POR) 414 415 $5,500(0.044)(1 - 0.2273) $187.00 416 417 418 419 420 421 ds, i.e., the value of g that 422forces AFN = 0, holding other 423 424 425 ation as shown on the426 3rd row above g, then finding 427 428 429 430 431 432 433 J K L M N H I 434 435 ek. In the figure above,436 set the AFN in the orange cell to 437 438 439 440 441 442 443 necessarily correct. For instance, suppose the firm is 444 eater level of production with its fixed assets. Here are 445 ring the excess capacity. 2. Calculate the required new 4. Calculate a revised target 446 fixed assets-to-sales ratio. ease in required fixed447 assets given excess capacity. 7. ty is ignored versus the required fixed assets when 448 om the previously calculated 449 AFN. 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 J K L M N O 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 50 51 P O 52 53 54 55 56 57 58 59 P 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 112 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% 41% 10% 3% 46% 100% O 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 164 165 P O 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 216 217 218 219 P O 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 260 261 262 263 264 265 266 267 268 269 270 271 272 273 P O 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 320 321 322 323 324 325 326 327 P O 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 372 373 374 375 376 377 378 379 380 381 P O 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 420 421 422 423 424 425 426 427 428 429 430 431 432 433 P O 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 471 472 473 474 475 476 P 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 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 Status Quo Inputs Actual Actual 1. Operating Ratios 2013 2012 2013 Sales growth rate 5% 15% 5% COGS (excl. depr.) / Sales 76% 75% 76% Depreciation / Net PP&E 9% 10% 10% Other op. exp. / Sales 10% 10% 10% Cash / Sales 1% 1% 1% Acc. rec. / Sales 8% 8% 10% Inventory / Sales 15% 17% 20% Net plant / Sales 33% 36% 40% Acc. pay. / Sales 4% 4% 4% Accruals / Sales 7% 6% 6% Tax rate 40% 40% 40% 2. Capital Structure Actual Market Weights % Long-term debt 22% 31% 41% % Short-term debt 3% 4% 10% % Preferred stock 0% 3% 3% % Common stock 75% 62% 46% 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 Actual Growth rate of dividends 11% 4.2% 5. Capital Structure Choices % Long-term debt, wLTD 41.0% % Short-term debt, wSTD 9.6% % Preferred stock, wps 3.4% % Common stock, ws 46.1% 2014 10% 76% 10% 10% 1% 10% 20% 40% 4% 6% 40% 28% 2% 3% 67% 9.0% 10.0% 8.0% 13.58% 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% 9% 10% 10% 8% 8% 14% 14% 5% 5% 5% 38.4% 8.0% 3.3% 50.3% 35.8% 6.5% 3.2% 54.4% 33.2% 5.0% 3.2% 58.6% The following projections incorporate the impact of financing feedback. They also ensure that the actual capital structure matches the target capital structure. Following are explanations of these two issues, beginning with the capital structure. Implementing the Target Capital Structure The preliminary financial policy held external financing 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. 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 Exc to "freeze." Fortunately, there is a simple solution. 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 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 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 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 Interest on bonds, based on average LT bonds and rLTD 100.0 Interest on LOC, based on average LOC and rLOC = rSTD +1.5% 0.0 Pre-tax earnings $380.0 Taxes (40%) 152.0 NI before pref. div. $228.0 Preferred dividend, based on average preferred stock and r ps 8.0 Net income $220.0 Regular common dividends $50.0 Special dividends $0.0 Addition to RE $170.0 2015 $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 2. Income Statement 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 Forecast 2016 $0.00 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 $50.0 $91.6 $0.0 $44.0 $15.8 $213.4 $41.6 $41.4 $297.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 Increase in financing Increase in total assets Amount of unadjusted deficit or surplus financing: $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 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). 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% $2,719 $1,200 $280 $100 $2,992 $1,148 $241 $100 $3,233 $1,156 $211 $105 $3,460 $1,148 $174 $110 Actual 2013 Forecast 2014 Forecast 2015 Forecast 2016 2014 $1,155 $3,355 $330 $25 5. Estimating the Intrinsic Stock Price Value of operations + ST investments Estimated total intrinsic value All debt Preferred stock Estimated intrinsic value of equity Number of shares Estimated intrinsic stock price = Statement of Cash Flows (Millions of Dollars) Status Quo 2013 $2,719 $0 $2,719 $1,480 $100 $1,139 $50 $22.78 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 Investing Activities Cash used to acquire fixed assets Sale of short-term investments Net cash provided (used) by investing activities 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 $228.0 $243.6 $263.0 $284.4 $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 ($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 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 $22.78 12/31/2013 Estimated intrinsic stock price = 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/9/2012 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% 10% 8% 14% 9% 10% 8% 14% 5% 5% 30.6% 3.5% 3.1% 62.8% 28% 2% 3% 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. 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 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 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 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 $0.00 $0.00 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 Forecast $31.8 $80.5 $375.1 $33.4 $92.1 $415.9 Note: $400.4 $420.4 $9.0 $6.1 $61.0 $58.8 $12.9 $14.3 Note: $8.9 $9.1 $60.8 $63.8 $247.8 $268.3 We subtract the previous LOC because the plan does not call for any projected LOC unless necessary. 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. $176.0 $206.4 Note: $225.6 $236.9 $401.6 $443.3 $0.0 $0.0 $401.6 0.97 $415.9 $443.3 0.97 $459.2 The increase in financing is equal to the sum of spontaneious 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. 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% $3,633 $1,111 $128 $112 Forecast 2017 2018 $1,472 $4,274 $420 $217 5.0% 10.97% $3,814 $3,814 Note: $1,068 $76 $114 Forecast 2018 The value of operations at the horizon is equal to the horizon value. $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 12/9/2012 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