Stock reconciliation: 8 ways to automate

What a stock reconciliation covers

A stock reconciliation compares the stock you counted with the stock you should have, and explains the difference. What you should have is opening stock, plus deliveries and anything transferred in, minus sales, waste, staff use and anything transferred out. The gap between that figure and the count is the stock variance. A bar might call the whole job a bar stock take. A kitchen calls it a food stock check. A shop or warehouse calls it reconciling the stock.

A stock count is only the first half. It tells you what is on the shelf. The reconciliation tells you whether that is what should be there, and if not, where the rest went. Most variances have dull causes: a delivery that was never booked in, a transfer to another bar nobody recorded, waste that was thrown away but not written down. Find those first, before anyone decides something was stolen.

The UK rules on stock records

Limited companies have to keep stock records. GOV.UK's guide to company and accounting records says a company's accounting records must include the stock it owns at the end of the financial year and the stocktakings used to work out that figure. The duty comes from section 386 of the Companies Act 2006, which applies to any company whose business involves dealing in goods. HMRC can check these records, and the same guide says they must be kept for six years.

Bars have one more thing to get right. GOV.UK's guidance on specified quantities says gin, rum, vodka and whisky sold by the glass must be in 25ml or 35ml measures, or multiples of them, but not both on the same premises. Wine by the glass has its own sizes. That matters for the reconciliation, because sales come off the till in measures and the count is in bottles. Convert both to the same unit before comparing, or the variance is wrong before you start. The same is true of pour cost, the cost of what was poured as a share of what it sold for.

What makes a reconciliation useful

A variance figure on its own does not tell you what to do. A useful reconciliation lists the lines that are out, gives a reason for each, and is checked by someone else. The versions below add those steps one at a time.

#1 - The stock reconciliation

Who it's for: One person reconciling one bar, kitchen or store.

What it is: A stock reconciliation is one area and one period, on a phone. It asks which area and which dates. Eight checks come before the comparison: everything counted, deliveries and transfers booked, waste and staff use logged, sales from the till report, opening stock from last time, and the same units throughout. Then the lines that are out, the variance value in pounds, and the reasons. The variance comes with its reasons, not just a number.

In practice: A pub landlord reconciles his spirits every Monday. He counts the back bar and cellar, ticks the checks, and finds two bottles of vodka short against the till. The delivery note shows one case came in a bottle light, so he writes that as the reason for one. The other is unexplained.

Why it works: The eight checks catch the usual reasons a reconciliation is wrong before you start blaming anyone. Most stock that looks missing was never booked in, was moved somewhere else, or was thrown away and not written down.

The common failure is comparing too soon. Someone counts, sees a gap, and reports a loss, and then the missing delivery note turns up. Ticking each check first makes you look for those causes before you write a variance. The reasons line then records what you found, so next week starts from facts. A company also needs its stocktaking records for its accounts, and a dated reconciliation with reasons is a clean record to keep.

Steps included:

• 1 text input (which area or stock)
• 1 text input (which period)
• 1 checklist (8 checks before you compare)
• 1 text input (lines that are out)
• 1 number input (variance value)
• 1 text input (reasons for the variance)

When to upgrade:

1. Someone else does the reconciliation
2. The figures need checking against the reports
3. A manager has to check the result
4. You reconcile the same area every week or month

#2 - With guidance above the checks

Who it's for: Bars and kitchens where supervisors do the reconciliation.

What it is: The same reconciliation, with two guidance panels above it. The first says how to work out expected stock: opening plus deliveries and transfers in, minus sales, transfers out, waste and staff use, all in the same unit. The second says how to explain a variance: check the easy causes first, recount, write what you found, and never change the count to make it match. Every supervisor works the figures out the same way.

In practice: A hotel bar has three supervisors who take turns doing the weekly reconciliation. A new one reads the first panel and realises she had been comparing bottles counted against measures sold. She converts the till figures to bottles, and the gin variance drops from 30 bottles to under one.

What it adds to the previous template:

1. How to work out expected stock, step by step
2. How to look for the cause of a variance
3. The same method from every supervisor

Why it works: Different people work out expected stock in different ways, and the variance changes with the method. The first panel puts one method on the screen, so this week's figure can be compared with last week's.

Units are the most common mistake. Spirits are sold by the 25ml or 35ml measure under the weights and measures rules, wine by the glass, and draught by the pint, while the count is in bottles, kegs and cases. Unless both sides use the same unit, the figure means nothing. The second panel also says not to change the count to make it match. A forced count hides the problem and makes next period wrong as well.

Steps included:

• 1 guidance panel (how to work out expected stock)
• 1 guidance panel (how to explain a variance)
• The six steps from #1

