Document Sample

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: | 1 |

posted: | 1/2/2012 |

language: | |

pages: | 16 |

OTHER DOCS BY gegeshandong

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.