@RISK and PrecisionTree
in Oil and Gas

@RISK is widely used in Oil & Gas, Renewable Energy, Utilities, and Engineering Services. Download and install a free trial version of the DecisionTools Suite (including @RISK and PrecisionTree) to view the models in full.

@RISK Models

Production Forecasting
This is a simple model forecasting production for a particular oil well.  The estimated reserves within the well are uncertain and are represented with a Lognormal distribution function.  The mean is 500,000 STB and the standard deviation is 50,000 STB.

The output in the model is the NPV of the reserves for the first 10 years of production.  Other factors considered include the decline rate, the gas-oil ratio (GOR), the prices of oil and gas, as well as the rate of increase of the prices of oil and gas.  The only input factor that contains an @RISK probability distribution function is reserves, but you could make the model more realistic by using distribution functions to describe the decline rate, GOR, price of oil, etc.

» Download example model: OilProdForecast.xls

Simple Volumetric Reserves
This model calculates simple volumetric reserves, N=AhR. The three input cells for A, h, and R contain Triang distributions. The single output cell, Reserves, is the product of the inputs, scaled down by 10^6.   After simulation, the resulting distribution for N is skewed to the right.

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

» Download example model: AHR1.xls

Exponential Decline
This model examines the familiar production forecasting model for oil and gas wells, the exponential decline curve.
The standard equation, q = qie-at (3.3), can be used with random variables for both qi (the initial production rate, sometimes called IP) and a (the constant decline rate). Here the model has an additional parameter, t (time), which makes the output (Rate, STB/YR) more complicated than the volumetric reserves output.

No longer do we just want a distribution of numbers for output. Instead we want a distribution of forecasts or graphs.  The worksheet has two input cells, IP and Decline, and a column of outputs for the Rate of production in STB/YR over 15 years. 

After simulation you can generate a summary graph like that shown in the model.  This graph shows uncertainty over the 15 year period.  The shaded region represents one standard deviation on each side of the mean. The dotted curves represent the 5th and 95th percentiles. Thus, between these dotted curves is a 90% confidence interval. We can think of the band as being made up of numerous decline curves, each of which resulted from choices of qi and a.

» Download example model: Band.xls

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

Production and Economic Forecast
using Exponential Decline
This model forecasts production, revenues, and present value based on exponential decline.  The exponential decline pattern for oil production is represented using the formula:

q = qI*exp(-at)
where qi is the annual production for the first year and a is the (fixed) annual percentage decline rate.

Uncertain input factors include:

• Yearly production (YrlProd), represented by a Lognorm distribution
• Decline rate (Declrate), represented by a Lognorm distribution
• GOR (constant Gas-Oil-Ratio), represented by Triang distribution
• Price of gas, represented by a Normal distribution
• Price of oil, represented by a Normal distribution
• Rate of increase of oil and gas prices, which are represented by Normal distributions embedded in the Revenue formulas.  For each year, a new sample is drawn from a new Normal distribution, modeling variation from year to year.

Operating expenses are fixed throughout the forecast. The first year expense may be thought of as capital investment.

Outputs are defined as:

• Total NPV
• OilGros, or production, for each year

Like the Band.xls model, you can generate a summary graph to see the change in OilGros over time.

Finally, a SimTable function has been used in the Discount Rate input that is used to calculate Total NPV.  This contains two possible values for Discount Rate – 12% and 14% - enabling you to run two back-to-back simulations to compare the effect of different discount rates on your Total NPV.

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

» Download example model: Declin.xls

Estimating Reserves from Horizontal Well Bore
This model represents reserves estimates involving the product of variables.  The uncertain factors, represented by Triang distributions, are:

• Fracture Recovery
• Fracture Spacing
• Horizontal Length
• Fraction Depleted
• Fraction Water Filled

The output is the Rh estimate.

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

» Download example model: Horiz.xls

Estimating Coal Bed Methane Reserves
This model represents reserves estimates involving the product of variables.  The uncertain factors, represented by Triang distributions, are:

• Drainage Area
• Thickness
• Gas Content
• Density
• Recovery Factor

