VIEWS: 2 PAGES: 16 POSTED ON: 1/2/2012
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