The Ops Accountant · Analyze · Power BI

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.

Field

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

PostedOver AFEAuthorized

Accruals vs. actual costs by month

Company net share, LOE and AFE accruals combined

ActualAccrued

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

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.