tailieunhanh - Financial Modeling with Crystal Ball and Excel Chapter 3

CHAPTER 3 Building a Crystal Ball Model Monte Carlo simulation is a tool for modeling uncertainty. Typically, we begin with a deterministic spreadsheet model of the situation we are analyzing, then use Crystal Ball to add stochastic assumptions to represent the most important sources of uncertainty. | 3 Building a Crystal Ball Model Monte Carlo simulation is a tool for modeling uncertainty. Typically we begin with a deterministic spreadsheet model of the situation we are analyzing then use Crystal Ball to add stochastic assumptions to represent the most important sources of uncertainty. In the past stochastic modeling was an endeavor best undertaken only by highly trained scientists and engineers working on mainframe computers. Crystal Ball has been developed over the years to make Monte Carlo simulation accessible to financial analysts and others using Excel on personal computer workstations rather than coding in a programming language such as C or FORTRAN. This chapter goes through the process of starting with a simple financial model and adding stochastic assumptions to a deterministic model in order to illustrate the basics of building Crystal Ball models. SIMULATION MODELING PROCESS Most analysts facing a business problem follow the typical process for stochastic modeling 1. Develop a model that behaves like the real problem with a special consideration of the assumptions the random or probabilistic input variables. 2. Run a set of trials to learn about the behavior of the simulation model. 3. Continually modify the model until it has credibility with the decision makers. 4. Analyze the forecast output statistics and graphics to help make decisions about the real problem. The analysis and decisions in step 4 often will lead you to think about new problems or variants of the old problem in which case you should go to step 1 to develop a new model or modify the old model to reflect the variant of the old problem. Example For the upcoming golf season Alaskan Golf AG wishes to order a shipment of golf clubs to offer for sale in its retail stores and affiliated pro shops. It plans to 28 Building a Crystal Ball Model 29 A 0 C 1 2 3 Variable Name Value 4 Clubs ordered 0 1000 5 Unit cost per club C 300 6 Demand during golf season D 800 7 Sales