Mastering Data Analysis in Excel Week 1 Quiz Answer
Mastering Data Analysis in Excel Quiz Answer Coursera
Mastering Data Analysis
Week 1 Quiz Answer
Please download the following workbook for the Excel Essentials Quiz.
This spreadsheet contains monthly continuously compounded returns for two stock indexes – RSP and SPY – and two individual stocks – Amazon and Duke Energy – for the 12 years from May 2003 to May 2015.
Use Excel’s chart function to generate a scatter plot of SPY index monthly returns (y axis) against Amazon monthly returns (x axis)
When you use “trendline” option for slope, R-squared, and the y-intercept, double-check your results against the equivalent cell formula answers.
What is the slope of the best-fit line (rounded to two decimal places)?
What is the coefficient of determination (R-squared)? Use the “rsq” Excel function (Trendline in Excel may give an inaccurate value for R-squared).
What is the Y-intercept, in percent? Use the “trendline” but double-check against the “intercept” function.
Answer Question 4 and 5 based on the information below:
The annual “Sharpe Ratio” is a metric that combines profitability and risk – it measures units of profitability per unit of risk.
First calculate the difference between the annual return of a stock and the annual return of a risk-free investment in government bonds. Second, divide that difference by the annualized population standard deviation of returns of the stock.
For example, if the annual return of a stock is 10%, the annual risk-free bond return is 2%, and the annualized population standard deviation of returns of the stock is 16%, then the Sharpe Ratio = 8%/16% = 0.5.
For this problem, you can estimate the annualized standard deviation of returns by multiplying your calculated value for the monthly population standard deviation of returns by the square root of 12.
Assuming the risk-free rate is 1.5% per year over the full 12-year interval measured, which asset had the higher Sharpe ratio: SPY or RSP?
For the asset you chose in Question 4, what was the Sharpe ratio? Round your results to two decimal places.
In the month ending on which date did Amazon achieve the highest returns?
Note: Use “paste special” and choose “values and number formats” to keep return values from changing.
What was the monthly return from the question above?
What was Duke Energy’s return that same month?
Using the Solver plug-in (Solver Add-In) for Excel, answer Questions 9 and 10, based on the information below:
Between possible pricing of $5 per pound to $25 per pound, the quantity of coffee Egger’s Roast Coffee can sell each month is a linear function of the retail selling price per pound. The linear function is (quantity sold in pounds) = (-400*(Price per pound)) + 10,000.
What is the revenue-maximizing selling price per pound for Egger’s Roast Coffee?
If this question is too challenging, there is another example below to review. This can also be found in “Course Resources” as a quick reference.
What is the monthly revenue at that price per pound? ( , indicates thousands)