Excel Essentials >> Week 1 >> Mastering Data Analysis in Excel
1. Question 1 Please download the following workbook for the Excel Essentials Quiz. Excel-Essentials-Quiz(1).xlsx 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. Question 1: What is the slope of the best-fit line (rounded to two decimal places)? 1 point 0.11 0.12 0.15 0.18 2. Question 2 What is the coefficient of determination (R-squared)? Use the “rsq” Excel function (Trendline in Excel may give an inaccurate value for R-squared). 1 point 0.18 0.20 0.22 0.24 3. Question 3 What is the Y-intercept, in percent? Use the “trendline” but double-check against the “intercept” function. 1 point 0.25% 0.35% 0.45% 0.55% 4. Question 4 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. Question 4: 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? 1 point RSP SPY 5. Question 5 For the asset you chose in Question 4, what was the Sharpe ratio? Round your results to two decimal places. 1 point 0.56 0.53 0.50 0.48 6. Question 6 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. 1 point September 1, 2010 October 1, 2009 April 2, 2007 July 3, 2006 7. Question 7 What was the monthly return from the question above? 1 point 22.9% 24.11% 43.27% 51.87% 8. Question 8 What was Duke Energy’s return that same month? 1 point 0.51% 1.13% 3.04% 3.18% 9. Question 9 Using the Solver plug-in (Solver Add-In) for Excel, answer Questions 9 and 10, based on the information below: Solver Add-In.xlsx 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. Question 9: 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. Solver_optimization_example(1).xlsx 1 point $5.00 $12.50 $13.50 $25.00 10. Question 10 What is the monthly revenue at that price per pound? ( , indicates thousands) 1 point $15,100 $62,500 $62,100 $40,000
*Please Wait 15 Seconds To Get The Pdf Loaded