PROVIDE EXCEL FORMULAS

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 c Suppose you manage an equity fund with the following securities. Use the following data to help build an active portfolio. Input Data Alpha Beta Standard Deviation Residual Standard Deviation Information Ratio Alpha/Residual Variance Market Data Expected Raturn Standard Deviation Required: Vogt Industries Isher Corporation 0.012 0.277 0.156 0.117 0.1026 0.877 500 12.00% 20.00% 0.006 1.015 0.168 0.048 0.1250 2.604 Treasury Bills 2.50% 0.00% Hedrock, Incorporated 0.016 1.630 0.181 0.113 0.1416 1.253 Using the information in the table above, please first calculate the initial weight of each stock in an active portfolio, using the Treynor Black approach. Then adjust each weight for beta. (Use cells A5 to D14 from the given information to complete this question.) Treynor-Black Model Initial Position Beta Adjusted Initial Position Vogt Industries Isher Corporation Hedrock, Incorporated