Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Task 1: Use the monthly returns of MSFT and Walmart between June 2007 and June 2022 to do the following (include June 2022 in your

Task 1: Use the monthly returns of MSFT and Walmart between June 2007 and June 2022 to do the following (include June 2022 in your calculation): a) Calculate each stock's arithmetic average using the AVERAGE function. b) Calculate each stock's geometric (compounded) average using the GEOMEAN function. Note: the GEOMEAN function does not recognize negative numbers. To complete this task you need to create a separate column where you have to add 1 to each line and then use the GEOMEAN function. Don't forget to subtract one from the result. c) Calculate each stock's sample standard deviation using the STDEV function. d) Calculate the sample covariance of MSFT and Walmart using the COVAR function. You also need the COUNT function. In general, the sample covariance of stock X and Y is given by (however, use the Excel function): (X, -X)(Y;-Y) Cov(X,Y)= =1 n-l Calculate the correlation coefficient of MSFT and Walmart. f) Using the Pivot Tables function, please prepare a chart showing the frequency distribution of monthly returns of MSFT and WMT. Discuss what you observe and how it relates what you found in a-c of this task. Presentation of your work: provide a file (Word or PDF would be best) with the results from your Excel file (no need to send the data; make sure to clearly label the cells where your answers are displayed), graphs and discussion.
image text in transcribed
Use the monthly returns of MSFT and Walmart between June 2007 and June 2022 to do the following (include June 2022 in your calculation): a) Calculate each stock's arithmetic average using the AVERAGE function. b) Calculate each stock's geometric (compounded) average using the GEOMEAN function. Note: the GEOMEAN function does not recognize negative numbers. To complete this task you need to create a separate column where you have to add 1 to each line and then use the GEOMEAN function. Don't forget to subtract one from the result. c) Calculate each stock's sample standard deviation using the STDEV function. d) Calculate the sample covariance of MSFT and Walmart using the COVAR function. You also need the COUNT function. In general, the sample covariance of stock X and Y is given by (however, use the Excel function): Cov(X,Y)=n1i=1n(XiX)(YiY) e) Calculate the correlation coefficient of MSFT and Walmart. f) Using the Pivot Tables function, please prepare a chart showing the frequency distribution of monthly returns of MSFT and WMT. Discuss what you observe and how it relates what you found in a-c of this task. Presentation of your work: provide a file (Word or PDF would be best) with the results from your Excel file (no need to send the data; make sure to clearly label the cells where your answers are displayed), graphs and discussion

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Valuation Measuring And Managing The Value Of Companies

Authors: McKinsey & Company Inc., Tom Copeland, Tim Koller, Jack Murrin

3rd Edition

0471361909, 978-0471361909

More Books

Students also viewed these Finance questions

Question

5. How do online reports differ from hard-copy reports?

Answered: 1 week ago