Consolidate all excel spreadsheets from part1 in a single Excel file.ONLY an EXCEL FILE will be accepted. You have write up with your own words. Do not copy from others work. In the last page, you have to show all your references in the last sheet in your Excel.

Part 1 Basic Financial Data Analysis Instruction: You have been retained by the SHB investment group to provide a basic analysis report to the president of the company, Stan Lee. You are now asked to analyze basic characteristic of Boeing (Ticker: BA). In order to do your analysis, you need to use two data resources to collect economic variables such as Federal Reserve Economic Data (FRED) 1 and Yahoo!Finance2 . The risk-free rate for computing risk premium is in FRED3 . You are able to obtain the historical price per share for the company from Yahoo! Finance. To locate the historical s&p 500 index price (Ticker: GSPC), you can use Yahoo! Finance. You must write your findings, all relevant information, and computations, so as to provide strong evidence. For this project, you must download monthly historical data from Jan. 2001 to Dec. 2017.

Problem 1. Compute monthly stock returns for Boeing and SP500 index using adjust close prices. And merge two returns data with risk-free rates by dates. (Just copy monthly risk free rates and paste next to monthly stock returns)

Problem 2. Let us generate the descriptive statistics of those obtained data and figure out the shape of the datasets. 1) First, calculate monthly means, standard deviations, skewnesses, and kurtoses of BA returns, SP500 index returns, and 3-month T-bill rates. 2) Next, plot the respective distributions for BA stock returns, SP500 index returns, and risk-free rates4 . 3) Provide your findings thoroughly using the obtained statistics and plotted charts.

Problem 3. To examine the linear relation among SP500 index returns, risk-free rates, and BA returns, 1) Compute correlation coefficients and covariances for all the combinations of them: SP500 index returns vs. 3-month T-bill rates (i.e. risk-free rates), SP500 index returns vs. BA stock returns, risk-free rates vs. BA stock returns.2) Draw three scatter plots for all the combinations of them: SP500 index returns vs. 3-month T-bill rates (i.e. risk-free rates), SP500 index returns vs. BA stock returns, risk-free rates vs. BA stock returns. 3) Explain all the values and plots and provide your findings.

Problem 4. The econometric CAPM model, which explains the variation of excess stock returns in response to the market risk premium., is given by5 Ri(t) Rf (t) = [Rm(t) Rf (t)] + (t) (1) where Ri(t) represents a stock return on asset i at t, Rm(t) represents a market return at time t 6 , Rf (t) is a risk free rate at time t, and i refers to noise term (or error term). 1) Create two columns for calculating monthly excess returns,Ri(t)Rf (t), and excess market return, Rm(t) Rf (t). 2) Compute the respective means and standard deviations for the excess return on market and the excess return on BA stock. 3) Also calculate covariance between two excess returns.

Problem 5. Regress the monthly excess BA returns on the excess market returns. Provide the estimated CAPM model and explain what the beta of the estimated model implies.

Problem 6. Using all the relevant statistics from Problem 4, compute the beta of the model using the below formula = i,m 2 m (2) where i,m refers to the convariance between the excess BA return and the excess market return, and 2 m is the variance of market index returns. Examine whether there is any difference between the beta that you obtained the previous problem and the value from the formula. Do you see any difference? If yes, please explain WHY. If no, please explain WHY NOT.

1) Stock Price & SP500 Index price: from Yahoo!Finance (https://finance.yahoo.com/)

2) 3 month T-bill: from FRED (https://fred.stlouisfed.org/)

1https://fred.stlouisfed.org/ 2http://finance.yahoo.com/ 3The 3 month T-bill rate has been generally regarded as risk free rates in the market. Note that all the rates are expressed in percent. 4Using histogram, you can plot the distribution of your data. If you do not know how to create a histogram table and plot, please refer to the next site (http : //www.excel easy.com/examples/histogram.html).

5Note that CAPM assumes zero intercept. 6 In this project, market returns are referring to SP500 index returns.

P.S. this is all what the instructor provided, nothing else, based on this info we have to do everything by ourselves.

Please copy from the link because I can't upload the excel file here, if needed I will pay extra, I need this homework to be submitted by Wednesday, Thank you for understanding.