homework 4

.5(X+Y)The column C is 90682
portfolio X 91058
The column D is98284
portfolio Y.
104446
102288
97830
95934
103664
96808
102580
92294
107746
96758
111346
100446
95760
105956
98812
95208
99796
101194
92412
97244
87906
102366
98966
103676
101246
104270
111520
90856
90172
96538
98538
95362
98562
108580
101408
103454
104158
108948
94022
108462
107534
109662
103034
105492
106738
107146
93722
107002
108256
103102
104566
103148
102510
91250
106916
95586
107222
96870
104472
97452
97478
105960
104434
100570
107666
109342
100766
103320
102348
107382
98550
91592
93286
93794
92876
104650
95850
106522
94746
95994
107248
104168
102872
91134
93330
95016
100426
102772
100268
92562
96392
99208
107934
92662
99428
90596
100002
105060
94162
102618
103238
96106
99490
104416
102822
100600
100092
99808
103172
91506
108536
102526
108332
103610
94776
99372
92662
109948
103882
93350
99500
96760
102224
94258
109358
106452
92222
106696
90336
94276
93894
100362
90014
92486
90846
92080
100316
93714
91240
94518
90224
104106
104376
105946
107058
103956
106836
99326
98988
102518
110982
109338
97770
93982
95338
103534
96454
106084
100132
107740
93942
94808
104442
97998
95596
108754
101390
93014
106396
88830
95548
107442
96810
87576
105446
113076
93730
95478
111060
99010
87988
91998
106132
106896
99394
97732
98182
109294
92576
106248
98082
96204
102068
101208
93938
95190
101722
106644
107980
105354
93672
92220
101750
100076
105480
100140
96486
93118
93586
92920
96112
102114
104244
90218
106912
109446
106908
108618
94744
102280
95638
105232
109596
98094
97352
106576
105868
95100
101552
91736
104534
96508
99252
92868
109310
94564
103944
102608
107052
104814
101914
103032
102492
105378
102284
90500
92546
99334
97102
108194
99546
104292
104694
99746
100384
103588
92262
96894
102010
106274
97684
104450
97844
101482
98682
109428
95562
97706
96762
103508
106382
111472
108756
105104
89352
93148
95636
107852
103100
98732
100498
105206
105906
105160
93538
105184
96114
101432
105852
96926
107650
104232
95954
108388
100884
100440
93274
107650
97496
95804
97692
98026
96610
94538
104186
107516
100620
94026
105768
97150
104404
97308
104154
102354
105740
100148
92492
95484
92682
98724
91840
106682
98496
96288
95652
101986
90208
92926
96980
95112
89274
108456
110836
99986
88716
109138
101232
108944
98462
96196
98334
95356
93144
109270
110052
107846
103628
98376
99826
97638
87968
91148
100426
98512
95664
106620
95784
99094
107672
103614
104520
99610
91032
108456
103614
91434
108406
103486
95100
109118
94786
102028
92750
106396
108620
98158
106132
100172
102992
92292
91498
104658
101624
average
std. dev.
loss possible?
correlation of X and Y
92392
94750
95238
90030
93652
94786
103548
91652
106256
101136
92790
103692
107752
99706
Econ 5, Project 4
Due Date: Feb 10th, by 7:00pm Pacific Time
You can submit your Excel spreadsheet AS MANY TIMES AS YOU WANT. You will see your score
each time. Your LAST SUBMISSION’s 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 X’s and Y’s 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.)
Leaderboard
•
•
•
•
•
•
•
•
When uploading the Excel spreadsheet to GradeScope, you will be prompted to provide a
name for the leaderboard. A leaderboard is a tool that allows you to monitor accuracy and
timing of your submission relative to others in the class.
You can pick your own leaderboard name, please make sure it is appropriate and
respectful, given that the leaderboard is visible to all students in the class. Use the same
leaderboard name for all submissions this quarter.
DO NOT use your real name as your leaderboard name. If you accidentally use your real
name as your leaderboard name, submit again, the original leaderboard entry will be
deleted.
The leaderboard will NOT affect your course grade at all. If you don’t want your
leaderboard name to be uniquely identified, you can type Optout Otter as your
leaderboard name.
DO NOT leave the leaderboard name blank, your submission will not be processed.
DO NOT share your leaderboard name with other students. A student’s leaderboard name
is known only to that particular student and course instructors.
Leaderboard ranks all submissions by the SUBMISSION TIME among the class
members. The earlier the submission, the higher the rank. Note though, the leaderboard
ONLY ranks those who got FULL SCORE on the autograder part of the assignment. For
example, if you and another student both got a full score, and you submitted earlier, your
leaderboard name will appear higher on the leaderboard than the other student’s. Please
notice that your leaderboard name would appear at the bottom of the leaderboard if your
submission scored less than FULL SCORE on the autograder part of the assignment.
After your submission, click on “Leaderboard” on the “Autograder Results page” to
check the ranking.
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