Question
Quarterly data for each asset class is contained in the Data spreadsheet of the Excel file Project Data. Details of the current asset allocation of
Quarterly data for each asset class is contained in the Data spreadsheet of the Excel file Project Data. Details of the current asset allocation of the fund including the current asset class assumptions are contained in the Old Asset Allocation spreadsheet of the Excel file Project Data. The spreadsheet shows how the current asset allocation was formed and is an example of how to use Solver. However, the current asset allocation of the fund is problematic and not consistent with the funds investment objectives and constraints. You should aim to improve the asset class assumptions, asset class selection and asset allocation of the fund while exploring alternative investment opportunities.
DATA here:
Cash (%) | Bond Index | Equity Index | Direct Property Index | Listed Property Index | Hedge Funds Index | Commodity Index | Data Sources: Thompson Reuters Datastream | ||||||
Q4 1995 | 5.08 | 191.187 | 593.26 | 760.31 | 1744.5 | 1980.31 | 2358.78 | Cash: US TREASURY BILL RATE -3 MONTH | |||||
Q1 1996 | 5.15 | 197.756 | 656.37 | 845.87 | 1887.12 | 2103.45 | 2649.02 | Bond Index: US BENCHMARK 10 YEAR DS GOVT. TOTAL RETURN INDEX | |||||
Q2 1996 | 5.16 | 184.576 | 645.44 | 836.37 | 1908.42 | 2176.34 | 3148.91 | Equity Index: S&P 500 COMPOSITE -TOTAL RETURN INDEX | |||||
Q3 1996 | 5.04 | 192.323 | 662.1 | 863.01 | 1998.22 | 2176.77 | 3131.09 | Direct Property: US NCREIF PROPERTY INDEX | |||||
Q4 1996 | 5.15 | 198.102 | 730.82 | 957.91 | 2175.76 | 2273.28 | 3289.91 | Listed Property: FTSE/NAREIT ALL EQUITY REITS USD-TOTAL RETURN INDEX | |||||
Q1 1997 | 5.34 | 199.494 | 789.56 | 1039.91 | 2539.38 | 2425.45 | 3341.45 | Hedge Fund Index: HFRI FUND OF FUNDS COMPOSITE | |||||
Q2 1997 | 5.17 | 198.534 | 824.78 | 1091.49 | 2459.21 | 2455.02 | 3418.42 | Commodity Index: S&P GSCI Commodity Total Return | |||||
Q3 1997 | 5.11 | 205.823 | 933.54 | 1240.74 | 2736.55 | 2681.09 | 3411.34 | Sample Period: Quarter 4 1995 to Q2 2018 | |||||
Q4 1997 | 5.35 | 216.069 | 927.51 | 1238.2 | 2888.03 | 2707.25 | 3554.71 | ||||||
Q1 1998 | 5.16 | 222.935 | 1010.74 | 1354.75 | 3004.15 | 2695.59 | 2943 | ||||||
Q2 1998 | 5.1 | 223.925 | 1108.14 | 1490.48 | 2908.1 | 2882.95 | 2703.16 | ||||||
Q3 1998 | 4.36 | 231.745 | 1089.45 | 1470.88 | 2681.98 | 2834.47 | 2306.79 | ||||||
Q4 1998 | 4.47 | 242.843 | 1130.2 | 1532.29 | 2518.87 | 2505.76 | 2226.68 | ||||||
Q1 1999 | 4.5 | 245.289 | 1216.14 | 1654.24 | 2435.44 | 2618.93 | 1921.42 | ||||||
Q2 1999 | 4.78 | 236.803 | 1345 | 1835.06 | 2637.78 | 2754.48 | 2262.64 | ||||||
Q3 1999 | 4.86 | 228.632 | 1297.8 | 1776.68 | 2466.41 | 2877.35 | 2506.06 | ||||||
Q4 1999 | 5.22 | 233.936 | 1365.28 | 1875.07 | 2363.52 | 2913.6 | 2659.89 | ||||||
Q1 2000 | 5.88 | 225.743 | 1411.7 | 1944.57 | 2407.28 | 3316.34 | 3022.05 | ||||||
Q2 2000 | 5.87 | 229.776 | 1412.14 | 1950.5 | 2661.54 | 3379.29 | 3292.27 | ||||||
Q3 2000 | 6.21 | 243.488 | 1472.87 | 2040.55 | 2900.79 | 3412.16 | 3603.63 | ||||||
Q4 2000 | 5.89 | 246.194 | 1400.14 | 1945.24 | 2763.99 | 3405.29 | 4068.7 | ||||||
Q1 2001 | 4.29 | 261.997 | 1314.76 | 1831.96 | 2970.85 | 3464.72 | 4151.438 | ||||||
Q2 2001 | 3.62 | 262.118 | 1255.54 | 1754.72 | 3067.48 | 3447.57 | 3868.75 | ||||||
Q3 2001 | 2.37 | 266.796 | 1183.43 | 1659.76 | 3366.11 | 3461.57 | 3590.94 | ||||||
Q4 2001 | 1.73 | 284.621 | 1120.31 | 1576.61 | 3222.67 | 3444.75 | 3059.61 | ||||||
Q1 2002 | 1.79 | 274.818 | 1096.22 | 1547.94 | 3425.4 | 3510 | 2791.91 | ||||||
Q2 2002 | 1.67 | 271.667 | 1073.01 | 1520.39 | 3696.96 | 3550.95 | 3260.22 | ||||||
Q3 2002 | 1.6 | 295.238 | 908.64 | 1292.94 | 3533.04 | 3488 | 3226.84 | ||||||
Q4 2002 | 1.19 | 308.47 | 894.74 | 1278.94 | 3440.4 | 3476.94 | 3287.8 | ||||||
Q1 2003 | 1.09 | 309.273 | 829.69 | 1191.46 | 3422.5 | 3559.61 | 4267.75 | ||||||
Q2 2003 | 0.84 | 318.667 | 933.41 | 1346.68 | 3898.29 | 3614.02 | 3859.72 | ||||||
Q3 2003 | 0.93 | 308.025 | 977.59 | 1416.3 | 4211.78 | 3722.23 | 4212.648 | ||||||
Q4 2003 | 0.91 | 307.127 | 1053.21 | 1533.02 | 4621.92 | 3855.71 | 4235.887 | ||||||
Q1 2004 | 0.93 | 320.45 | 1139.81 | 1666.16 | 5159.91 | 4002.11 | 4493.52 | ||||||
Q2 2004 | 1.3 | 306.623 | 1098.7 | 1612.31 | 4550.35 | 4028.54 | 5347.078 | ||||||
Q3 2004 | 1.68 | 323.385 | 1065.22 | 1569.96 | 5203.6 | 3980.02 | 5489.309 | ||||||
Q4 2004 | 2.18 | 327.377 | 1164.08 | 1724.08 | 5905.45 | 4046.15 | 5743.477 | ||||||
Q1 2005 | 2.73 | 336.979 | 1191.99 | 1773.28 | 6108.6 | 4209.78 | 5400.719 | ||||||
Q2 2005 | 3.06 | 332.392 | 1178.84 | 1760.87 | 6406.99 | 4183.48 | 5907.699 | ||||||
Q3 2005 | 3.47 | 332.095 | 1231.38 | 1847.76 | 6779.67 | 4324.3 | 6733.156 | ||||||
Q4 2005 | 3.99 | 328.529 | 1220.65 | 1841 | 6858.68 | 4364.01 | 6602.09 | ||||||
Q1 2006 | 4.52 | 333.447 | 1263.77 | 1915.1 | 7583.7 | 4655.51 | 6415.5 | ||||||
Q2 2006 | 4.87 | 322.109 | 1325.14 | 2016.59 | 8028.05 | 4835.23 | 6980.547 | ||||||
Q3 2006 | 4.77 | 330.417 | 1265.95 | 1936.3 | 8310.72 | 4704.66 | 7199.961 | ||||||
Q4 2006 | 4.89 | 341.792 | 1378.33 | 2118.32 | 9082.6 | 4819.65 | 5963.91 | ||||||
Q1 2007 | 4.9 | 340.949 | 1438.06 | 2220.72 | 10795.29 | 5060.55 | 5594.926 | ||||||
Q2 2007 | 4.68 | 347.437 | 1512.58 | 2346.3 | 10061.13 | 5240.69 | 5723.328 | ||||||
Q3 2007 | 3.72 | 347.521 | 1453.09 | 2264.5 | 8925.55 | 5405.05 | 5994.086 | ||||||
Q4 2007 | 3.29 | 367.101 | 1453.7 | 2276.26 | 8651.67 | 5567.04 | 7412.961 | ||||||
Q1 2008 | 1.36 | 387.887 | 1331.29 | 2095.21 | 7811.23 | 5348.36 | 7596.469 | ||||||
Q2 2008 | 1.87 | 387.473 | 1388.28 | 2196.54 | 8697.23 | 5327.93 | 9615.148 | ||||||
Q3 2008 | 0.9 | 385.763 | 1296.32 | 2061.77 | 8482.37 | 5231.76 | 8575.328 | ||||||
Q4 2008 | 0.11 | 395.396 | 930.99 | 1490.31 | 5277.29 | 4515.68 | 5041.992 | ||||||
Q1 2009 | 0.21 | 427.161 | 869.89 | 1403.19 | 4430.03 | 4361.81 | 3608.409 | ||||||
Q2 2009 | 0.19 | 421.917 | 929.23 | 1509.14 | 4777.42 | 4392.59 | 3956.891 | ||||||
Q3 2009 | 0.14 | 405.657 | 1010.48 | 1650.73 | 5757.26 | 4625.84 | 4362.672 | ||||||
Q4 2009 | 0.06 | 421.641 | 1093.08 | 1795.56 | 5984.48 | 4753.44 | 4518.996 | ||||||
Q1 2010 | 0.16 | 419.622 | 1070.52 | 1767.18 | 6004.11 | 4809.86 | 4189.77 | ||||||
Q2 2010 | 0.18 | 430.79 | 1110.88 | 1842.32 | 7203.58 | 4939.95 | 4211.234 | ||||||
Q3 2010 | 0.16 | 456.537 | 1127.79 | 1880.06 | 7740.57 | 4805.86 | 4337.578 | ||||||
Q4 2010 | 0.12 | 469.287 | 1213.4 | 2033.85 | 8159.52 | 4997.8 | 4737.824 | ||||||
Q1 2011 | 0.09 | 436.585 | 1320.88 | 2224.64 | 8814.87 | 5110.43 | 5040.035 | ||||||
Q2 2011 | 0.03 | 458.718 | 1346.29 | 2277.3 | 9266.88 | 5211.03 | 5298.164 | ||||||
Q3 2011 | 0.02 | 501.645 | 1172.53 | 1993.76 | 8188.38 | 5107.34 | 4652.289 | ||||||
Q4 2011 | 0.02 | 516.321 | 1229.1 | 2102.72 | 8581.93 | 4885.66 | 4987.352 | ||||||
Q1 2012 | 0.07 | 517.699 | 1351.95 | 2325.56 | 9742.29 | 4897.14 | 5130.848 | ||||||
Q2 2012 | 0.09 | 530.364 | 1354.58 | 2342.31 | 10234.74 | 4960.26 | 4869.543 | ||||||
Q3 2012 | 0.1 | 543.09 | 1402.8 | 2440.23 | 10421.72 | 4896.05 | 5023.426 | ||||||
Q4 2012 | 0.05 | 551.055 | 1379.85 | 2413.92 | 10342.23 | 4962.68 | 4826.152 | ||||||
Q1 2013 | 0.07 | 536.222 | 1517.93 | 2670.85 | 11322.17 | 5148.84 | 5116.172 | ||||||
Q2 2013 | 0.04 | 549.102 | 1626.67 | 2878.3 | 12519.68 | 5251.81 | 4760.477 | ||||||
Q3 2013 | 0.02 | 516.559 | 1691.42 | 3008.79 | 11453.1 | 5264.17 | 4836.328 | ||||||
Q4 2013 | 0.07 | 512.425 | 1770.61 | 3166.11 | 11235.33 | 5365.14 | 4671.02 | ||||||
Q1 2014 | 0.05 | 519.767 | 1797.02 | 3229.64 | 11574.87 | 5471.47 | 4849.766 | ||||||
Q2 2014 | 0.04 | 528.036 | 1878.48 | 3394.05 | 12701.09 | 5489.25 | 4977.266 | ||||||
Q3 2014 | 0.02 | 540.95 | 1931.59 | 3507.21 | 12997.91 | 5589.22 | 4800.84 | ||||||
Q4 2014 | 0.04 | 549.332 | 2031.92 | 3708.46 | 13762.06 | 5589.59 | 4156.324 | ||||||
Q1 2015 | 0.03 | 569.51 | 2046.74 | 3754.36 | 14827.95 | 5685.45 | 3181.166 | ||||||
Q2 2015 | 0.01 | 563.888 | 2116.1 | 3901.01 | 14283.73 | 5834.28 | 3278.621 | ||||||
Q3 2015 | -0.01 | 565.498 | 2077.57 | 3849.96 | 14140.22 | 5843.03 | 2674.265 | ||||||
Q4 2015 | 0.16 | 559.291 | 2078.58 | 3873.37 | 14005.03 | 5669.87 | 2504.545 | ||||||
Q1 2016 | 0.21 | 592.966 | 1852.21 | 3470.5 | 13171.14 | 5512.48 | 1888.292 | ||||||
Q2 2016 | 0.26 | 596.232 | 2058.69 | 3878.14 | 16012.17 | 5514.85 | 2211.317 | ||||||
Q3 2016 | 0.28 | 608.177 | 2181.74 | 4132.96 | 17039.12 | 5599.8 | 2183.406 | ||||||
Q4 2016 | 0.5 | 582.906 | 2163.26 | 4121.03 | 15102.24 | 5627.27 | 2197.235 | ||||||
Q1 2017 | 0.75 | 571.375 | 2307.87 | 4419.4 | 16111.38 | 5749.65 | 2398.728 | ||||||
Q2 2017 | 1.01 | 572.438 | 2396.92 | 4611.21 | 16150.64 | 5857.17 | 2161.223 | ||||||
Q3 2017 | 1.04 | 584.747 | 2474.02 | 4784.94 | 16722.99 | 5934.52 | 2258.003 | ||||||
Q4 2017 | 1.37 | 582.88 | 2584.62 | 5023.01 | 17483.36 | 6079.5 | 2484.844 | ||||||
Q1 2018 | 1.7 | 562.182 | 2619.55 | 5116.99 | 15634.08 | 6277.5 | 2474.593 | ||||||
Q2 2018 | 1.89 | 556.72 | 2697.79 | 5292.9 | 16590.99 | 6163.44 | 2803.959 |
1. Investment objectives and constraints (5%)
Marks will be awarded based on how well investment objectives and constraints are embedded in analysis, discussion, arguments and viewpoints.
2. Justifiable asset class assumptions (7%)
Marks will be awarded ba sed on whether the report provide s all the required assumptions for each asset class and how well assumptions are justified by analysis of historical data and other relevant factors and /or other supporting evidence.
3. Justifiable asset class selection (7%)
Marks will be awarded based on : first, whether the report provides a justif iable asset class selection; second, how well the asset class selection is justified by analysis of historical data and other relevant factors and /or other supporting evidence ; thir d, how well the report discuss and describe the due diligence required for the selected alternative investment asset classes.
4. Justifiable asset allocation (7%)
Marks will be awarded based on whether the report provides a justifiable asset allocation and how well the asset allocation is justified by analysis of historical data and other relevant factors and /or other supporting evidence.
5. Communication and writing (4%)
The current asset allocation for the endowment fund of the Fantasy University is as follows: Asset Classes Weight The endowment fund is a perpetual fund that provides scholarships, research grants and other research support to the university. The current asset allocation is problematic. You are the newly employed portfolio manager of this fund. Recently, the university has decided to review the asset allocation of the endowment fund to see if alternative investment opportunities can be explored and non-traditional assets can be included into the portfolio Investment Objectives and Constraints of the Fund: Cash Equity 90% 69% 22% 1. Return target * As high as possible while all the other investment objectives and constraints are met. Return should not be lower than 8% per annum 2. Risk target Standard deviation of the returns should not be higher than 12% per annum * The fund should not have large exposure to extreme losses 3. Liquidity target At least 70% of the funds should be invested in liquid assets . The fund should hold at least 5% ofcash 4. Other constraints: . At least 50% of the funds should be invested in traditional assets, ie. bonds. equity and cash Short-selling is not permitted but the fund can invest in investment vehicles that short sell, for example, hedge funds * The potential alternative investment asset classes that the fund can invest are listed below: The current asset allocation for the endowment fund of the Fantasy University is as follows: Asset Classes Weight The endowment fund is a perpetual fund that provides scholarships, research grants and other research support to the university. The current asset allocation is problematic. You are the newly employed portfolio manager of this fund. Recently, the university has decided to review the asset allocation of the endowment fund to see if alternative investment opportunities can be explored and non-traditional assets can be included into the portfolio Investment Objectives and Constraints of the Fund: Cash Equity 90% 69% 22% 1. Return target * As high as possible while all the other investment objectives and constraints are met. Return should not be lower than 8% per annum 2. Risk target Standard deviation of the returns should not be higher than 12% per annum * The fund should not have large exposure to extreme losses 3. Liquidity target At least 70% of the funds should be invested in liquid assets . The fund should hold at least 5% ofcash 4. Other constraints: . At least 50% of the funds should be invested in traditional assets, ie. bonds. equity and cash Short-selling is not permitted but the fund can invest in investment vehicles that short sell, for example, hedge funds * The potential alternative investment asset classes that the fund can invest are listed belowStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started