Example Models

@RISK Models

xls Projecting Oil Prices

Minimum edition: @RISK 6.0 Industrial. This model illustrates one possible way oil prices might change through time, as influenced by the market. @RISK's distribution fitting tool is used to simulate future absolute price changes based on historical daily oil prices.

xls Hydroelectric Power

Minimum edition: @RISK 6.0 Standard. This model illustrates one possible simulation of hydroelectric power generation for a 120-month horizon. There are three sources of uncertainty: monthly desired power (as a percentage of the maximum possible output), monthly rainfall, and monthly evaporation. This model is based on Roy L. Nersesian's book Energy Risk Modeling.

xls Solar and Wind Power 1

Minimum edition: @RISK 6.0 Standard. This model illustrates the daily output of a combination of solar and wind energy units. The solar unit is affected by cloud cover with a temperature adjustment. The wind unit is modeled by a random wind speed and an ouput function of this wind speed. The @RISK outputs include hourly and total daily output values for solar, wind, and combined solar and wind. This model is based on Roy L. Nersesian's book Energy Risk Modeling.

xls Solar and Wind Power 2

Minimum edition: @RISK 6.0 Industrial. This example extends the model in the file SolarAndWindPower1.xlsx. Its goal is to use RISKOptimizer to find the best combination of solar and wind units to match energy uncertain demands for a 100-day period. This model is based on Roy L. Nersesian's book Energy Risk Modeling.

xls Hedging with Oil Swaps

This model is of an oil operator who faces random oil prices and uncertainty in oil volumes over the next five years. To forecast future oil prices, @RISK's Time Series Fit tool is used to fit actual historical oil prices. Then to evaluate a hedge against decreases in oil prices, a "base" model is compared to a model with oil swaps.

xls Oil Output Smoothing

Minimum edition: @RISK Industrial. This model, based on a real consulting experience, is of an oil company that has leased a field with 10 old wells to be worked over and 15 new wells yet to be developed. It uses a standard model of exponential decline for annual outputs from the wells. The goal is to find a production schedule (when to start production of each well) to minimize the variability of year-to-year oil outputs.

xls Oil Pipeline Risks

This model simulates risks in a network of oil pipelines. There are nine types of risks, and there are nine routes in the pipeline network. Each route has three characteristics: diameter, mean pressure, and distance. For each type of risk and each route, two quantities are simulated: the number of events where that risk type occurs and the typical magnitude of such a risk. These are then accumulated to find the severities of the risk types, by route and total over all routes.

xls Oil Transportation with Weather Disruptions

An oil rig in the Gulf of Mexico serves as an inventory stocking point for several other platforms. Crude oil is generated daily. Some of it is shipped to the coast, some is sent through a pipeline, and the rest is stored at the rig. The potential crude output is constrained because of storage capacity. If the inventory after shipping and pipeline amounts would be greater than the storage capacity, the crude output is decreased so that the final inventory that day is equal to storage capacity. There are also severe weather events that interrupt shipping. These events occur at random times and have random durations.

xls 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.

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.

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.

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.

xls 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.

xls Volumetric Reserves 4 - Austin Chalk
       Horizontal Drilling Model

This model is similar to other volumetric analyses in this series, but the context now is the Austin Chalk horizontal well, as reported in a Journal of Petroleum Technology article. There are now five uncertain inputs that are multiplied or divided to obtain the Reserves output.

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.

xls Waterflood Project with Production Scenarios

This model analyzes a waterflood project, where recoverable oil must be estimated and one of four production schedules is used 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 the project over an 18 year horizon, given information about initial costs, operating costs, reservoir description, production schedules, prices, working interest, and taxes.

xlsCorrelation between Porosity and Water Saturation

Two important characteristics of rocks are porosity (percentage of "open space") and water saturation (fraction of water in the pore space). This model uses simulation and structural properties to "derive" the commonly observed negative correlation between these two characteristics. This finding could then be applied to a typical volumetric analysis for the amount of oil reserves.

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.

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. 

PrecisionTree Models

xls 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.

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.

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 OilDry, 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

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. 

PrecisionTree Combined
with @RISK

xls 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

130 East Seneca Street
Suite 505
Ithaca, NY 14850
800 432 RISK (US/Can)
+1 607 277 8000
+1 607 277 8001 fax
Palisade EMEA & India
+44 1895 425050 salesEMEA@palisade.com
Palisade Asia-Pacific
+61 2 8249 8239
Palisade アジア・
+81 3 5456 5287 tel
Palisade Latinoamérica
+1 607 277 8000 x318
+54-1152528795  Argentina
+56-25813492 Chile
+507-8365675 Panamá
+52 55 5350 2852 México
+511-7086781 Perú
+57-15085187 Colombia
Palisade Brasil
+55 (21) 3958 1443
+1 607 277 8000 x318 tel