tailieunhanh - Financial Modeling with Crystal Ball and Excel Chapter 2

CHAPTER 2 Analyzing Crystal Ball Forecasts In this chapter, using an example of accumulating funds for retirement, we see the graphical and numerical summaries of forecasts that Crystal Ball provides automatically. This chapter serves as a review of elementary statistical analysis, focused on the standard output built into Crystal Ball. | 2 Analyzing Crystal Ball Forecasts In this chapter using an example of accumulating funds for retirement we see the graphical and numerical summaries of forecasts that Crystal Ball provides automatically. This chapter serves as a review of elementary statistical analysis focused on the standard output built into Crystal Ball. SIMULATING A 50-50 PORTFOLIO Let s say that you want to start saving for your retirement. You are 30 years old and wish to retire at age 60. You plan to put away an inflation-adjusted 10 000 per year and would like to know how much wealth you will have accumulated after 30 years. At this point you consider only two types of assets stocks and bonds. If you had perfect foresight you would know exactly what returns each investment would bring over the next 30 years. With that information you wouldn t need Crystal Ball and could optimize your portfolio by investing in only those assets that you knew would go up. Of course no one has perfect foresight so what do you do In this chapter we ll consider an oversimplified model for investing retirement funds and use it to illustrate how to analyze Crystal Ball forecasts. Overview. For this model we assume that returns on stocks and bonds during the next 30 years will resemble in a statistical sense the returns that have been observed during the years 1926 to 2004. You have heard that diversification is a good thing to do when investing your money so you decide initially to split the money you want to set aside for retirement each year by putting one half into stocks and one half into bonds. We call this the 50-50 portfolio and model it in the Excel file . The model draws returns on stocks and bonds randomly for each year and calculates the wealth you will have accumulated for retirement 30 years from now assuming a constant 3 percent inflation rate. A segment of this model is shown in Figure . Note that rows 14 through 40 are hidden to save space. 11 12 FINANCIAL MODELING