problems10

Exercise 1 dataPage 1DATA FOR 6 FURNITURE COMPANIESLa-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 36.67% 0.20% 41.54% 21.92% 26.13% 22.50%1983 122.82% 61.43% 195.09% 62.27% 73.38% 117.89%1984 14.44% 63.51% -38.38% -1.27% 45.15% 7.80%1985 21.39% 28.42% 1.30% 81.17% 24.27% 38.14%1986 45.36% -7.44% 21.89% 19.83% 10.73% 54.48%1987 20.19% 48.27% 9.11% -10.21% -11.92% 26.82%1988 -8.94% -11.28% 12.65% 13.77% 7.06% -6.24%1989 27.02% 12.85% 12.08% 32.55% -7.55% 123.03%1990 -11.64% 2.42% -17.13% -6.48% 1.31% 15.48%1991 20.29% 6.90% 3.62% 50.12% -5.54% 19.92%1992 34.08% 22.21% 33.46% 84.40% 5.71% 62.76%Beta 0.80 0.95 0.65 0.85 0.85 1.40Mean returns 29.24% 20.68% 25.02% 31.64% 15.34% 43.87%A B C D E F G12345678910111213141516Exercise 1 dataPage 2- =AVERAGE(G3:G13)DATA FOR 6 FURNITURE COMPANIESH12345678910111213141516Exercise 1 data (中文)Page 36家家具公司的数据La-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 36.67% 0.20% 41.54% 21.92% 26.13% 22.50%1983 122.82% 61.43% 195.09% 62.27% 73.38% 117.89%1984 14.44% 63.51% -38.38% -1.27% 45.15% 7.80%1985 21.39% 28.42% 1.30% 81.17% 24.27% 38.14%1986 45.36% -7.44% 21.89% 19.83% 10.73% 54.48%1987 20.19% 48.27% 9.11% -10.21% -11.92% 26.82%1988 -8.94% -11.28% 12.65% 13.77% 7.06% -6.24%1989 27.02% 12.85% 12.08% 32.55% -7.55% 123.03%1990 -11.64% 2.42% -17.13% -6.48% 1.31% 15.48%1991 20.29% 6.90% 3.62% 50.12% -5.54% 19.92%1992 34.08% 22.21% 33.46% 84.40% 5.71% 62.76%Beta 0.80 0.95 0.65 0.85 0.85 1.40平均收益 29.24% 20.68% 25.02% 31.64% 15.34% 43.87%A B C D E F G12345678910111213141516Exercise 1 data (中文)Page 4- =AVERAGE(G3:G13)6家家具公司的数据H12345678910111213141516Exercise 1Page 5La-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 36.67% 0.20% 41.54% 21.92% 26.13% 22.50%1983 122.82% 61.43% 195.09% 62.27% 73.38% 117.89%1984 14.44% 63.51% -38.38% -1.27% 45.15% 7.80%1985 21.39% 28.42% 1.30% 81.17% 24.27% 38.14%1986 45.36% -7.44% 21.89% 19.83% 10.73% 54.48%1987 20.19% 48.27% 9.11% -10.21% -11.92% 26.82%1988 -8.94% -11.28% 12.65% 13.77% 7.06% -6.24%1989 27.02% 12.85% 12.08% 32.55% -7.55% 123.03%1990 -11.64% 2.42% -17.13% -6.48% 1.31% 15.48%1991 20.29% 6.90% 3.62% 50.12% -5.54% 19.92%1992 34.08% 22.21% 33.46% 84.40% 5.71% 62.76%mean 29.24% 20.68% 25.02% 31.64% 15.34% 43.87%sigma 33.94% 25.47% 57.74% 32.13% 24.38% 40.66%Excess return matrixLa-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 7.42% -20.48% 16.52% -9.73% 10.79% -21.37%1983 93.58% 40.75% 170.07% 30.63% 58.04% 74.02%1984 -14.81% 42.83% -63.40% -32.92% 29.81% -36.07%1985 -7.85% 7.74% -23.72% 49.53% 8.94% -5.73%1986 16.12% -28.12% -3.13% -11.81% -4.61% 10.61%1987 -9.05% 27.59% -15.91% -41.86% -27.26% -17.05%1988 -38.18% -31.96% -12.37% -17.87% -8.27% -50.11%1989 -2.23% -7.83% -12.94% 0.91% -22.89% 79.16%1990 -40.89% -18.26% -42.15% -38.12% -14.03% -28.39%1991 -8.95% -13.78% -21.40% 18.48% -20.88% -23.95%1992 4.84% 1.53% 8.44% 52.75% -9.63% 18.89%average 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% this line included as check only!transpose of excess return matrix1982 1983 1984 1985 1986 1987 1988 1989La-Z-Boy 7.42% 93.58% -14.81% -7.85% 16.12% -9.05% -38.18% -2.23%Kimball -20.48% 40.75% 42.83% 7.74% -28.12% 27.59% -31.96% -7.83%Flexsteel 16.52% 170.07% -63.40% -23.72% -3.13% -15.91% -12.37% -12.94%Leggett -9.73% 30.63% -32.92% 49.53% -11.81% -41.86% -17.87% 0.91%Miller 10.79% 58.04% 29.81% 8.94% -4.61% -27.26% -8.27% -22.89%Shaw -21.37% 74.02% -36.07% -5.73% 10.61% -17.05% -50.11% 79.16%Variance-covariance matrixLa-Z-Boy Kimball Flexsteel Leggett Miller Shaw MeansLa-Z-Boy 0.1152 0.0398 0.1792 0.0492 0.0568 0.0989 29.24%Kimball 0.0398 0.0649 0.0447 0.0062 0.0349 0.0269 20.68%Flexsteel 0.1792 0.0447 0.3334 0.0775 0.0886 0.1487 25.02%Leggett 0.0492 0.0062 0.0775 0.1033 0.0191 0.0597 31.64%Miller 0.0568 0.0349 0.0886 0.0191 0.0594 0.0243 15.34%Exercise 1Page 6Shaw 0.0989 0.0269 0.1487 0.0597 0.0243 0.1653 43.87%Exercise 1Page 71990 1991 1992-40.89% -8.95% 4.84%-18.26% -13.78% 1.53%-42.15% -21.40% 8.44%-38.12% 18.48% 52.75%-14.03% -20.88% -9.63%-28.39% -23.95% 18.89%this line included as check only!Exercise 1 (中文)Page 8La-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 36.67% 0.20% 41.54% 21.92% 26.13% 22.50%1983 122.82% 61.43% 195.09% 62.27% 73.38% 117.89%1984 14.44% 63.51% -38.38% -1.27% 45.15% 7.80%1985 21.39% 28.42% 1.30% 81.17% 24.27% 38.14%1986 45.36% -7.44% 21.89% 19.83% 10.73% 54.48%1987 20.19% 48.27% 9.11% -10.21% -11.92% 26.82%1988 -8.94% -11.28% 12.65% 13.77% 7.06% -6.24%1989 27.02% 12.85% 12.08% 32.55% -7.55% 123.03%1990 -11.64% 2.42% -17.13% -6.48% 1.31% 15.48%1991 20.29% 6.90% 3.62% 50.12% -5.54% 19.92%1992 34.08% 22.21% 33.46% 84.40% 5.71% 62.76%均值 29.24% 20.68% 25.02% 31.64% 15.34% 43.87%标准差 33.94% 25.47% 57.74% 32.13% 24.38% 40.66%超额收益矩阵La-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 7.42% -20.48% 16.52% -9.73% 10.79% -21.37%1983 93.58% 40.75% 170.07% 30.63% 58.04% 74.02%1984 -14.81% 42.83% -63.40% -32.92% 29.81% -36.07%1985 -7.85% 7.74% -23.72% 49.53% 8.94% -5.73%1986 16.12% -28.12% -3.13% -11.81% -4.61% 10.61%1987 -9.05% 27.59% -15.91% -41.86% -27.26% -17.05%1988 -38.18% -31.96% -12.37% -17.87% -8.27% -50.11%1989 -2.23% -7.83% -12.94% 0.91% -22.89% 79.16%1990 -40.89% -18.26% -42.15% -38.12% -14.03% -28.39%1991 -8.95% -13.78% -21.40% 18.48% -20.88% -23.95%1992 4.84% 1.53% 8.44% 52.75% -9.63% 18.89%均值 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%这一行仅供检查！转置超额收益矩阵1982 1983 1984 1985 1986 1987 1988 1989La-Z-Boy 7.42% 93.58% -14.81% -7.85% 16.12% -9.05% -38.18% -2.23%Kimball -20.48% 40.75% 42.83% 7.74% -28.12% 27.59% -31.96% -7.83%Flexsteel 16.52% 170.07% -63.40% -23.72% -3.13% -15.91% -12.37% -12.94%Leggett -9.73% 30.63% -32.92% 49.53% -11.81% -41.86% -17.87% 0.91%Miller 10.79% 58.04% 29.81% 8.94% -4.61% -27.26% -8.27% -22.89%Shaw -21.37% 74.02% -36.07% -5.73% 10.61% -17.05% -50.11% 79.16%方差-协方差矩阵La-Z-Boy Kimball Flexsteel Leggett Miller Shaw 均值La-Z-Boy 0.1152 0.0398 0.1792 0.0492 0.0568 0.0989 29.24%Kimball 0.0398 0.0649 0.0447 0.0062 0.0349 0.0269 20.68%Flexsteel 0.1792 0.0447 0.3334 0.0775 0.0886 0.1487 25.02%Leggett 0.0492 0.0062 0.0775 0.1033 0.0191 0.0597 31.64%Miller 0.0568 0.0349 0.0886 0.0191 0.0594 0.0243 15.34%Exercise 1 (中文)Page 9Shaw 0.0989 0.0269 0.1487 0.0597 0.0243 0.1653 43.87%Exercise 1 (中文)Page 101990 1991 1992-40.89% -8.95% 4.84%-18.26% -13.78% 1.53%-42.15% -21.40% 8.44%-38.12% 18.48% 52.75%-14.03% -20.88% -9.63%-28.39% -23.95% 18.89%Exercise 2Page 11DATA FOR 6 FURNITURE COMPANIESLa-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 36.67% 0.20% 41.54% 21.92% 26.13% 22.50%1983 122.82% 61.43% 195.09% 62.27% 73.38% 117.89%1984 14.44% 63.51% -38.38% -1.27% 45.15% 7.80%1985 21.39% 28.42% 1.30% 81.17% 24.27% 38.14%1986 45.36% -7.44% 21.89% 19.83% 10.73% 54.48%1987 20.19% 48.27% 9.11% -10.21% -11.92% 26.82%1988 -8.94% -11.28% 12.65% 13.77% 7.06% -6.24%1989 27.02% 12.85% 12.08% 32.55% -7.55% 123.03%1990 -11.64% 2.42% -17.13% -6.48% 1.31% 15.48%1991 20.29% 6.90% 3.62% 50.12% -5.54% 19.92%1992 34.08% 22.21% 33.46% 84.40% 5.71% 62.76%Beta 0.80 0.95 0.65 0.85 0.85 1.40Mean returns 29.24% 20.68% 25.02% 31.64% 15.34% 43.87%Standard deviation 35.60% 26.71% 60.55% 33.70% 25.57% 42.65%Variance 0.1267 0.0713 0.3667 0.1136 0.0654 0.1819Market port. variance 18%La-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustriesLa-Z-Boy 0.1267 0.0016 0.0084 0.0026 0.0015 0.0041Kimball 0.0016 0.0713 0.0047 0.0015 0.0008 0.0023Flexsteel 0.0084 0.0047 0.3667 0.0075 0.0043 0.0120Leggett & Platt 0.0026 0.0015 0.0075 0.1136 0.0013 0.0037Herman Miller 0.0015 0.0008 0.0043 0.0013 0.0654 0.0021Shaw Industries 0.0041 0.0023 0.0120 0.0037 0.0021 0.1819A B C D E F G123456789101112131415161718192021222324252627Exercise 2Page 12- =AVERAGE(G3:G13)- =STDEV(G3:G13)- =VAR(G3:G13)- =IF(B21:G21=A22:A27,B18:G18,MMULT(TRANSPOSE(B18:G18),B18:G18)*B19)DATA FOR 6 FURNITURE COMPANIESH I J K L M N123456789101112131415161718192021222324252627Exercise 2 (中文)Page 136家家具公司的数据La-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustries1982 36.67% 0.20% 41.54% 21.92% 26.13% 22.50%1983 122.82% 61.43% 195.09% 62.27% 73.38% 117.89%1984 14.44% 63.51% -38.38% -1.27% 45.15% 7.80%1985 21.39% 28.42% 1.30% 81.17% 24.27% 38.14%1986 45.36% -7.44% 21.89% 19.83% 10.73% 54.48%1987 20.19% 48.27% 9.11% -10.21% -11.92% 26.82%1988 -8.94% -11.28% 12.65% 13.77% 7.06% -6.24%1989 27.02% 12.85% 12.08% 32.55% -7.55% 123.03%1990 -11.64% 2.42% -17.13% -6.48% 1.31% 15.48%1991 20.29% 6.90% 3.62% 50.12% -5.54% 19.92%1992 34.08% 22.21% 33.46% 84.40% 5.71% 62.76%Beta 0.80 0.95 0.65 0.85 0.85 1.40平均收益 29.24% 20.68% 25.02% 31.64% 15.34% 43.87%标准差 35.60% 26.71% 60.55% 33.70% 25.57% 42.65%方差 0.1267 0.0713 0.3667 0.1136 0.0654 0.1819市场投资组合 方差 18%La-Z-Boy Kimball Flexsteel Leggett& Platt HermanMiller ShawIndustriesLa-Z-Boy 0.1267 0.0016 0.0084 0.0026 0.0015 0.0041Kimball 0.0016 0.0713 0.0047 0.0015 0.0008 0.0023Flexsteel 0.0084 0.0047 0.3667 0.0075 0.0043 0.0120Leggett & Platt 0.0026 0.0015 0.0075 0.1136 0.0013 0.0037Herman Miller 0.0015 0.0008 0.0043 0.0013 0.0654 0.0021Shaw Industries 0.0041 0.0023 0.0120 0.0037 0.0021 0.1819A B C D E F G123456789101112131415161718192021222324252627Exercise 2 (中文)Page 14- =AVERAGE(G3:G13)- =STDEV(G3:G13)- =VAR(G3:G13)- =IF(B21:G21=A22:A27,B18:G18,MMULT(TRANSPOSE(B18:G18),B18:G18)*B19)6家家具公司的数据H I J K L M N123456789101112131415161718192021222324252627Note: I have used the function varcovar defined in Chapter 10 to compute the variance-covariance matrix of the DJ30. This function must be copied from fm3_chapter10.xls.AlcoaAAAmericanInternationalGroupAIGAmericanExpressAXPBoeingBACitigroupCCaterpillarCATDupontDDAlcoaAA 0.0103 0.0020 0.0031 0.0032 0.0034 0.0053 0.0038AmericanInternational GroupAIG 0.0020 0.0051 0.0035 0.0019 0.0036 0.0018 0.0020AmericanExpressAXP 0.0031 0.0035 0.0056 0.0035 0.0045 0.0032 0.0026BoeingBA 0.0032 0.0019 0.0035 0.0082 0.0024 0.0032 0.0025CitigroupC 0.0034 0.0036 0.0045 0.0024 0.0072 0.0027 0.0025CaterpillarCAT 0.0053 0.0018 0.0032 0.0032 0.0027 0.0074 0.0033DupontDD 0.0038 0.0020 0.0026 0.0025 0.0025 0.0033 0.0049DisneyDIS 0.0029 0.0020 0.0035 0.0032 0.0036 0.0023 0.0017GeneralElectricGE 0.0021 0.0022 0.0024 0.0015 0.0026 0.0010 0.0019GeneralMotorsGM 0.0032 0.0018 0.0028 0.0026 0.0031 0.0027 0.0012HomeDepotHD 0.0033 0.0022 0.0030 0.0010 0.0028 0.0016 0.0017HoneywellHON 0.0058 0.0024 0.0044 0.0043 0.0034 0.0049 0.0037Hewlett-PackardHPQ 0.0059 0.0013 0.0030 0.0018 0.0029 0.0015 0.0016IBM 0.0041 0.0017 0.0026 0.0009 0.0040 0.0021 0.0017IntelINTC 0.0065 0.0019 0.0037 0.0019 0.0043 0.0032 0.0019Johnson&JohnsonJNJ 0.0002 0.0015 0.0016 0.0009 0.0015 0.0008 0.0007JPMorganChaseJPM 0.0048 0.0034 0.0046 0.0020 0.0065 0.0034 0.0025CocaColaKO 0.0007 0.0020 0.0023 0.0021 0.0027 0.0008 0.0014McDonaldsMCD 0.0024 0.0026 0.0031 0.0021 0.0025 0.0025 0.00193MMMM 0.0033 0.0011 0.0015 0.0025 0.0017 0.0028 0.0025AltriaMO 0.0013 0.0000 0.0008 0.0011 0.0004 0.0009 0.0005MerckMRK 0.0002 0.0024 0.0022 0.0017 0.0015 0.0010 0.0012MicrosoftMSFT 0.0036 0.0023 0.0019 0.0007 0.0029 0.0010 0.0016PfizerPFE 0.0003 0.0020 0.0015 0.0007 0.0016 0.0007 -0.0001Procter &GamblePG -0.0002 0.0005 0.0013 0.0010 0.0009 0.0002 0.0008ATTT 0.0021 0.0021 0.0020 0.0015 0.0019 0.0018 0.0012UnitedTechnologiesUTX 0.0042 0.0025 0.0038 0.0043 0.0035 0.0032 0.0025VerizonVZ 0.0023 0.0017 0.0018 0.0015 0.0023 0.0021 0.0016Wal-martWMT 0.0017 0.0021 0.0021 0.0004 0.0027 0.0014 0.0014Exxon-MobilXOM 0.0019 0.0012 0.0015 0.0018 0.0012 0.0019 0.0015DisneyDISGeneralElectricGEGeneralMotorsGMHomeDepotHDHoneywellHONHewlett-PackardHPQIBM IntelINTCJohnson&JohnsonJNJ0.0029 0.0021 0.0032 0.0033 0.0058 0.0059 0.0041 0.0065 0.00020.0020 0.0022 0.0018 0.0022 0.0024 0.0013 0.0017 0.0019 0.00150.0035 0.0024 0.0028 0.0030 0.0044 0.0030 0.0026 0.0037 0.00160.0032 0.0015 0.0026 0.0010 0.0043 0.0018 0.0009 0.0019 0.00090.0036 0.0026 0.0031 0.0028 0.0034 0.0029 0.0040 0.0043 0.00150.0023 0.0010 0.0027 0.0016 0.0049 0.0015 0.0021 0.0032 0.00080.0017 0.0019 0.0012 0.0017 0.0037 0.0016 0.0017 0.0019 0.00070.0069 0.0018 0.0036 0.0021 0.0035 0.0044 0.0028 0.0050 0.00050.0018 0.0044 0.0016 0.0028 0.0026 0.0020 0.0026 0.0028 0.00100.0036 0.0016 0.0113 0.0024 0.0035 0.0034 0.0023 0.0046 0.00040.0021 0.0028 0.0024 0.0075 0.0022 0.0033 0.0030 0.0029 0.00020.0035 0.0026 0.0035 0.0022 0.0117 0.0036 0.0034 0.0048 0.00060.0044 0.0020 0.0034 0.0033 0.0036 0.0140 0.0055 0.0089 -0.00040.0028 0.0026 0.0023 0.0030 0.0034 0.0055 0.0080 0.0066 0.00070.0050 0.0028 0.0046 0.0029 0.0048 0.0089 0.0066 0.0177 0.00080.0005 0.0010 0.0004 0.0002 0.0006 -0.0004 0.0007 0.0008 0.00340.0038 0.0026 0.0046 0.0041 0.0045 0.0042 0.0041 0.0056 0.00050.0013 0.0004 0.0010 0.0006 0.0019 0.0003 0.0002 0.0001 0.00170.0022 0.0018 0.0022 0.0028 0.0041 0.0016 0.0019 0.0019 0.0012Note: I have used the function varcovar defined in Chapter 10 to compute the variance-covariance matrix of the DJ30. This function must be copied from fm3_chapter10.xls.0.0016 0.0008 0.0009 0.0010 0.0026 0.0014 0.0016 0.0019 0.00070.0003 0.0003 0.0002 0.0002 0.0028 0.0018 0.0018 0.0011 0.00090.0013 0.0010 0.0008 -0.0003 0.0022 -0.0001 0.0002 0.0010 0.00230.0016 0.0034 0.0018 0.0034 0.0020 0.0050 0.0049 0.0067 0.00050.0013 0.0010 0.0009 0.0008 0.0006 0.0005 0.0008 0.0011 0.0018-0.0005 0.0002 0.0004 0.0001 0.0016 -0.0005 -0.0008 -0.0012 0.00140.0011 0.0017 0.0005 0.0017 0.0028 0.0017 0.0020 0.0005 0.00100.0035 0.0028 0.0028 0.0029 0.0050 0.0033 0.0023 0.0034 0.00120.0018 0.0014 0.0009 0.0015 0.0026 0.0016 0.0028 0.0016 0.00070.0007 0.0020 0.0012 0.0036 0.0013 0.0002 0.0016 0.0011 0.00120.0007 0.0007 0.0012 0.0004 0.0022 0.0011 0.0016 0.0004 0.0005JPMorganChaseJPMCoca ColaKOMcDonaldsMCD3MMMMAltriaMOMerckMRKMicrosoftMSFTPfizerPFEProcter &GamblePG0.0048 0.0007 0.0024 0.0033 0.0013 0.0002 0.0036 0.0003 -0.00020.0034 0.0020 0.0026 0.0011 0.0000 0.0024 0.0023 0.0020 0.00050.0046 0.0023 0.0031 0.0015 0.0008 0.0022 0.0019 0.0015 0.00130.0020 0.0021 0.0021 0.0025 0.0011 0.0017 0.0007 0.0007 0.00100.0065 0.0027 0.0025 0.0017 0.0004 0.0015 0.0029 0.0016 0.00090.0034 0.0008 0.0025 0.0028 0.0009 0.0010 0.0010 0.0007 0.00020.0025 0.0014 0.0019 0.0025 0.0005 0.0012 0.0016 -0.0001 0.00080.0038 0.0013 0.0022 0.0016 0.0003 0.0013 0.0016 0.0013 -0.00050.0026 0.0004 0.0018 0.0008 0.0003 0.0010 0.0034 0.0010 0.00020.0046 0.0010 0.0022 0.0009 0.0002 0.0008 0.0018 0.0009 0.00040.0041 0.0006 0.0028 0.0010 0.0002 -0.0003 0.0034 0.0008 0.00010.0045 0.0019 0.0041 0.0026 0.0028 0.0022 0.0020 0.0006 0.00160.0042 0.0003 0.0016 0.0014 0.0018 -0.0001 0.0050 0.0005 -0.00050.0041 0.0002 0.0019 0.0016 0.0018 0.0002 0.0049 0.0008 -0.00080.0056 0.0001 0.0019 0.0019 0.0011 0.0010 0.0067 0.0011 -0.00120.0005 0.0017 0.0012 0.0007 0.0009 0.0023 0.0005 0.0018 0.00140.0105 0.0022 0.0034 0.0014 0.0009 0.0017 0.0041 0.0020 0.00080.0022 0.0052 0.0020 0.0011 0.0010 0.0024 0.000