Project
You must work in teams of four students unless you have been allowed to work in smaller teams.
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 an automatic C 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 cash flows for the next five years
(excluding the current year for which data for a few quarters may be available) for the assigned company and a terminal value at the end of the forecast horizon. You should then condense it to a final valuation for the company.
You
must use the documents provided below so that we can all use the same page
reemergence. The deliverables for each company include the following:
- All files should be labeled with the last names of your team
members followed by the company name. You are required to submit
one Excel file and one Word file for each company. I will deduct
five points if you fail to do so.
- Use the documents provided even if more recent documents are available.
- An ability to view three scenarios: "Normal," "Optimistic," and "Pessimistic"
- Please stick with the labels below. They will make it easier for
me to understand your worksheet references.
- 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 full-model.xls for a template.
- You can include any other supporting worksheets, but I will not
read them.
- An equity research report (maximum 7 pages not including the cover page). Make sure that your research report lists five key value drivers and a description of why you think they are the key value drivers.
- No quarterly forecasts are required. I have provided quarterly data for your convenience.
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.
- You should look at past trends if available.
- Listen to conference calls to identify key drivers.
Reports
You must use the documents provided on Blackboard so that we can all use the same
page references You also have the data for the last 10 years in a spreadsheet.