The Ops Accountant · Analyze · Excel

Analyze · Case study 4 · Excel

Accrual review template

Every month, last month's accruals get compared to what actually came in. This template does that comparison by well name and cost center, flags what needs a conversation, and rolls it up with a pivot. It's built on structured tables, so next month it's a paste and a refresh.

The workbook

Inputs

  • Accruals: the accrual as booked, by well, cost center and group (LOE or AFE)
  • Actuals: net invoices and operator JIBs for the accrual month
  • Wells: the well master, with status

Review

  • One row per accrual line
  • Every calculated column is a formula on the table, so rows added at the bottom calculate themselves

Output

  • A filtered list of lines to follow up
  • A pivot of variance by month and group, for the trend

The review table · August 2026

Accrued at net for August, compared to the actual net costs that came in for August. Prior is July's accrual for the same line, for context.

KeyWell nameCost centerGroupStatusPrior accrualAccrued netActualVarianceVar %Flag
Baker 1|CC-4001Baker 1CC-4001LOEProducing5,232.166,553.376,364.28189.093.0%
Clark 2|CC-5001Clark 2CC-5001LOESold—847.24920.00-72.76-7.9%Well sold: stop accruing
Davis 4H|CC-6001Davis 4HCC-6001LOEProducing26,205.5221,609.4421,859.80-250.36-1.1%
Jones Unit 1|CC-2201Jones Unit 1CC-2201LOEProducing17,559.6316,060.5517,009.01-948.46-5.6%
Jones Unit 1H|CC-2240Jones Unit 1HCC-2240LOEProducing21,981.6019,949.2219,412.84536.382.8%
Kessler 1|CC-7210Kessler 1CC-7210LOEProducing4,809.904,996.985,595.97-598.99-10.7%
Ortega 3H|CC-7105Ortega 3HCC-7105LOEProducing—6,634.976,289.75345.225.5%
Ramirez 1H|CC-7002Ramirez 1HCC-7002LOEProducing3,952.514,014.694,109.37-94.68-2.3%
Smith 1H|CC-1001Smith 1HCC-1001LOEProducing21,374.5922,620.2024,940.57-2,320.37-9.3%
Smith 2H|CC-1002Smith 2HCC-1002LOEProducing17,510.0517,235.6619,281.98-2,046.32-10.6%
Smith 3H|CC-1003Smith 3HCC-1003LOEShut-in10,680.0010,680.00623.0210,056.981614.2%Shut in: zero the accrual
Thompson 2H|CC-7001Thompson 2HCC-7001LOEProducing6,669.126,095.136,908.46-813.33-11.8%
Davis 4H|CC-6001Davis 4HCC-6001AFEProducing212,317.29197,470.00318,500.00-121,030.00-38.0%Under-accrued: update the field estimate
Harlan 1|CC-8001Harlan 1CC-8001AFEP&A in progress—23,140.6722,800.00340.671.5%
Miller 3H|CC-3105Miller 3HCC-3105AFEDrilling197,703.20381,942.20379,999.991,942.210.5%

Three lines need follow-up: Davis 4H's workover was under-accrued by $121,030 because the field estimate lagged, Smith 3H kept accruing its old average after it was shut in, and Clark 2 is still accruing after it was sold.

The formulas

Every column is a structured reference on the Review table. B1 holds the accrual month.

ColumnFormulaWhy
Key=[@[Well name]]&"|"&[@[Cost center]]One key for every lookup. Never look up on the name alone.
Status=XLOOKUP([@Key], Wells[Key], Wells[Status], "Not in well master")The if-not-found argument turns a missing well into a visible flag instead of #N/A.
Prior accrual=XLOOKUP([@Key]&[@Group], PriorAccruals[Key]&PriorAccruals[Group], PriorAccruals[Accrued net], 0)Two-part lookup on key and group, with no helper column.
Actual=SUMIFS(Actuals[Net], Actuals[Key], [@Key], Actuals[Group], [@Group], Actuals[Month], $B$1)Sums every invoice and JIB line for the well, group and month.
Variance=[@[Accrued net]]-[@Actual]Positive is over-accrued; negative is under-accrued.
Var %=IF([@Actual]=0, "", [@Variance]/[@Actual])Blank instead of a divide-by-zero error.
Flag=IFS([@Status]="Sold", "Well sold: stop accruing", AND([@Status]="Shut-in", [@[Accrued net]]>0), "Shut in: zero the accrual", ABS([@Variance])<5000, "", [@[Var %]]<=-0.2, "Under-accrued: update the field estimate", [@[Var %]]>=0.2, "Over-accrued: check the estimate", TRUE, "Review")Status checks come first, so a sold or shut-in well is flagged whatever its variance. Then a dollar floor, so small misses don't create noise.

The follow-up list

One dynamic-array formula on the Output sheet returns only the flagged lines, largest dollar miss first. It updates the moment the review table does.

=LET(
    t,      Review,
    flag,   Review[Flag],
    miss,   ABS(Review[Variance]),
    flagged, FILTER(t, flag <> "", "Nothing to follow up"),
    SORTBY(flagged, FILTER(miss, flag <> ""), -1)
)

The pivot

A pivot on the full year of the review table, variance by month and group. Under-accruals cluster in AFE months where the field estimate lagged.

MonthLOE varianceAFE varianceTotal
Loading…

Why it's built this way

All wells and figures are made up. The method follows the accrual approach on the monthly accruals page.