Building a Financial Model
Your boss believes that company ABC is a good investment and he wants to buy some shares in this company - however, since he is new at this, he brings you the past 10 years financial statements for company ABC and asks you if it is a good idea to buy. In particular, he wants you to estimate whether the current price of their stock is reasonable, undervalued or overvalued. Based on the information given, you have synthesized the assumptions below. Use these to answer the following questions:
1) Build pro-forma Balance Sheets and Income Statements for the next 6 years
2) Is the company overvalued or undervalued according to your assumptions? What advicewould you give your boss?
3) Show me at least two examples where changing one single assumption in your spreadsheet changes your investment decision.
Profit and loss assumptions Sales growth COGS/Sales SG&A/Sales Depreciation/Gross fixed assets Interest rate Growth rate of total dividends Tax rate 0.05423 0.589 0.1484 0.061 apply this to average value of assets on the book, like it's done in The First Financial Model example 0.051 apply this to average debt during the year 0.0319 0.4089 Balance sheet assumptions Assets Cash and short-term investments Receivables/Sales Inventories/Sales Other Current Assets/Sales Property, Plant, Equipment, Net/Sales Investments and advances Other Assets, annual growth rate constant 0.2013 0.1377 0.0305 0.3001 constant 0.065544 Liabilities Accounts Payable/Sales Accrued Expense/Sales Other Current Liabilities 0.09774 0.071234 constant Debt/Assets 0.319875 Deferred Taxes (growth rate) 0.03651 Other liabilities, annual growth 0.125333 Treasury stock plug Note: If there are any additional items on the balance sheet and income statements for which you don't see an assumption, assume these elements remain constant. Year 3 Year 4 Year 5 Year 6 1) PRO-FORMA FINANCIAL STATEMENTS PROFIT AND LOSS (in million dollars) Sales Cost of Goods Sold Selling, General, and Administrative Expense Operating Income Before Depreciation Depreciation and Amortization Interest Expense Nonoperating Income (Expense) and Special Items Pretax Income Income Taxes - Total Net Income Dividend Retained earnings Year 0 Year 1 Year 2 9218 9717.89214 5530.8 -5723.8385 1214-1442.1352 2473.2 2551.91848 3401 -407.8606 110 -143.66918 -8 2015.2 1992.38869 487.8177 -814.68774 1527.382 1177.70096 298 -37.56866 1229.382 1140.13229 Year 0 Year 1 ASSETS (in million dollars) Cash and Short-Term Investments Receivables Inventories - Total Other Current Assets Total Current Assets 99 99 1563] 1956.21169 917) 1338.15375 249 296.39571 2828 3689.76115 Property, plant, and Equipment, Gross Depreciation, Depletion, and Amortization (Accumulated) Property, Plant, and Equipment, Net 7089 6283.47883 3775 3367.1394 3314 2916.33943 Investments and Advances Intangibles Other Assets TOTAL ASSETS 320 320 652 652 1071 1141.19762 8185 8719.2982 Year 2 Year 3 Year 4 Year 5 Year 6 LIABILITIES (in million dollars) Accounts Payable Accrued Expense Other Current Liabilities Year 0 Year 1 774 949.826778 606 692.244329 12 12 2845 2789.08551 543 562.82493 1124 1264.87427 6270.85582 Debt Deferred Taxes Other Liabilities Total Liabilities EQUITY (in million dollars) Common Stock Capital Surplus Retained Earnings Less: Treasury Stock Total stockholders equity TOTAL LIABILITIES AND EQUITY 520 102 520 102 60197159.13229 4360 -5253.3878 2281 2527.74454 8185 8798.60035 FREE CASH FLOW (in million dollars) - use these to answer question 2 Year 3 Year 4 Year 5 Year 6 Year 1 Year 2 1177.70096 407.860604 8707.2982 Profit after tax Add back depreciation Change in net working capital Increase in operating current assets Add increase in operating current liabilities Subtract capital expenditures Subtract increase in other assets Add back after-tax interest FCF 2) Valuing ABC Additional Assumptions: WACC Long-term Free Cash Flow growth rate 12.50% 4.00% Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year FCF Terminal When calculating terminal value, assume dividends will grow at the long-term growth rate forever from here on. Total Assume all the cash flows above occur at the end of the year when you get your discounted value!!! (no mid-year discounting) Discounted Value Add back initial cash (in Year 0) firm value subtract total debt value (in Year 0) implied equity value INCLUDE ALL LONG TERM DEBT!!! 150 number of shares outstanding (at the end of Year 0, in millions) implied value per share 43 market price per share (at the end of Year 0) ABC over or under-valued ? Assume a 10% tolerance level (i.e. if you are within 10% of the market price, call this correctly valued by the market) What advice do you give your boss? The options are BUY, SELL, HOLD 13) Show me at least two examples where changing one single assumption in your spreadsheet changes your investment decision. 2 Example 1 Tell me which cell (that contains an assumption) should be changed to what value (if your spreadsheet is built correctly, when 3 Example 2 I change that particular cell to the value you indicated, I should see the implied value per share in cell B133 changing) Profit and loss assumptions Sales growth COGS/Sales SG&A/Sales Depreciation/Gross fixed assets Interest rate Growth rate of total dividends Tax rate 0.05423 0.589 0.1484 0.061 apply this to average value of assets on the book, like it's done in The First Financial Model example 0.051 apply this to average debt during the year 0.0319 0.4089 Balance sheet assumptions Assets Cash and short-term investments Receivables/Sales Inventories/Sales Other Current Assets/Sales Property, Plant, Equipment, Net/Sales Investments and advances Other Assets, annual growth rate constant 0.2013 0.1377 0.0305 0.3001 constant 0.065544 Liabilities Accounts Payable/Sales Accrued Expense/Sales Other Current Liabilities 0.09774 0.071234 constant Debt/Assets 0.319875 Deferred Taxes (growth rate) 0.03651 Other liabilities, annual growth 0.125333 Treasury stock plug Note: If there are any additional items on the balance sheet and income statements for which you don't see an assumption, assume these elements remain constant. Year 3 Year 4 Year 5 Year 6 1) PRO-FORMA FINANCIAL STATEMENTS PROFIT AND LOSS (in million dollars) Sales Cost of Goods Sold Selling, General, and Administrative Expense Operating Income Before Depreciation Depreciation and Amortization Interest Expense Nonoperating Income (Expense) and Special Items Pretax Income Income Taxes - Total Net Income Dividend Retained earnings Year 0 Year 1 Year 2 9218 9717.89214 5530.8 -5723.8385 1214-1442.1352 2473.2 2551.91848 3401 -407.8606 110 -143.66918 -8 2015.2 1992.38869 487.8177 -814.68774 1527.382 1177.70096 298 -37.56866 1229.382 1140.13229 Year 0 Year 1 ASSETS (in million dollars) Cash and Short-Term Investments Receivables Inventories - Total Other Current Assets Total Current Assets 99 99 1563] 1956.21169 917) 1338.15375 249 296.39571 2828 3689.76115 Property, plant, and Equipment, Gross Depreciation, Depletion, and Amortization (Accumulated) Property, Plant, and Equipment, Net 7089 6283.47883 3775 3367.1394 3314 2916.33943 Investments and Advances Intangibles Other Assets TOTAL ASSETS 320 320 652 652 1071 1141.19762 8185 8719.2982 Year 2 Year 3 Year 4 Year 5 Year 6 LIABILITIES (in million dollars) Accounts Payable Accrued Expense Other Current Liabilities Year 0 Year 1 774 949.826778 606 692.244329 12 12 2845 2789.08551 543 562.82493 1124 1264.87427 6270.85582 Debt Deferred Taxes Other Liabilities Total Liabilities EQUITY (in million dollars) Common Stock Capital Surplus Retained Earnings Less: Treasury Stock Total stockholders equity TOTAL LIABILITIES AND EQUITY 520 102 520 102 60197159.13229 4360 -5253.3878 2281 2527.74454 8185 8798.60035 FREE CASH FLOW (in million dollars) - use these to answer question 2 Year 3 Year 4 Year 5 Year 6 Year 1 Year 2 1177.70096 407.860604 8707.2982 Profit after tax Add back depreciation Change in net working capital Increase in operating current assets Add increase in operating current liabilities Subtract capital expenditures Subtract increase in other assets Add back after-tax interest FCF 2) Valuing ABC Additional Assumptions: WACC Long-term Free Cash Flow growth rate 12.50% 4.00% Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year FCF Terminal When calculating terminal value, assume dividends will grow at the long-term growth rate forever from here on. Total Assume all the cash flows above occur at the end of the year when you get your discounted value!!! (no mid-year discounting) Discounted Value Add back initial cash (in Year 0) firm value subtract total debt value (in Year 0) implied equity value INCLUDE ALL LONG TERM DEBT!!! 150 number of shares outstanding (at the end of Year 0, in millions) implied value per share 43 market price per share (at the end of Year 0) ABC over or under-valued ? Assume a 10% tolerance level (i.e. if you are within 10% of the market price, call this correctly valued by the market) What advice do you give your boss? The options are BUY, SELL, HOLD 13) Show me at least two examples where changing one single assumption in your spreadsheet changes your investment decision. 2 Example 1 Tell me which cell (that contains an assumption) should be changed to what value (if your spreadsheet is built correctly, when 3 Example 2 I change that particular cell to the value you indicated, I should see the implied value per share in cell B133 changing)
