Analyze · Case study 2 · Power BI
Operations accounting dashboard
Three questions a JIB supervisor answers every month: which wells cost the most per barrel, how close the accruals came, and which AFEs are running out of room. This page recreates the dashboard in your browser from the same sample data model; the DAX behind each number is below.
LOE per BOE by well
Year to date, 100% of LOE ÷ BOE (6 Mcf = 1 BOE)
AFE spend vs. authorized
Operated AFEs, posted cost to date
Accruals vs. actual costs by month
Company net share, LOE and AFE accruals combined
The data model
A star schema keyed on well name + cost center, the same key used everywhere on this site, so a cost or a barrel can never land on a lookalike well.
Wells (dimension)
One row per well name + cost center: type, operator, field, status, royalty burden, company WI. Key column WellKey.
Calendar (dimension)
One row per accounting month, related to every fact table's month.
Facts
Invoices, JIB lines, non-op JIBs, production, revenue and accruals, each related to Wells on WellKey and to Calendar on month.
DAX measures
WellKey = Wells[well_name] & "|" & Wells[cost_center] -- calculated column, same on every fact table
LOE (100%) =
CALCULATE ( SUM ( Invoices[amount] ), Invoices[cost_type] = "LOE" )
+ CALCULATE ( SUM ( NonopJIB[gross_amount] ), NonopJIB[cost_type] = "LOE" )
BOE = SUMX ( Production, Production[oil_bbl] + Production[gas_mcf] / 6 )
LOE per BOE = DIVIDE ( [LOE (100%)], [BOE] )
Accrued = SUM ( Accruals[accrued_net] )
Actual = SUM ( Accruals[actual_net] )
-- Dollar-weighted error, so a $10K miss on a shut-in well doesn't outweigh a $120K miss on an AFE
Accrual WAPE =
DIVIDE (
SUMX ( Accruals, ABS ( Accruals[accrued_net] - Accruals[actual_net] ) ),
[Actual]
)
AFE Posted = CALCULATE ( SUM ( Invoices[amount] ), NOT ISBLANK ( Invoices[afe_no] ) )
AFE % Spent = DIVIDE ( [AFE Posted], SELECTEDVALUE ( AFEs[gross_amount] ) )
AFEs Over = COUNTROWS ( FILTER ( VALUES ( AFEs[afe_no] ), [AFE % Spent] > 1 ) )
Reading the dashboard
- Harlan 1 has the highest cost per barrel; it was producing very little before its P&A, which is the story the chart should tell.
- Davis 4H's workover AFE is the one bar past its authorized tick. That's a supplemental AFE before the next JIB.
- Accruals track actuals except where an AFE's field estimate lagged; hover the months where the lines split.
The charts are drawn from an in-browser SQLite copy of the sample data; nothing is sent anywhere. All wells, owners, vendors and figures are made up.