Print
this Article
How can you compare
the effects of different decisions on your spreadsheet model?
Decision variables are cells in your model that you can control,
such as how much to charge for a product or how many wells
to drill. But, in situations with uncertainty, it is not always
obvious what effect changing a decision variable can have
on your model's results.
The Decision Table tool automatically runs multiple simulations
to test different values for one or two decision variables.
You can then analyze the results in Crystal Ball using the
forecast, trend, and overlay charts.
In the oil field development model above, there are several
decision variables that you control, such as the number of
wells to drill, rate of oil production, and size of the facility
to build (shown in yellow). All of these have a direct impact
on the net present value (NPV) of the development project.
Defining a Decision Variable
You are most interested in how the NPV compares for different facility sizes, which can vary from as little as 50 or as much as 350 thousand barrels per day (mbd) of oil. How can you determine the right facility given the uncertainty surrounding well costs, production levels, and reservoir size?
First you define the facility size as a decision variable in Crystal Ball:
Then you enter the lower and upper bounds of the facility size along with a step size of 50. This will define facility sizes between 50 mbd to 350 mbd in increments of 50 mbd for a total of 7 possible sizes.
Running the Decision Table Tool
You are now ready to run the Decision Table tool, a simple, three-step wizard available from the CBTools menu.
In the first several dialogs, you specify the forecast variable that you want to examine and the decision variables to analyze.
In the final dialog (shown below), you can select how many values to test (7) and the number of trials to run per simulation (1000).
Viewing the Results
In this example, the tool ran seven simulations (one for each facility size) for 1000 random trials each. The Decision Table tool compiles the results into a table of forecast cells indexed by the decision variables.
The mean values of the NPV forecasts are shown in light blue. The facility size that resulted in the best mean NPV was 200 mbd.
Comparing Forecasts
Even though the 200 mbd facility resulted in the highest mean NPV, the overlay chart shows that this facility (light gray) has a large amount of uncertainty (and possibly a higher risk) compared to other facility sizes.
In contrast, the 100 mbd curve (in red) is steeper and will always result in a positive NPV. This is less risky, although it doesn't have the upside of the larger facility sizes.
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
> 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