When to upgrade: When the figures need checking against the reports (#3).

#3 - With a photo of the reports

Who it's for: Businesses where an owner or accountant checks the figures later.

What it is: The guided reconciliation, with a photo step straight after the checks. The person photographs the till report and the count sheet so the dates and totals can be read. The photos sit on the same record as the variance and the reasons. The figures can be checked against the reports they came from.

In practice: A café owner has two sites and an accountant who does the books monthly. Each Sunday the managers photograph the till report and the count sheet onto the reconciliation. At month end, the accountant checks the closing stock figure against the count sheets without asking for any paper.

What it adds to the previous template:

1. A photo of the till report
2. A photo of the count sheet
3. The source of each figure on the same record

Why it works: A reconciliation is only as good as the figures that went in. A photo of the till report shows the dates and the sales totals used. A photo of the count sheet shows what was counted.

Check the dates on the till report match the period on the reconciliation. A report run for the wrong day is one of the easiest ways to get a false variance. Lay each sheet flat in good light so every figure can be read. The company accounting rules require the stocktakings used to work out year end stock to be kept, and a photo of the count sheet on a dated record covers that. It also saves hunting for paper when the year end stock figure is questioned.

Steps included:

• The guided reconciliation from #2
• 1 photo (the till report and the count sheet)

When to upgrade: When a manager has to check the result (#4).

#4 - With signatures from both sides

Who it's for: Businesses where stock losses are taken seriously.

What it is: The photo reconciliation, with two signatures at the end. The person who did the reconciliation signs that the figures are accurate. The manager signs once they have read the figures and the reasons. Every variance has two names against it.

In practice: A wine bar's assistant manager does the weekly reconciliation and signs. The general manager reads the reasons, asks about one, and signs once the answer makes sense. When the owner asks about a run of losses on one champagne, both names are on every week's figure.

What it adds to the previous template:

1. A signature from the person who did it
2. A signature from the manager who checked it
3. A clear point where the result was accepted

Why it works: A reconciliation done and checked by the same person is easy to get wrong without anyone noticing. A second name means someone else has looked at the figures and the reasons.

Keep the two people different if you can. The person who counts should not be the only person who checks, and ideally the person who checks does not work the bar every shift. The manager should sign against the reasons, not just the total. If a reason looks thin, such as "probably waste", they should ask before signing, and write the answer into the reasons so it is there next week. If the manager is away, a deputy signs rather than leaving the record open. A record signed weeks later, from memory, is worth much less than one signed the same day.

Steps included:

• The photo reconciliation from #3
• 1 signature (reconciled by)
• 1 signature (checked by the manager)

When to upgrade: When you reconcile the same area every week or month (#5).

#5 - With the last reconciliation on screen

Who it's for: Teams reconciling the same area every week or month.

What it is: The signed reconciliation, with a Pilla briefing on the "which area" step. Pilla looks up the last reconciliation for the same area. It says the period, the variance value, the lines that were out, and any reasons left unexplained. This week starts with last week's problem lines.

In practice: A cocktail bar's manager starts the Monday reconciliation. Pilla tells her the last two weeks were both short on the same rum, with no reason found. She counts that rum first, checks the cocktail specs, and finds the new menu uses a 50ml pour the till still rings as 25ml.

What it adds to the previous template:

1. The last reconciliation for this area, before you start
2. The lines that were out last time
3. Any reasons left unexplained

Why it works: One week's variance can be noise. The same line out three weeks running is a pattern. The briefing puts the pattern on screen at the moment you start, so you check those lines first.

It also helps you see whether a fix worked. If last week's reason was a till button set up wrong, this week's figures should show the variance gone. If it is still there, the reason was wrong. Over a few months the briefing shows which lines and which areas are out most often, which is where a change of process, a spec card or a lock on the store will make the most difference. A line that is never out can be counted less often, which saves time on the next stock day.

Steps included:

• The signed reconciliation from #4
• 1 Pilla briefing of the last reconciliation, on the "which area" step

When to upgrade: When an unexplained variance needs a proper account (#6).

#6 - With more detail when stock is missing

Who it's for: Teams that need unexplained losses looked into.

What it is: The same reconciliation, with a Pilla rule on the reasons step. If the person says any variance is unexplained, that stock is missing, or that the loss is bigger than usual, the next step opens. It asks the likely cause of each unexplained line, who will look into it, and when they will report back. If everything is explained, the step stays out of the way. An unexplained loss leaves the reconciliation with a name on it.

In practice: A restaurant reconciles its wine weekly. Six bottles of one Malbec are unexplained. The follow-up opens. The manager writes that the cause may be bottles opened for by the glass and not rung through, that the head sommelier will check the by the glass log, and that he will report back on Thursday.

What it adds to the previous template:

1. A follow-up that opens only when a variance is unexplained
2. The likely cause of each line
3. Who looks into it, and by when

Why it works: "Six bottles unexplained" is a number. "Possibly by the glass not rung through, sommelier checking, back Thursday" is a plan. The follow-up asks for it while the figures are fresh.

Naming a likely cause matters even when it is a guess. It points the person looking into it at the right records: the waste log, the transfer book, the till settings or the CCTV. Keep the language neutral. Most unexplained variances turn out to be process, not theft, and a follow-up that reads like an accusation makes staff less likely to report mistakes honestly.

Steps included:

• The reconciliation with the last one on screen, from #5
• 1 Pilla rule on the reasons step
• 1 follow-up step in the chain (what happens next)

When to upgrade: When the person running the business should hear about it straight away (#7).

#7 - With a direct message about the variance

Who it's for: The person who set the reconciliation up.

What it is: When the follow-up step is filled in, Pilla sends a direct message to the person who set the automation up. It says the area, the period, the variance value, the lines that are out, and who is looking into them. A reconciliation where everything is explained sends nothing. You hear about a stock loss the day it is found.

In practice: An operations manager looks after six bars. She gets a direct message on Monday afternoon that one bar is £180 short on spirits with no reason found, and that the bar manager is checking the transfer book. She asks the bar it supplied at the weekend to check their side too.

What it adds to the previous template:

1. A direct message after the follow-up step
2. The area, the variance and who is looking into it
3. Nothing sent when everything is explained

Why it works: The person who does the reconciliation is often not the person who can change suppliers, staffing or security. The message joins the two up, and only when there is something to act on.

With several sites, it also shows patterns no single manager can see. Two bars short on the same line after the same weekend points to a transfer that one side recorded and the other did not. The message carries the variance value, so the person reading it can tell a small blip from a real loss. Set the reconciliation up as whoever manages stock across the sites, because the message goes to the person who set it up.

Steps included:

• The follow-up reconciliation from #6
• 1 direct message, sent when the follow-up is completed

When to upgrade: When the bigger risk is a reconciliation that never gets done (#8).

#8 - With a chase if it is late

Who it's for: Businesses with a fixed stock day.

What it is: The same reconciliation, plus a chase if it is not finished by the deadline. Pilla messages you, the person who set it up, to get it done. A reconciliation finished on time does not trigger one. A skipped stock day is chased the same week.

In practice: A bar group reconciles every Monday by 5pm. At 5pm, two of its eight bars have not finished. You get a message for each. One manager was covering a shift and had not started. He does it on Tuesday morning before the delivery.

What it adds to the previous template:

1. A deadline on the reconciliation
2. A chase when the deadline passes and it is unfinished
3. No message when it was done on time

Why it works: A reconciliation only shows a loss if it happens. Skip a week and the next one covers two weeks, which makes any variance harder to explain and any cause harder to find.

Set the deadline before the next delivery if you can. Once new stock arrives, the count no longer matches the period, and the reconciliation has to be worked out around it. Doing it on the same day at the same time makes it a habit, and a habit is harder to skip on a busy week. Reconciliations finished on time send nothing, so the chases you get are a list of the areas still to do. Two chases in a row for the same bar usually means the stock day clashes with something, and moving it is easier than chasing it.

Steps included:

• The reconciliation from #7, including the direct message about the variance
• 1 chase if the reconciliation is not finished by the deadline

When to upgrade: This is the full route. Use an earlier version if you do not want the message or the chase.

How to pick the right version

You don't need to know the product to choose. You need to know who does the reconciliation, who checks it, and how often you do it.

Is it just you, or do supervisors do it?

If you reconcile your own stock, #1 is enough. Once supervisors take turns, put the method above the checks: #2.

Does someone check the figures later?

If nobody checks, the figures are enough. If an owner or accountant does, add the photo of the reports: #3.

Does a manager have to check it?

If losses matter, add the signatures: #4.

After that, the choice is about repeat reconciliations and losses. If you reconcile the same area every week or month, use #5. If an unexplained loss should get a named person and a date, use #6. If you want a direct message about it, use #7. If the real risk is a stock day that gets skipped, use #8.

Related workflows

• Stock count - counting what is on the shelves and in the stockroom
• Delivery received - checking goods in when they arrive
• Stock rotation - moving older stock to the front
• Daily cash count - counting the till at the end of the day
• Food cost percentage - working out how much of each sale goes on food

Conclusion

A stock reconciliation compares what you counted with what you should have, and explains the stock variance. Start with the eight checks, add photos and signatures when someone else needs to check the figures, and add Pilla when you reconcile the same area every week or month. Open any version in the playground and use it on your next stock day.