problems09

Exercise 1Page 1DATA FOR 6 FURNITURE COMPANIESVariance-covariancematrixLa-Z-Boy Kimball Flexsteel Leggett Miller ShawLa-Z-Boy 0.1152 0.0398 0.1792 0.0492 0.0568 0.0989Kimball 0.0398 0.0649 0.0447 0.0062 0.0349 0.0269Flexsteel 0.1792 0.0447 0.3334 0.0775 0.0886 0.1487Leggett 0.0492 0.0062 0.0775 0.1033 0.0191 0.0597Miller 0.0568 0.0349 0.0886 0.0191 0.0594 0.0243Shaw 0.0989 0.0269 0.1487 0.0597 0.0243 0.1653Constant 10%raw normalizedportfolio weights3.1708 0.7203 =0)0.273237 50.276159 50.279273 50.282597 50.286153 50.289968 50.294069 50.298491 50.303273 50.30846 50.314106 50.320276 50.327045 50.334505 50.342768 50.35197 50.362282 50.373917 5E ffic ie n t F ro n tie r2 0 . 5 %2 0 . 0 %1 9 . 5 %1 9 . 0 %1 8 . 5 %1 8 . 0 %1 7 . 5 %1 7 . 0 %1 6 . 5 %1 6 . 0 %2 0 . 0 %2 5 . 0 %3 0 . 0 %3 5 . 0 %s ig m ameanI J K L M N O P Q545556575859606162636465666768697071727374757677787980818283Exercise 1Page 510.1R S T U1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253Exercise 1（中文）Page 66家家具公司的数据方差-协方差矩阵 La-Z-Boy Kimball Flexsteel Leggett Miller ShawLa-Z-Boy 0.1152 0.0398 0.1792 0.0492 0.0568 0.0989Kimball 0.0398 0.0649 0.0447 0.0062 0.0349 0.0269Flexsteel 0.1792 0.0447 0.3334 0.0775 0.0886 0.1487Leggett 0.0492 0.0062 0.0775 0.1033 0.0191 0.0597Miller 0.0568 0.0349 0.0886 0.0191 0.0594 0.0243Shaw 0.0989 0.0269 0.1487 0.0597 0.0243 0.1653常数 10%行 标准化的投资组合 权数3.1708 0.7203 =0)0.273237 50.276159 50.279273 50.282597 50.286153 50.289968 50.294069 50.298491 50.303273 50.30846 50.314106 50.320276 50.327045 50.334505 50.342768 50.35197 50.362282 50.373917 5I J K L M N O P Q5556575859606162636465666768697071727374757677787980818283Exercise 1（中文）Page 1010.1R S T U123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354UN-9Mepsilon 1 - Use solver to find epsilon such that minimum portfolio weight = 0.Variance-covariance matrix minusLa-Z-Boy Kimball Flexsteel & Platt Miller Shaw Means constantLa-Z-Boy 0.1152 0.0398 0.1792 0.0492 0.0568 0.0989 La-Z-Boy 29.24% 19.24%Kimball 0.0398 0.0649 0.0447 0.0062 0.0349 0.0269 Kimball 20.68% 10.68%Flexsteel 0.1792 0.0447 0.3334 0.0775 0.0886 0.1487 Flexsteel 25.02% 15.02%& Platt 0.0492 0.0062 0.0775 0.1033 0.0191 0.0597 & Platt 31.64% 21.64%Miller 0.0568 0.0349 0.0886 0.0191 0.0594 0.0243 Miller 15.34% 5.34%Shaw 0.0989 0.0269 0.1487 0.0597 0.0243 0.1653 Shaw 43.87% 33.87%constant 10%Explanationraw normalized When epsilon = 1, the variance-covariance matrix is the originalportfolio weights matrix (as in Exercise 1). For this case the optimized portfolio3.1708 0.7203 contains at least one short-selling position (illustrated here).0.4861 0.1104 minimum-2.4405 -0.5544 port. weight We use Solver to determine epsilon so that the entry in cell E221.3533 0.3074 -0.5544 is zero. You can determine manually that when epsilon is larger0.0614 0.0140 than this, there are short-selling positions.1.7712 0.4023Epsilon shrinks the variance-covariance matrix towards thediagonal. (Try setting epsilon = 0 to see what this means.)Here are the Solver settings:UN-9Mepsilon 1 - 使用规划求解来找使最小的投资组权数= 0的epsilon.方差-协方差矩阵 减La-Z-Boy Kimball Flexsteel & Platt Miller Shaw 均值 常数La-Z-Boy 0.1152 0.0398 0.1792 0.0492 0.0568 0.0989 La-Z-Boy 29.24% 19.24%Kimball 0.0398 0.0649 0.0447 0.0062 0.0349 0.0269 Kimball 20.68% 10.68%Flexsteel 0.1792 0.0447 0.3334 0.0775 0.0886 0.1487 Flexsteel 25.02% 15.02%& Platt 0.0492 0.0062 0.0775 0.1033 0.0191 0.0597 & Platt 31.64% 21.64%Miller 0.0568 0.0349 0.0886 0.0191 0.0594 0.0243 Miller 15.34% 5.34%Shaw 0.0989 0.0269 0.1487 0.0597 0.0243 0.1653 Shaw 43.87% 33.87%常数 10%解释行 标准化的 当 epsilon = 1, the 方差-协方差矩阵为原先的投资组合 权数 矩阵 (如在练习1中那样). 在此情况下优化配置的投资组合3.1708 0.7203 包含最少一个卖空头寸 (这里举例说明了).0.4861 0.1104 最小-2.4405 -0.5544 投资组合权数 我们用规划求解来确定 epsilon，使单元格E22中为1.3533 0.3074 -0.5544 0. 当epsilon比这个，你可以手动地选择，0.0614 0.0140 这里有卖空的头寸。1.7712 0.4023Epsilon使方差-协方差矩阵向对角阵压缩。(试一下设epsilon = 0 来看看均值是什么.)以下是规划求解的设定:Exercise 3Page 13Variance-covariance matrix MeanreturnsMeanminusconstant0.400 0.030 0.020 0.000 0.06 -0.0050.030 0.200 0.001 -0.060 0.05 -0.0150.020 0.001 0.300 0.030 0.07 0.0050.000 -0.060 0.030 0.100 0.08 0.015Constant 0.065z x z y0.1019 0.0540 -0.0101 -0.11630.5657 0.2998 -0.0353 -0.40670.1141 0.0605 0.0047 0.05441.1052 0.5857 0.1274 1.4687Transpose x0.0540 0.2998 0.0605 0.5857Transpose y-0.1163 -0.4067 0.0544 1.4687Mean(x) 0.0693 Mean(y) 0.0940 - =MMULT(B19:E19,F2:F5)Var(x) 0.0367 Var(y) 0.3341 - =MMULT(MMULT(B19:E19,A2:D5),F9:F12)Sigma(x) 0.1917 Sigma(y) 0.5780 - =SQRT(F22)Cov(x,y) 0.0498 - =MMULT(MMULT(B16:E16,A2:D5),F9:F12)Corr(x,y) 0.4496 - =C25/(C23*F23)Consider a portfolio z = lambda*x + (1-lambda)*yLambda -2.89306Cov(z,y) -1E-14 - =B29*C26+(1-B29)*F22Beta z wrt y -3.1E-14 - =B30/F22Using Solver to get beta = 0:Lambda -2.89306Cov(z,y) -6.2E-15 - -6.21724893790088E-15Beta z wrt y -1.9E-14 - -1.86088897479995E-14Thus the portfolio z which has beta = 0 wrt y has the following asset proportions:Asset 1 -0.6089Asset 2 -2.4508Asset 3 0.0366Asset 4 4.0231A B C D E F G H I123456789101112131415161718192021222324252627282930313233343536373839404142434445Exercise 3Page 14- =MMULT(MMULT(B19:E19,A2:D5),F9:F12)J K123456789101112131415161718192021222324252627282930313233343536373839404142434445Exercise 3（中文）Page 15方差-协方差矩阵 平均收益 均值减常数0.400 0.030 0.020 0.000 0.06 -0.0050.030 0.200 0.001 -0.060 0.05 -0.0150.020 0.001 0.300 0.030 0.07 0.0050.000 -0.060 0.030 0.100 0.08 0.015常数 0.065z x z y0.1019 0.0540 -0.0101 -0.11630.5657 0.2998 -0.0353 -0.40670.1141 0.0605 0.0047 0.05441.1052 0.5857 0.1274 1.4687转置x0.0540 0.2998 0.0605 0.5857转置y-0.1163 -0.4067 0.0544 1.4687均值(x) 0.0693 均值(y) 0.0940 - =MMULT(B19:E19,F2:F5)方差(x) 0.0367 方差(y) 0.3341 - =MMULT(MMULT(B19:E19,A2:D5),F9:F12)标准差(x) 0.1917 标准差(y) 0.5780 - =SQRT(F22)协方差(x,y) 0.0498 - =MMULT(MMULT(B16:E16,A2:D5),F9:F12)相关系数(x,y) 0.4496 - =C25/(C23*F23)考虑投资组合 z = lambda*x + (1-lambda)*yLambda -2.89306协方差(z,y) -1E-14 - =B29*C26+(1-B29)*F22Beta z wrt y -3.1E-14 - =B30/F22使用规划求解使得beta = 0:Lambda -2.89306协方差(z,y) -6.2E-15 - -6.21724893790088E-15Beta z wrt y -1.9E-14 - -1.86088897479995E-14因此投资组合 z ，其 beta = 0 wrt y， 它拥有以下资产比例:资产 1 -0.6089资产 2 -2.4508资产 3 0.0366资产 4 4.0231A B C D E F G H I123456789101112131415161718192021222324252627282930313233343536373839404142434445Exercise 3（中文）Page 16- =MMULT(MMULT(B19:E19,A2:D5),F9:F12)J K123456789101112131415161718192021222324252627282930313233343536373839404142434445A FOUR-ASSET PORTFOLIO PROBLEMVariance-covariance Mean returns St. dev.0.10 0.01 0.03 0.05 6% 31.62%0.01 0.30 0.06 -0.04 8% 54.77%0.03 0.06 0.40 0.02 10% 63.25%0.05 -0.04 0.02 0.50 15% 70.71%Constant 0.05Portfolio 1 Portfolio 2 Both these portfolios are efficientz x z y0.3861 35.53% -0.0460 -12.31%0.2567 23.62% 0.1091 29.18%0.1688 15.53% 0.1016 27.17%0.2752 25.32% 0.2093 55.96%Mean 0.09372 0.12707Variance 0.08624 0.20608Covariance 0.11692Proportion of 1 -0.9Portfolio mean 15.71%Portfolio var. 41.39% 0.069854 0.743945 -0.39986641Portfolio st. dev. 64.34% 0.413933Data table of portfoliosProportion of 1 0.6434 15.71%-1.4 75.52% 17.37%-1.15 69.89% 16.54%-0.9 64.34% 15.71%-0.65 58.88% 14.87%-0.4 53.55% 14.04%-0.15 48.39% 13.21%0.1 43.45% 12.37%0.35 38.84% 11.54%0.6 34.66% 10.71%0.85 31.11% 9.87%1.1 28.41% 9.04%1.35 26.82% 8.21%1.6 26.55% 7.37%1.85 27.63% 6.54%2.1 29.92% 5.70%2.35 33.16% 4.87%2.6 37.12% 4.04%2.85 41.58% 3.20%Stock A 31.62% 6.00%Stock B 54.77% 8.00%Stock C 63.25% 10.00%A B C D E F G H123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051Stock D 70.71% 15.00%A B C D E F G H52constant1%3%5%10%I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950514资产投资组合问题方差-协方差 平均收益 标准差0.10 0.01 0.03 0.05 6% 31.62%0.01 0.30 0.06 -0.04 8% 54.77%0.03 0.06 0.40 0.02 10% 63.25%0.05 -0.04 0.02 0.50 15% 70.71%常数 0.05投资组合 1 投资组合 2 两个投资组合都是有效的z x z y0.3861 35.53% -0.0460 -12.31%0.2567 23.62% 0.1091 29.18%0.1688 15.53% 0.1016 27.17%0.2752 25.32% 0.2093 55.96%均值 0.09372 0.12707方差 0.08624 0.20608协方差 0.11692比例： 1 -0.9投资组合 均值 15.71%投资组合 方差 41.39% 0.069854 0.743945 -0.39986641投资组合 标准差 64.34% 0.413933模拟运算表： 各个投资组合比例：1 0.6434 15.71%-1.4 75.52% 17.37%-1.15 69.89% 16.54%-0.9 64.34% 15.71%-0.65 58.88% 14.87%-0.4 53.55% 14.04%-0.15 48.39% 13.21%0.1 43.45% 12.37%0.35 38.84% 11.54%0.6 34.66% 10.71%0.85 31.11% 9.87%1.1 28.41% 9.04%1.35 26.82% 8.21%1.6 26.55% 7.37%1.85 27.63% 6.54%2.1 29.92% 5.70%2.35 33.16% 4.87%2.6 37.12% 4.04%2.85 41.58% 3.20%股票 A 31.62% 6.00%股票 B 54.77% 8.00%股票 C 63.25% 10.00%A B C D E F G H123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051股票 D 70.71% 15.00%A B C D E F G H52常数1%3%5%10%I123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051E ffic ie n t F ro n tie r S h o w in g th e In d iv id u a l S to c k s8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %0 %2 %4 %6 %8 %1 0 %1 2 %1 4 %1 6 %1 8 %2 0 %S ta n d a rd d e v ia tio n o f p o rtfo lioExpectedportfolioreturnS t o c k BS t o c k AS t o c k CS t o c k D每 只 股 票 与 有 效 前 沿8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %0 %2 %4 %6 %8 %1 0 %1 2 %1 4 %1 6 %1 8 %2 0 %投 资 组 合 标 准 差投资组合期望收益S t o c k BS t o c k AS t o c k CS t o c k D股 票 B股 票 A股 票 C股 票 D