The output is the reserves estimate G.

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

» Download example model: Coalbed.xls

Waterflood Prospect – Production Economics
This model examines the problem of a waterflood prospect, where recoverable must be estimated and then one of four types of observed production schedules is followed to generate a revenue stream. The model combines volumetric estimates, prices, costs, and production scheduling.

The overall objective is to estimate the Internal Rate of Return (IRR) for a waterflood project, given information about initial costs, operating costs, reservoir description, production schedules, prices, working interest, interest rate, and taxes.  The fixed startup costs are for 10 producers (300 ft depth), 30 injectors (3000 ft depth), 6 supply wells (1000 ft depth), surface lines (6000 ft of 4 in., 8000 ft of 2 in), a plant, and the lease.

Reservoir Description
We know the area of the waterflood prospect because of primary production. We need to estimate the pay thickness and porosity because of variations recorded at the well penetrations. Recovery (factor) is estimated based on other waterflood data for similar reservoirs.  Area is fixed, but net pay, porosity, and recovery are represented by Uniform distributions.

Production Schedules
Four equally likely production schedules are considered. We can think of these as a range from optimistic to pessimistic. In each case, first production is not achieved until the end of the third year.  The four possible schedules are modeled with a Discrete distribution:
=RiskDiscrete({1,2,3,4},{1,1,1,1})

This returns one of the integers 1,2,3,4.  This number is used as the "offset" entry in the Excel formulas such as:

B15: =CHOOSE(SCHEDULE,J15,K15,L15,M15)*NP*OILPRICE

Output and Simulation
The expense for each year is calculated with a Uniform distribution.

The annual discount rate is modeled with a Simtable function representing four different discount rates – 10%, 20%, 30%, and 40% - that will be used to calculate NPV.

=RiskSimtable({0.1,0.2,0.3,0.4})

Our outputs are Np and NPV.

By running four back-to-back simulations, @RISK will sample each of the four discount rates from Simtable for each simulation.  This will let you compare the effect of different discount rates on total NPV.  The internal rate of return (IRR) is the discount rate that will yield an NPV of 0 for profit. To put it differently, the IRR will yield future net income equal to initial investment. When we examine the simulation results graphs, we can look for a trend in the NPV as a function of this interest rate.

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

» Download example model: Flood.xls

Porosity and Water Saturation with Dependency
Porosity and water saturation are parameters often thought to be negatively correlated. These variables are among the data generally available for individual completion intervals as well as reservoirs. This example examines a process of estimating porosity and water saturation from electric well logs. The purpose is two-fold. First, we see how the equations that convert raw wireline log measurements into water saturation and porosity values actually impose a relationship between those two parameters. Second, we compare the effects of using uniform versus triangular distributions to generate the estimates.

Given estimates of bulk density and true formation resistivity from logs, we want to estimate formation porosity and water saturation. This problem can focus on either an interval in a given wellbore or on a reservoir with several well penetrations where we hope to describe average formation properties throughout the reservoir.  Our job is to assign @RISK distribution functions to each of several parameters, including the bulk density, formation resistivity, and others, and then deduce corresponding distributions for porosity, formation factor, and water saturation -  the outputs.

The example follows the pattern suggested by Walstrom and lets each uncertain input be represented by a Uniform distribution.  For contrast, it also examines a parallel case, using Triangular distributions for each of the uncertain parameters.  Then run your simulation and step through the output ranges.  You can compare the results of Uniform input parameters to Triangular input parameters by copying and overlaying histograms and cumulative graphs.

The overall result is quite plausible: using Triangular distributions for each of the input variables causes a much steeper CDF. This behavior would be more obvious for models where the output variables were either sums or products of the inputs.  Our model involves exponential functions, roots, and rational functions.  Nevertheless, it should seem reasonable that when we assign more probability to values of the input variables close to their means, which is what happens with the Triangular distribution compared to the Uniform, the output variables reflect the same pattern. To put it differently, when the inputs have less uncertainty (as measured by the variance), so do the outputs.

