What you need to know:
- We are sharing a Power Bi report designed for a fictitious, junior oil and gas company.
- It combines data from multiple sources to identify cash performance of individual wells
- Once constructed, a simple refresh will update the report, allowing future analysis to be greatly simplified.
We want to share a report which we have been using for some time now. It is designed for the Executives of a fictitious, junior oil and gas company, ExplorCo, but is based on components from actual reports constructed for clients. Note all the data (except for Well ID) are fictitious and any resemblance to real data is purely coincidental.
Power BI Report
A quick walk through each page will illustrate the story we wish to present.
Current Production
This is designed to be a one-page snapshot of current production. Production data is daily and would be updated via a direct connection to a SCADA system or field data capture systems such as Peloton. Current production is in the top left, next to a breakdown by product (oil and gas). These visuals also compare current production to company targets. This is a quick way to observe the relative contribution of each commodity, especially important in a low gas price environment. ExplorCo has ten wells, and we list the individual production (Wells on Production) next. Individual well production is then broken out by commodity (Production Mix).
On the bottom, we repeat the individual well data, only this time we have categorized (colour coded) the wells by Property. This is useful for those who do not automatically think in well locations (translation, anyone in Finance/Accounting). Finally, we have added the wells and production to a map. The circle size is directly related to current production, and color is once again tied to property. If you are walking through the actual model, you will not that as you click on any of the individual items in a visual, all the other visualizations will change to highlight the same selected well.
Production
On this page we have added a couple of graphs to illustrate historical production. On the top is total production. The bottom graph illustrates the same, only additional detail has been added – production mix by commodity. Off to the right we have a table of historical production, by month and commodity.
Production(detailed)
This page offers another view of the trend in production. Here we have summarized the historic production by formation (top graph) and then by property on the bottom graph. On the right we have included the monthly list of production broken out by commodity.
Production Actual to Forecast
In any organization, all activity begins with planning and management will want to know how they are performing to those plans. The better teams we’ve had the fortune to work with use this information, both good and bad, to re-examine their forecasting efficiency.
That said, we have presented production actuals to forecasts. We can see that ExplorCo has been missing its oil targets for some time, and has recently been missing it’s gas forecasts.
Capital Expenditure
The next two pages examine the capital costs associated with drilling ExplorCo’s ten wells. One feature I find particularly useful is the ability to drill down into the individual costs. At the top level you will see the total cost associated with individual wells. Drilling down a level (by selecting the double arrow button in the top left) you will see the breakdown by Drilling and Completions. Next you will see larger groups of cost categories, followed by the individual detailed capital accounts. In a real-world example, you have the ability to go down to the invoice level which can facilitate a review of costs by supplier. Having a report like that available to Operations managers will quickly enable them to compare their suppliers.
Revenue
Next, we proceed to revenue. In this example we have a price file for daily commodity prices. In the background we are calculating daily revenue, but for our report, we are aggregating to the monthly level. At the bottom we have summarized revenue by formation and property. When you select an individual month in the graph, the monthly totals for formation and property are updated below. This will also be reflected in the monthly list on the right side of the page.
Cash Flow
The next page gets us closer to being able to make some decisions from our data. On this page we have combined the revenue and cost data to examine how individual wells are performing from a cash perspective.
On the left we have ranked the wells according to monthly cash flow, with the well(s) performing poorly at the top of the page, as we want to highlight these to management. Beside that we have added the revenue and expenses for the month for each individual well. Of particular concern will be the expense items, so we have added drill down capability to this table. Using the drill down tool at the top left of the Expense table we can drill down 3 levels to identify the largest cost items for a well. In a similar manner to the capital costs (although not shown) we can then compare the results well by well and see which items stand out. Being able to drill down to a single cost item, the manager or analyst, can quickly identify the supplier responsible and perform their inquiries.
Conclusion
That summarizes the report in its current form, which leads us to two(2) go forward benefits of reports such as these.
The first is once constructed, future inquiries can quickly be examined, rather than a repeated, investigative exercise anytime a well is performing poorly.
Secondly, if changes are required, it would only take an analyst a short period of time to make those changes and publish the report to the cloud (in this case, the Power BI service). This would be unnoticeable to management– all they would see are the changes they requested to the next time they opened the link or viewed it on their phone.
If you have any questions on how to increase your efficiency in financial modelling, reporting, data analysis, or if you would like to have free consultation give us a call at (403) 361-1754 or via the form on our Contact page.