Docstoc

Portfolio Allocation_rbg

Document Sample
Portfolio Allocation_rbg Powered By Docstoc
					                                                              Crystal Ball Report
                                                   Simulation started on 3/30/01 at 16:23:02
                                                   Simulation stopped on 3/30/01 at 16:23:30




                                                             Sensitiv ity Chart

                                                     Target Forecast: Total expected return

                   *   Growth and Income fund                     .93

                   *   Aggressive Growth fund                     .90

                   *   Income fund                                .44

                       Money Market fund                          -.04




                       * - Correlated assumption                         -1       -0.5            0               0.5            1

                                                                                   Measured by Rank Correlation




                                                     Forecast: Total expected return

                          1,000 Trials                         Frequency Chart                                      0 Outliers
                                  .035                                                                                  35



                                  .026                                                                                  26.25

                                                         Roger B.
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_SD7500_OptQuest Grinde                                                              1/2/2012, 12:26 PM
                                  .018                                                                                  17.5



                                  .009                                                                                  8.75
                      * - Correlated assumption                                  -1              -0.5             0              0.5            1

                                                                                                  Measured by Rank Correlation




             Forecast: Total expected return                                                                                                    Cell: C17

                  Summary:
                     Certainty Level is 95.00%
                     Certainty Range is from ($6,467) to +Infinity dollars
                     Display Range is from ($20,000) to $35,000 dollars
                     Entire Range is from ($16,609) to $34,653 dollars
                     After 1,000 Trials, the Std. Error of the Mean is $236

                  Statistics:                                                                                        Value
                       Trials                                                                                         1000
                       Mean                                                                                         $6,373
                       Median                                                                                       $6,438
                       Mode                                                                                              ---
                       Standard Deviation                                                                           $7,470
                       Variance                                                                                $55,806,236
                       Skewness                                                                                       -0.04
                       Kurtosis                                                                                        3.03
                       Coeff. of Variability                                                                           1.17
                       Range Minimum                                                                              ($16,609)
                       Range Maximum                                                                               $34,653
                       Range Width                                                                                 $51,262
                       Mean Std. Error                                                                             $236.23


                                                   Forecast: Total expected return

                        1,000 Trials                                Frequency Chart                                                0 Outliers
                                .035                                                                                                   35



                                .026                                                                                                   26.25



                                .018                                                                                                   17.5



                                .009                                                                                                   8.75


                                                                                  Mean = $6,373
                                .000                                                                                                   0

                                       ($20,000)   ($6,250)                 $7,500                  $21,250             $35,000
                                                      Certainty is 95.00% from ($6,467) to +Infinity dollars




                                                         Roger B.
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_SD7500_OptQuest Grinde                                                                               1/2/2012, 12:26 PM
                             .009                                                                                                                     8.75


                                                                                   Mean = $6,373
                             .000                                                                                                                     0

                                    ($20,000)       ($6,250)                 $7,500                  $21,250                        $35,000
                                                       Certainty is 95.00% from ($6,467) to +Infinity dollars




             Forecast: Total expected return (cont'd)                                                                                                                         Cell: C17

                  Percentiles:

                                       Percentile                                                                         dollars
                                             0%                                                                        ($16,609)
                                             5%                                                                         ($6,467)
                                            10%                                                                         ($3,274)
                                            15%                                                                         ($1,205)
                                            20%                                                                             $98
                                            25%                                                                          $1,404
                                            30%                                                                          $2,617
                                            35%                                                                          $3,434
                                            40%                                                                          $4,622
                                            45%                                                                          $5,537
                                            50%                                                                          $6,438
                                            55%                                                                          $7,535
                                            60%                                                                          $8,448
                                            65%                                                                          $9,394
                                            70%                                                                         $10,310
                                            75%                                                                         $11,400
                                            80%                                                                         $12,728
                                            85%                                                                         $13,861
                                            90%                                                                         $15,935
                                            95%                                                                         $18,290
                                           100%                                                                         $34,653

             End of Forecast




                                                                                                                                  M o n e y M a r k e t fu n d




                                                                                                                                                  M e a n = 3 .0 %


                                                                                                           2 .0 %      2 .5 %                3 .0 %                  3 .5 %     4 .0 %




                                                                                                                                       In c o m e fu n d




                                                                                                                                                  M e a n = 5 .0 %


                                                                                                          - 1 0 .0 %   - 2 .5 %              5 .0 %              1 2 .5 %       2 0 .0 %




                                                                                                                          G r o w th a n d In c o m e fu n d

                                                         Roger B.
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_SD7500_OptQuest Grinde                                                                                                                    1/2/2012, 12:26 PM




                                                                                                                                                  M e a n = 7 .0 %
                                                             Assumptions


             Assumption: Money Market fund                                                                                                       Cell: C5

                   Uniform distribution with parameters:                                            M o n e y M a r k e t fu n d


                       Minimum                               2.0%
                       Maximum                               4.0%

                                                                                                                     M e a n = 3 .0 %


                                                                             2 .0 %       2 .5 %               3 .0 %                   3 .5 %    4 .0 %




             Assumption: Income fund                                                                                                             Cell: C6

                   Normal distribution with parameters:                                                  In c o m e fu n d


                      Mean                                   5.0%
                      Standard Dev.                          5.0%

                  Selected range is from -Infinity to +Infinity                                                      M e a n = 5 .0 %


                                                                            - 1 0 .0 %   - 2 .5 %              5 .0 %               1 2 .5 %     2 0 .0 %




                  Correlated with:
                      Aggressive Growth fund (C8)                    0.29
                      Growth and Income fund (C7)                    0.37


             Assumption: Growth and Income fund                                                                                                  Cell: C7

                   Normal distribution with parameters:                                      G r o w th a n d In c o m e fu n d


                      Mean                                   7.0%
                      Standard Dev.                         12.0%

                  Selected range is from -Infinity to +Infinity                                                      M e a n = 7 .0 %


                                                                            - 2 9 .0 %   - 1 1 .0 %            7 .0 %               2 5 .0 %     4 3 .0 %




                  Correlated with:
                      Aggressive Growth fund (C8)                    0.72
                      Income fund (C6)                               0.37

             Assumption: Aggressive Growth fund                                                                                                  Cell: C8

                   Normal distribution with parameters:                                      A g g r e s s iv e G r o w th fu n d


                      Mean                                  11.0%
                      Standard Dev.                         18.0%

                  Selected range is from -Infinity to +Infinity                                                     M e a n = 1 1 .0 %


                                                                            - 4 3 .0 %   - 1 6 .0 %            1 1 .0 %             3 8 .0 %     6 5 .0 %




                  Correlated with:
                      Growth and Income fund (C7)                    0.72
                      Income fund (C6)                               0.29


             End of Assumptions




                                                         Roger B.
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_SD7500_OptQuest Grinde                                                                                     1/2/2012, 12:26 PM
                                                        Decision Variables

             Decision Variable: Money Market fund                            Cell: C13

                  Variable bounds:
                       Lower                                 $0
                       Upper                            $50,000

             Decision Variable: Income fund                                  Cell: C14

                  Variable bounds:
                       Lower                            $10,000
                       Upper                            $25,000

             Decision Variable: Growth and Income fund                       Cell: C15

                  Variable bounds:
                       Lower                                 $0
                       Upper                            $80,000

             Decision Variable: Aggressive Growth fund                       Cell: C16

                  Variable bounds:
                       Lower                           $10,000
                       Upper                          $100,000


             End of Decision Variables




                                                         Roger B.
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_SD7500_OptQuest Grinde            1/2/2012, 12:26 PM
              Simulation Report: Assuming Returns are Correlated
                                                               Crystal Ball Report
                                                    Simulation started on 3/30/01 at 16:05:38
                                                    Simulation stopped on 3/30/01 at 16:06:05




                                                              Sensitiv ity Chart

                                                      Target Forecast: Total expected return

                    *   Aggressive Growth fund                     .93

                    *   Growth and Income fund                     .88

                    *   Income fund                                .47

                        Money Market fund                          .01




                        * - Correlated assumption                        -1        -0.5            0               0.5            1

                                                                                    Measured by Rank Correlation




                                                      Forecast: Total expected return

                           1,000 Trials                         Frequency Chart                                      0 Outliers
                                   .041                                                                                  41



                                   .031                                                                                  30.75
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_CorrelationRoger B. Grinde                                                           1/2/2012, 12:26 PM

                                   .021                                                                                  20.5



                                   .010                                                                                  10.25
                        * - Correlated assumption                                  -1              -0.5             0              0.5            1

                                                                                                    Measured by Rank Correlation




              Forecast: Total expected return                                                                                                     Cell: C17

                   Summary:
                      Certainty Level is 95.00%
                      Certainty Range is from ($5,655) to +Infinity dollars
                      Display Range is from ($20,000) to $35,000 dollars
                      Entire Range is from ($16,088) to $30,330 dollars
                      After 1,000 Trials, the Std. Error of the Mean is $234

                   Statistics:                                                                                         Value
                        Trials                                                                                          1000
                        Mean                                                                                          $6,409
                        Median                                                                                        $6,531
                        Mode                                                                                               ---
                        Standard Deviation                                                                            $7,386
                        Variance                                                                                 $54,547,972
                        Skewness                                                                                         0.02
                        Kurtosis                                                                                         3.04
                        Coeff. of Variability                                                                            1.15
                        Range Minimum                                                                               ($16,088)
                        Range Maximum                                                                                $30,330
                        Range Width                                                                                  $46,418
                        Mean Std. Error                                                                              $233.56


                                                     Forecast: Total expected return

                          1,000 Trials                                Frequency Chart                                                0 Outliers
                                  .041                                                                                                   41



                                  .031                                                                                                   30.75



                                  .021                                                                                                   20.5



                                  .010                                                                                                   10.25


                                                                                    Mean = $6,409
                                  .000                                                                                                   0

                                         ($20,000)   ($6,250)                 $7,500                  $21,250             $35,000
                                                        Certainty is 95.00% from ($5,655) to +Infinity dollars




54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_CorrelationRoger B. Grinde                                                                             1/2/2012, 12:26 PM
                               .010                                                                                                                     10.25


                                                                                     Mean = $6,409
                               .000                                                                                                                     0

                                      ($20,000)       ($6,250)                 $7,500                  $21,250                        $35,000
                                                         Certainty is 95.00% from ($5,655) to +Infinity dollars




              Forecast: Total expected return (cont'd)                                                                                                                          Cell: C17

                   Percentiles:

                                         Percentile                                                                         dollars
                                               0%                                                                        ($16,088)
                                               5%                                                                         ($5,655)
                                              10%                                                                         ($3,052)
                                              15%                                                                         ($1,008)
                                              20%                                                                            $271
                                              25%                                                                          $1,277
                                              30%                                                                          $2,498
                                              35%                                                                          $3,484
                                              40%                                                                          $4,307
                                              45%                                                                          $5,365
                                              50%                                                                          $6,531
                                              55%                                                                          $7,695
                                              60%                                                                          $8,349
                                              65%                                                                          $9,310
                                              70%                                                                         $10,386
                                              75%                                                                         $11,419
                                              80%                                                                         $12,431
                                              85%                                                                         $13,896
                                              90%                                                                         $15,689
                                              95%                                                                         $18,659
                                             100%                                                                         $30,330

              End of Forecast




                                                                                                                                    Mo n e y Ma r k e t fu n d




                                                                                                                                                    M e a n = 3 .0 %


                                                                                                             2 .0 %      2 .5 %                3 .0 %                  3 .5 %     4 .0 %




                                                                                                                                         In c o m e fu n d




                                                                                                                                                    M e a n = 5 .0 %


                                                                                                            - 1 0 .0 %   - 2 .5 %              5 .0 %              1 2 .5 %       2 0 .0 %




                                                                                                                            G r o w th a n d In c o m e fu n d

54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_CorrelationRoger B. Grinde                                                                                                                  1/2/2012, 12:26 PM




                                                                                                                                                    M e a n = 7 .0 %
                                                               Assumptions


              Assumption: Money Market fund                                                                                                         Cell: C5

                    Uniform distribution with parameters:                                               Mo n e y Ma r k e t fu n d


                        Minimum                                2.0%
                        Maximum                                4.0%

                                                                                                                        M e a n = 3 .0 %


                                                                                 2 .0 %       2 .5 %               3 .0 %                  3 .5 %    4 .0 %




              Assumption: Income fund                                                                                                               Cell: C6

                    Normal distribution with parameters:                                                     In c o m e fu n d


                       Mean                                    5.0%
                       Standard Dev.                           5.0%

                   Selected range is from -Infinity to +Infinity                                                        M e a n = 5 .0 %


                                                                                - 1 0 .0 %   - 2 .5 %              5 .0 %               1 2 .5 %    2 0 .0 %




                   Correlated with:
                       Aggressive Growth fund (C8)                       0.29
                       Growth and Income fund (C7)                       0.37


              Assumption: Growth and Income fund                                                                                                    Cell: C7

                    Normal distribution with parameters:                                         G r o w th a n d In c o m e fu n d


                       Mean                                    7.0%
                       Standard Dev.                          12.0%

                   Selected range is from -Infinity to +Infinity                                                        M e a n = 7 .0 %


                                                                                - 2 9 .0 %   - 1 1 .0 %            7 .0 %               2 5 .0 %    4 3 .0 %




                   Correlated with:
                       Aggressive Growth fund (C8)                       0.72
                       Income fund (C6)                                  0.37

              Assumption: Aggressive Growth fund                                                                                                    Cell: C8

                    Normal distribution with parameters:                                         A g g r e s s iv e G r o w th fu n d


                       Mean                                   11.0%
                       Standard Dev.                          18.0%

                   Selected range is from -Infinity to +Infinity                                                        M e a n = 1 1 .0 %


                                                                                - 4 3 .0 %   - 1 6 .0 %           1 1 .0 %              3 8 .0 %    6 5 .0 %




                   Correlated with:
                       Growth and Income fund (C7)                       0.72
                       Income fund (C6)                                  0.29


              End of Assumptions




54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_CorrelationRoger B. Grinde                                                                                    1/2/2012, 12:26 PM
       Simulation Report: Assuming NO Correlation among returns
                                                          Crystal Ball Report
                                               Simulation started on 3/30/01 at 15:30:49
                                               Simulation stopped on 3/30/01 at 15:31:11




                                                           Sensitiv ity Chart

                                          Target Forecast: Total expected return (no correlation)

                Aggressive Growth fund                          .81

                Growth and Income fund                          .48

                Income fund                                     .17

                Money Market fund                               .05




                                                                      -1        -0.5            0               0.5            1

                                                                                 Measured by Rank Correlation




                                                    Roger B. Grinde
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_No_Correlation                                                                    1/2/2012, 12:26 PM
                                         Forecast: Total expected return (no correlation)

                   1,000 Trials                              Frequency Chart                                      0 Outliers
                           .036                                                                                       36
                                                                                 -1             -0.5             0               0.5            1

                                                                                                  Measured by Rank Correlation




       Forecast: Total expected return (no correlation)                                                                                         Cell: C17

            Summary:
               Certainty Level is 95.00%
               Certainty Range is from ($2,973) to $18,986 dollars
               Display Range is from ($15,000) to $25,000 dollars
               Entire Range is from ($11,194) to $24,748 dollars
               After 1,000 Trials, the Std. Error of the Mean is $172

            Statistics:                                                                                              Value
                 Trials                                                                                              1000
                 Mean                                                                                              $6,842
                 Median                                                                                            $6,759
                 Mode                                                                                                   ---
                 Standard Deviation                                                                                $5,449
                 Variance                                                                                     $29,689,224
                 Skewness                                                                                             0.14
                 Kurtosis                                                                                             3.00
                 Coeff. of Variability                                                                                0.80
                 Range Minimum                                                                                   ($11,194)
                 Range Maximum                                                                                    $24,748
                 Range Width                                                                                      $35,942
                 Mean Std. Error                                                                                  $172.31


                                           Forecast: Total expected return (no correlation)

                  1,000 Trials                                      Frequency Chart                                                0 Outliers
                        .036                                                                                                           36



                        .027                                                                                                           27



                        .018                                                                                                           18



                        .009                                                                                                           9


                                                                                         Mean = $6,842
                        .000                                                                                                           0

                               ($15,000)            ($5,000)                $5,000                 $15,000              $25,000
                                                       Certainty is 95.00% from ($2,973) to $18,986 dollars




                                                    Roger B. Grinde
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_No_Correlation                                                                                     1/2/2012, 12:26 PM
                        .009                                                                                                                    9


                                                                                    Mean = $6,842
                        .000                                                                                                                    0

                               ($15,000)       ($5,000)                $5,000                 $15,000                         $25,000
                                                  Certainty is 95.00% from ($2,973) to $18,986 dollars




       Forecast: Total expected return (no correlation) (cont'd)                                                                                                        Cell: C17

            Percentiles:

                                  Percentile                                                                        dollars
                                        0%                                                                       ($11,194)
                                        5%                                                                        ($2,165)
                                       10%                                                                            $62
                                       15%                                                                         $1,163
                                       20%                                                                         $2,179
                                       25%                                                                         $2,950
                                       30%                                                                         $3,809
                                       35%                                                                         $4,608
                                       40%                                                                         $5,458
                                       45%                                                                         $6,113
                                       50%                                                                         $6,759
                                       55%                                                                         $7,462
                                       60%                                                                         $8,258
                                       65%                                                                         $8,897
                                       70%                                                                         $9,594
                                       75%                                                                        $10,310
                                       80%                                                                        $11,213
                                       85%                                                                        $12,433
                                       90%                                                                        $13,988
                                       95%                                                                        $15,959
                                      100%                                                                        $24,748

       End of Forecast




                                                                                                                            Mo n e y Ma r k e t fu n d




                                                                                                                                            M e a n = 3 .0 %


                                                                                                     2 .0 %      2 .5 %                3 .0 %                  3 .5 %     4 .0 %




                                                                                                                                 In c o m e fu n d




                                                                                                                                            M e a n = 5 .0 %


                                                                                                    - 1 0 .0 %   - 2 .5 %              5 .0 %              1 2 .5 %       2 0 .0 %




                                                    Roger B. Grinde
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_No_Correlation                                                                                                          1/2/2012, 12:26 PM
                                                                                                                    G r o w th a n d In c o m e fu n d
                                                       Assumptions


       Assumption: Money Market fund                                                                                                      Cell: C5

             Uniform distribution with parameters:                                            Mo n e y Ma r k e t fu n d


                 Minimum                               2.0%
                 Maximum                               4.0%

                                                                                                              M e a n = 3 .0 %


                                                                       2 .0 %       2 .5 %               3 .0 %                  3 .5 %    4 .0 %




       Assumption: Income fund                                                                                                            Cell: C6

             Normal distribution with parameters:                                                  In c o m e fu n d


                Mean                                   5.0%
                Standard Dev.                          5.0%

            Selected range is from -Infinity to +Infinity                                                     M e a n = 5 .0 %


                                                                      - 1 0 .0 %   - 2 .5 %              5 .0 %               1 2 .5 %    2 0 .0 %




       Assumption: Growth and Income fund                                                                                                 Cell: C7

             Normal distribution with parameters:                                      G r o w th a n d In c o m e fu n d


                Mean                                   7.0%
                Standard Dev.                         12.0%

            Selected range is from -Infinity to +Infinity                                                     M e a n = 7 .0 %


                                                                      - 2 9 .0 %   - 1 1 .0 %            7 .0 %               2 5 .0 %    4 3 .0 %




       Assumption: Aggressive Growth fund                                                                                                 Cell: C8

             Normal distribution with parameters:                                      A g g r e s s iv e G r o w th fu n d


                Mean                                  11.0%
                Standard Dev.                         18.0%

            Selected range is from -Infinity to +Infinity                                                     M e a n = 1 1 .0 %


                                                                      - 4 3 .0 %   - 1 6 .0 %           1 1 .0 %              3 8 .0 %    6 5 .0 %




       End of Assumptions




                                                    Roger B. Grinde
54a44729-b305-44d1-a724-979333dbe5bc.xls, REPORT_No_Correlation                                                                           1/2/2012, 12:26 PM
           A                  B                          C               D                     E                F               G               H          I

      1        Portfolio Allocation Model                                                                  Assumption: Money Market fund
                                                                                                           Uniform distribution
      2
                                                                                                             Minimum 2.0%
      3                                               Annual          Lower                Upper             Maximum 4.0%
      4               Investments                     return          bound                bound
      5        Money Market fund                            3.0%               $0             $50,000
                                                                                                         Assumption: Income fund (Corr.)
      6        Income fund                                  5.0%          $10,000             $25,000    Normal distribution
      7        Growth and Income fund                       7.0%               $0             $80,000      Mean 5.0%
      8        Aggressive Growth fund                      11.0%          $10,000            $100,000      Standard Dev. 5.0%
      9        Total amount available                   $100,000                                         Selected range is
     10                                                                                                    from -Infinity to +Infinity
     11                                               Amount                                            Assumption: Growth and Income fund (Corr.)
     12            Decision variables                 invested                                          Normal distribution
     13        Money Market fund                          $25,000                                         Mean 7.0%
     14        Income fund                                $25,000                                         Standard Dev. 12.0%
     15        Growth and Income fund                     $25,000                                       Selected range is
     16        Aggressive Growth fund                     $25,000                                         from -Infinity to +Infinity
     17        Total expected return                       $6,500                                       Assumption: Aggressive Growth fund (Corr.)
     18                                                                                                 Normal distribution
                                                                                                          Mean 11.0%
     19                                                       $C$17: =SUMPRODUCT(C5:C8,C13:C16)
                                                                                                          Standard Dev. 18.0%
     20                                                       (Forecast Cell)
                                                                                                        Selected range is
     21                                                                                                   from -Infinity to +Infinity
     22




54a44729-b305-44d1-a724-979333dbe5bc.xls, Portfolio                          Roger B. Grinde                                                         1/2/2012, 12:26 PM
                                                           J               K              L

                                                       1
                                                       2
                                                       3
                                                       4
                                                       5
                                                       6   0.0025     625000000           1562500
                                                       7   0.0144     625000000           9000000
                                                       8   0.0324     625000000          20250000
                                                       9                              $30,812,500
                                                      10
                                                      11
                                                      12
                                                      13
                                                      14
                                                      15
                                                      16
                                                      17
                                                      18
                                                      19
                                                      20
                                                      21
                                                      22




54a44729-b305-44d1-a724-979333dbe5bc.xls, Portfolio                 Roger B. Grinde                 1/2/2012, 12:26 PM
                                         A               B                     C                D            E

                                     1       Portfolio Allocation Model
                                     2
                                     3                                      Annual           Lower         Upper
                                     4              Investments             return           bound         bound
                                     5       Money Market fund                    3.0%                $0      $50,000
                                     6       Income fund                          5.0%           $10,000      $25,000
                                     7       Growth and Income fund               7.0%                $0      $80,000
                                     8       Aggressive Growth fund              11.0%           $10,000     $100,000
                                     9       Total amount available           $100,000
                                    10
                                    11                                     Amount
                                    12           Decision variables        invested
                                    13       Money Market fund                 $25,000
                                    14       Income fund                       $25,000
                                    15       Growth and Income fund            $25,000
                                    16       Aggressive Growth fund            $25,000
                                    17       Total expected return              $6,500
                                    18
                                    19                                $C$17: =SUMPRODUCT(C5:C8,C13:C16)
                                    20




54a44729-b305-44d1-a724-979333dbe5bc.xls, Shell                        Roger B. Grinde                                  1/2/2012, 12:26 PM

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:2
posted:1/2/2012
language:
pages:16