PART A: SHARE VALUATION AND INVESTMENT APPRAISAL (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.
Microsoft Word - Project Instructions_FINC12_201_211 BOND BUSINESS SCHOOL FINC12_201 Project: FINC12_201_211 Topic: Excel sensitivity analysis project Format: This is both an Excel and written (Word) individual report. The project must be done individually. Due date: 2pm, Monday 5th of April, 2021. Total marks: The project is graded out of 100. Weighting: The project is worth 30% of your final grade. Submission details: You will need to submit both a Word document (containing your answers and discussion) and an Excel file containing your working. BOTH files need to be submitted [see below]. Ensure you place Parts A and B on different worksheets in your Excel file. Before submission, affix the signed coversheet (which you can find on iLearn) to the front of the word file and label each file in the following form “STUDENTID_FINC12_201_Project_211” and upload both your Word file and Excel file through iLearn. Total words: There is no word limit, except that the answer for Part A should not exceed three (3) pages and that your answer for Part B should not exceed one (1) page (see details below). 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 once you enter the profession! 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 data collection involved in part A will build on the skills you learnt in your BMC certification from FINC12_200. The second context involves the estimation of a simulation, which while similar to the tutorial material has some unique aspects which will hopefully provide you with the opportunity to reflect on how you will approach the problem, not least in terms of how best to construct your model and the layout of your spreadsheet. 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 topics 9 and 10. 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 (which can be found on iLearn where this document was located). Task description: PART A: SHARE VALUATION AND INVESTMENT APPRAISAL (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 Bloomberg for the period 1/1/2013 to 31/12/2020. If you are off-campus and have no remote access to Bloomberg you can use yahoo finance for this purpose. 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. Once established, consider the impact of errors or uncertainty in your choice of model, model inputs and model estimates and whether (and to what extent) these impact on your buy or sell decision. 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. 1 Ref: https://www.spglobal.com/spdji/en/indices/equity/sp‐asx‐200/#data PART B: SIMULATION AND MODEL BUILDING (50 marks) Background: Part B relates to the following scenario; the context being the economic performance (as measured by GDP) of two fictional countries; Country A (which is a large, stable, industrial country) and Country B (which is a smaller, less developed and therefore more volatile country). The two nations (countries) are not identical in their economies nor in their productive capacity or industrial composition and therefore have both independent economic cycles and differing economic growth profiles. The GDP growth profiles for each nation are as follows; GDP growth rate distribution function Country A Country B State Probability Annual GDP growth Probability Annual GDP growth Depression 0.030 -1.10% 0.015 -2.75% Recession 0.045 -0.40% 0.035 -1.15% Below average 0.235 0.750% 0.215 2.30% Average 0.365 2.10% 0.400 3.15% Above average 0.170 3.20% 0.200 5.95% Boom 0.130 5.15% 0.110 9.20% Hyperinflation 0.025 -1.25% 0.025 -2.00% Required: To clarify, in normal circumstances (ie. average, see table above) country A’s GDP will grow at a rate of 2.10% per annum, and country B’s GDP will grow at a rate of 3.15% per annum. Both nations, with the objective of frugality, have entered into a mutual agreement such that their central bank operations will be under the auspices of a single institution (being the Central Bank and which is the institution that you work for). To monitor the relative performance between the two countries, the central bank monitors the exchange rate, the annual value for which is defined as the ratio of country A’s level of GDP in that year to country B’s level of GDP in that year, viz; COUNTRY A COUNTRYB GDPFX GDP The central bank, in it’s role of providing fiscal policy stimulus, can therefore simultaneously provide stimulus exclusively to one country, exclusively to the other, or some combination. For example, the most neutral stance the central bank could adopt would be to apply equal stimulus to each country; that is; Fiscal Policy stance = Stimulus A: Stimulus B = 50:50 If a neutral stance is adopted, the growth rates in GDP for each country are as they appear in the probability distribution function (pdf) table above. Alternatively, the central bank could decide to alter their stimulus such that it was in favor of country A, according to Fiscal stance = Stimulus A: Stimulus B = 55:45. The R&D expenditure budget will have an impact on the GDP growth for each market. That is, for every 1% difference in stimulus the distribution of growth rates will change by 0.25%. For example, the stance of 45:55 will shift the distribution of country A’s GDP growth rates upwards by 0.25 x (55-45) = 2.5%, and the distribution of country B’s GDP growth rates downwards by 0.25 x (45-55) = -2.5%. Further, the impacts of a country being in a depression or hyperinflation state are somewhat persistent, with countries remaining in the depression state for 4 years and for 3 years once entering a hyperinflation state. REQUIRED: Suppose the central bank has a long-term focus (being a 100-year period), and that the fiscal stance needs to be set in the first period and then CANNOT BE CHANGED over the 100-year period. Given this, your jobs is to determine what stimulus package would be the most optimal assuming the central bank objective is to maximise the probability of the exchange rate (FX) remaining unchanged over the 100-year period. Ensure that you show all working and include a brief explanation of the result obtained and any assumptions made. ADDITIONAL INFORMATION: * when altering the expenditure budget, only consider whole numbers (ie. integers; eg 49-51; 48-52 and so on). * ensure you make all decisions using second order stochastic dominance as a decision rule and use 100 trials for all simulations. * Note that, as we discussed extensively in class, running simulations can require a lot of computing resources. It is recommended that you turn of automatic calculations (as we did in Topic 7), backup your spreadsheet often and restart your computer prior to doing any work in Excel. Further, once you have simulated your random variables to your satisfaction it is recommended that you hardcode them as values as this will reduce the computing resources required. 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