ECON University of California Santa Distribution of Payouts of Portfolio X Worksheet
Description
2 attachmentsSlide 1 of 2attachment_1attachment_1attachment_2attachment_2.slider-slide > img { width: 100%; display: block; }
.slider-slide > img:focus { margin: auto; }
Unformatted Attachment Preview
.5(X+Y)
The column C is 94670
portfolio X 99610
The column D is98364
portfolio Y.91436
106908
99010
106256
101824
103480
98554
101652
108302
102854
106054
107168
104102
102114
105378
102224
90128
90220
108256
96504
106836
106912
97998
107516
99986
100142
109270
104676
97706
107328
106084
95414
104622
102734
95748
108418
109310
101224
96186
97732
95836
104228
97588
93148
102444
90670
102790
91050
89352
92084
106916
90508
93722
98490
109756
98158
94426
106558
94564
100530
94808
100620
97684
100714
99746
95016
103716
105184
100016
109714
94022
102770
94752
97512
107672
109662
101708
99496
96508
100982
97770
102830
103614
107554
105946
96114
103110
92294
100076
106738
94280
90926
96126
106522
92920
93872
95954
107980
109002
104578
106682
102618
97308
100372
96084
97622
92662
109056
101232
107852
108620
105224
91498
104692
109076
107174
107050
99142
98902
99666
102028
98396
96552
101170
107274
96418
95586
96758
107610
104934
106580
101502
100140
99572
99060
97638
100612
101390
108594
92668
93170
102010
98270
91240
101238
105708
108482
100670
91228
105716
90688
104434
102936
99094
107534
104728
103032
90172
90132
93982
98820
104048
92926
113076
98290
103944
105172
110052
90280
96432
108754
108866
102580
111642
95652
102562
103228
92156
95478
98550
109890
92134
106396
92680
99094
108232
103238
101432
102822
100648
94776
97608
99340
107954
108812
106014
92726
94276
90590
94538
94258
105354
96486
102090
104822
101194
105160
108134
92446
102102
109446
99490
96680
109118
109210
105240
106032
90904
106452
105232
92790
92486
109294
99172
108452
100492
101858
95100
106206
100936
92334
101522
100416
93514
98334
96190
93868
105964
111644
107950
96900
109338
96762
92376
98754
95872
103182
102072
110426
97510
99492
106738
107326
93226
111888
99176
102348
103956
107442
107674
87906
107824
98376
99334
91434
89204
106620
106396
107350
111472
106482
95552
96894
102872
100094
101854
92750
98182
92974
107416
92662
97452
95904
96112
93010
96694
93896
106644
96066
104154
98666
103786
98618
96130
101750
90218
96086
97220
94472
94522
101670
91134
108726
93350
93856
93164
92562
92502
93492
99372
106576
108852
97384
103610
95638
105330
98094
92576
93274
92510
106896
92492
95790
101546
90494
97234
107222
95510
102922
89944
95238
105434
111346
103508
95068
104450
96342
100126
109556
105776
86784
95850
109138
98966
104534
107052
91946
91446
107040
103528
96980
88988
94268
91032
106624
97996
97148
103004
98154
99910
94876
93942
97800
95812
104250
97938
101482
97732
99796
96722
93928
98290
94006
103882
109036
94528
95688
103320
93538
91560
108536
103152
99064
average
std. dev.
loss possible?
correlation of X and Y
89756
103034
111520
105998
105828
95886
94448
101914
104640
95136
101136
95510
104040
99448
Econ 5, Project 4
Due Date: October 22nd, by 12:00pm (noon) Pacific Time
You can submit your Excel spreadsheet AS MANY TIMES AS YOU WANT. You will see your score
each time. Your LAST SUBMISSIONs score will be your score for the project. Autograder will only
score the numeric parts of the assignment, the graphs will be graded by your TA after the due date so they
cannot be revised.
Overview
In this project, you advise the Dally Dilly Dolly Foundation, a fictional non-profit group, about how to
invest their endowment. The foundation has $96,000 to invest in two different assets, portfolio X and
portfolio Y. You have three investment options. You can invest all of the money in portfolio X, all of the
money in portfolio Y, or half of the money in each portfolio. Your goal is to provide a high rate of return
over the next year while not losing any of the money invested.
In your data provided, each line represents one possible outcome for portfolios X and Y. Both outcomes
on the same line occur simultaneously. For example, if the outcome in cell C3 occurs for portfolio X, then
the outcome in cell D3 occurs for portfolio Y.
There are 200 possible outcomes, each with equal probability. (Note this is a population and not a
sample.) On each line, payouts are given for investing in a single portfolio. The first number is the payout
for investing $96,000 only in portfolio X. The second number corresponds to investing the same amount
only in portfolio Y. These outcomes are all in dollars, and listed in cells C3 to D202. For example, if
someone invests $96,000 and receives a payout of $98,200 in a year, the net gain is $2,200.
In this project, you will be able to see the benefits of diversification. In later material, we can use
correlation as a tool to determine how much diversification occurs when investing in two different assets.
To Receive Credit
General Instructions:
Make sure your capitalization is correct for any non-numeric entries as the autograder is
case sensitive.
Dataset sent to each student is different. Do not share your dataset with others.
Excel Instructions:
The Excel spreadsheet you submit must have a title of the following form: Project4.xlsx.
Make sure Sheet 1 in your Excel file includes all calculations, answers, and graphs. Only
Sheet 1 will be graded.
Make sure Sheet 1 is formatted as specified in the instructions; autograder will only grade
entries that are in the correct cells.
You MUST use Excel, NOT Google Sheets, NOT Numbers. Make sure you download
the file as an Excel spreadsheet. If you accidently open it in Google Sheets download it to
your computer as an Excel file, do NOT cut and paste to an Excel spreadsheet as you
might mess up the format and create errors that make it impossible for Autograder to read
your file.
Graph Instructions:
All graphs must also be submitted as a single PDF with titled Project4.pdf as instructed
below.
All graphs MUST include the following:
o A title that describes the main point.
o A graph number as described in the instructions. The graph number must be at the
beginning of the graph title.
o Meaningful, readable labels for axes and categories.
o A legend when it necessary.
o The graph must be self-explanatory, i.e. someone who is not familiar with the
context and data could understand its main message.
Required Elements (all elements must be in Sheet 1 in your Excel file)
(1) For each possible outcome, calculate the payout if you invest half of the money in each portfolio.
Your average needs to be in column E. For example, the average of cells C3 and D3 needs to be in
cell E3. (Although this element will not be graded directly, these are necessary calculations for other
elements that will be graded.)
(2) In cell C205, calculate the average payout of all portfolio X outcomes. In cell C206, calculate the
standard deviation.
(3) In cell D205, calculate the average payout of all portfolio Y outcomes. In cell D206, calculate the
standard deviation.
(4) In cell E205, calculate the average payout of the even split of money invested in the portfolios X and
Y. In cell E206, calculate the standard deviation.
(5) Determine if each investment strategy could have a negative return. In cell C210 (for portfolio X),
D210 (for portfolio Y), and E210 (for an even split of money invested in each portfolio). If a negative
return is possible, enter the number 1 in the respective cell; otherwise, enter 0. (Note: If it is helpful,
use the MIN function in Excel to find the minimum payout for each column in cells C208, D208, and
E208.)
(6) In cell D215, calculate the correlation of portfolio Xs and Ys rate of return. The function in Excel for
correlation is CORREL.
(7) Graph 1: Create a histogram of the payouts of portfolio X. Format the horizontal axis to have 5 bins.
After you have created a histogram with 5 bins, enlarge the graph if necessary to see the range of each
bin. Include an appropriate axis title for each axis. (Notes: In some versions of Excel, double clicking
the numbers on the horizontal axis will open up the Format Axis feature. Also in some Excel
versions, if you single click in the histogram, a + will appear just outside the upper right corner. If
you click the + sign, you can add axis titles. Make sure to label each axis appropriately.)
Submitting your project
You must include the two files described in the To Receive Credit section above. One
file needs to be submitted as an Excel spreadsheet, while the other a PDF. Sheet 1 must
be formatted as described above.
You must also submit all graphs in PDF form. The easiest way to do this is to copy the
Excel graphs into Word using COPY followed by PASTE IMAGE. (It may also be listed
as Paste Picture.) Once all graphs are in the word doc and nicely formatted and sized, you
can save the document as a PDF. We require that you submit as a PDF rather than a word
document because formatting is sometimes compromised in the transfer for word
documents, but PDFs always transfer exactly as you formatted it. This file must have a
title of the following format Project4.pdf.
All graphs need to be in BOTH on Sheet 1 of the excel file AND separately, in the PDF
file.
These two files must be uploaded to GradeScope before the project deadline.
Your submission must have NO hidden rows or columns.
Grading Rubric for Project 4 (each element must be completely correct to receive credit;
no partial credit given)
Required Elements 2 & 3 are correct
Required Element 4 is correct
Required Element 5 is correct
Required Element 6 is correct
Required Element 7 is correct
1 point
1 point
1 point
1 point
1 point
Purchase answer to see full
attachment
Tags:
standard deviation
economic growth
Income Statement
Global Market
Payout range
User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.