You may also want to examine the simulation statistics for the outputs. In particular, the standard deviation and variance for Phiunif and Swunif should be appreciably larger than those of their counterparts, Phitri and Swtri.

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

» Download example model: Phisw.xls

Scheduling According to Reserves Estimate
This example links both capital expenditures and the drilling schedule to the estimated size of a discovered field, or more precisely to the volume of reserves. At the top left of the worksheet (Cells A6..C9), is a simple reserve estimation using Area, Net Pay and a Recovery factor. Below that are two tables: CAPEX Schedule and Drilling Schedule.

The distributions for Area, Net Pay and Recovery (factor) are all Triangular. After deciding on the distributions to model field size, a preliminary run was made selecting Recoverable (i.e., reserves) as the output. Estimates of P5 and P95 (the 5% and 95% probability levels in the CDF) of 25 MMbbl and 115 MMbbl were found. That is, only 5% of the time would reserves be less than 25 MMbbl and only 5% of the time would reserves be more than 115 MMbbl.

Returning to the worksheet, these two "extreme" values were used as limits for an interpolation process, calling them "approxmin" and "approxmax" in cells F5 and F6. Then we found the relative size of the field compared to these two extremes by taking a ratio in cell F7:

relsize = (sampled recoverable - min)/(max-min)
Thus, we found the ratio, then made sure it was between 0 and 1. Because we used P5 and P95 as cutoff values for field size, every once in a while (10 % of the time) the field size sampled will fall outside the range from approxmin to approxmax.  We forced relsize, however, to always be at least 0.0 and at most 1.00. As described next, this insured that we would always calculate interpolated values in D17..D21.  Thus, for a relatively small field, we would plan a modest CAPEX and a modest drilling schedule. Likewise for a large field, we would anticipate a larger CAPEX and a more wells to be drilled.

The particular estimates for the quantities (of CAPEX and wells) is not the whole story. We also need to know the timing of the expenditures and the drilling activity. All those combinations are included in the four columns titled "low" and "high" - one each for CAPEX and Drilling.  Our model assumes that any discovery in between the two extremes should correspond to schedules of investment and drilling proportional to the field size. We capture that proportionality with the ratio “relsize.” We implement it by interpolating both schedules for each year. Thus, cell D17 has the formula:

=B17 + relsize * (C17 - B17)

When relsize is 0, this expression reduces to B17, the "low" case. When relsize is 1.0, the expression yields the "high" case, C17. This interpolated value (you may think of it as a weighted average also) will always give us a number in between the "low" and "high" values. Thus, the sum of D17..D21 will always lie between the
sums of B17..B21 and C17..C21. Column G is handled the same way. In fact, if you type the formula in Cell D17, being sure to use an absolute reference for relsize, you can copy the formula down D18..D21 and also to G17..G21.

Try running a simulation with 500 iterations, selecting output ranges of Recoverable oil, CAPEX schedule, Total
CAPEX, Drilling schedule, and Total number of wells. For the two schedule ranges, the results in the @RISK – Results window will be displayed as Summary Graphs.

This example was taken from Decisions Involving Uncertainty: An @RISK Tutorial for the Petroleum Industry by James Murtha, published by Palisade Corporation, where a detailed, step-by-step explanation can be found.

» Download example model: Sched1.xls

Two-Objective Prospect
This model predicts the chance of success, and range of volume outcomes (given success), of an undrilled, two-objective oil or gas prospect. The model incorporates the impact of both dependence (Does my chance of success go up in the lower objective given that I find oil or gas in the upper objective, and if so, how much?), and decision behavior (Do I need to find hydrocarbons in both zones to carry the prospect, or will just the upper and/or lower be sufficient?).

To run the model:

1) Assess chance of geologic success (Pg) and volumes for each objective independently.  Determine P1/P99 (maximum/minimum plausible) values from the reserves distribution.  Enter Pg, P1 and P99 into the model.

2) Open associated .rsk file from @RISK

3) Estimate the minimum commercial volume – the smallest volume necessary to justify completion of the prospect – and whether you think each zone is a viable standalone objective (could ‘carry’ the whole well).

