Excel Assignment # 2
.pdf
keyboard_arrow_up
School
Washington State University *
*We aren’t endorsed by this school
Course
309
Subject
Mathematics
Date
Jan 9, 2024
Type
Pages
3
Uploaded by ConstableJellyfish18213
SPMGT 374
1
EXCEL ASSIGNMENT # 2
Risk & Return Excel (5 points)
Due by Monday, October 9
th
, 11:59 pm (PT)
Overview
The purpose of this assignment is for students to apply the principles of risk and return and use
excel software to analyze investments in the sport industry.
Learning Objectives
By the end of this assignment, you will be able to:
•
Perform basic excel formulas and functions.
•
Calculate the expected rate of return, standard deviation, and coefficient of variation for stand-
alone investments where the data is provided.
•
Calculate the expected rate of return and beta of a portfolio investment where the data is
provided.
•
Interpret the above calculations to choose the best investment.
•
Analyze and make recommendations about potential investments and critically analyze the
recommendations of others.
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.
Grading and Evaluation
You will receive full grades so long as you make a genuine attempt to answer the questions.
Points will not be automatically deducted when some of your responses are incorrect (so long as
you make a genuine attempt). Think of this as a chance to practice your new knowledge... and
have fun!
After you submit your Excel spreadsheet, you will receive the correct answers in an
announcement on Canvas. Make sure to compare your spreadsheet with the answer Excel
spreadsheet you receive to check your understanding of the material.
Assignment steps
1.
Calculate the expected rate of return for Franchise A
a.
First calculate the probable returns for each projected rate of return.
i.
In C4 write [=A4*B4].
ii.
Drag the equation down to apply from C4 to C8
b.
Next calculate the expected rate of return for the investment.
i.
In C9 write [=SUM(C4:C8)]
2.
Calculate the standard deviation for Franchise A
SPMGT 374
2
a.
Start by calculating the deviations from the expected ROR for each projected
ROR.
i.
In D4 write [=A4-$C$9]
ii.
Drag the equation down to D8
iii.
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.
b.
Next, square the deviations
i.
In E4 write [=D4^2]
ii.
Drag the equation down to E8
iii.
The ^ s
ign means “to the power of”
c.
Next calculate the variance
i.
In F4 write [=E4*B4]
ii.
Drag the equation down to F8
d.
Last, calculate the Standard deviation
i.
In F9 write [=SQRT(SUM(F4:F8))]
ii.
This will sum the variance values and then square root the answer. Be
careful with your parentheses. Make sure you know and use the order of
operations.
3.
Calculate the Coefficient of Variation for Franchise A
a.
In F10 write [=F9/C9]
4.
Copy all the work you have done so far to the columns to the right of the table
a.
What happens when you increase the projected return of the first value in cell
(A4)?
b.
What happens if you make all the probabilities the same?
5.
Calculate the expected rate of return for Franchise B
a.
Repeat all the steps used to answer Question 1
b.
Based on this information alone would you invest in Franchise A or B?
6.
Calculate the standard deviation for Franchise B
a.
Repeat all the steps used to answer Question 2
b.
Based on this information alone would you invest in Franchise A or B?
7.
Calculate the Coefficient of Variation for Franchise B
a.
Repeat all the steps to answer question 3
b.
Which Franchise would you invest in?
8.
Calculate the Expected rate of return for Portfolio A
a.
Start by calculating the Weight
i.
In E22 write “Weight”
ii.
In E23 write [=B23/$B$28]
iii.
Drag the equation down to E27
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help