Consolidating branch results into one report: layout and pitfalls

Seven branches, seven different spreadsheets. One counts in pieces, another in boxes, a third has no date, and in a fourth one product appears under three names. Whoever compiles the report spends days fixing tables, and management does not trust the final figure. A simple question such as "what happened in which branch?" gets no answer within a day. The branches are not working badly: nobody gave them a single form.

One form, issued before the count

Send the form before the count starts, not after the results come in. These things must be fixed in it:

• the order and names of the columns: item code, name, unit, book balance, counted quantity, difference, comment;
• the item code comes from one master list, and a branch does not write the name its own way;
• each item has one unit of measure: pieces or boxes, not both;
• the header shows the branch name, the responsible person, and the cut-off date and time;
• only the cells to be filled in are marked, and the formulas for differences and totals are locked.

One date, one unit, a separate line for "goods in transit"

The cut-off date must be the same for every branch, otherwise stock moved between branches is counted twice or not at all. Give goods in transit a separate row: from which branch to which, when it was sent, who received it.

A hypothetical example. Branch A has sent 40 pieces to branch B, and at the cut-off the truck is still on the road. Branch A has already taken them off its balance, branch B has not yet received them. Without an "in transit" row, 40 pieces seem to vanish from the total and are booked as a shortage. With the row, the overall balance adds up.

The consolidation routine

1. When you receive each branch's file, check the header, the date, the completeness of the codes and any empty cells.
2. Compare the totals the branch reported (number of items and total quantity) with the rows in the file.
3. In the consolidated workbook, give each branch its own sheet, and build the overall sheet with formulas rather than typing it in.
4. Check the control total: the sum of the branches, allowing for goods in transit, must equal the overall balance.
5. Show differences by branch and product group to see at a glance where the problem has piled up.

A hypothetical control total: three branches hold 1,200, 950 and 1,400 items, 3,550 in all. The consolidated sheet shows 3,510. So 40 items went astray on the way: perhaps a row was not copied, or two codes merged into one. That gap must be found before the report goes out.

Roles should be clear in advance too. The branch manager fills in the form and confirms the totals. The report author at head office receives the files, maintains the consolidated workbook and sends questions back to the branches. The accountant confirms the book balances and the goods in transit. Set a single channel and deadline for questions, for example: after the count the branch sends its file within one working day.

Structure of the consolidated report

A manager should be able to read the report from its first sheet. A recommended structure:

• first sheet: a short summary with the number of items checked, the share of items with differences and the three largest differences;
• a table by branch: for each branch the items, the number of differences and their value;
• a breakdown by product group;
• goods in transit and unconfirmed items shown separately;
• notes: the branches' explanations and open questions.

Agree with the accountant beforehand which price values the differences (cost or selling price) and state it in the report. Otherwise two different figures cause a dispute.

Typical mistakes

• numbers stored as text (such as "1 200" with a space) are left out of the sum;
• merged cells and manually inserted rows break the formula;
• the same item is entered under different codes;
• a branch later "corrects" its table and sends a new version without comment;
• tables are combined without the branch name and the rows get mixed up.

Conclusion

Branch results merge into one report when they share one form, one date and one unit, and a control total checks the rest. Inventory counts stock in supermarkets, store chains and pharmacies without stopping operations, and delivers the results with a discrepancy analysis in Excel or 1C format.