4) Finally, model the chance uplift for the lower zone, if you find hydrocarbons in the upper zone. For independent zones, chance uplift is zero (Pg doesn’t change). For fully dependent zones, Pg for the lower zone would go to 100%, given success in the upper zone.

The model checks to see if the chance uplift you have modeled violates Bayesian mathematics, and if so, a red warning cell appears. There is a great deal of help embedded in cell notes, including how to interpret the results.

This example was developed by Exploration Analysis, Inc. copyright 2002. 

» Download example model: 2ZoneExample.zip

PrecisionTree Models

Oil Drilling
This oil drilling example is a classic decision tree problem.  Our first decision is whether to run geological tests on the prospective site.  Then, depending on the test results, the next decision is whether to drill for oil.  The final chance event is the amount of oil found.  The tree progresses from left to right – the decision to test is always made before the decision to drill.
For the oil drilling example, a decision node with two possible outcomes, Test and Don't Test, represents our initial decision.  For each branch from a decision node there is a label and a value.  In PrecisionTree, the values, probabilities and labels for all nodes and branches in a decision tree may be entered using the PrecisionTree interface or directly in your Excel worksheet. 

Since testing costs $10,000, the value for the Test branch is -10000.  If we don’t test, our value is 0 since there are no costs associated with that option.  Since the decision has two outcomes, two branches extend to the right of the node.   If a test is performed, a branch extends to the Test chance node, describing possible outcomes from the test.  There are three branches (or possible outcomes) from the Test chance node, each with an associated probability of occurrence:  No Structure, Open Structure or Closed Structure.

All nodes return the expected value or certainty equivalent of the node.  This value is shown in the cell beneath the node name.  The method used to calculate these values depends on the default settings for the model. 

Each branch from a decision node has a TRUE or FALSE label.  If a branch is selected as the optimum path, TRUE is shown.  Unselected branches display FALSE.

At the end of each path in the decision tree are end nodes.  The payoff and probability for each path through the tree are returned by the end nodes.  In this example, the payoff returned depends on the cost of testing, the cost of drilling and the amount of oil found.

» Download example model: Oil.xls

Oil Drilling with Formulas
This model is exactly the same model as Oil.xls, except here it is defined as a formula tree rather than a cumulative tree.  Examine both the default payoff formula defined at the tree root and the individual payoffs defined at each payoff node to see how the model works in this case.  In general, a formula tree is more difficult to construct than a cumulative tree, but far more flexible in the type of calculations it can perform.

All nodes use the default payoff formula defined at the tree root except the payoff nodes circled in red, which override the default.

» Download example model: Oil_form.xls

Oil Drilling Influence Diagram
Influence diagrams present a decision in a simple, graphical form.  Decisions, chance events and payoffs (values) are drawn as shapes (called nodes) and are connected by arrows (called arcs) which define their relationship to each other.  In this way, a complex decision may be reduced to a few shapes and lines.  Influence diagrams are excellent for showing the relationship between events and the general structure of a decision clearly and concisely.

• Nodes.  In PrecisionTree, decision nodes are drawn as green squares, chance nodes as red circles and payoff nodes as blue diamonds.  Calculation nodes are blue rectangles that take results from predecessor nodes and combine them using calculations to generate new values. 
• Arcs.  Arcs point from a predecessor node to a successor node, indicating a dependence between the two nodes.  An arc may contain different forms of influence: value, timing or structural or a combination of the three. 

You can also define the probabilities and values for the possible outcomes in the influence diagram Value Table.
The first node for the oil drilling influence diagram is a chance node named Amount of Oil.  This node, directly or indirectly, influences many of the other nodes in your model.  To set up this node, first change the name and type of the initial payoff node in the diagram to a chance node named Amount of Oil. Click on the initial payoff node to be replaced, displaying the Influence Diagram Node Settings dialog box. 

There are three possible outcomes for Amount of Oil – Dry, Wet and Soaking.  These are specified in the Outcomes tab. By clicking the Add button, a third outcome can be added to the default Outcome #1 and Outcome#2. 

» Download example model: Oil_infl.xls

Oil Drilling with Linked Trees
This example calculates the long-run cost of an oil exploration in net present value (NPV).  The tree is linked to the spreadsheet table above it.  Values in the tree, such as the testing cost, drilling cost and amount of oil found and are passed into the spreadsheet.  The result is the NPV for the project, and is passed back to the tree as its payoff values.
A decision tree is often built in conjunction with a detailed spreadsheet model that calculates the financial results of each decision option.  The decision tree is good for displaying your possible options, but usually a standard spreadsheet model is better for calculating the numeric results of each option.  Integrating these two formats is key to an effective decision analysis.  In PrecisionTree, this linking of a decision tree to a payoff spreadsheet model is called a “linked tree”.

In the linked tree in Oil_link.xls, the default location for end node payoff values is cell B20, next to the label NPV at 10%.  The Drill Decision decision node is linked to cell B11, Drilling Costs.  The branch values from this node (70000 and 0) will be placed in cell B11 as PrecisionTree calculates the payoff values of paths through the tree which include these branches.

When using a linked model, each possible path through the decision tree represents one scenario and one recalculation of the linked model.  For example, to calculate the payoffs for a decision tree with 500 end nodes (i.e., 500 possible paths through a tree), the linked model will be recalculated 500 times with 500 different sets of branch values. When calculating the value of a path across the tree, PrecisionTree:

• Inserts the value for each branch on the path into the cell or range specified for it. 
• Calculates the linked model (using the inserted values) to generate a new payoff value.
• Returns this new payoff value at the end node for the path. 

» Download example model: Oil_link.xls

PrecisionTree Combined
with @RISK

Oil Drilling
The results of the Oil Found chance node in the oil drilling decision tree Oil.xls are divided into three discrete outcomes - Dry, Wet and Soaking. But, in reality, the amount of oil found should be described with a continuous distribution. Suppose the amount of money made from drilling follows a Lognormal distribution with a mean of $22900 and a standard deviation of $50000, or the @RISK distribution RiskLognorm(22900,50000).

To use this function in the oil drilling model, change the chance node to have only one branch, and define the value of the branch by the @RISK function.  During an @RISK simulation, the RiskLognorm function will return random values for the payoff value of the Results node and PrecisionTree will calculate a new expected value for the tree.

But, what about the decision to Drill or Not Drill? If the expected value of the Drill node changes, the optimum decision could change iteration to iteration. That would imply that we know the outcome of drilling before the decision is made.  To avoid this situation, PrecisionTree has an option Decisions Follow Current Optimal Path to force decisions before running an @RISK simulation. Every decision node in the tree will be changed to a forced decision node, which causes each decision node to select the decision that’s optimal when the command is used. This avoids changes in a decision due to changing a decision tree’s values and probabilities during a risk analysis.

Using @RISK to Analyze Decision Options – Value of Perfect Information

There may be times when you want to know the outcome of a chance event before making a decision. You want to know the value of perfect information.

Before running a risk analysis, you know the expected value of the Drill or Don’t Drill decision from the value of the Drill Decision node. If you ran a risk analysis on the model without forcing decisions, the return value of the Drill Decision node would reflect the expected value of the decision if you could perfectly predict the future. The difference between the two values is the highest price you should pay (perhaps by running more tests) to find out more information before making the decision.

Running a risk analysis on a decision tree can produce many types of results, depending on the cells in your model you select as outputs. True expected value, the value of perfect information, and path probabilities can be determined. 

Select the value of a start node of a tree (or the beginning of any subtree) to generate a risk profile from an @RISK simulation. Since @RISK distributions generate a wider range of random variables, the resulting graph will be smoother and more complete than the traditional discrete risk profile.

To use @RISK:

• Change uncertain values into distributions (all cells in light red are distributions in this model)
• Add an output cell (for this model cell C44 is a good choice)
• Press @RISK's simulate icon
• Examine the results of a full blown Monte-Carlo simulation

» Download example model: OilSimulationWithRisk.xls