To evaluate risks such as O&G production, derivatives, commodities, and operating expenditures, the Exploration and Production (E&P) section of Petrobras developed an in-house system for corporate-wide use called Progride. To get around limitations within Progride and handle risk analysis on integrated projects as a whole, Petrobras integrated @RISK and Progride within Excel.
The Challenge
In terms of its market value, Petrobras, the Brazilian oil producer and refinery operator, is the sixteenth largest global oil and gas company in the world. Its investments amount to more than $69 billion, and these include more than thirteen thousand productive oil and gas wells and 112 production platforms. (At the time this case study was published, Petrobras was the eighth largest global company in the world and its investments amounted to more than $53 billion.)
Recently the company implemented a corporate-wide protocol for evaluating the economic risks associated with potential investments. Key risks of interest to Petrobras include those associated with: the production of oil and natural gas; the demand for derivatives; the prices of various commodities; the start dates for various operations; and changes in company capital (CAPEX) and operating (OPEX) expenditures. In performing analysis of these risks, Petrobras looks to several key indicators: expected NPV; the standard deviation of NPV to get a sense of the risk associated with the reward; the probability of a negative NPV, or of losing money; and the 95% Value at Risk (VAR) of the NPV, which is the minimum amount that Petrobras stands a 5% chance of losing.
Petrobras's Progride System
To evaluate these risks, the Exploration and Production (E & P) section of Petrobras developed an in-house system for corporate-wide use called Progride. Progride is based in Excel and uses customized C++ subroutines to perform it analyses. The company uses it to model a variety of E & P applications, including: oil trends like prices and spreads; income taxes and royalties; depreciation, residual value, and economic life of a project; sensitivity analysis; and other internal economic indicators. Progride also has risk analysis capabilities.
The system is capable of handling complexities such as multiple dependent projects (i.e. a base project plus complementary projects) within the same geographic site or “concession.” A concession represents the right for Petrobras to drill in a specific area, and typically carries with it obligations of rent, tax, and royalty payments. Proglide can also manage anticipated future production projects as well as projects that reduce operating expenses (i.e. have “negative” future operating expense).
Conventional Progride Analysis
E & P analysts at Petrobras enter a variety of data to run a conventional Progride analysis, making assumptions for what is uncertain. These data include: discount, inflation, tax and exchange rates; operation start dates; depreciation time; oil prices; production curves at the site for oil, natural gas, and water as well as water injection; capital expenditures such as drilling, surface installations, and collection; fixed and variable operating costs; and the cost of abandonment.
From such an analysis, Progride returns the NPV, IRR, and project return time; the best date for project abandonment; and the financial exposure to the company. However these results are based on best-guess point values in the input data that are assumed to be 100% certain. Thus risk is not taken into account.
Risk Analysis in Progride
For a risk analysis in Progride, analysts must construct distributions to represent ranges of possibilities for key input variables. These include: probabilistic production curves at the site for oil, natural gas, and water as well as water injection; unit production operational capacity; distributions for deviations in each CAPEX and OPEX component; and a distribution for the operation’s start date.
From this risk analysis, Progride provides the expected NPV of the project along with a histogram and measures of dispersion, or risk, around the expected NPV. It also estimates the probability of a negative NPV, or of losing money on the project.
Limitations of Progride
As useful as Progride is to Petrobras, it has a number of significant limitations that led the company to look to @RISK. For one, Progride’s risk analysis does not provide user access to the simulation data, preventing analysts from getting a closer look at different probabilistic scenarios. Furthermore, Progride cannot effectively handle more complex projects, such as those done in partnership with other companies, projects that share common infrastructure, or integrated projects with multiple concessions.
Figure 1
To illustrate the effect of these limitations, consider an integrated development project with production from two concessions, or specific sites. Petrobras invests in a single shared production oil flow unit to handle both concessions – a shared CAPEX. However, the company must treat each concession individually for Brazil’s National Petroleum Agency (ANP) for purposes such as area rent, royalties, and R&D. See Figure 1.
In such a situation, risk analysis must be done on the integrated project as a whole. There are risk factors shared by both of the concessions, such as prices and CAPEX. And, there are independent risk factors, such as production and project start dates. However, some costs should be treated individually for each concession, like area rent, royalties, and R&D. Certain types of depreciation (on wells, for instance) should also be calculated for each concession as well. Multiple-concession projects are fairly common, yet Progride cannot analyze them without running for thousands of hours – an impractical, and potentially inaccurate, prospect.
Figure 2
Figure 3
Figure 4
The @RISK-Progride Solution
To address this problem, Petrobras E&P brought in @RISK. Rafael Hartke, who heads up financial planning and risk management for Petrobras’s investment operations, has developed a method to integrate @RISK with Progride, all in Excel. This system allows him to make an accurate and comprehensive assessment of a production platform with multiple concessions—and to save a huge amount of run time.
For the multiple concession project described above, the company sets up three separate Progride analyses – one for concession A, one for concession B, and one for the integrated project A+B. Then, analysts model probabilistic scenarios in @RISK for both the shared risk factors (prices, CAPEX deviations, e.g.) and the independent risks (production and start dates of operation, e.g.) for each concession. A conventional Progride analysis is performed for each concession individually, using the probabilistic scenarios generated by @RISK as input data. This analysis is performed while running @RISK by calling a macro that runs Progride during the @RISK simulation. The results of these two conventional concession analyses form the probabilistic scenario for each (e.g. production, revenue, CAPEX, OPEX, fiscal costs, and depreciation). See Figures 2 and 3.
These individual concession results are then summed together to determine the probabilistic scenarios for the integrated project as a whole (A+B). Next, a conventional Progride analysis is performed on the integrated project in @RISK using the combined probabilistic scenarios as entry data. Desired risk indicators for the entire project are then calculated from the analysis results. See Figure 4.
Easy as 3-2-1
The @RISK-Progride process can be summed up in 3 stages, 2 functions, and 1 macro.
The 3 stages of the process are:
- Use @RISK to model and generate probabilistic scenarios for the shared and independent risk factors.
- Link the probabilistic scenarios generated in @RISK to the input data of each concession’s conventional Progride analysis using the Excel’s Offset and @RISK’s RiskCurrentIter functions.
- Execute each Prograde conventional analysis individually in @RISK, calling the macro which executes the Progride conventional analysis after each @RISK iteration.
The two primary functions used to accomplish the integration between @RISK and Progride are:
- RiskCurrentIter(): This is an @RISK Statistics function which returns the value of the current iteration of the simulation while running.
- Offset(cell ref, rows, columns): This is an Excel function which returns the value of a cell located a specified number of rows and columns from a particular cell reference.
Finally, there is only one macro needed in the process. @RISK is set to run this Excel macro after each iteration recalculation during a simulation. The macro simply runs Progride and contains the code: SendKeys “%PX”, True
EBenefits of the @RISK-Progride Solution
Petrobras’s integrated solution gives the company a number of valuable benefits. First, it permits the separation of probabilistic scenarios for shared risk factors common to the projects being analyzed. Second, the use of @RISK allows the analysis of projects undertaken with partners. Similar to the above example, a separate Progride analysis is done for each partner, treating the fiscal aspects individually, but sharing the project risk factors. Third, as we’ve just seen, this approach enables the analysis of integrated projects with multiple concessions, with a separate Progride analysis for each concession that treats the fiscal and depreciation aspects individually while sharing project risk factors. Similarly, projects that share infrastructure with other, separate projects or concessions can now also be modeled in the same manner. Lastly, @RISK helps Petrobras reduce the calculation time for projections that used to take thousands of hours to a single day. If you multiply the saved hours by staff pay rates, you can get a pretty good picture of some huge cost savings.