One-minute Spotlight: Crystal Ball Basics

	ONE-MINUTE SPOTLIGHT	Crystal Ball
Print this Article How does Crystal Ball work? Crystal Ball Standard Edition is an easy-to-use simulation program that helps you to analyze the risks and uncertainties associated with your Microsoft Excel spreadsheet models. This Spotlight is a quick overview of how Crystal Ball is used (it may take a little more than a minute!) Excel models are deterministic, which means that the inputs are fixed (one value to one cell). You can only see one solution at a time. If you want to view alternative results, you need to manually change the inputs in the model. Simulation is a way to quickly generate and analyze many possible results. Excel by itself cannot run simulations, so you need an add-in program like Crystal Ball to make Excel do this. It Takes a Spreadsheet to Make a Model Because Crystal Ball is an analysis tool, you can use it to simulate existing or new spreadsheets in any industry and for any application. For example, if you were a researcher in the pharmaceutical industry, you might develop the spreadsheet below to analyze the financial success of your project. Without simulation, you calculate a Net Profit of $9,200,000. Not bad, but you have no sense of the likelihood of this result. Are you 75% sure this will happen? 100% sure? A simulation can show you the probability of a given outcome. Identifying Uncertainty in Your Model and Defining Assumptions The first step to using Crystal Ball is to determine which model inputs are uncertain. Which values are estimates? Which are averages? Once you have identified these, you use your knowledge of the uncertainty around the input to create a probability distribution for that cell (what we call an assumption). Crystal Ball lets you define these distributions. In this case, you know Marketing Costs (cell C6) can vary between $14,000,000 and $19,000,000, but are most likely to be $16,000,000. You then use Crystal Ball to define a Triangular distribution with these parameters, as shown below. The width of the triangle represents the range of possible costs, and the height of the triangle represents the likelihood of the value actually happening. The highest point of the triangle is $16,000,000, the most likely value. Identify Which Forecasts You Want to Analyze The next step is to identify a forecast. A forecast is a formula cell that you want to measure and analyze. In this model, you select the Net Profit (cell C23). You can define multiple assumptions and forecasts (see our Technote for suggestions on how to select assumptions). Once finished, you use the Crystal Ball command or toolbar icon to run a simulation. For each trial in this simulation, Crystal Ball enters a random value into the Marketing Cost cell based on the values you used to define the triangular distribution. For trial #1, the random value might be $15,000,000, followed by $17,500,000 for trial #2, $16,875,000 for trial #3, and so on. Each time Crystal Ball enters a random value, it recalculates the spreadsheet and saves the forecast value in its memory for later analysis. Analyzing Your Simulation Results If you run a simulation for 5000 trials, then you have created 5000 forecasts (or possible outcomes), compared to the single outcome you started with in the deterministic spreadsheet. Simulation results are displayed in interactive histograms, or frequency charts. The chart below shows the results of 5000 trials of Net Profit. (NOTE: the number of trials here was chosen arbitrarily. To run a more accurate number of trials, you would use the Precision Control feature.) Note that the range of possible Net Profit values is $6,300,000 to $11,100,000, with a mean (average) value of $8,800,000. There is only a 38% certainty that you will receive a Net Profit of $9,200,000 as you originally predicted. Imagine how much more complex such simulations get with multiple assumptions, and you begin to see the power of Crystal Ball. For a more detailed description of simulation, visit our Risk Overview section. You can also download slideshow demos with additional details. Was this One-Minute Spotlight helpful? Please take a moment to e-mail (feedback@crystalball.com) us your comments and suggestions for other One-Minute Spotlights. Related Topics > List of current One-Minute Spotlights > Monte Carlo Simulation Overview page > Crystal Ball Standard Edition home page > Crystal Ball Standard Edition features page > Crystal Ball Professional Edition home page > Crystal Ball Professional Edition features page > Tech Support home page