|
ONE-MINUTE
SPOTLIGHT |
> Return to One-Minute Spotlights |
|
Provided courtesy
of Decisioneering, Inc., makers of the Crystal Ball®
line of risk analysis software. |
|
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.
1515 Arapahoe St., Suite 1311•Denver, CO 80202•USA Broadgate Court, 199 Bishopsgate •London EC2M 3TY•UK |
|||||||||||||||
|
+1
303.534.4818