Below are several examples of custom applications written by Palisade Custom Development. All were written using @RISK’s XDK in Excel.
In this example, an application was created using @RISK to generate the aggregate loss resulting from multiple large or catastrophic losses occurring within a given period of time. Aggregate losses are determined gross and net of any Excess of Loss Reinsurance.
Parametric distributions for frequency and severity of loss gross of reinsurance must be specified by the user. Frequency and severity are assumed to be mutually independent.
In addition, the user can specify an Excess of Loss Reinsurance program. The following features of the reinsurance program must be specified:
- Number of layers
- Limit and retention by layer
- Reinsurance premium by layer
- Number of reinstatements by layer;
- Reinstatement premium as percentage of reinsurance premium
The output includes the aggregate loss gross and net of reinsurance, and the reinsured loss.
This application is powered by @RISK and was built with the collaboration of Waszink Actuarial Advisory.
In this example, @RISK is used for cost risk analysis and estimation. The application prompts the user for a three-point estimate for each cost item in the project as a way to recognize uncertainty in these cost elements. A risk register is created using a simply colored grid interface. Next, because in real life costs are seldom independent of each other, the user is able to set up correlations between related cost elements. Finally, the user can define external risk events that will affect the total cost of the project.
After entering this information, the application runs an @RISK simulation and presents results in tables and graphs. The results can be used to identify risk drivers and map out mitigation strategies.
View a brief movie illustrating the steps to build a Cost Risk Analysis model:
In this example, @RISK is used to analyze the investment of funds for retirement planning. The application prompts the user for portfolio parameters and profile characteristics of the client.
Once the information is entered, the application runs an @RISK simulation to assess the performance of the portfolio in future years as well as the effects of various withdrawal rates after retirement. Results are presented in tables and graphs.
This is an example of the use of @RISK automation applied to stock portfolio optimization.
Oil companies need to assess new fields or prospects where very little hard data exists. Based on seismic data, analysts can estimate the probability distribution of the reserve size. With little actual data available, companies still must quantify and optimize the Net Present Value (NPV) of this asset. The number of wells to drill, the size of the processing facility, and the plateau rate of the field must all be optimized.
This analysis can be simplified by representing the production profile by three phases:
(1) Build up: The period when wells are drilled to gain enough production to fill the facilities.
(2) Plateau: After reaching the desired production rate (plateau), the period when production is continued at that rate as long as the reservoir pressure is constant and until a certain fraction of the reserves is produced. In the early stages of development, this fraction can only be estimated, and production above a certain rate influences plateau duration.
(3) Decline: The period when production rates, P, decline by the same proportion in each time step, leading to an exponential function: P(t) = P(0) exp(-c*t), where t is the time since the plateau phase began and c is some constant.
With only estimates for the total Stock Tank Oil Initially In Place (STOIIP = reserve size) and percent recovery amounts, the objective is to select a production rate, a facility size, and well numbers to maximize some financial measure. In this example, the measure used is the P10 of the NPV distribution. In other words, the oil company wants to optimize an NPV value which they are 90% confident of achieving or exceeding.
As described, the problem is neither trivial nor overly complex. A high plateau rate doesn’t lose any reserves, but it does increase costs with extra wells and larger facilities. However, facility costs per unit decrease with a larger throughput, so choosing the largest allowed rate and selecting a facility and number of wells to match might be appropriate.
This example allows you to import Microsoft Project data to Excel to recreate precedence relationships within your spreadsheet and perform an integrated risk analysis with @RISK in Excel. The example assumes that all relationships are Finish-Start (with no lags) and that the project is using a 7 day/8 hour calendar.
In addition, you can define three-point estimates for cost and duration of project activities, and create a risk register for the analysis.