Optimal Portfolio Assignment Solution Strudwick by 3O6K6N

VIEWS: 11 PAGES: 12

									                     PORTFOLIO OPTIMISATION ASSIGNM
Inputs & Visual Adjustments:                          Outputs:
   Allow Short Sales                                  Visualisation of Efficient Frontier
 FALSE                                                 12%
   Display Efficient Frontier with SS
 FALSE
                                                       11%
   Display Efficient Frontier without SS
 FALSE Simulation                                      10%
   View
 FALSE                                                     9%
  100           % to be Shorted
                                                           8%
   300            Number of Portfolios
                                                           7%
                                                           6%
          Recalculate
                                                           5%
Asset Allocation:
                                                           4%
Risk-Free Rate                  4.00%           400
Required Return                 5.10%           510        3%
Std Deviation                   4.06%                      2%
                                                                      0%     2%   4%         6%           8%
 70%
                                 35%
 60%                             65%                  Individual Stock Information
 50%                            -35%                              120%
 40%                                                              100%
                                                       Primary Axis




Risk Free Risky AssetBorrowing                                        80%
30%
     35%        65%        0%
 20%                                                                  60%
 10%                                                                  40%
           35%
                  0.00%   65%
                                4.00%   0%                            20%
  0%             12.40%         7.36%
         Risk Free   Risky Asset    Borrowing                         0%
                                                                            CHH   FER         UBIX             IN



Short Sales TRUE
# of Rand      300 100                                                                  Beta
                10      10                                                              Mean Return
                50      25                                                              Std Dev
               300      50                                                              % of with SS
               500      75                                                              % of Without SS
              1000     100

Calculation of the Random Portfolios
              CHH       FER       UBIX                  IN       LN       S
Rand #      0.029863 0.235649 0.822332                0.38766 0.903041 0.160305            2.539
Prop            1.2%      9.3%     32.4%                15.3%    35.6%     6.3%            2.539

Portfolio Details
           Mean (r)             4.35%
           VARP                 0.37%
           STDEVP               6.11%
Storage for the 300 random portfolios              Means    Std Devs
 StdDev     Mean (r)                    Min (r):      0.00%     0.00%
                                        Max (r):      0.00%     0.00%
                                                                                             Stock Return Information

