Retail

Sales reconciliation: why £445 in retail exports becomes £275 in the sales report

Rebuild a monthly sales total from six invoice arrivals. Identify a repeated line, a price correction and a genuine credit.

5 min readBy Mayur Kale

Download the retail CSV and executable SQL. The six arrivals are £120, £80, £80, minus £20, £90 and £95. Their sum is £445. Before looking at the formulas, decide which records a ledger should retain. The two £80 deliveries share line inv-102. The £95 entry revises inv-104. The minus £20 is a separate credit. Removing the repeated £80 and superseded £90 leaves £275.

North contributes £120 minus £20 plus £95, or £195 across three lines. South contributes £80 on one line. Check counts and subtotals independently. Moving the £80 line to North leaves the grand total untouched while making both regional reports wrong. A control over the grand total alone would accept that defect.

In a terminal with SQLite installed, run the downloaded script against a fresh in-memory database. The script creates the fixture, ranks revisions by line, and groups the surviving rows. Its final column contains pence.

sqlite3 :memory: < sales.sql

The expected output is September North with 3 lines and 19500 pence, followed by South with 1 line and 8000 pence. To test the workbook independently, change the latest inv-104 value from 9500 to 9600. The reported total should rise by 100 pence. Change the superseded value instead and the result should stay fixed. These are stronger checks than matching the original screenshot because they predict behaviour under a controlled change.

Download the example workbook. The formulas cover the supplied rows. Extend the ranges before adding new records.

Start with the number finance is trying to close

Imagine a shop manager arriving at the Monday trading meeting with the £445 export. Finance brings £275. It is tempting to ask IT which dashboard refreshed last. Both figures could have been refreshed a minute ago. One person has added everything that came out of the till interface. The other has added the sales lines that were current at close. Before anyone changes a formula, ask each person to point to one amount in their total and show what happened to it on the shop floor.

Take the £80 first. Two different shoppers can spend £80 on the same morning. A member of staff can also press the export button twice. Removing duplicate amounts would treat those situations alike. Look for an identifier the till assigns when a sale line is created. Bring the original receipt into the investigation and check whether the identifier stays the same when that receipt is reprinted. If receipt numbers restart at each shop, bring the shop identifier too. Write the rule in terms a manager can test with two paper receipts.

Now take the £90 and £95. Ask whether the second amount replaces the whole earlier line or records an additional charge. In the supplied file it replaces the line. A different till could legitimately export a £5 adjustment as a separate event. Applying the replacement rule to that system might turn a £95 sale into a £5 sale. Request a genuine example of a correction from the system owner before adopting a rule across every shop. Store the answer with the feed specification so that a new finance analyst can find it at the next close.

Explain the difference in the order it occurred

A good reconciliation meeting is quite short when everyone can follow the money. Write £445 at the top of a sheet. Cross out the second delivery of the £80 line and write £365 beside it. Cross out the superseded £90 and write £275 beside that. Circle the negative £20 and leave it in place. A colleague should be able to trace each mark to a record, identify who supplied it and repeat the calculation without opening the dashboard.

That little bridge also gives you a way to diagnose tomorrow's failure. If a report unexpectedly shows £365, inspect how it handles revisions. If it shows £295, inspect the treatment of the credit. Neither amount proves a particular defect. Each gives the investigator a specific place to begin. A monitoring alert that says only that the reports differ leaves the morning shift with substantially more work.

Suppose the business also wants settlement reconciliation. Ask for a separate export of payouts, fees and settlement dates. A card sale on Friday might be part of a later deposit together with other sales. Splitting a payment between a voucher and a card can create two tender records against one basket. Joining each tender to every basket line can multiply sales. Establish the population on each side before trying to explain a difference to the bank. The six example lines answer a sales question. They contain no evidence about cash received.

Make month end a reproducible event

Choose an exact cutoff with finance and record the timezone. Give the close a name that identifies both the trading period and the version of the data used. Save the source files or an immutable reference to them. A monthly label alone cannot distinguish the report handed to the directors on Tuesday from the amended version prepared on Thursday. A finance colleague checking last month's pack should be able to retrieve both the approved figure and its explanation.

Run a tabletop exercise with a late credit. Suppose September has been signed off and a £15 credit arrives on 2 October with a September posting date. One possible rule restates September and asks the owner to approve £260. Another keeps September at £275 and discloses the £15 in a later adjustment schedule. Have finance choose which view to publish and how to label the other. Test the choice by rerunning the September report after the credit has arrived and comparing what a reader sees with what finance approved.

Some differences need a person. Two records might share the same sale, revision and shop while disagreeing about price. Preserve both inputs, show the conflicting amounts and send the row to the owner who can consult the till history. Picking the larger amount or whichever record came last hides a decision in the software. Keep the last approved report accessible while the owner decides whether the conflict affects the close.

Give the shop manager a meaningful acceptance test

Start with two shops and the smallest period that includes a sale, a return and a correction. Ask finance for subtotals calculated outside the proposed reporting process. Keep the number of accepted lines beside each subtotal. Two offsetting errors can leave the pounds looking right while the line counts reveal that the underlying mix has changed. If the business reviews performance by department, run one control at department level as well.

Next, send exactly the same file twice. The source history should show two deliveries while the approved sales total stays still. Then send a genuine additional £80 sale with a new line identity. The accepted total should increase. Those two checks together are more informative than a large file with thousands of unique amounts. They demonstrate that the system can recognise a repeat delivery without suppressing legitimate trade.

Finish by asking the manager to answer a practical question using the output. Which shop needs to review its returns, and which lines support that discussion? Give them the region, accepted amount, credit indicator and a route back to the relevant business record. If reaching a receipt still requires a developer to run a private query, the calculation may be ready while the handover still needs work.

Decide what should wake someone up

Set alerts around the close that the team actually runs. An empty delivery at nine in the morning could be entirely normal for a shop that opens at ten. The same empty delivery after a busy Saturday deserves investigation. Compare incoming shops with a trading calendar and an expected delivery schedule. Keep a distinction between a shop that reported zero sales and one whose file has yet to arrive. The second case leaves the total incomplete even when every received line is valid.

For the finance owner, prepare a compact close note with the accepted total, the shop count, the cutoff and any unresolved amount. For the operator, keep the input filenames, failed checks and the records waiting for review. Give each reader an example of how to use their view. Finance should be able to decide whether to release the pack. The operator should be able to locate the missing shop, recover its delivery and show exactly which published figure changed as a result.

Turn the trend into shipped software

I contract as a senior fullstack and data engineer, building the production systems your customers and your reporting depend on.

Explore services↗