Can you do this RWA and send back the excel file to my email plsMy email:
[email protected]Sheet1 DatePEPCRBLNKEDISJNJXOMMKT (VTI) 4/1/1688.791641116.22770755.73886597.41994598.03369969.2534196.164963 5/1/1687.247978121.12417652.22091793.60843798.56725369.73910597.832855 6/1/1691.361565137.10833752.20200392.287613106.85575974.05398697.668816 7/1/1694.635849125.8658652.63903490.523369110.31775770.269928102.000221 8/1/1692.75045125.01248254.66871689.765015105.12912868.840042102.210793 9/1/1695.173981108.66576449.93594488.244537104.75743169.538628101.917831 10/1/1693.800247113.41610747.72574688.082977102.8596866.383545100.169693 11/1/1687.587723134.91583347.62113294.19340598.700669.554558104.668076 12/1/1692.228752138.43089348.34396799.039902102.87397872.549652106.084747 1/1/1791.479492131.03596550.494244105.960678101.12386367.429527108.728394 2/1/1797.297234134.43699654.560513105.424408109.12448165.363815112.745781 3/1/1798.601822132.98399453.195545108.584564111.94982966.521172112.301453 4/1/17100.539383133.76896753.054893110.700897110.97907366.229172114.007561 5/1/17103.725616140.31631550.737747103.365532115.2755265.296371115.160561 6/1/17103.204819140.68643256.492306101.747154119.69532866.101318115.718468 7/1/17104.205696130.76057456.732891105.271187120.08438965.536339118.437309 8/1/17103.419304128.68437250.73774797.632111119.767762.498615118.614807 9/1/17100.273529131.24662849.98660395.094833118.38066167.775436120.987473 10/1/1799.193672135.15060453.01375294.361626126.9398568.908043124.145264 11/1/17104.853935136.41857958.248611101.124474126.86701268.858452127.90728 12/1/17108.662155138.65254260.302067103.719643128.0011969.787743128.760971 1/1/18109.0065154.00212165.986725105.682114126.59954172.841591136.161255 2/1/1899.428787137.16365164.835678100.323616118.98652663.196121131.04216 3/1/1898.903244139.88755864.26499997.678398118.15339762.88987127.949921 4/1/1892.139931144.62370366.35125797.571426116.62287965.536613129.046982 5/1/1891.510078138.72145169.65958496.735069110.28878868.478378132.558792 6/1/18100.292572138.27883977.30468101.928246112.70435370.441162132.927933 7/1/18105.939621129.68344174.825699110.43766123.08869269.402374137.921173 8/1/18103.185196136.27502479.972328109.815689125.10424868.261429142.655151 9/1/18102.991737134.48350582.627045114.638885129.19183373.13768142.255875 10/1/18104.382347145.04072673.186195112.570404130.893668.543976132.342529 11/1/18113.262001166.58204773.264229113.217415137.35453868.389137135.007324 12/1/18102.617569147.28059472.523048107.49234121.43285459.254467121.902969 1/1/19105.482681154.11671480.094231110.173546125.22499163.677479133.090912 2/1/19108.263214150.35501183.860291111.477608128.57484468.674004137.827377 3/1/19115.662788150.00230482.373421109.689461132.41130170.99157139.02829 4/1/19120.853699156.62017886.135521135.316269133.74687270.534691145.266449 5/1/19120.806503146.98701575.651749130.445786124.22737962.179127135.896927 6/1/19123.760605159.74975682.559532137.954025132.83096368.09951144.976654 7/1/19121.510361162.53813284.605087141.28334124.19045366.082207147.571228 8/1/19129.990707158.57989583.100426136.442535122.41657360.85677144.497803 9/1/19130.342468155.94331492.603004129.543869124.31394263.529888146.398087 10/1/19131.310196149.08813588.294289129.146255126.86977460.794708150.166092 11/1/19130.027451148.63882492.179031150.677261132.106461.298557155.85408 12/1/19130.831573148.638824100.150017143.768646141.12930363.540272159.360321 1/1/20136.905304147.85566795.197395138.309998144.03179956.565224160.126511 2/1/20127.275063139.69084288.356628117.650002130.10948246.840229147.317947 3/1/20115.77471981.1070482.01958596.599998127.67734535.071091126.234756 4/1/20128.3867894.92700286.420921108.150002146.08943242.922138143.522827 5/1/20127.668648105.96671397.721664117.300003144.8334241.998486151.267792 6/1/20128.357666109.70567397.438736111.510002137.87640442.106636154.042969 7/1/20134.635345109.27044797.001472116.940002142.90594539.620914163.631607 8/1/20136.98259132.258102111.192451131.869995150.40615837.605972175.257431 9/1/20135.554688114.660004125.033722124.080002146.93635632.961494168.366943 10/1/20131.298737113.82119.595741121.25135.32000731.319654165.761993 11/1/20142.075302139.580002134.156784148.009995142.79116836.610012185.327042 12/1/20146.084503131.919998140.89949181.179993156.40470940.531681193.208649 1/1/21135.488144135.309998133.318954168.169998162.1190844.091232193.345703 2/1/21128.166611154.869995134.506531189.039993157.47801253.462093199.415482 3/1/21140.329483172.880005132.889999184.520004164.35000655.830002206.003525 4/1/21143.020004178.009995136.639999186.490005161.63999955.5214.199997 Part 1: Getting the data Download the starter worksheet from the link. The source data includes 6 stocks: PEP, CBRL, NKE, DIS, JNJ, XOM As well as a market benchmark: VTI Part 2: Calculate the monthly return for each column of price. Calculate the monthly return for your stock given the following equation: Monthly return = (Price_t / Price t-1) -1 When you are done, you should have 60 monthly returns for the two companies and the market benchmark. Part 3: Return Analysis Next, you will analyze the returns. Complete the following tasks: · Calculate the following summary statistics for each series of returns: Mean, Median, Variance, Stdev, Skewness, Kurtosis, Max, Min, and Count · Calculate the Sharpe Ratio, Treynor Ratio, 5-year holding period return, and the $1 invested equivalent · Build the correlation matrix for the 4 assets · Find the confidence intervals: 68-95-99 for all companies · Determine the probability of losing (and making) money in a given month. Then compare this to the historically observed values · Build a frequency distribution for the observed return data (PEP and VTI) Part 4: Regression Analysis For this section, you will estimate a linear regression, by regressing the bench market returns against each stock’s returns. Complete the following tasks: · Estimate the alpha, beta, and R-squared for 60-months for the main company, competitors, and market benchmark. · Find two different beta values for PEP published on a financial website (e.g. yahoo finance). Part 5: Graphs For this section you will need to create the following graphs: · Create a time-series graph of the stock’s price for all companies and the benchmark (you may want to have a secondary axis for the benchmark) · Create a time-series graph of the $1 invested for your main company versus the market benchmark · Create a bar chart of the last 12 monthly returns for the company and the market benchmark (same graph) Deliverables: Excel file only