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
- Filter holds the finished rule in this live flow in Strata, Oplema's browser app for data prep. OUTPUT TRUE has 13 rows.
- Select Summarize, the lower block at the end.
netis the net value, the quarter's total after rebates, andpricedis one short ofrows. - Select Filter. In Expression, double-click
07in2026-07-01and type06. Press Escape to leave the editor. - Select Summarize and compare
netwith your prediction. - Put the date back to
07, and check that Summarize returns to where it started. - 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, Sales | Amount | Running |
|---|---|---|
| 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, Finance | Amount | Running |
|---|---|---|
| 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.
| Step | Amount | Running |
|---|---|---|
| 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.