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.
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)?
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).
3. What is the Y-intercept, in percent? Use the “trendline” but double-check against the “intercept” function.
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?
5. For the asset you chose in Question 4, what was the Sharpe ratio? Round your results to two decimal places.
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?
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.
- $5.00
- $12.50
- $13.50
- $25.00
10. What is the monthly revenue at that price per pound? ( , indicates thousands)
- $15,100
- $62,500
- $62,100
- $40,000
Binary Classification (graded)
1. A test for “driving while intoxicated” was given 100 times. 20 people tested were actually intoxicated, and 10 people were mis-classified as intoxicated. What would the False Positive rate be?
2. If a fire alarm malfunctions and fails to go off when there actually is a fire, that is a:
- False Negative
- False Positive
- True Positive
- True Negative
3. Use the Binary Classification Metrics Spreadsheet Definitions to answer the following:
If the “classification incidence/test incidence” is 10% for the whole population, and the true “condition incidence” is 12% for the whole population, the True Positive rate:
- must be 100%
- cannot be 100%
- can be 100%
- must be 0%
4. Use the Cancer Diagnosis Spreadsheet to answer Questions 4 to 6.
Keep the cost per False Positive test set at $500. Use MS Solver to determine the maximum cost per False Negative test that permits an average cost per test of $100.
5. Assume a cost of $15,000 per False Negative (FN) and $100 per False Positive (FP). What is the minimum average cost per test?
- $1.00
- $259,800
- $25.98
- $16,551
6.If, instead of assuming a cost $15,000 per FN and $100 per FP, the costs are assumed to be $7,500 per FN and $50 per FP, what changes?
- The minimum cost threshold of 16,551.930
- The True Positive Rate
- The False Positive Rate
- The minimum Cost per Test
7. Use logic and the definition in the Binary Performance Metrics Spreadsheet to answer the following question.
In general, increasing the cost per FN while keeping the cost per FP constant will cause the cost-minimizing threshold score to:
- Decrease
- Stay the Same
- Increase
8. Make a copy of the Bombers and Seagulls Spreadsheet to answer questions 8-10.
Modify the spreadsheet data so that there are 4 bombers instead of 3, and 16 seagulls instead of 17, by changing the actual condition for the radar score of 66 from a 0 to a 1 in cell D43.
What is the new Area Under the Curve:
9. Assuming the costs for classification errors are 5 million pounds per FN and 4 million pounds per FP, how much does changing the value at Cell D43 from 0 to 1 change the minimum cost per event?
- Increases by 950,000 pounds
- Increases by 5 million pounds.
- Unknown
- Increases by 250,000 pounds
10. Change the cost per FN to 50 million pounds. How does changing the data in cell D43 from a 0 to a 1 change the cost-minimizing threshold?
- Decreases it from 75 to 66.
- Decreases it from 75 to 62
- Decreases it from 75 to 70
- Increases it from 66 to 75.
11. Use the Binary Performance Metrics Spreadsheet definitions to answer the following question.
A population tested for “driving while intoxicated” has a Condition incidence of 20%. If the test has a true positive rate of 70% and a false positive rate of 10%, what is the test’s Positive Predictive Value (PPV)?
12.Use the Soldier Performance Spreadsheet to answer question 12.
Rank the outcomes using soldier’s age as the score, with the oldest at the top. A threshold of 24 years represents what point on the ROC Curve?
- .33, .67
- .67, .33
- .5, .5
- .25, .75