Excel file for World With Risk case

User Generated

iviv543

Business Finance

Description

Complete the risk & return and bond calculations (First two tabs on Excel file)


just notified me of an error in the World with Risk Excel file. I've corrected the error and posted a new version on the assignment page. However, if you've like to just correct the formula on the sheet you are already working on...

Cell references in B63-B67 should be to D35-D39. Therefore, in cell B63 just change =F35 to =D35 and drag down to D39. All your formulas will update correctly and any calculations based on that data will adjust automatically.

My apologies for the confusion.

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Here you go! Let me know if you need any edits or have any questions.

Goldenwest Creamery is preparing for Board of Directors meeting. At the meeting m
assets to their marketable securities portfolio. Board will be interested in the past p
whole, thus, your job is integrate and interpret the risk-return relationship by compu
recommendation.

a. Use the data given to calculate annual returns for Goldenwest and the Market Index, and then calculate average retu
period. (Hint: Remember, returns are calculated by subtracting the beginning price from the ending price to get the ca
the dividend to the capital gain or loss, and dividing the result by the beginning price. Assume that dividends are alrea
Also, you cannot calculate the rate of return for 2012 because you do not have 2011 data.)

Year
2017
2016
2015
2014

Goldenwest Creamery
Stock Price
$77.13
$76.45
$71.13
$83.88

Dividend
$4.50
$4.35
$4.13
$3.75

S & P 500 Index
Including Dividends
17,495.97
13,178.55
13,019.97
9,651.05

2013

$88.00

$3.38

8,403.42

2012

$81.63

N/A

7,058.96

Total
Goldenwest
Creamery

$20.11

Goldenwest
Creamery

Total return in dollars =
Total return in dollars =

(Current price - purchase price) +
-4.5

Total return in percentage =
Total return in percentage =
Total return in percentage =

Capital gains yield +
(Current price - purchase price)/purchase price +
-5.513%

Now calculate the rates of return for the company, the index, and each of the investment options:

Goldenwest Annual Return
2017
6.78%
2016
13.59%
2015
-10.28%
2014
-0.42%
2013
11.94%
Geometric mean
return

Goldenwest

1+ GW Return
106.8%
113.6%
89.7%
99.6%
111.9%

Index Annual Return
32.76%
1.22%
34.91%
14.85%
19.05%

103.9%

Index

Note: To get the geometric mean return, utilize the GEOMEAN formula, select the values in the 1+ Goodman Return, then close
1". Repeat process for Index.

Note: To get the geometric mean return, utilize the GEOMEAN formula, select the values in the 1+ Goodman Return, then close
1". Repeat process for Index.

b. Calculate the standard deviation of the returns for Goldewest, the Market Index, and all options. (Hint: Use the sam
formula, which corresponds to the STDEV function in Excel.)
S
Goldenwest
9.8%

Standard deviation of returns
How would you compare the results?

c. Construct a scatter diagram graph that shows Goldewest's returns on the vertical axis and the Market Index’s return
Construct three more scatter diagram graphs that show each of the three option's returns on the vertical axis and the M
the horizontal axis.
It is easiest to make scatter diagrams with a data set that has the X-axis variable in the left column, so we reformat the
returns data calculated above and show it just below.
Year

Index
32.8%
1.2%
34.9%
14.8%
19.0%

2017
2016
2015
2014
2013

Goldenwest
6.8%
13.6%
-10.3%
-0.4%
11.9%

Russell 2000
10.9%
12.3%
-7.2%
15.6%
18.0%

Returns
40.0%
35.0%
30.0%
25.0%
20.0%
15.0%
10.0%
5.0%
0.0%
-5.0%
-10.0%
-15.0%

2017

Index

2016

Goldenwest

2015

Russell 2000

2014

Schwab Dividend

2013

Pimco Bonds

To make the graph, we first selected the range with the returns and the column heads, then clicked the chart wizard, then choos
connected lines. That gave us the data poin...


Anonymous
Great! 10/10 would recommend using Studypool to help you study.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags