Lesson 15 of 15 · Lab

How to reconcile two reports that disagree

A table is ready for analysis when you can account for every row and every euro in it, and say what it still doesn't know. A successful run doesn't prove that. Test it by changing one rule and predicting the result, then close a bridge from the old figure to the new one.

Fictional Vandelay Coffee Supply's rules from this course are collapsed into one flow here, starting at the 16 deduplicated invoices with their customers. Max Thrust, the founder, has one request left: "Show me why I should believe this."

Move the start of the rule back to June 1, which readmits INV-1001. It's €1,200 at list, from Starhopper. What does net become?

€12,558, up €1,080, not €1,200, because Starhopper's rebate applies. If you can predict a rule's effect to the euro, you understand the table.

Change a rule and predict the result

  1. Filter holds the finished rule in this live flow in Strata, Oplema's browser app for data prep. OUTPUT TRUE has 13 rows.
  2. Select Summarize, the lower block at the end. net is the net value, the quarter's total after rebates, and priced is one short of rows.
  3. Select Filter. In Expression, double-click 07 in 2026-07-01 and type 06. Press Escape to leave the editor.
  4. Select Summarize and compare net with your prediction.
  5. Put the date back to 07, and check that Summarize returns to where it started.
  6. The upper block at the end is Vista Source. It publishes the table under the name vandelay_q3_invoices, ready for dashboards in Vista, Oplema's browser app for dashboards.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

Reconcile two reports with a bridge

A bridge walks one number to another in named steps. If it closes, you have explained the difference and not just replaced it. Both reports reach €11,478.

From Don Tremendous, SalesAmountRunning
Everything stacked, 18 rows€15,850
Overlap duplicates, INV-1008 and INV-1010-€1,440€14,410
Pending, INV-1016-€1,300€13,110
June, INV-1001-€1,200€11,910
Starhopper's 10% rebate-€432€11,478
From Sydney Swell, FinanceAmountRunning
Her lookup, net of rebate€10,168
Dunder Mifflin's doubled invoices-€1,690€8,478
Initech restored+€3,000€11,478

Rows close too: 18 stacked = 2 duplicates + 3 rejected + 13 finished. North €5,578, South €5,900, South by €322.

Template: reconciliation bridge

Copy this for your own reports, one row per correction. Practice on the raw July-August invoices, September invoices and customer register.

StepAmountRunning
The report's total
Named correction, with the rows it touches
Another correction, until nothing is left
The agreed total
Rows: stacked = removed + rejected + finished

In Excel, the bridge is this table with a running SUM, and Power Query keeps the applied steps beside it. In SQL, run COUNT(*), COUNT(amount) and SUM(amount) after each step. A bridge that needs an "other" line hasn't closed yet.

Hand over the table and its limits

The total is ready to hand over, with its limits stated. INV-1014 is still unpriced, so the true figure is higher. Excalibur has no Q3 invoices, and nobody has said why. Regions use the current register, so Dunder Mifflin's July invoice counts as North. South's lead rests on Initech, one new customer with two invoices.

A contract for the next person fits in one line: one row per booked Q3 invoice, 13 rows, 12 priced, net value in euros, regions as of the current register.

You send it all to Max. Don's total was too high, Sydney's region was wrong and you can show each step. He reads it, and asks the question this table can't answer alone.

"So where does the hire go?"

Course complete Now use your own file.

The prepared Q3 table for fictional Vandelay Coffee Supply, with both reports reconciled to the euro, and a bridge template for your own files.