TASK
- Create a MS Excel financial model that represents your views of the future prospects of your target company. The financial model is to include forecasts for at least five years. Your model should contain a complete set of financial statements and appropriate supplemental schedules. Thus, at a minimum your forecasts should include an income statement, balance sheet, statement of cash flows (that reconciles to the change in cash), free cash flow statement ( in a separate tab). Your Model should have a tab that clearly shows the drivers or key factors (or Input) for the model. This section should also clearly show your Forecast Assumptions which will “drive” all your forecasts and later will be used for Sensitivity and Scenario Analysis.
- Provide a discussion of the method you used to forecast the key factor(s) driving the firm’s operations. Your discussion should answer the following questions: What are the key factors that drive the operations of the firm? How did you conclude that these are the key factors? How did you forecast these key factors?
- For the tax rate projection, it is okay to just use the firm’s historical effective tax rate [i.e., taxes paid divided by earnings before taxes].
- Discuss what forecasting horizon you have chosen for your company and why. Explain what terminal value growth rate you intend to use in your perpetuity free cash flow model and what you have done to prepare the cash flows for the terminal value calculation.
- Discuss the reasonableness of the forecasts you have generated using whatever metrics you believe would be relevant. Discuss the methods you used to check the overall reasonableness of your forecasts. Include any relevant graphs or tables you think demonstrate the reasonableness of your forecasts. If there are elements of your forecasts that you were not able to make reasonable, indicate what they are and how it is that the forecasts appear unreasonable.
