Question

According to contemporary investment theory it is safer to diversify your portfolio by selecting a variety of investments rather than choosing a single investment option. Using the problem solved in class add constraints so that each fund type is used for a minimum of 5% of the total portfolio. (the optimal solution should indicate to total annual return of 17.56%) Complete the same SolverTable by changing beta with the new model that incorporates the new constraints. Describe the differences between the two sets of SolverTable results. What is your conclusion about portfolio diversification as it relates to risk tolerance (changes in the beta)?

this is the question solved in class

Please leave your calculation below

thanks！

Portfolio Homework Problem investments rather than choosing a single investment option. Using the problem solved in class add constraints so that each fund type is used for a minimum of 5% of the total portfolio. (the optimal solution should indicate to total annual return of 17.56%) Complete the same Solver Table by changing beta with the new model that incorporates the new constraints. Describe the differences between the two sets of SolverTable results. What is your conclusion about portfolio diversification as it relates to risk tolerance (changes in the beta)? Portfolio Selection Problem In this problem you will examine the allocations of investor funds among six categories of mutual funds for the bull market of the late 1990s. To maximize total annual return (unadjusted for risk), investors allocate money across the six fund categories whose average annual returns and betas (risk) are given below. As long as the returns are positive an investor should ensure that 100% of available investment funds are actually invested. Investors also typically make investment choices based on risk tolerance. To begin you will assume that your risk tolerance is very high (no risk is too great). Hint: you should set up a non-binding constraint for risk (beta). In the event of another bull market similar to the late 1990's, you will have a guideline for investment decisions if you find the optimal allocation of investment funds for this historical time period by maximizing total annual return. fter determining the optimal investment strategy for a high risk portfolio, use SolverTable to see if the total annual return and investment strategy change for lower levels of risk (vary beta from .43 to 1.13 in increments of 0.05) 1 Fund Type 9 Data Aggressive Growth Equity/I Small Real Growth/Income Growth Company Estate ncome 18.38 18.83 11.02 0.94 1 Annual Return 5.52 18.62 15.15 1.13 2 Beta 1.00 0.77 0.92 0.43 3 % of Investment 5 Constraints 1.25 Risk Tolerance Actual Beta 0.92 Total % invested 100% %Available Total Annual Return 18.83

Answer 1

The Beta(risk) of products range from 0.43 -1.13. An investor should have a risk tolerance of at least 0.43 if he wants to invest in any of these products. The maximum risk due to investment    is 1.13

Option 1- Initial question

We need to

• find the percent of investment in each product
• so that we maximize profit
• subject to constraints

Decision variable

xi= the percent of investment ion each product

i=1,2,3,4,5,6

objective

maximize

Profit= 18.38 X1 + 18.62 X2 +          15.15 X3 + 18.83 X4 + 11.02 X5 + 5.52 X6

Constraints

1.13 X1 +1X2+0.77X3+0.92X4+0.94X5+0.43X6   ≤1.25

X1 +X2+X3+X4+X5+X6 = 1

Xi ≥ 0 (non-negativity )

Cells	Name	Final Value	Shadow Price	RHS Value	Allowable Increase	Allowable Decrease
D10<=F10	Risk tolerance Aggressive growth	0.92	0	1.25	1E+100	0.33

• The shadow price is zero – Non-binding constraint. Binding constraints have shadow price e.g. Total investment Aggressive growth

• Se the allowable increase and decrease

1,25 + ( 1E+100) à (huge number)

1.25- 0.33 =0.92

Between risk tolerance values of 0.92 and   1,25 + ( 1E+100), the shadow price of risk tolerance is 0. The constraint is non-binding ie risk tolerance is not a limiting factor of the profit, within this range of value

D Define Name Trace Precedents Calculate Now 4 Trace Dependents ф Error Checking Use in Formula Watch Calculation i Calculate Sheet Insert AutoSum Recently Financial Logical Text Date & Lookup & Math More Function Name Time Reference & Trig Functions Manager Create from SelectionRemove Arrows Evaluate Formula Window Options Used Calculation Function Library Defined Names Formula Auditing Security Warning Automatic update of links has been disabled Options... D7 smal company Aggressive growth real estate annual return 5.52 18.38 18.62 15.15 18.83 11.02 0.92 0.94 0.43 1.13 0.77 Investment percent Risk tolerance 10 SUMPRODUCT D6:16,D7:17) 1.25 Total investment SUM(D7:17) 12 13 14 annual returns 15 DS:15,D7:l 16

