![]() ![]() Probability, AUC, and Excel Linest Function > Week 5 Probability, AUC, and Excel Linest Function > Week 5 > Mastering Data Analysis in Excel 1.Parametric Models for Regression (graded) > Week 4 Parametric Models for Regression (graded) > Week 4 > Mastering Data Analysis in Excel 1. ![]() Information Measures (graded) > Week 3 Information Measures (graded) > Week 3 > Mastering Data Analysis in Excel 1.Binary Classification (graded) > Week 2 Binary Classification (graded) > Week 2 > Mastering Data Analysis in Excel 1.What is the monthly revenue at that price per pound? (, indicates thousands) This can also be found in “Course Resources” as a quick reference. If this question is too challenging, there is another example below to review. Question 9: What is the revenue-maximizing selling price per pound for Egger’s Roast Coffee? The linear function is (quantity sold in pounds) = (-400*(Price per pound)) + 10,000. ![]() Using the Solver plug-in (Solver Add-In) for Excel, answer Questions 9 and 10, based on the information below:īetween 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. What was Duke Energy’s return that same month? What was the monthly return from the question above? Note: Use “paste special” and choose “values and number formats” to keep return values from changing. In the month ending on which date did Amazon achieve the highest returns? 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?įor the asset you chose in Question 4, what was the Sharpe ratio? Round your results to two decimal places. Second, divide that difference by the annualized population standard deviation of returns of the stock.įor 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.įor 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. The annual “Sharpe Ratio” is a metric that combines profitability and risk – it measures units of profitability per unit of risk.įirst calculate the difference between the annual return of a stock and the annual return of a risk-free investment in government bonds. What is the Y-intercept, in percent? Use the “trendline” but double-check against the “intercept” function.Īnswer Question 4 and 5 based on the information below: What is the coefficient of determination (R-squared)? Use the “rsq” Excel function (Trendline in Excel may give an inaccurate value for R-squared). Question 1: What is the slope of the best-fit line (rounded to two decimal places)? When you use “trendline” option for slope, R-squared, and the y-intercept, double-check your results against the equivalent cell formula answers. Use Excel’s chart function to generate a scatter plot of SPY index monthly returns (y axis) against Amazon monthly returns (x axis) 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. Please download the following workbook for the Excel Essentials Quiz. Excel Essentials > Week 1 > Mastering Data Analysis in Excel ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |