Financial Modeling Project

Project

Please email the TA, not me, regarding the project. Please email the final submission of the project to the TA.

Please do NOT copy my on emails to TAs. I am flooded with emails.

For every hour of delay in submission past the deadline, you will lose five points. You must work in teams that you formed earlier. Teams also have the right to eject a team member if he/she is not contributing to the team. You have to let me know quickly if such dysfunctional behavior is occurring. Anyone ejected from the team will get a F for the entire course regardless of his/her performance on the assignments and the final exam. At the end of the project, you should let me know via email if someone in your team did not contribute his/her fair share to the team effort.

You are required to forecast the ANNUAL cash flows for the next five years for the assigned company and a terminal value at the end of the forecast horizon. You should then arrive at final equity value for the company. If you already have data for some quarters of the current year, pretend that the you were forecasting soon after the last annual data was released, i.e., ignore quarterly data for the current year.

You must use the documents and spreadsheets provided in the course folder so that we can all use the same page references. Use the documents provided even if more recent documents are available. The deliverables for each company include the following:

• You are required to submit one Excel file and one Word file for each company. Your files should should be labeled with the last names of your team members followed by the company name. I will deduct five points if you fail to do so.
• Spreadsheet (Google sheets will be rejected.):
  • Main worksheet with collapsible rows with separate sections for checks, assumptions, income statement, balance sheet, cash flows, free cash flows, key ratios, and valuation. See m20-03-full-model.xlsx for an example.
  • An ability to view three scenarios: “Normal,” “Optimistic,” and “Pessimistic.”
  • Sensitivity tables highlighting key value drivers.
  • You can include any other supporting worksheets, but I will not read them.
• Word document (PDF files and Google docs will be rejected.): A research report (maximum 7 pages not including the cover page). Make sure that your report analyzes the company along the six key value drivers that we discussed in class.

Grading

I will consider the following factors in assigning you a grade.

• What your teammates say about your contribution to the team effort.
• Conceptual clarity and correctness of your model: 40%
• Quality of your write up justifying your assumptions and adjustments: 40%
• Professional look of the spreadsheet and the write up, on-time submissions: 20%
• Each team member should be familiar with all aspects of both models. I reserve the right to call on a team member to verify his/her knowledge of the model.

Suggestions

• You should check the reasonableness of your earnings forecasts by checking analyst forecasts available on Yahoo and elsewhere.