I'm working on this financial model and I need some serious help.

Building a Financial Model Your boss believes that company ABC is a good investment and he wants to buy some shares in you the past 10 years financial statements for company ABC and asks you if it is a good idea to b current price of their stock is reasonable, undervalued or overvalued. Based on the information 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 advice wou 3) Show me at least two examples where changing one single assumption in your spreadsheet c Note: This case closely matches the First Financial model example posted on the website. Use th Profit and loss assumptions Sales growth COGS/Sales SG&A/Sales Depreciation/Gross fixed assets Interest rate Growth rate of total dividends Tax rate 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 Liabilities Accounts Payable/Sales 0.05423 0.589 0.1484 0.061 apply this to average value of assets on 0.051 apply this to average debt during the ye 0.0319 0.4089 constant 0.2013 0.1377 0.0305 0.3001 constant 0.065544 0.09774 Accrued Expense/Sales Other Current Liabilities 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, ass 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 ASSETS (in million dollars) Cash and Short-Term Investments Receivables Inventories - Total Other Current Assets Total Current Assets Year 2016 Year 2017 Year 2018 9218 $9,717.89 $10,244.89 5530.8 $5,723.84 $ 6,034.24 1214 1214 1214 2473.2 340 110 8 2015.2 487.8177 1527.382 298 1229.382 0 Year 2016 Year 2017 Year 2018 99 1563 917 249 2828 Property, Plant, and Equipment, Gross Depreciation, Depletion, and Amortization (Accumulated) Property, Plant, and Equipment, Net 7089 3775 3314 Investments and Advances Intangibles Other Assets 320 652 1071 TOTAL ASSETS 8185 LIABILITIES (in million dollars) Accounts Payable Year 2016 Year 2017 Year 2018 774 Accrued Expense Other Current Liabilities Debt Deferred Taxes Other Liabilities 606 12 2845 543 1124 EQUITY (in million dollars) Common Stock Capital Surplus Retained Earnings Less: Treasury Stock Total stockholders equity TOTAL LIABILITIES AND EQUITY 520 102 6019 4360 2281 8185 FREE CASH FLOW (in million dollars) - use these to answer question 2 Year 2017 Year 2018 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 Year FCF Terminal Total Discounted Value Add back initial cash firm value subtract total debt value (in Year 2016) 0.125 0.04 2017 2018 Assume all the cash flows above occur a This should be ALL debt that the compa implied equity value number of shares outstanding (at the end of Year 2016, in millions) implied value per share market price per share ABC over or under-valued ? What advice do you give your boss? 150 43 Use an IF statement with three potentia Use an IF statement (the advice can be B 3) Show me at least two examples where changing one single assumption in your spreadsheet cha Example 1 Example 2 Tell me which cell (that contains an assu I change that particular cell to the value buy some shares in this company - however, since he is new at this, he brings it is a good idea to buy. In particular, he wants you to estimate whether the d on the information given, you have synthesized the assumptions below. Use ars ns? What advice would you give your boss? n your spreadsheet changes your investment decision. n the website. Use that as an example if you get stuck. average value of assets on the book, like it's done in The First Financial Model example average debt during the year on't see an assumption, assume these elements remain constant. Year 2019 Year 2020 Year 2021 Year 2022 $10,800.47 $11,386.18 $12,003.66 $12,654.61 $ 6,361.48 $ 6,706.46 $ 7,070.15 $ 7,453.57 1214 1214 1214 1214 If you don't have any info, assume constant!!! Year 2019 Year 2020 Year 2021 Year 2022 IF you don't have any info in assumptions, assume constant Year 2019 Year 2020 Year 2021 Year 2022 IF you don't have any info in assumptions, assume constant IF you don't have any info in assumptions, assume constant Year 2019 2019 Year 2020 2020 Year 2021 2021 Year 2022 2022 When calculating terminal value, assume dividends will grow at the growth rate forever from here on. e cash flows above occur at the end of the year when you get your discounted value!!! e ALL debt that the company has (all three debt accounts should be added here) ement with three potential outcomes: Over/Under/Correctly valued (use a +/- 10% confidence interval for the correctly valued conclusion) ement (the advice can be Buy/Sell/Hold) your spreadsheet changes your investment decision. h cell (that contains an assumption) should be changed to what value (if your spreadsheet is built correctly, when t particular cell to the value you indicated, I should see the implied value per share in cell B133 changing, as well as your advice to your bos assume constant assume constant assume constant vidends will he correctly valued conclusion) well as your advice to your boss if applicable)