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 monthWells: 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.
| Key | Well name | Cost center | Group | Status | Prior accrual | Accrued net | Actual | Variance | Var % | Flag |
|---|---|---|---|---|---|---|---|---|---|---|
| Baker 1|CC-4001 | Baker 1 | CC-4001 | LOE | Producing | 5,232.16 | 6,553.37 | 6,364.28 | 189.09 | 3.0% | |
| Clark 2|CC-5001 | Clark 2 | CC-5001 | LOE | Sold | — | 847.24 | 920.00 | -72.76 | -7.9% | Well sold: stop accruing |
| Davis 4H|CC-6001 | Davis 4H | CC-6001 | LOE | Producing | 26,205.52 | 21,609.44 | 21,859.80 | -250.36 | -1.1% | |
| Jones Unit 1|CC-2201 | Jones Unit 1 | CC-2201 | LOE | Producing | 17,559.63 | 16,060.55 | 17,009.01 | -948.46 | -5.6% | |
| Jones Unit 1H|CC-2240 | Jones Unit 1H | CC-2240 | LOE | Producing | 21,981.60 | 19,949.22 | 19,412.84 | 536.38 | 2.8% | |
| Kessler 1|CC-7210 | Kessler 1 | CC-7210 | LOE | Producing | 4,809.90 | 4,996.98 | 5,595.97 | -598.99 | -10.7% | |
| Ortega 3H|CC-7105 | Ortega 3H | CC-7105 | LOE | Producing | — | 6,634.97 | 6,289.75 | 345.22 | 5.5% | |
| Ramirez 1H|CC-7002 | Ramirez 1H | CC-7002 | LOE | Producing | 3,952.51 | 4,014.69 | 4,109.37 | -94.68 | -2.3% | |
| Smith 1H|CC-1001 | Smith 1H | CC-1001 | LOE | Producing | 21,374.59 | 22,620.20 | 24,940.57 | -2,320.37 | -9.3% | |
| Smith 2H|CC-1002 | Smith 2H | CC-1002 | LOE | Producing | 17,510.05 | 17,235.66 | 19,281.98 | -2,046.32 | -10.6% | |
| Smith 3H|CC-1003 | Smith 3H | CC-1003 | LOE | Shut-in | 10,680.00 | 10,680.00 | 623.02 | 10,056.98 | 1614.2% | Shut in: zero the accrual |
| Thompson 2H|CC-7001 | Thompson 2H | CC-7001 | LOE | Producing | 6,669.12 | 6,095.13 | 6,908.46 | -813.33 | -11.8% | |
| Davis 4H|CC-6001 | Davis 4H | CC-6001 | AFE | Producing | 212,317.29 | 197,470.00 | 318,500.00 | -121,030.00 | -38.0% | Under-accrued: update the field estimate |
| Harlan 1|CC-8001 | Harlan 1 | CC-8001 | AFE | P&A in progress | — | 23,140.67 | 22,800.00 | 340.67 | 1.5% | |
| Miller 3H|CC-3105 | Miller 3H | CC-3105 | AFE | Drilling | 197,703.20 | 381,942.20 | 379,999.99 | 1,942.21 | 0.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.
| Column | Formula | Why |
|---|---|---|
| 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.
| Month | LOE variance | AFE variance | Total |
|---|---|---|---|
| Loading… | |||
Why it's built this way
- Structured tables, not ranges. New rows pick up every formula, and references read as words instead of cell addresses.
- XLOOKUP over VLOOKUP. It doesn't break when columns move, it looks in any direction, and its not-found argument documents the exception.
- The key is well name + cost center. Same rule as every other tool on this site.
- Flags say what to do. "Update the field estimate" sends the reviewer to the right person; "Review" alone doesn't.
- The numbers come from the same data. The table above and the pivot below are the same accruals the SQL query and the dashboard use.
All wells and figures are made up. The method follows the accrual approach on the monthly accruals page.