Analyzing Decision Table Results

This Decision Table analysis example uses a Crystal Ball example model, Oil Field Development.xlsx. This spreadsheet model predicts how best to develop a new oil field by selecting the optimal number of wells to drill, rate of oil production, and size of the refinery to build that will maximize the net present value of the text box.

To generate results, Crystal Ball Run Preferences are set to use Monte Carlo simulation with the same sequence of random numbers and a seed value of 999. Then, the Decision Table tool is started. The NPV forecast is selected with Facility Size and Wells To Drill selected as decision variables. The following options are selected:

When the Decision Table tool is run, it runs a simulation for each combination of decision variable values. Then, it compiles the results in a table of forecast cells indexed by the decision variables.

For this example, the Decision Table tool ran 42 simulations, one for each combination of wells to drill and facility sizes. The simulation that resulted in the best mean NPV was the combination of 12 wells and a facility size of 150 mbd (Figure 61, Decision Table for Oil Field Development Results).

Figure 61. Decision Table for Oil Field Development Results

To view one or more of the forecasts in the decision table, select the cells and click Forecast Chart. To compare one or more forecasts on the same chart, select the cells and click the Trend Chart or Overlay Chart button in column A (Figure 62, Trend Chart of 150 Mbd Forecasts).

Figure 62. Trend Chart of 150 Mbd Forecasts

You can create the trend chart shown in Figure 62, Trend Chart of 150 Mbd Forecasts by selecting all the forecast cells in the Facility Size (150.00) column of the results table and clicking on Trend Chart. This chart shows that the forecast with the highest mean NPV also has the largest uncertainty compared to other forecasts with smaller NPVs of the same facility size. This indicates a higher risk that you could avoid with a different number of wells (although the lower risk is accompanied with a lower NPV).