Connections Properties AlEdit Links Sort Filter K Clear mn z Show/Hide Model A 2 From From From Other Existing Refresh Access Web Text Sources Connections All Text to OpenSolver Model Advanced Columns D nies Get External Data Connections Sort & Filter What is AutoModel? AutoModel AutoModel is a feature of OpenSolver that tries to automaticaly determine the problem you are trying to optimise by observing the structure of the spreadsheet. It wil turn its best guess into a Solver model, which you can then edit in this window. Security Warning Automatic update of links has been disabled Options... Objective Celi s0515 maximise me target value: D7 Variable Cells: $7 Aggressive growth Growth equity Growth/income smal company real estate <Add new constraint> 5 annual return 18.38 18.62 15.15 18.83 11.02 5.52 11 = SFS11 beta 1 0.77 0.43 1.13 0.92 0.94 7 Investment percent Cancel Update constraint Delete elected constraint 10 Risk tolerance S1.25 11 Total investment Make unconstr ained variable cels non-negati 12 Show named ranges in constraint ist 13 Sensitrvity Analysis List sensitivity analysis on the same sheet with top left cell 14 Output sens tivity analysis: updating any previous output sheet onanew sheet 15 annual returns 16 Solver Engine: Current Solver Engine: CBC Solver Engine... 17

Connections Clear Properties Filtert. Reapply From From From From Other Eisting Access Web Text Sources Connections ons! Sort Text to Remove Data Consolidate What-f Group Ungroup Subtot Refresh Ga Edit Links Advanced Columns Duplicates Validation- All- Analysis Get External Data Connections Sort & Filter Data Tools Outline Security Warning Automatic update of links has been disabled Options... D7 0 Aggressive growth Growth equity Growth/income smal company real estate 5 annual return 18.62 15.15 18.83 11.02 5.52 18.38 0.77 0.92 0.94 0.43 1.13 7 Investment percent 10 Risk tolerance 0.92 1.25 11 Total investment 12 13 14 15 annual returns 18.83 16 17

Security Warning Automatic update of links has been disabled Options... f 1E+100 G16 1 OpenSolver Sensitivity Report CBC 2 Worksheet: [may 19.xlsx] Sheet19 Sensitivity 3 Report Created: 01-06-2019 09:54:12 5 Decision Variables Final ValueReduced Costs Objective Value Allowable Increase Allowable Decrease Cells Name Investment percent Aggressive growth 1E+100 D7 0 18.38 0.4500001 Investment percent Growth 18.62 0.2100001 1E+100 1E+100 Investment percent equity 0 15.15 3.6800001 G7 Investment percent Growth/income 10 18.83 1E+100 0.2100001 Investment percent smal company 1E+100 H7 0 11.02 7.8100001 Investment percent real estate 1E+100 12 5.52 13.3100001 13 14 Constraints Final Value Shadow Price RHS Value Allowable Increase Allowable Decrease 15 Cells Name D10 F10Risk tolerance Aggressive growth 1E+100 16 0.92 1.25 0.33 17 D11-F11Total investment Aggressive growth 18.83 0.358695652 D7-0 Investment percent Aggressive growth 1E+100 0.45 Investment percent Growth 1E+100 19 0 0.21 Investment percent equity 3.68 2.2 G7-0 Investment percent Growth/income 1E+100 21 H73-0 Investment percent smal company 1E+100 7.81 Investment percent real estate 13.31 0.673469388 23 173-0 24

Option 2- After adding an additional constraint of

                                                                         Xi   ≥ 0.05

Cells	Name	Final Value	Shadow Price	RHS Value	Allowable Increase	Allowable Decrease
D10<=F10	Risk tolerance Aggressive growth	0.9035	0	1.25	1E+100	0.3465

• The shadow price is zero – Non-binding constraint.

• Se the allowable increase and decrease:

0.9035 + ( 1E+100) à (huge number)

0.9035 - 0.3465=0.557

