Excel versus 1C: a monthly reconciliation with the local accountant

A familiar scene: the Chinese warehouse manager keeps an Excel sheet, the local accountant keeps another set of records in 1C, and at month-end the figures differ. Each side is sure it is right. Instead of trading opinions in meetings, turn the reconciliation into a fixed routine: the same day each month, the same two tables and the same way of sorting the gaps.

Step one: agree the moment, then export

Settle two things first. The first is the point in time, for example "close of business on 31 March". The second is where each side's figures come from. Ask the accountant to export 1C stock balances for that moment by item and warehouse instead of copying them by hand. The warehouse saves its own figures for the same moment and explains how the Excel sheet is built: filled in daily, or totalled from delivery notes. Both tables need at least four columns: item code, name, unit and quantity.

Also check that both sides cover the same scope. 1C may hold several warehouses or sub-locations, such as main, in transit, quarantine and consignment, while Excel shows one total. If the accountant's report includes only the main warehouse and your Excel also counts goods awaiting inspection, a difference is guaranteed from the start.

Step two: put the two tables side by side

Place both exports in one workbook and look up quantities by item code (VLOOKUP or XLOOKUP will do). Add three columns: 1C quantity, warehouse quantity and difference. Then work through three checks:

1. filter the rows where the difference is not zero and sort them from largest to smallest;
2. find the rows that appear in only one table, which usually means the same goods carry different names or codes rather than being missing;
3. look at the unit column: a difference that is an exact multiple of the carton size or set size points to a conversion problem.

Step three: sort the gaps into five groups

1. Dates: goods leave on the 30th and the document is raised on the 2nd. Compare at one cut-off and read the documents on either side of it.
2. Units: Excel counts pieces while 1C counts cartons or sets, so one wrong conversion produces neat multiples.
3. Names and codes: the warehouse uses a Chinese short name or pinyin, the accountant a full Russian or Uzbek name. The quantity looks short when it is really split between two records.
4. Documents: goods received without an invoice, goods in transit, unsigned delivery notes, returns, transfers and consignment stock may be physically present but not yet in the books, or the other way round.
5. Adjustments and write-offs: samples, gifts, scrap and production use noted in Excel with no document in 1C, or an adjustment by the accountant that the warehouse never heard about.

An illustration

Suppose you compare 1,200 item lines. 1,090 agree and 110 differ. Of these, 14 lines appear only in Excel and 9 only in 1C. They turn out to be the same goods under different names, and the difference disappears once they are merged. A further 62 lines are timing differences or documents not yet posted, cleared by raising the document or correcting the date. The remaining 25 need a count on the shelf. 14 + 9 + 62 + 25 = 110. (The figures are invented.) Of 110 differing lines, only 25 truly need a walk to the shelf.

Make it a monthly habit with the accountant

Treat the accountant as a partner, not an opponent. They know how 1C is organised and what local documents look like, and you know how goods actually move through the warehouse. Each side holds half of the answer. In practice:

• fix a reconciliation day every month rather than waiting for year-end;
• reconcile from the same exported table, not from chat messages;
• record each difference with a cause group, a document number and a person responsible;
• when you meet an unfamiliar 1C term or document name, ask the accountant to explain it rather than guess, since asking each other saves time;
• put anything unexplained on a "to be checked" list with a deadline, and let a count on the shelf settle it.

The last point matters: when two sets of records cannot settle an argument, let the goods speak.

In short

A mismatch is rarely anyone's fault. More often the two sides are working from different bases. When UzWarehouse counts, we agree the basis first and the numbers second, then match the count line by line with your 1C, Excel or ERP records and list the differences by cause.