Instructions

You can complete this assignment on Excel by following the steps outlined in detail below. I have used the symbols [ ] to designate what you should enter into Excel. For example if you see [=E3*2] I want you to type in the equals sign followed by E3, *, and finishing with 2. Typically you will press enter when you are done filling in the information.

Calculate the expected rate of return for Franchise A

1. First calculate the probable returns for each projected rate of return.
   1. In C4 write [=A4*B4].
   2. Drag the equation down to apply from C4 to C8
2. Next calculate the expected rate of return for the investment.
   1. In C9 write [=SUM(C4:C8)]

1B) Calculate the standard deviation for Franchise A

3. Start by calculating the deviations from the expected ROR for each projected ROR.
   1. In D4 write [=A4-$C$9]
   2. Drag the equation down to D8
   3. The $ signs tell excel to retain the same cell (C9) in each of the equations even while the other cell in the equation changes. In other words the $ signs anchor the cell.
4. Next, square the deviations
   1. In E4 write [=D4^2]
   2. Drag the equation down to E8
   3. The ^ sign means to the power of
5. Next calculate the variance
   1. In F4 write [=E4*B4]
   2. Drag the equation down to F8
6. Last, calculate the Standard deviation
   1. In F9 write [=SQRT(SUM(F4:F8))]
   2. This will sum the variance values and then squareroot the answer. Be careful with your parentheses. Make sure you know and use the order of operations.

1C) Calculate the Coefficient of Variation for Franchise A

7. In F10 write [=F9/C9]

1D) Copy all the work you have done so far to the columns to the right of the table

8. What happens when you increase the projected return of the first value in cell (A4)?
9. What happens if you make all the probabilities the same?

1E) Calculate the expected rate of return for Franchise B

10. Repeat all the steps used to answer Question 1
11. Based on this information alone would you invest in Franchise A or B?

1F) Calculate the standard deviation for Franchise B

12. Repeat all the steps used to answer Question 2
    13. Based on this information alone would you invest in Franchise A or B?

1G) Calculate the Coefficient of Variation for Franchise B

1. 14. Repeat all the steps to answer question 3
   15. Which Franchise would you invest in?

1H) Calculate the Expected rate of return for Portfolio A

1. 16. Start by calculating the Weight
       1. In E22 write Weight
       2. In E23 write [=B23/$B$28]
       3. Drag the equation down to E27
       4. This equation will determine how much of the total portfolio investment is held in each individual stock
   17. Next calculate the weighted returns for each stock.
       1. In F22 write Weight x Return
       2. In F23 write [=C23*E23]
       3. Drag the equation down to F27
   18. Last calculate the expected rate of return
       1. In F28 write [=SUM(F23:F27)]

1I) Imagine you are thinking of investing $300M in Stock F with a rate of return of 9.5. Should you do it?

1. 19. Copy and paste your working to the cells below.
   20. Add Stock F
       1. Right click on the row number of directly below Stock E on your pasted table
       2. Select insert
       3. Write 300 in the investment column and 9.5 for the rate of return column.
   21. Check your equations are linked to the correct cells. Copy pasting and adding rows may have moved your equations.

1. 5. Did the expected rate of return increase or decrease when adding the stock?

1J) Calculate the Portfolio Beta for Portfolio A

1. 23. Calculate the weighted Beta of each stock.
       1. In G22 write Beta x Weight
       2. In G23 write [=E23*D23]
       3. Drag the equation down to E27

1. 10. Calculate the Portfolio Beta
       1. In G28 write [=SUM(G23:G27}]

1K) and one extra tricky one: Imagine you could sell stock C and purchase another stock for $220M, what beta would the replacement asset need to lower the portfolio Beta to exactly 1? (You do not need to answer this question to get full marks for the assignment).