Only need the excel section completed. I will do the written report stuff. Model is the format I want it complete in. Thanks

Date 6/1/2010 7/1/2010 8/1/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/1/2011 2/1/2011 3/1/2011 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/1/2011 11/1/2011 12/1/2011 1/1/2012 2/1/2012 3/1/2012 4/1/2012 5/1/2012 6/1/2012 7/1/2012 8/1/2012 9/1/2012 10/1/2012 11/1/2012 12/1/2012 1/1/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 10/1/2013 11/1/2013 12/1/2013 1/1/2014 2/1/2014 3/1/2014 4/1/2014 S&P 500 index ... S&P500 firm price ... Nasdaq index ... Nasdaq firm 1 price ... Nasdaq firm 2 price ... 5/1/2014 6/1/2014 7/1/2014 8/1/2014 9/1/2014 10/1/2014 11/1/2014 12/1/2014 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 9/1/2015 10/1/2015 11/1/2015 12/1/2015 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 Return Date 7/1/2010 8/1/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/1/2011 2/1/2011 3/1/2011 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/1/2011 11/1/2011 12/1/2011 1/1/2012 2/1/2012 3/1/2012 4/1/2012 5/1/2012 6/1/2012 7/1/2012 8/1/2012 9/1/2012 10/1/2012 11/1/2012 12/1/2012 1/1/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 10/1/2013 11/1/2013 12/1/2013 1/1/2014 2/1/2014 3/1/2014 4/1/2014 5/1/2014 S&P 500 return ... S&P500 firm return ... Nasdaq return ... Nasdaq firm 1 return ... 6/1/2014 7/1/2014 8/1/2014 9/1/2014 10/1/2014 11/1/2014 12/1/2014 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 9/1/2015 10/1/2015 11/1/2015 12/1/2015 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 Average S.D. Annual return Annual S.D. Nasdaq firm 2 return ... Two graphs S&P 500 S&P 500 S&P 500 firm Nasdaq Nasdaq firm 1 Nasdaq firm 2 S&P 500 firm Nasdaq Nasdaq firm 1 Nasdaq firm 2 1 Corr Corr Corr Corr 1 Corr Corr Corr 1 Corr Corr 1 Corr 1 Dates Inputs of Table 5 Y inputs X inputs Label Label Inputs of Table 6 Y inputs X inputs Label Label Inputs of Table 7 Y inputs X inputs Label Label S&P 500 firm Annual return Expected return Jensen's alpha Nasdaq 1 firm Nasdaq 2 firm SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations ANOVA df SS MS F Significance F Coefficients Standard Error t Stat P-value Lower 95% Regression Residual Total Intercept S&P500 Upper 95% Lower 95.0% Upper 95.0% Similar to Table 5 Similar to Table 5 Risk free rate Portfolio 1 return S.D. Correlation Nasdaq 1 Nasdaq 2 S&P 500 Nasdaq 1 Sharpe ratio of Nasdaq 1 return S.D. S&P 500 Nasdaq 1 1 0.95 0.9 0.85 0.8 0.75 0.7 0.65 0.6 0.55 0.5 0.45 0.4 0.35 0.3 0.25 0.2 0.15 0.1 0.05 0 0 0.05 0.1 0.15 0.2 0.25 0.3 0.35 0.4 0.45 0.5 0.55 0.6 0.65 0.7 0.75 0.8 0.85 0.9 0.95 1 Optimal portfolio Sharpe ratio Weight 1 Weight 2 0.5 0.5 1 0 Weight 1 0.5 S&P500 firm Weight 2 0.5 Nasdaq 1 0 0 Boeing Autodesk mean S.D. Sharpe S&P500 Portfolio 2 return S.D. S&P 500 Nasdaq 2 Sharpe ratio of Nasdaq 2 return S.D. S&P 500 Nasdaq 2 1 0.95 0.9 0.85 0.8 0.75 0.7 0.65 0.6 0.55 0.5 0.45 0.4 0.35 0.3 0.25 0.2 0.15 0.1 0.05 0 0 0.05 0.1 0.15 0.2 0.25 0.3 0.35 0.4 0.45 0.5 0.55 0.6 0.65 0.7 0.75 0.8 0.85 0.9 0.95 1 Optimal portfolio Sharpe ratio Weight 1 Weight 2 0.5 0.5 1 0 Weight 1 0.5 S&P500 firm Weight 2 0.5 Nasdaq 2 0 0 Boeing Autodesk mean S.D. Sharpe Discussion of A level Portfolio 1 Optimal Two graphs S.D. return Portfolio 2 Optimal S.D. Complete Portfolio return A y 1-y CP return CP S.D. Sharpe ratio Important Notice for Assignment 1: Assignment 1 accounts for 30% of your final marks. This assignment consists of a written report and Excel working sheets. Based on previous students' feedback, the time of completing this assignment is about 30 hours, depending on your Excel skills. Please start this assignment early (after you studying topics 1 - 3) to avoid late submission. I wish you all the best on assignment 1. To encourage you to do this assignment 1, please see the following feedback from a previous student: \"Hi Jing, I'm not sure if you remember me, but I was in your Investment Analysis class in 2012. I moved to New York in July 2013. I started at the Royal Bank of Scotland in December 2013 on a Currency Options Trading Desk. I have however just been accepted into the JP Morgan graduate program and am starting as a Junior Hedge Fund Analyst on June 2nd. I had quite a rigorous and competitive interview process - 7 in total, a dinner and a case study. It was what I learnt in your class during the applied learning exercises that gave me the skills required to complete the case to a high standard and get the position. I just wanted to say thank you, because if it wasn't for your class I would not have had this life changing opportunity.\" 1 Assignment 1 (Case study) Hello Our friend, John has recently inherited $100,000 and he is interested in investing it. I thought since you are doing that Investment paper, you should be able to apply some of your knowledge and help him out. He wants to be able to invest by himself later on, so in addition to recommendations on what to do with the money he wants to understand your analysis. Since it is a small portfolio and he has not got a lot of time, and with transaction costs it is not worth him taking on too many stocks. I have suggested a two-stock portfolio with the risk-free asset. So please just focus on one company from the S&P 500, and pick two companies from the NASDAQ. Please choose one of the two NASDAQ stocks (remember, explain your choice) to construct the optimal risky portfolio with the S&P 500 company you selected. Then use John's answers (your own answers) to the utility questionnaire to determine how he should allocate his money (forming the complete portfolio using the right optimal risky portfolio and the risk-free asset). The annual risk free rate is 2% and the sample period is from 1 June 2010 to 2 June 2017 (Please use these exact days for sample period selection). When you choose your S&P 500 company, choose one which pays dividends, as he wants some income from his investments. Also make sure all three companies have been listed for a while, so you can get adequate data for analysis. For the data, just check http://finance.yahoo.com. Don't worry too much if the companies you choose do not look ideal. You are not trying to sell him a particular company, although a good choice of companies will make your assignment much easier (some tips are given below). Understanding how to construct an optimal and a complete portfolio and how to evaluate a company are the key tasks here. Tips of choosing your companies: Play around with the stock returns and standard deviation (S.D.) of the company. A company with an average annual return below the annual risk free rate is obviously not a good choice. In general, a company with high return (in comparison with returns of other stocks) and low S.D. (in comparison with S.D. of other stocks) is a good choice. 2 Please prepare a written report (3-5 pages, 1.5 line space) on your portfolio construction and company analysis, after you finish the following calculations in all the steps in Excel. The written report is worth 58 marks, and should include the following: 1. basic information of your three chosen companies (including the firm's age, which industry it belongs to, business life stage, etc. for no more than one page for all three companies) (Please do not just copy and paste from the company website. The analysis of the public information is required.) (3 marks); any strategies of these firms for developing in the international markets (6 marks); and why you choose these three companies; (3 marks) 2. your three companies' basic statistical analysis from your own calculations (return, risk and correlations of your three companies; alpha, beta and R-squared of your companies and whether your companies are overpriced or underpriced; and relevant discussion); (14 marks) 3. the summary of the portfolio construction and selection (with portfolio weights, return, risk of portfolios and which portfolio is better and why; and any relevant information and analysis); (11 marks) 4. evaluating your three companies by comparing each chosen firm with any two competitors in the same industry using the relative valuation model. You need to calculate and compare the intrinsic value of your chosen companies and their competitors; (The formula for the relative valuation method is: intrinsic value0 = industry P/E ratio*company's EPS1) (15 marks) 5. the future prospects of your three companies based on your analysis and the discussion of the complete portfolio (including any borrowing or lending situation if any). (6 marks) The following tables and calculation should be included in an Excel file, 42 marks. (I will show you an example of how to do steps 1, 2 and 4 in Excel on Stream. As for how to collect data, please see \"help and hint of assignment\" at the end of this file. I will also go through all the steps with you during my internal class in Week Five.) Step 1 - Table of monthly adjusted closing prices for the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies for the sample period 3 (from 1 June 2010 to 2 June 2017); (2.5 marks) Table 1 looks like this: Date S&P 500 index ... S&P500 firm price ... Nasdaq index ... Nasdaq firm 1 price ... Nasdaq firm 2 price ... Step 2 - Table of monthly returns for the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies for the sample period; (2.5 marks) When calculating monthly returns, please note the following: You can use either one of the following two methods to calculate returns (based on different assumptions). I will explain the difference in Air New Zealand example posted on Stream. (1) Monthly return = ln (Pt+1 / Pt); Where Pt = Price for month t (say May), and Pt+1 = Price for month t+1 (say June). 'Ln' stands for natural logarithm in Excel. (2) Monthly return = (Pt+1 - Pt)/Pt In your table, you will have 'n1' adjusted closing prices. Using the above formula, you will have 'n-1' monthly returns. Using the monthly returns, Excel can calculate the monthly S.D. for step 4. Table 2 looks like this: Date S&P 500 return ... S&P500 firm return ... Nasdaq return ... Nasdaq firm 1 return ... Nasdaq firm 2 return ... Step 3 - Two graphs on the monthly returns for the sample period; one with monthly returns of the S&P 500 index and your S&P 500 company, and the other with monthly returns of the NASDAQ index and the two NASDAQ companies; (2 marks) Step 4 - Summary table of the average monthly returns and standard deviations (S.D.) and the average annual returns and S.D. of the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies; (5 marks) 1 `n' is the total number of observations. 4 After getting the monthly average return (the average of the n-1 monthly returns), and the monthly S.D., to annualize your average monthly return, please use the following formula (you only need to annualize your average monthly return, not all n-1 monthly returns): Rannual = (1 + Rmonthlyaverage)12 -1 To annualize your monthly standard deviation: S.D.annual = S.D.monthly * 120.5 Table 3 looks like this: S&P 500 S&P500 firm Nasdaq Nasdaq firm 1 Nasdaq firm 2 Average monthly return Monthly S.D. Annual return Annual S.D. Step 5 - Correlation matrix among the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies; (1 mark) (To calculate the correlation matrix, use Excel - data or tool data analysis correlation. The input is the 5 time series of monthly returns of the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies that you get in Step 2.) Table 4 looks like this: S&P 500 firm Nasdaq Nasdaq firm 1 S&P 500 S&P 500 firm Nasdaq Nasdaq firm 1 S&P 500 1 Corr Corr Corr 1 Corr Corr 1 Corr 1 Nasdaq firm 2 Corr Corr Corr Corr Nasdaq firm 2 1 Step 6 - Using the regression method to find and highlight three companies' (your S&P 500 company and two NASDAQ companies) betas (against the proper market index) and R-squared of the regressions. Show the input data of the regressions and the three regression result tables; (7 marks) 5 (To run the regression, use Excel - data or tool data analysis regression.) Tables 5-7 each looks like this: SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations ANOVA df SS MS F Significance F Coefficients Standard Error t Stat Pvalue Lower 95% Regression Residual Total Upper 95% Lower Upper 95.0% 95.0% Intercept S&P500 From Step 7 to Step 10, you need to use annual returns and S.D., rather than monthly returns and S.D. Step 7 - Using Jensen's alpha equation, calculate the alpha of your S&P 500 stock and two NASDAQ stocks2 (3 marks) Step 8 - Formation of two optimal risky portfolios (Portfolio one consists of your S&P 500 stock and NASDAQ stock 1; and portfolio two consists of your S&P 500 stock and NASDAQ stock 2) (The optimal risky portfolio is different from the global minimum variance portfolio and you need to consider the existence of the risk-free asset when calculating the weights of your stocks): calculating the weights of two stocks in each optimal risky portfolio; calculating the expected return & risk of the two optimal risky portfolios; and calculating the Sharpe ratios of two optimal risky portfolios (10 marks) 2 Please go on with the project, even if you find your stock is overpriced. 6 (Please refer to topic 3, section 7.3 \"Asset allocation with stocks, bonds and bills\" in the textbook, if you have difficulties doing step 8 to step 10.) The inputs in the above formulae should be expected data (return, risk and covariance). I am happy for you to use historical data you get from step 4 and step 5 as inputs here, assuming the past will go on. If you decide to use CAPM to calculate expected returns, it is also OK, but you might find expected returns using CAPM are very low. You also need to justify your estimations. Step 9 - Answer the utility questionnaire on Pages 174-175 (10th edition) or Pages 166167 (9th edition) in the textbook and get your \"A\"--the degree of risk aversion (1 mark) In terms of the utility questionnaire, after you answer all the questions yourself and get the score for the questionnaire in the textbook, convert the questionnaire score into \"A\" value (degree of risk aversion) using the following guideline: If you are a conservative investor, your \"A\" is 5-6; if you are a moderate investor, your \"A\" is 3-4; if you are an aggressive investor, your \"A\" is 1-2. Step 10 - Two graphs of the optimal portfolios and CALs: provide two input tables for the investment opportunities curves and show two graphs for each efficient frontier and CALs (4 marks) Step 11 - Determination of the complete portfolio along the CAL according to John's degree of risk aversion (your own risk aversion level): calculating the weights between the optimal risky portfolio and the risk-free asset; calculating the return and risk of the complete portfolio; (4 marks) Thank you. 7 Help and Hints for Assignment 1 In order to get the data, please go to YahooFinance (http://finance.yahoo.com/) On the top of the page, click the S&P 500 or NASDAQ link, and that will take you to the S&P 500 or NASDAQ page. Once you enter S&P 500 page, the link called Historical Data gives you the information on S&P 500 index. There is another link called Components. Components page will take you to top 30 NASDAQ listed companies. To get a list of S&P 500 listed firms, please use this website: https://en.wikipedia.org/wiki/List_of_S%26P_500_companies To get a list of Nasdaq 100 listed firms, please use this website: https://en.wikipedia.org/wiki/NASDAQ-100 Once you have identified certain companies of interest, there are a few important and useful links on the company's page. You can use these links to search for more information about the company. You can also go to the company's website for more detailed information about this company. 8 In particular, you need to go to the Historical data link to download MONTHLY price information for the sample period. Select the start and end dates of the sample period (from 1 June 2010 to 2 June 2017) (Please enter the exact start and end dates for the sample period for the accuracy of calculation), and select monthly prices, press Apply, and then Download data. You should choose adjusted closing prices. All the calculation can be done in Excel. Please go to 'Insert - Function' for simple calculations, such as sum (=SUM() in Excel), average (=AVERAGE() in Excel), standard deviation(=STDEV() in Excel), and ab (=a^b in Excel). Please go to 'Tool or Data - Data Analysis' for more complicated calculations, such as correlation matrix and regression analysis. For Data Analysis function, you need to go to Excel file options - add-ins - Excel add-ins, then press \"go\" and tick \"analysis toolpak\" and then \"OK\". You will find some more tips for Assignment 1 in the \"important notes\" under relevant topics in Section C of the study guide. 9