Part A - Risk Management (30 points) You are required to estimate the risk for a hypothetical holding of a US-based portfolio manager of 5,000 shares of Apple. This task is to be completed in Microsoft Excel. You are required to use the template Template_data_and_analysis_Part_A.xlsx provided in Canvas. (you have to rename the file before submitting it). 1. Data download Download the price data of Apple shares for the period from July 31, 2019 to July 31, 2024. The price data should be daily. You can find the price data from various sources such as Yahoo finance (https://au.finance.yahoo.com). In Yahoo finance the share code is AAPL and you need to select the "Historical Data" tab. You are required to copy the data in the Excel tab AAPL. 2. VaR calculations Estimate the market risk for your stock holding held on August 1, 2024 (you are working out the risk position assuming that you own these shares at the open of trading that day). You will do this by estimating the Value-at-Risk for the stock using historical data. You are required to calculate the 10-day VaR for the portfolio of shares at a confidence level of 99%. This risk estimate applies to the next 10 trading days from August 1, 2024 until August 14, 2024 (i.e. - it should be a forecast of risk). Based on what you have learnt from EFB344, you have several options for how to compute this risk measure: a) the normal distribution using a 252-day rolling window, b) the normal distribution using a 66 -day rolling window, c) the normal distribution using the EWMA (lambda = 0.94), d) historical simulation based on a window of 252 days, e) historical simulation based on a window of 66 days. To aid in the decision of which to use, you are going to consider the recent historical performance of the five models in calculating 1-day VaR at the confidence level of 99%. You will do so by first examining the frequency of instances when the VaR was exceeded by the observed return using the five years of data. You will then evaluate the appropriateness of these frequencies over time relative to the Basel traffic light levels discussed in the lectures. Based on this performance, and any other factor you think is appropriate, select the best model and report the required VaR(10, 99%) for August 1, 2024. Additional instructions: - You can use arithmetic as well as log returns (the results will be very similar). - You can assume that the mean returns are zero as discussed in the lectures. Page 3 of 5 - You should initialise your EWMA using the sample variance/covariance of the first 252 daily returns. - While you have around five years of data, you will only be able to produce around four years of VaR estimates. 3. Presenting your results - You are to conduct your analysis in a copy of the Excel file "Template_data_and_analysis_Part_A.xlsx" provided on the Canvas page (you have to rename the file before submitting it). This file contains a tab where you have to copy the raw historical share prices as well as a front page for you to summarize your results. All working is to be contained in the subsequent tabs. - The front tab asks you to provide the following: - Your name and Student number - The exceedance probabilities for the five models above. - A graph summarizing the Basel Traffic Light results (such as the one shown in lecture 3). - Which of the models above is your preferred model based on the backtesting results. - The final VaR(10,99%) for the stock based on your preferred model. - An evaluation of the relative performance of the five models and a clear justification of which model is superior based on your backtesting. This is essentially a discussion of how you should interpret the backtesting results in order to select the most appropriate model. This should be no more than 300 words. - Your excel file should be formatted in a reasonably clear way, so that someone who was given the same job after you would be able to understand your working and replicate what you have done. Additional Notes and Instructions: - After downloading the raw data from Yahoo finance or another financial database, you will need to make it suitable for analysis. - Your excel spreadsheet must contain the formulas that you have used for all calculations (i.e. - don't paste the values for the calculations). - There is an Excel file available on Canvas (called "Excel guide.xlsx") that includes instructions for how to do several useful things in excel. It also includes some informative examples which might be of interest. Please look at this file.