Finance Excel Statistics and Probability
Everything you need is attached. On the excel sheet named Final, I have already finished all the tabs on the bottom so please don’t change the numbers. The only thing I need completed is the tab called Answers. The questions are all given in the answers tab. I also added another excel that has equations that you will need to use. More instructions below: Part 4 of 4 (11 points total): Look at the Answer tab on the Project Template. The answers to these questions must go there –and the may be typed in (that is, the formulas used to find the answer do not need to be here). 1. What is the probability of the portfolio with the higher mean outperforming the other portfolio? 2. What is the probability portfolio 1 outperforming the S&P? 3. What is the probability portfolio 2 outperforming the S&P? Probability questions are answered using the Difference between Two Means (paired) tab in the Statistics Template. (3 points total) 4. Considering portfolio 1, is the risk measured by Beta less than the S&P, greater than the S&P, or essentially the same? 5. Considering portfolio 2, is the risk measured by Beta less than the S&P, greater than the S&P, or essentially the same? First – use the Normal Distribution tab in the Statistics Template. The regression results will show you the SE of Beta. Plug in the value for Beta as the mean, the SE in the “SD or SE†cell, and 1 as the critical value. We will use 80% as our (arbitrary) cutoff. If the Prob > CV is 80% or more, we say Beta is significantly greater than 1, and therefore the portfolio is riskier than the S&P. If the Prob < CV is 80% or more, we say Beta is significantly less than 1, and therefore the portfolio is less risky than the S&P. If neither are true, we say the portfolio has the same risk as the S&P. (2 points total) 6. Considering portfolio 1, is the excess return measured by Alpha less than the 0, greater than 0, or essentially 0? 7. Considering portfolio 2, is the excess return measured by Alpha less than the 0, greater than 0, or essentially 0? Again – use the Normal Distribution tab in the Statistics Template. The regression results will show you the SE of Alpha. Plug in the value for Alpha as the mean, the SE in the “SD or SE†cell, and 0 as the critical value. We will use 80% as our (arbitrary) cutoff. If the Prob > CV is 80% or more, we say Alpha is significantly greater than 0, and therefore has an excess return not related to the S&P. If the Prob < CV is 80% or more, we say Alpha is significantly less than 0, and therefore has a loss not related to the S&P. (NOTE – this is very unlikely to happen). If neither is true, we say the portfolio does not have a return or loss unrelated to the S&P. (2 points total) 8. What do you conclude about the diversification of portfolios 1 and 2 based on Correlations and SD? We want more than half the correlations to be between -0.3 and +0.3 in order to say there are diversification benefits due to correlations. And we want the SD of the portfolio to be less than 4 or more stocks’ SD for the seven stock portfolio and less than 5 or more stocks’ SD for the eight stock portfolio to say there are diversification benefits due to SD. (2 points) 9. Considering everything above, which portfolio would you choose? (Only 1 or 2 – you can’t choose the S&P.) List ONE reason based on the above The types of things you’d want to consider are risk, return, diversification, comparison to the S&P etc. No particular correct answers here, just make sure they are based only on the statistics you see, and your interpretation of those statistics makes sense. (1 point) 10. Which stock is the weakest in that portfolio (based on the Sharpe Ratio) – and would you say it is sector based or stock based? If the sector of that stock is in the last five of the eleven sectors for the P/E then it is likely sector based. If not – then assume its stock based. (DON’T GUESS – You’ll have to use the pivot table create in Part 3. Without that, you’ll receive no points for this question.) (1 point)
1 What is the probability of the portfolio with the higher mean outperforming the other portfolio?
2 What is the probability portfolio 1 outperforming the S&P?
3 What is the probability portfolio 2 outperforming the S&P?
4 Considering portfolio 1, is the risk measured by Beta less than the S&P, greater than the S&P, or essentially the same?
(See the criteria for this question in the Project Question page before answering)
Prob Beta > 1 Prob Beta < 1 Conclusion (type either less risky, more risky, same risk)
5 Considering portfolio 2, is the risk measured by Beta less than the S&P, greater than the S&P, or essentially the same?
(See the criteria for this question in the Project Question page before answering)
Prob Beta > 1 Prob Beta < 1 Conclusion (type either less risky, more risky, same risk)
6 Considering portfolio 1, is there an excess return or loss (measured by Alpha) not related to the S&P?
(See the criteria for this question in the Project Question page before answering)
Prob Alpha > 0 Prob Alpha < 0 Conclusion (type either excess return, excess loss, or none)
7 Considering portfolio 2, is there an excess return or loss (measured by Alpha) not related to the S&P?
(See the criteria for this question in the Project Question page before answering)
Prob Alpha > 0 Prob Alpha < 0 Conclusion (type either excess return, excess loss, or none)
8 What do you conclude about the diversification of portfolios 1 and 2 based on Correlations and SD?
Correlations Std Deviation (type either Yes if diversified and No if not)
(for Port1)
Correlations Std Deviation (type either Yes if diversified and No if not)
(for Port 2)
9 Considering everything above, which portfolio would you choose?
(type either 1 or 2; you can’t pick the S&P)
List one reason based on the above
10 Which stock is the weakest in that portfolio (based on the Sharpe Ratio) – and would you say it is sector based or stock based?
(type the ticker only)
(type sector or stock; remember – you can’t guess. You MUST have the pivot table to back this up)
Mean 0.003015157 REMEMBER –
SD or SE 0.000921252 If you are pasting data in columns A or B that come from a FORMULA,
Critical Value 0.00% you will need to use “PASTE SPECIAL – VALUES”
Prob < CV 0.05%
Prob > CV 99.95% Yellow cells are calculated and cannot be changed
SIDE CALCULATION
SD 1.75%
N 171
SE 0.13%
NOTES:
SD when you are looking at DATA, SE when are looking at a sample STATISTIC
The critical value is the point for which you are measuring the probability
The probability of being EXACTLY the critical value is essentially 0
Example 1: Example 2:
Mean return 0.57% Mean return 0.57%
SD 2.06% SD 2.06%
CV 0 N 171
Prob < CV 39.10% SE 0.16%
Prob > CV 60.90% CV 0
Prob < CV 0.01%
That is, the probability of seeing a return less than 0 is 39% Prob > CV 99.99%
That is, the probability of averaging a return < 0 over time is almost 0
