How a reconciliation statement is prepared

The count is over and you have dozens of count sheets in front of you. They are not yet the result, only raw material. The result is the reconciliation statement: a table in which, for every position, the booked and the actual quantity stand side by side. The report, the explanations and any write-off all start from this table, so any mistake in it is carried into every later step.

What the statement contains

The heading gives the name of the organisation, the warehouse, the date and hour at which the balance was "frozen", the committee members and the person responsible for the stock. The main columns are:

• the name and article code of the position;
• the unit of measure;
• the price (cost or selling price, but the same one throughout);
• the booked quantity and amount;
• the actual quantity and amount;
• the difference: shortage (−) or surplus (+), in quantity and amount;
• a note: the cause or a document number.

At the bottom come the totals and the signatures of the committee members.

How to prepare it

1. Fix the balance moment. Take the book data for exactly that date and hour, and make sure all receipts and issues up to that moment are posted.
2. Export balances by position from the accounting system (1C or Excel).
3. Enter the data from the count sheets into the table. Let two people do it: one reads and the other types, or enter the data twice and check the rows that do not match.
4. Align names and units. If the records say "pieces" and the count says "boxes", convert to one unit with a coefficient.
5. Calculate the difference: actual minus booked. A negative number is a shortage, a positive one a surplus.
6. Multiply the difference by the price to get an amount.
7. Goods that are not in the records but were found in the warehouse go on a separate line with a book balance of zero.
8. Work out the totals for shortages and surpluses separately.

In Excel the difference is found by simple subtraction and the totals with SUMIF: shortages on one side, surpluses on the other. Highlighting the rows with a difference in colour makes the statement far easier to review.

A hypothetical example and offsetting

• 5-litre oil: booked 140, actual 134, difference −6 pcs, at 78,000 so'm: −468,000 so'm;
• pasta (box): booked 85, actual 88, difference +3 boxes, at 96,000 so'm: +288,000 so'm;
• sugar (kg): booked 520, actual 509, difference −11 kg, at 13,000 so'm: −143,000 so'm.

Total shortage 611,000 so'm, total surplus 288,000 so'm, net difference a shortage of 323,000 so'm.

When a surplus may offset a shortage

Sometimes a surplus is "set against" a shortage. This is acceptable only for goods of the same kind that resemble each other, and only on a decision of the committee, usually in a case of mixed-up grades. In the statement the totals for shortages and surpluses must always stay visible separately, because the net difference understates the true size of the problem. In the example above the net difference is 323,000 so'm, while the real shortage is 611,000 so'm. The surplus of pasta does not cover the shortage of sugar or oil: they are different goods.

How to read the statement

There is no need to read every line of a finished statement in one go. First sort by the amount of the difference and set aside the ten biggest lines: very often most of the total difference sits in them, so it makes sense to start checking there. Then look for odd-looking figures. If the actual number is exactly 10 or 100 times the booked one, someone has usually slipped over a zero or a decimal point. If the difference equals exactly the contents of one box, the unit of measure has been confused. If two similar lines show equal differences with opposite signs, the article codes have been swapped.

Typical mistakes

• the book data and the count are taken on different dates;
• positions that are not in the records but were found in the warehouse are left out of the statement;
• price types are mixed: some rows at cost, some at selling price;
• units of measure do not match;
• rows are quietly edited after signing;
• there is no note on the difference, and "cause: unknown" does not count as a note.

Conclusion

A good statement is a simple, complete table that can be checked. In it every figure leads to a count sheet and every difference leads to a document. The Reviziya team can help prepare the statement and analyse the differences by cause.