In this guide 6 sections
Branch totals drift when each branch keeps its own item codes, price lists, customer records or ledgers, and transfers booked on one side only add to it. Find the first document that differs. Then build one shared master for items and prices, and add a two-step transfer so stock in transit never disappears between a sender and a receiver.
Start by finding where the totals first diverge
Pick one item and one month. Take the closing quantity at head office and add up the closing quantities from every branch, then compare the two figures. A gap is your thread. Pull it.
Do not reconcile the whole business at once. Work down from item to branch to document. The first document that exists at one branch and has no twin at the other, whether a bill, a delivery note or a journal entry, is usually the cause.
Most gaps fall into four buckets. Codes that mean different things. Prices that differ. Transfers booked once. Ledgers that never meet.
- Closing stock of one item, branch by branch
- Sales of one customer across all branches
- One inter-branch transfer, both sides
- The ledger balance each company shows for the other
Local item codes make one product look like three
One branch calls an item Steel Rod 12mm, another SR-12 and a third Rod 12 MS. Head office then sees three products with three stock balances, and none of the three is wrong on its own.
Unit of measure causes the same split. One branch counts cartons while another counts pieces, and the conversion factor lives in a long-serving storekeeper’s head, so totals match only when that person is in the room.
Build one item master with a single code, and map every local alias to it so reports roll up cleanly. Merge the duplicates, retire old codes on a cut-off date, and let only a central user approve new items.
Branch price lists and discounts hide inside invoices
When each branch edits its rate card, the same customer pays different amounts across towns, margin reports disagree, and nobody can tell which rate head office approved. Trace one invoice.
Keep one price list with an effective date, and let a branch override it only through a named discount rule with a reason code. Overrides become visible. They stop being silent.
Check how credit notes post. A return booked at the branch rate but approved at the head office rate leaves a small difference on every single document that passes through.
Stock transfers and company ledgers create the biggest gaps
A truck leaves branch A on Monday. Stock falls there that day. Branch B books the receipt on Thursday, once the driver hands over the delivery note, so for three days the goods exist nowhere. Auditors dislike that.
A two-step transfer fixes this. The sender raises a transfer out, stock moves to an in-transit location, and the receiver confirms quantity and damage against it. Anything still on the road shows as a list, so the van with the missing cartons is no surprise at stocktake.
Group companies add a second problem. Each legal entity keeps its own ledger, so inter-company sales never quite match the purchases booked in the sister company, unless a single document posts both sides. Then reconcile the two inter-company accounts every month.
- In-transit location with an age report
- Receiver confirms quantity, damage and shortage
- One document posts both inter-company entries
- Month-end lock so late edits cannot reopen a closed branch
Design choices that keep branches in agreement
Decide which data is global and which is local: items, customers, tax settings and price lists are global, while cash drawers, local staff and counters belong to one branch. Write the list down before anyone builds.
Next, ask what happens when the network drops. Many mismatches begin as offline workarounds, and a system that queues entries and syncs with a visible status beats one that sends branches back to private spreadsheets.
Finish with a reconciliation report that head office can run daily, then take your last two months of mismatches and test the design against them one by one. Each one should show up.
Branch reconciliation worksheet
One sheet, one mismatch per row. Columns are item code, branch, document type, document number, the quantity at the branch, the same figure at head office and the gap. Two last columns name the cause from four buckets (code, price, transfer, ledger) and a named owner for the fix, and a footer lists the global masters.
Open a blank worksheet to printQuestions owners ask
Why does one branch show more stock than head office?
Often a transfer arrived at the branch but was never booked out at the sender, so check delivery notes against the in-transit list before blaming the stock count.
Should every branch use the same price list?
Share the base list, with dated changes. Allow overrides only through a discount rule that records who approved the override, the reason code and the date it starts, so every difference is visible.
Do group companies need separate ledgers?
Legal entities usually do. What matters is that inter-company entries post from a single document, so both ledgers agree by construction rather than by month-end correction.
Can spreadsheets keep branches in sync?
For two small branches, perhaps. Past that, copies drift and versions multiply until nobody can say which workbook is current, whereas a shared master and live posting remove the problem.