Topic: Excel sensitivity analysis project
Format: This is both an Excel and written individual report. The project
must be done individually.
Due date: 2pm, November 30th, 2020
Total marks: The project is graded out of 100.
Weighting: The project is worth 30% of your final grade.
Total words: All working and any data collected is to be shown in the Excel
document. The answers (and any discussion) should be placed in a word
document (BOTH files need to be submitted [see below]). There is no
word limit, except that the answer for each question part should not
exceed five (5) pages.
Learning Outcomes: This piece of assessment addresses SLOs 2, 3, 4, and 5.
Background:
One of the focuses of the FINC12_201 course, and indeed one of the most transferrable skills to not
just the profession, but also to other subjects you will undertake during your studies is the ability to do
sensitivity analysis. In an uncertain world, generating a singular answer is not as appealing as achieving
an understanding of which (and whether) inputs or errors in our methodology or estimation impact on
our decision-making. This assignment provides you with two contexts for you to hone these important
proficiencies. The skills however are highly generalisable and I invite you all to reflect, as you complete
this report, on what context you personally see as being the way you would use these tools!
The first of these contexts is one which is likely familiar to most of you; being share value appraisal, and
this draws upon some of the earlier examples we did with the dividend discount model and growth in
perpetuity from our time value of money material (Topic 2). The second context involves the estimation
of an asset pricing model, specifically the CAPM a task which is relevant to both firms seeking to raise
capital as well as traders and fund managers seeking to identify suitable allocation opportunities. As such,
as background for the assignment, students are directed towards the Topic 2 material on perpetuities and
growth, the simulation material in topics 7 and 8, and the asset pricing and portfolio concepts discussed
in topic 9, 10 and 11.
Submission:
The report submission will consist of two 2 files; an Excel file containing all working for the project
(and any data used) and a word document (file) containing your answers and discussion. Submission
will be via Turnitin on iLearn (with full details being provided on iLearn as we approach the due date).
The page limit for Part A is 3 pages in total (no appendices are allowed).
Assessment criteria:
For each calculation in the report, ensure you; justify/explain the methods used and any assumptions
made;
(i) describe and justify any data used (including showing ALL calculations and working),
(ii) comment on the reasonableness of the estimates obtained and
(iii) show working of and discuss any sensitivity performed.
Details on the marking scheme are contained in the assessment rubric.
Task description:
PART A: (50 marks)
In your introductory finance course(s) you would have considered a number of financial models, the
inputs for which were required to be estimated before the models could be of any practical use. One
example of this is the dividend discount model (ie. perpetuity approach to share valuation). Recall that in
the first few weeks of this semester we considered the estimation of different growth rate assumptions
for this model. In this part of the report you will be conducting a sensitivity analysis based on this model.
Required:
Identify an Australian company from the ASX 200.1 Download daily data from either Bloomberg or yahoo
finance for this company for the period 1/10/2013 to 30/9/2020.
Calculate an estimate of the price of an ordinary share in this company and comment on
whether you believe the stock is currently a buy or a sell.
To accomplish this using the dividend discount model, you will need estimates of the discount rate (cost of equity capital),
the initial level of dividends and the growth rate in dividends. Hint: see tutorial 2, Example 3 as a starting point.
PART B: (50 marks)
Background: An alternate means of evaluating the expected performance (and thereby mispricing of
an asset or portfolio) is via an asset pricing model. The concepts are related as the discount rate from
Part A is often calculated using the expected return from Part B.
Required:
Determine if this security is underpriced or overpriced according to the CAPM. Comment on
how consistent your conclusion is with respect to your answer from part A. In your answer,
carefully explain the steps in your approach and justify any decisions with respect to your data or
method.
Formatting Guidelines:
Consistent with industry best practice, reports in this subject will adopt either the APA or CMS formatting and reference
styles.
When formatting your report, please remember:
 Page margins
 Spacing of text
 Font to be used
 Positioning of titles
 Inclusion of cover sheet
When formatting the reference list, please remember:
 Reference .list if not included in the word limit
 Start a new page of for the reference list
 Title the page ‘References’ and position text at the top and centre of the page
 Either APA or CMS guidelines for each citation to be used
 Either APA or CMS formatting of reference e.g., double-spaced, first line for each
reference flush left with page, and subsequent lines are indented. No line spaces
between references