Question

Search Practice Test Excel Les File Home Insert Page Layout Formulas Data Review View Help ACROBAT X 1AA Calibri 2 Wrap Text General Paste BIU -A Conditional Format as $ % Cell Insert Merge & Center Formatting Table Styles Clipboard Font Alignment Number Styles A38 (6 points) You manage a portfolio worth $50 million and you only choose to invest in stock 1, 2 and 3. Base on Table 1 information, what is the highest portfolio return you can achieve if you want to keep beta of your portfc in the answer sheet. Assuming that NO short-sales allowed. A B C 21 22 23 Table 4. Portfolio C information 24 Stock 25 Dollar Investment 26 (6 points) You manage a portfolio worth $50 million and you only choose to invest in stock 1 and 2. Base on Table 1 information, how much you need to invest in stock 1 (of course, the rest will be invested in stock 2) so that your portfolio has total holing return of 15.05% ? Assuming that NO short-sales allowed. In your answer, include 2 number after decimal place, say, 27 45,500,231.20. 28 29 30 31 32 33 34 35 Table 5. Portfolio D infomation 36 Stock

Answer 1

We will solve this problem using "Solver" function in excel

Attaching the calculations, final values obtained and the "Solver" dialogue box :-

A. В E 1 Stocks Total Return (%) Allocation (%) Column E Column C Column D 2 14% 83% 1 11.55% 20% 17% 3.50% 4 2 5 15.05% 6 SUM 100% 7 N

Stocks Total Return (%) Allocation (%) Column E Column C Column D 0.82500332412439C3*D3 1 0.14 0.2 0.17499767587561C4*D4 4 2 5 SUM -SUM(D3:D4) =SUM(E3:E4) 6 7 LLJ N t LO. LO

A. C D Е G Н J K L М 1 Stocks Total Return (%) Allocation (%) Column E Column C Column D 2 Solver Parameters X 14% 83% 11.55% 1 E SE$6 17% Set Target Cell: 20% 3.50% Solve 4 2. О Min O Max O Value of: Equal To: 0.1505 5 Мax Close 100% By Changing Cells: 15.05% 6 SUM 7 $D$3:$D$4 Guess Subject to the Constraints: Options $D$3:$D$4> 1% SD$6 100% Add 10 Change 11 Reset All 12 Delete 13 Help 14 15 16

In the Solver function dialogue box, we will set the value of cell E6 = 15.05% along with the following restrictions :-

1) Value of D3 & D4 should be greater than 1%

2) Value of D6 (which is sum of D3 to D4) should be equal to 100%

Note :-

Column E is obtained by multiplying Column C with D, that is, multiplying the returns of Stock A and B with their respective allocations. Cell E6 is the sum of Cell E3 & Cell E4.

Hence, allocation to Stock 1 = 82.5% and Stock 2 = 17.5%

Hence, amount invested in Stock 1 = 82.5% of $50 million = $ 41.25mn to get a total return of 15.05%