Between risk tolerance values of 0.557 and   0.9035 + (1E+100) the shadow price of risk tolerance is 0. The constraint is non-binding ie risk tolerance is not a limiting factor of the profit, within this range of value

Home InsertPage Layout Formulas DataRiew e Developer 祐一ù 個国connections ↓BR rk, clearply | Filter-,Advanced OpenSolver - Model Properties 2↓ Sort Sort Filter From From From From OtherExisting Refresh Access Web Text Sources Connections All what is AutoModel? Get External Data Sort & Filter AutoModel is a feature of OpenSolver that tries to automatcaly determine the problem you are trying to optimise by observing the structure of the spreadsheet. It wl turn its best guess into a Solver model, which you can then edit in this window objective Cell:$15 Security Warning Automatic update of links has been disabled Options... maximiseminmise target value: M10 Variable Cells: sD$7:s57 Aggressive growth Growth equity Growth/income smal company real estate $11-SFS11 5 annual return 18.38 18.62 15.15 18.83 11.02 5.52 1.13 1 0.77 092 0.94 0.43 7 Investment percent Add constraint Cancel Delete selected constraint 10 Risk tolerance S1.25 Make unconstrained variable cels non-negative 11 Total investment Show named ranges in constraint list 12 Sensitivity AnalysisList senstvity analysis on the same sheet with top left cell: 13 G on a new sheet 14 Output sensitivity analysis: updating any previous output sheet 15 annual returns Solver Engine: Current Solver Engine: CC Solver Engine... 16 17 Show model after saving Clear Model Options. Save Model

From From From From Other Existing Access Web Text SourcesConnecti Refresh Sort Filter Edit Links Advanced ions All Sort & Filter Get External Data Connections Security Warning Automatic update of links has been disabled Options... M10 Aggressive growth Growth equity Growth/income smal company real estate 4 5 annual return 11.02 5.52 18.38 18.62 15.15 18.83 0.92 1.13 1 0.77 0.94 0.43 6 7 Investment percent 0.05 0.05 0.05 0.75 0.05 0.05 10 Risk tolerance 0.9035 1.25 11 Total investment 12 13 14 15 annual returns 17.557 16

f Cells 1 OpenSolver Sensitivity Report CBC 2 Worksheet: [may 19.xlsx] Sheet19 Sensitivity 1 3 Report Created: 01-06-2019 09:55:22 4 5 Decision Variables Final Value Reduced Costs Objective Value Allowable Increase Allowable Decrease Cells Name Investment percent Aggressive growtlh 18.38 0.05 0 0.4500001 1E+100 Investment percent Growth 0.05 0.2100001 1E+100 Investment percent equity 0.05 3.6800001 1E+100 Investment percent Growth/income 0.2100001 10 18.83 1E+100 Investment percent smal company 0.05 11.02 7.8100001 1E+100 1E+100 Investment percent real estate 0.05 0 13.3100001 14 Constraints Final Value Shadow Price RHS Value Allowable Increase Allowable Decrease Name D10< F10Risk tolerance Aggressive growth D11-F11Total investment Aggressive growth D70 Investment percent Aggressive growth 0.05 0.9035 0 1.25 1E+100 0.3465 18.83 0.376630435 0.05 1E+100 Investment percent Growth E73-0 0.05 0.05 1E+100 F73-0 Investment percent equity 0.05 0.05 1E+100 G7-0Investment percent Growth/income 0.75 0.75 1E+100 Investment percent smal company H7220 0.05 0.05 1E+100 Investment percent real estate 0.05 0.05 1E+100 D7-0.05Investment percent Aggressive growth 0.05 0.05 0.05 Investment percent Growth E7> 0.05 0.05 0.05 0.05 F73-0.05 Investment percent equity 0.05 0.05 0.05 G7-0.05 nvestment percent Growth/income 0.75 0.05 1E+100 H7-0.05Investment percent smal company 0.05 0.05 0.05 Investment percent real estate 7-0.05 0.05 0.05 0.05

Comparison

Comparing the two, we find that lower limit of risk tolerance range has increased for the second option. So even if the risk tolerance of investor decreases to a very lower level i.e. he becomes very risk averse, the profit is not going to be affected

So, with portfolio diversification, even an investor with lower risk tolerance can invest and can reap similar profit