Why the 1C or Excel balance differs from the physical count

Before a revision starts, the accountant prints the balance from the program while the storekeeper counts what is on the shelves. The two figures do not match. The first thought is usually the same: "somebody took it". In practice, however, a difference in the records more often comes not from the warehouse but from how the data were entered. Whether it is 1C, an Excel sheet or another program, mistakes arise in much the same places. If you know the causes, you can explain a large part of the difference before counting even begins.

The most common causes

• Documents entered late or backdated. The goods physically arrived yesterday, but the document was made out today. In between, the balance looks wrong. A backdated document also changes the balance for past days.
• A negative balance. If goods are sold before the receipt is booked, the program either takes them into the minus or refuses to post the sale. When the receipt is entered later, the figure seems to be corrected, but the records for the period in between are already distorted.
• One item, two names. "Oil 1 l", "Oil 1 litre" and "oil 1l": if these are three separate cards, the balance is split in three, with a surplus on one and a shortage on another.
• Units of measure. If a box holds 12 pieces and one card counts boxes while another counts pieces, the gap is large. With cement, tiles or cable, kilograms, bags, tonnes, metres and pieces get mixed up especially often.
• Returns and replacements. Goods returned by a customer may not have been put back on the books, or may have gone onto the wrong card.
• Manual edits in Excel. When several people work in different copies, it is unclear which file is the main one, and the same entry is made twice or not at all.

A check before the count

If you clean the data yourself before going out to count, there is less pointless counting. A practical order:

1. Print the list of negative balances and explain each one.
2. Look for duplicate cards: similar name, different code.
3. Mark items whose unit of measure is given in two ways.
4. Review the list of documents entered in the last week but carrying an earlier date.
5. Open the movement history of the items with the biggest differences and find the day from which the figures stopped matching.

The last step is the most useful: the receipt and issue history of an item usually shows the "break".

After the count: grouping the differences

When you compare the count with the records, sort the differences into three groups:

• data errors: card, unit, date;
• process errors: issuing without a document, wrong receiving, an unprocessed return;
• real loss: the cause is still unknown.

Only the third group resembles a shortage. Once the other two are fixed, the difference falls considerably. While correcting data, write down every change: what, when and by whom. Otherwise the correction itself becomes a new source of disputes.

A hypothetical example: a warehouse has two cards for the same cable. One shows plus 300 metres, the other minus 80 metres. In total there are 220 metres on the rack. At first glance one card has a surplus and the other a shortage, but in fact it is one item entered in two ways. Once the cards are merged, the difference vanishes by itself and nobody has to be blamed.

Rules for an Excel sheet

If you keep the balance in Excel, four rules prevent many problems. First, there should be one master file, edited by a single responsible person, while everyone else works from copies. Second, give every item a unique code and search by code, not by name. Third, record receipts and issues on separate sheets and calculate the balance by formula rather than typing it in. Fourth, save dated copies of the file and mark rows instead of deleting them.

Conclusion

A difference between the book balance and the shelf does not always mean a loss. A late document, a doubled card and a wrong unit of measure all look like a shortage from outside. If you check the data first and count afterwards, it is easier to pick out the real loss. A day spent checking data before a revision also saves days of counting. When the balance does not agree with the documents, the Reviziya team can compare the book data with the count and help find the source of the difference.