ON ASSIGNMENT                                   Created by X
                                                                                                Date
                                                                                              2-May-91
                                                                                              4-Jun-91
                                                                                                1-Jul-91
                                                                                              1-Aug-91
                                                                                              2-Sep-91
                                            Tangent                                            1-Oct-91
                                            Return                         5.68%              1-Nov-91
                                            Std Dev                        6.20%              2-Dec-91
                                                                                              6-Jan-92
                                            Minimum Variance                                  3-Feb-92
                                            Return     #N/A                                   3-Mar-92
                                            Std Dev      0.00%                                 1-Apr-92
                                                                                              1-May-92
                                            Sharpe Ratio                                      2-Jun-92
                                            Tangent Portfolio                      0.27         1-Jul-92
                                            Minimum Variance                       #N/A       3-Aug-92
                                                                                              1-Sep-92
                                                                                               2-Oct-92
         10%        12%           14%                                                         2-Nov-92
                                                                                              1-Dec-92
                                                                                              5-Jan-93
                                        10%                                                   1-Feb-93
                                                               Graph Options:                 1-Mar-93
                                              Secondary Axis




                                        8%
                                        6%                     Primary Axis                  Variance-Covariance Matrix (Cre
                                        4%                          Mean Return
                                        2%                                                     CHH
                                                               Secondary Axis                  FER
                                        0%
   IN          LN             S                                     Beta                       UBIX
                                                                                                IN
                                                                                                LN
        CHH         FER           UBIX                     IN           LN          S            S
           0.36        0.18          1.12                    0.37         0.25        0.90
         3.46%       3.17%         8.85%                   3.21%        1.02%       4.69%    With Short Sales
         8.52%       5.74%        10.28%                   9.09%        9.04%      11.49%
         59.8%      -15.9%         79.9%                    8.0%       -77.3%       45.5%      CHH
         10.0%       19.1%         42.7%                   13.9%         4.5%        9.9%      FER
                                                                                               UBIX
                                                                                                IN
                                                                                                LN
                                                                                                 S

                                                                                             Transpose of X
                                                                                              0.197089

                                                                                             Transpose of Y
                                                                                              0.243554
Mean
Var
StdDev

Cov (x,y)
Corr (x,y)

A Single Portfolio Calculation
Proportion of X:
p's Mean return:
p's std dev:

Gap
Prop of X

      0%
     14%
     28%
     42%
     56%
     70%
     84%
     98%
    112%
    126%
    140%
    154%
    168%
    182%
    196%
    210%
    224%
    238%
    252%
    266%
    280%
    294%
    308%
    322%
    336%
    350%
    364%
    378%
    392%
    406%
Stock Return Information
             CHH       FER        UBIX           IN        LN        S       Market       PR
             14.91%    12.75%      2.94%        19.62%    -2.42%     7.41%     5.23%      4.91%
              -0.54%     0.87%     9.71%        -8.99%    11.18%    -3.38%     4.65%      5.61%
            -11.96%      0.86%     4.17%       -11.60%    -4.37%    -2.27%     1.33%     -2.18%
             10.03%      5.13%    24.00%         0.00%     1.48%     4.19%    16.45%      9.16%
              -6.40%     4.07%    14.92%        -3.07%   -10.82%    -6.25%     7.24%      0.42%
             11.80%      9.73%    13.68%         0.86%    13.11%    23.81%    16.24%     11.77%
             13.33%      5.07%    10.35%        19.54%    15.07%    28.85%    16.53%     14.15%
               8.33%    -2.76%    11.76%         1.22%    -5.54%    -7.49%     5.08%      1.39%
             11.78%      6.38%    22.37%        -3.61%    10.07%    13.11%    18.10%     11.83%
              -4.17%    -0.67%    -3.23%        -4.25%   -13.09%     1.45%    -1.80%     -6.37%
               4.35%    -0.40%    -3.33%         3.13%     9.09%    12.00%     1.84%      3.40%
              -5.42%     5.16%    -5.27%         0.55%     4.08%    -6.12%    -5.17%     -0.14%
             10.13%    10.39%     18.52%        20.51%    11.17%    22.28%    19.65%     15.59%
             11.20%    11.76%     16.67%         8.51%    10.05%    -2.41%    10.24%     11.34%
              -3.96%    -2.11%     5.36%        -3.92%    -6.64%    -3.45%     2.05%     -1.68%
               1.86%     7.53%    11.86%        20.41%     0.67%     5.95%    10.28%      8.29%
              -0.75%    -5.00%    24.24%        -0.85%    -5.74%    14.61%    19.66%      6.13%
              -5.30%     0.30%    -3.05%        -0.85%     3.04%    -5.88%    -3.81%     -0.44%
              -8.00%     1.06%    10.52%         4.00%   -15.91%    25.00%    14.54%     -0.06%
             20.43%      9.47%     6.98%        12.61%    12.97%     1.66%     5.66%     10.02%
               1.43%     7.69%    -2.72%         0.00%    -2.59%    -4.96%    -3.18%     -1.38%
              -2.53%    -7.14%    25.14%        -0.75%    -9.50%   -12.17%    11.80%      3.19%
               8.89%    -7.21%   -12.05%         0.75%    -1.93%     1.98%    -6.92%     -4.92%

Variance-Covariance Matrix (Created using VBA)
            CHH       FER         UBIX        IN          LN         S                  Means  Means-C
             0.0073    0.0025      0.0016    0.0046       0.0050    0.0035               3.46%   2.46%
             0.0025    0.0033      0.0007    0.0029       0.0027    0.0019               3.17%   2.17%
             0.0016    0.0007      0.0106    0.0009       0.0006    0.0026               8.85%   7.85%
             0.0046    0.0029      0.0009    0.0083       0.0029    0.0049               3.21%   2.21%
             0.0050    0.0027      0.0006    0.0029       0.0082    0.0035               1.02%   0.02%
             0.0035    0.0019      0.0026    0.0049       0.0035    0.0132               4.69%   3.69%

With Short Sales                           C               0.01     4.0000             Without Short Sales
               Z         X                      Z        Y                                 A
              3.0929    0.1971              2.917602 0.243554                             0.0000
              9.8416    0.6271              7.300477 0.609424                             0.0000
              7.2125    0.4596              6.518631 0.544158                             1.0000
             -1.3873   -0.0884             -1.375095 -0.114789                            0.0000
             -4.6723   -0.2977             -4.822831 -0.402597                            0.0000
              1.6056    0.1023              1.440523 0.120251                             0.0000
                                                                                           100%
Transpose of X
           0.627135 0.459601 -0.088405 -0.297731 0.102311

Transpose of Y                                                                         Transpose of A
           0.609424 0.544158 -0.114789 -0.402597 0.120251                                  0         0

              X         Y                                                                            A
              0.0663     0.0738                                        Mean            8.85%
              0.0042     0.0053                                        StdDev         10.28%
              0.0650     0.0730                                        Theta       0.763475
                                                                       'Without Short Sales' Table Created using VB
               0.0047                                                       C      Std Dev
            0.991144                                                         -0.49     5.20%
                                                                             -0.48     5.20%
A Single Portfolio Calculation                                               -0.47     5.20%
Proportion of X:             0.3                                             -0.46     5.20%
p's Mean return:          7.15%                                              -0.45     5.20%
p's std dev:              7.04%    7.04%   0.00038 0.002608 0.001974         -0.44     5.20%
                                                                             -0.43     5.20%
                 0.14         15                                             -0.42     5.20%
            Std Dev     Return                                               -0.41     5.20%
                  7%         7%                                              -0.40     5.20%
                  7%         7%                                              -0.39     5.20%
                  7%         7%                                              -0.38     5.20%
                  7%         7%                                              -0.37     5.20%
                  7%         7%                                              -0.36     5.21%
                  7%         7%                                              -0.35     5.21%
                  7%         7%                                              -0.34     5.21%
                  7%         7%                                              -0.33     5.21%
                  7%         7%                                              -0.32     5.21%
                  6%         7%                                              -0.31     5.21%
                  6%         6%                                              -0.30     5.21%
                  6%         6%                                              -0.29     5.21%
                  6%         6%                                              -0.28     5.21%
                  6%         6%                                              -0.27     5.22%
                  6%         6%                                              -0.26     5.22%
                  6%         6%                                              -0.25     5.22%
                  6%         6%                                              -0.24     5.22%
                  6%         6%                                              -0.23     5.22%
                  6%         6%                                              -0.22     5.22%
                  6%         5%                                              -0.21     5.23%
                  6%         5%                                              -0.20     5.23%
                  5%         5%                                              -0.19     5.23%
                  5%         5%                                              -0.18     5.23%
                  5%         5%                                              -0.17     5.24%
                  5%         5%                                              -0.16     5.24%
                  5%         5%                                              -0.15     5.25%
                  5%         5%                                              -0.14     5.25%
                  5%         5%                                              -0.13     5.26%
                  5%         5%                                              -0.12     5.27%
                  5%         4%                                              -0.11     5.27%
                  5%         4%                                              -0.10     5.29%
                                                                             -0.09     5.30%
                                                                             -0.08     5.32%
                                                                             -0.07     5.34%
                                                                             -0.06     5.37%
                                                                             -0.05     5.41%
                                                                             -0.04     5.46%
                                                                             -0.03     5.53%
-0.02    5.64%
-0.01    5.82%
 0.00    6.12%
 0.01    6.67%
 0.02    7.89%
 0.03   10.03%
 0.04   10.28%
 0.05   10.28%
 0.06   10.28%
 0.07   10.28%
 0.08   10.28%
 0.09   10.28%
 0.10   10.28%
 A        B       Opt
 8.77%   10.00%   10.23%
 2.14%    1.89%    1.85%
-3.64%   -3.72%   -3.73%
 7.08%    9.33%    9.75%
-0.38%    0.18%    0.29%
10.66%   12.55%   12.91%
14.20%   13.01%   12.79%
 0.69%    2.53%    2.87%
 9.02%   11.46%   11.91%
-4.73%   -3.14%   -2.84%
 3.35%    2.93%    2.85%
 0.12%   -1.54%   -1.85%
15.40%   13.95%   13.68%
10.30%   10.45%   10.47%
-2.33%   -1.89%   -1.81%
 9.17%    6.71%    6.25%
 3.41%    4.26%    4.42%
-0.96%   -2.51%   -2.81%
 1.28%    1.93%    2.06%
10.57%   11.64%   11.84%
 0.16%    1.10%    1.27%
-0.17%   -0.24%   -0.25%
-3.21%   -1.50%   -1.18%




                With SS Tangent                Without SS Tangent
                Weights Return                     A        B
                     0.221    0.008               7.36%    28.44%
                     0.618    0.020              20.56%    26.35%
                     0.502    0.044              19.13%    18.69%
                    -0.102   -0.003              22.24%     5.85%
                    -0.351   -0.004              21.83%     8.41%
                     0.111    0.005               8.88%    12.27%
                         1                          1.00     1.00
                Return       7.01%        R       3.95%     4.32%
                StdDev       6.89%        S       5.84%     5.82%
                   49.47% X               V       0.34%     0.34%
                   50.53% Y             Cov       0.33%
     1        0          0        0
                                      Weight    -18.74%   118.74%
                                      Return      4.39%
                                                       Std Dev    5.85%
                                                       Sharpe: 0.066674

Short Sales' Table Created using VBA
                Mean        CHH      FER      UBIX      IN       LN       S     SHARPE
                  4.41%       0.054   0.674    0.209     0.000    0.016   0.047   0.0792 0.471689
                  4.41%       0.054   0.674    0.210     0.000    0.016   0.047   0.0797
                  4.42%       0.054   0.674    0.210     0.000    0.015   0.047   0.0805
                  4.42%       0.054   0.673    0.211     0.000    0.015   0.047   0.0813
                  4.43%       0.054   0.673    0.211     0.000    0.014   0.047   0.0820
                  4.43%       0.054   0.673    0.212     0.000    0.014   0.047   0.0829
                  4.44%       0.055   0.673    0.212     0.000    0.013   0.047   0.0837
                  4.44%       0.055   0.673    0.213     0.000    0.013   0.047   0.0846
                  4.44%       0.055   0.672    0.213     0.000    0.012   0.047   0.0855
                  4.45%       0.056   0.672    0.214     0.000    0.011   0.047   0.0864
                  4.45%       0.056   0.672    0.215     0.000    0.010   0.047   0.0874
                  4.46%       0.056   0.672    0.215     0.000    0.010   0.047   0.0885
                  4.47%       0.056   0.671    0.216     0.000    0.009   0.048   0.0895
                  4.47%       0.057   0.671    0.217     0.000    0.008   0.048   0.0907
                  4.48%       0.057   0.671    0.217     0.000    0.007   0.048   0.0919
                  4.48%       0.057   0.670    0.218     0.000    0.006   0.048   0.0931
                  4.49%       0.058   0.670    0.219     0.000    0.005   0.048   0.0945
                  4.50%       0.058   0.670    0.220     0.000    0.004   0.048   0.0959
                  4.51%       0.058   0.669    0.221     0.000    0.003   0.048   0.0974
                  4.52%       0.059   0.669    0.222     0.000    0.002   0.048   0.0989
                  4.52%       0.059   0.669    0.223     0.000    0.001   0.048   0.1006
                  4.53%       0.059   0.668    0.224     0.000    0.000   0.049   0.1023
                  4.54%       0.059   0.668    0.225     0.000    0.000   0.049   0.1035
                  4.55%       0.059   0.666    0.226     0.000    0.000   0.049   0.1050
                  4.56%       0.058   0.665    0.228     0.000    0.000   0.049   0.1065
                  4.56%       0.058   0.664    0.229     0.000    0.000   0.049   0.1081
                  4.57%       0.058   0.662    0.231     0.000    0.000   0.049   0.1098
                  4.58%       0.057   0.661    0.233     0.000    0.000   0.049   0.1117
                  4.59%       0.057   0.659    0.235     0.000    0.000   0.049   0.1137
                  4.61%       0.057   0.658    0.237     0.000    0.000   0.049   0.1159
                  4.62%       0.056   0.656    0.239     0.000    0.000   0.049   0.1182
                  4.63%       0.056   0.654    0.241     0.000    0.000   0.049   0.1208
                  4.65%       0.055   0.652    0.244     0.000    0.000   0.050   0.1236
                  4.66%       0.054   0.649    0.247     0.000    0.000   0.050   0.1266
                  4.68%       0.054   0.647    0.250     0.000    0.000   0.050   0.1300
                  4.70%       0.053   0.644    0.254     0.000    0.000   0.050   0.1337
                  4.72%       0.052   0.640    0.258     0.000    0.000   0.050   0.1379
                  4.75%       0.051   0.637    0.262     0.000    0.000   0.050   0.1425
                  4.78%       0.050   0.632    0.267     0.000    0.000   0.050   0.1477
                  4.81%       0.049   0.628    0.273     0.000    0.000   0.051   0.1535
                  4.85%       0.047   0.622    0.279     0.000    0.000   0.051   0.1602
                  4.89%       0.046   0.616    0.287     0.000    0.000   0.051   0.1679
                  4.94%       0.044   0.608    0.296     0.000    0.000   0.052   0.1768
                  5.01%       0.041   0.599    0.307     0.000    0.000   0.052   0.1873
                  5.08%       0.039   0.589    0.320     0.000    0.000   0.053   0.1998
                  5.17%       0.035   0.575    0.336     0.000    0.000   0.053   0.2148
                  5.29%       0.031   0.558    0.357     0.000    0.000   0.054   0.2332
5.45%   0.025   0.536   0.384   0.000   0.000   0.055   0.2561
5.66%   0.017   0.505   0.422   0.000   0.000   0.057   0.2851
5.97%   0.005   0.459   0.477   0.000   0.000   0.059   0.3224
6.46%   0.000   0.376   0.563   0.000   0.000   0.060   0.3693
7.36%   0.000   0.217   0.722   0.000   0.000   0.061   0.4263
8.71%   0.000   0.000   0.966   0.000   0.000   0.034   0.4696
8.85%   0.000   0.000   1.000   0.000   0.000   0.000   0.4717
8.85%   0.000   0.000   1.000   0.000   0.000   0.000   0.4717
8.85%   0.000   0.000   1.000   0.000   0.000   0.000   0.4717
8.85%   0.000   0.000   1.000   0.000   0.000   0.000   0.4717
8.85%   0.000   0.000   1.000   0.000   0.000   0.000   0.4717
8.85%   0.000   0.000   1.000   0.000   0.000   0.000   0.4717
8.85%   0.000   0.000   1.000   0.000   0.000   0.000   0.4717

								
To top