FNAN 341
004 DCF instructions
This assignment must be submitted on-line through ExPrep. To begin, you should log into your ExPrep account, download the Excel file(s) associated with the assignment, enter answers into relevant cells (which are highlighted in yellow and possibly other colors), and then submit the same file(s) you downloaded, but now with answers, to your ExPrep account.
You are encouraged to use Excel, but may use a calculator instead. However, note that the margin of error for many problems is very small (frequently as little as .01 percent). Therefore, some answers could be marked incorrect due to rounding.
Each student’s downloaded Excel files have unique identifiers and uploading a submission into your account that has a different person’s identifier than yours, regardless of the reason, is considered a violation of class policies (for the first incidence) or cheating (for any subsequent incidences). Therefore, it is critical to start the assignment with the relevant Excel file(s) downloaded from your own ExPrep account. Both the person with the account that receives a file with another’s identifier and the person whose account was the source of the identifier are considered to have violated course policy or the Honor Code when the system spots an identifier belonging to one person in a file submitted by another. Therefore, it is critical to protect access to your work and files. Copying and pasting anything from another file can trigger a flag and would be considered a class policy violation or cheating.
If you work with one or more classmates, make sure that the files do not get mixed up in any way at any time. Submitting a file with someone else’s identifier or having your identifier in another’s submission for any reason, including a so-called honest mistake, is considered a violation of course policy or the Honor Code.
You are able to submit this assignment to ExPrep 999 times. Therefore, after any submission, you can download your most recent or highest-scoring attempt to see what was answered correctly and what was not. This opportunity for immediate feedback has been very useful in both promoting learning and enabling students to earn higher scores. It is permissible (and encouraged) to make revisions to either the spreadsheet you previously submitted or your “student feedback” spreadsheet and then resubmit the modified file. The system will not flag this as a violation of class policy or cheating.
To clarify expectations for assignments even more. All work in your spreadsheets should be your own. First, the file should be yours, meaning that it was originally downloaded from your ExPrep account, so it has your unique identifier. Second, you should not cut and paste anything from another external source such as another spreadsheet (copying and pasting within the spreadsheet is fine), document, or website. Anything means anything – not just formulas, but labels, headings, titles, etc. It may seem inefficient to require you to type in everything, but I think it will help you learn.
Although you must submit your own Excel file(s) obtained from your own account, you can work together with classmates and are encouraged to do so. However, the purpose of collaboration is to enhance learning, not free riding. Therefore, each member of a group that works together must actively contribute. Cheating is not permitted and will result in an allegation of an Honor Code violation. If you do anything other than work alone, you are encouraged ask me whether your approach would be considered cheating. An obvious example (but not the only form) of cheating is when a student copies or receives answers from another person or another unauthorized source (note that this semester’s Blackboard site is an authorized source). The provider of information that would trigger a cheating allegation for the recipient is also considered to have cheated.
On the following page, there are additional assumptions or instructions for this assignment other than what’s provided in the relevant Excel file.
A1. All relevant instructions are provided in the question in the Excel spreadsheet.
Q1. Use the approaches covered in class and on the overheads to fill in the highlighted cells.
Q2. Use the approaches covered in class and on the overheads to fill in the highlighted cells.
Q3. Use the approaches covered in class and on the overheads to fill in the highlighted cells.
Q4. Use the assumptions described in the table when modeling items that are not computed as totals or subtotals, or that are not computed using information available from the model.
Variable
Modeling assumptions
Revenue
The revenue growth rate in each forecast year is 0.21 percentage points greater than the compound annual growth rate from the historical period
Operating profit
The operating profit margin in each forecast year is 0.85 percentage points better than the operating profit margin from the previous year
Net income
The net profit margin in each forecast year equals the average net profit margin from the last 2 years of the historical period
PP&E
PP&E to revenue in each year in the forecast period is 0.57 percentage points greater than the ratio from the previous year
Q5. Use the approaches covered in class and on the overheads to fill in the highlighted cells. Forecast terminal value as a growing perpetuity.
Q6. Use the approaches covered in class and on the overheads to fill in the highlighted cells. Forecast terminal value from an exit multiple (for EV/EBITDA).
Q7. Use the approaches covered in class and on the overheads to fill in the highlighted cells. Conduct a sensitivity analysis of the stock evaluated in Q5 that computes 36 estimated share prices as of the valuation date for all pairs of the following combinations of the terminal growth rate and WACC:
6 terminal growth rates: base – 1.2 percentage point, base – 0.6 percentage points, base, base + 0.6 percentage points, base + 1.2 percentage point, & base + 1.8 percentage point
6 WACCS: WACC – 2.1 percentage points, WACC – 1.4 percentage points, WACC – 0.7 percentage points, WACC, WACC + 0.7 percentage points, & WACC + 1.4 percentage points
Q8. Use the approaches covered in class and on the overheads to fill in the highlighted cells. Conduct a sensitivity analysis of the stock evaluated in Q6 that computes 35 estimated share prices as of the valuation date for all pairs of the following combinations of exit multiples for EV/EBITDA and WACC:
7 exit multiples for EV/EBITDA: base – 7.50, base – 5.00, base – 2.50, base, base + 2.50, base + 5.00, & base + 7.50
5 WACCS: WACC – 1.2 percentage points, WACC – 0.6 percentage points, WACC, WACC + 0.6 percentage points, & WACC + 1.2 percentage points
2