Mastering Data Analysis in Excel Coursera Answer

In this blog you will find the correct answer of the Coursera quiz Mastering Data Analysis in Excel Coursera Answer mixsaver always try to brings best blogs and best coupon codes

1. Background Information: You are provided below with an Excel Spreadsheet that gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks.

Excel Problem Type: Summing a column

Problem Information: Daily continuously compounded returns can be summed to obtain returns over longer time intervals. Sum the daily returns to calculate annual continuously compounded returns for 2010. Give each result in percent, rounded to two digits to the right of the decimal place – for example, 11.76%.

Solve: What is the Dow Chemical Annual return?

18.65%
23.23%
26.15%
20.51%

2. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. Use this spreadsheet to answer the question.

Excel Problem Type: Calculating correlation for a two-column array

Question: What is the correlation between daily continuously compounded returns for Dow Chemical and for the S&P 500 Index? Round your answer two digits to the right of the decimal place – for example, .84

3. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. Use this spreadsheet to answer the question.

Excel Problem Type: Identifying the maximum value in a column and sorting multiple columns while preserving rows.

Question: On what day in 2010 did Dow Chemical returns out perform S&P 500 Index returns the most?

February 9, 2010
October 25, 2010
April 28, 2010
February 1, 2010

4. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. Use this spreadsheet to answer the question.

Excel Problem Type: Using Excel “If” statements to determine how many days in 2010 Dow Chemical returns are higher than Dupont Returns.

Problem Information: Assuming Dow Chemical Returns are in Column B and Dupont Returns in Column C, the “If” statements will be of the form =IF(B3>C3, 1, 0).

Set up a column of “If” statements and then each day where Dow return > Dupont return will have a value of 1, otherwise 0.

Question: How many days out of the 252 trading days in 2010 did Dow outperform Dupont?

5. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. Use this spreadsheet to answer the question.

Excel Problem Type: Sorting multiple columns while preserving rows

Question: What was the fifth-worst performing day for the S&P 500 Index in 2010?

May 20, 2010
June 29, 2010
February 4, 2010
May 10, 2010

6. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. Use this spreadsheet to answer the question.

Excel Problem Type: Defining the Sharpe Ratio

Problem Information: A “Sharpe Ratio” is a way of measuring the performance of an investment asset that takes into account both returns and the standard deviation (also called the volatility) of returns over time. A stock’s Sharpe ratio is the difference between its returns and the return of a risk-free investment, such as a government bond, divided by the standard deviation of returns of the asset. For example, if a stock returns 15% per year, and the risk-free asset returns 3% per year, and the volatility of the stock is 18% per year, the Sharpe Ratio is 12%/18% = .67.

Question: Assume a risk-free asset returns 2% per year, and the standard deviation of returns of Dupont stock is 20%. What is the Sharpe Ratio for Dupont stock for 2010? Give the answer to two digits to the right of the decimal place.

7. Excel Problem Type: Optimization using the “Solver” plug-in

Problem Information: Assume that at a particular gas station, the quantity of automobile fuel sold in a week is a function of the fuel’s retail price.

The quantity of fuel sold in a week (in gallons) = (1,000 – 300x), where x is the price in dollars per gallon.

The function f(x) for revenues from weekly sales, in dollars, will equal x*(1000 – 300x) = 1000x – 300x^2.

Without using calculus or any other advanced math, the MS Solver plug-in can be used to find the input value for x that results in a maximum value for a function f(x). The price x is in the Solver “variable cell” and the function 1000x – 300x^2 is the Solver “objective.”

Question: What is the price x that maximizes weekly revenues?

$1.45 per gallon
$16.67 per gallon
$1.67 per gallon
$14.50 per gallon

8. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. Use this spreadsheet to answer the question.

Excel Problem Type: Scatter plots and trend line options

Solve: Generate a scatter plot that pairs the daily returns of Dow Chemical (y axis) “against” the S&P 500 returns (x axis). The slope of the regression line is also called “Beta.”

Question: What is Beta for Dow Chemical? Give the answer rounded two digits to the right of the decimal place.

1.55
1.00
1.62
1.66

Excel Essentials

1. 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.

Question 1: What is the slope of the best-fit line (rounded to two decimal places)?

0.11
0.12
0.15
0.18

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).

0.18
0.20
0.22
0.24

3. What is the Y-intercept, in percent? Use the “trendline” but double-check against the “intercept” function.

0.25%
0.35%
0.45%
0.55%

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?

SPY
RSP

5. For the asset you chose in Question 4, what was the Sharpe ratio? Round your results to two decimal places.

0.56
0.53
0.50
0.48

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.

September 1, 2010
October 1, 2009
April 2, 2007
July 3, 2006

7. What was the monthly return from the question above?

22.9%
24.11%
43.27%
51.87%

8. What was Duke Energy’s return that same month?

0.51%
1.13%
3.04%
3.18%

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.