Excel stock balances don’t match reality. The salesperson promises a customer an item that’s no longer in stock. Inventory again reveals a 10% shortage. If this sounds familiar — the problem isn’t people, it’s the accounting system. Proper product accounting isn’t just a spreadsheet with quantities, it’s a connected process from receipt to sale.

In this article, we’ll break down how product accounting should work in a real system: what documents you need, what FIFO is, how inventory counts, reservations, and serial numbers work — and how to transition from Excel.

Why Excel and paper notebooks fail at product accounting?

Excel is a spreadsheet; product accounting is a process. A spreadsheet doesn’t know that a shipment should automatically reduce stock, or that a return should restore it. As a result, every transaction is entered twice (or forgotten), and within a month, file balances no longer match reality.

Specific issues with running product accounting in Excel:

• No single source of truth. The salesperson has one spreadsheet, the warehouse manager another, the accountant a third. When numbers diverge — nobody knows which is correct.
• Cost calculated by guess. With different purchase prices for the same item, Excel won’t calculate real cost automatically. Selling at a profit or a loss — unknown.
• No movement history. An item “disappeared” — where? When? Who wrote it off? In Excel this is lost information; in ERP, it’s an audit trail for every unit.
• Reservations = promises from memory. A manager promises stock to one customer, then another. Excel doesn’t see this, and double-sells appear.
• Inventory counting — quarterly stress. The whole team is blocked, the store stops working, result — minus 10-15% with no explanation.

More on 5 typical signs that your accounting isn’t coping — in our separate article on warehouse accounting.

What is FIFO and why does a retail business need it?

FIFO (First In, First Out) is a cost accounting method where the oldest batch of goods is written off first when a sale occurs. It’s the standard for retail, warehousing, and manufacturing.

Example. You purchased the same item in three batches:

You sell 150 units. FIFO writes off: 100 pcs × $50 + 50 pcs × $55 = $7,750 cost. At a sale price of $70 × 150 = $10,500 revenue, gross profit is $2,750.

Without FIFO (or with manual calculation), you usually take “average price” — and get a distorted picture. In ERPJS, FIFO works automatically for every shipment and write-off — sorts batches by date, consumes sequentially, calculates proportional cost.

What documents do you need for proper product accounting?

In a proper accounting system, every operation is a separate document with its own status, author, and date. The minimum set is 8 document types:

1. Receipt. Incoming supply from a vendor. Increases stock, records purchase prices for FIFO.
2. Shipment. Outgoing delivery to a customer. Reduces stock, calculates cost by FIFO.
3. Transfer. Internal transfer between warehouses. Doesn’t change total quantity but records location.
4. Write-off. Item damaged, lost, used for internal purposes. Reduces stock with a stated reason.
5. Return to vendor. Defective or surplus batch. Reduces stock and liabilities.
6. Return from customer. Claim processing. Restores stock, returns cost.
7. Inventory count. Physical recount with discrepancy protocol.
8. Additional costs. Distribution of customs duties, delivery, packaging onto product cost.

Every document in ERPJS automatically updates registers: Product status (real-time stock across warehouses), Product history (full audit trail of every movement), and Serial number status (for items with unique identification).

How should inventory counting look in a normal system?

Inventory counting in Excel is a notepad where the warehouse manager writes down what was counted, and someone then manually reconciles with the spreadsheet. In a normal system, it’s a managed three-step process.

Step 1. Inventory sheet builder. You select the warehouse, date, and product categories — the system generates an empty sheet listing all items that should be in stock according to accounting records.

Step 2. Physical count. The warehouse manager (or barcode scanner) fills in actual quantities for each line. For large inventories, counting is split by zones and employees.

Step 3. Inventory comparison. The system automatically generates a discrepancy report: where there’s shortage, where there’s surplus, for what amount. Based on the report, write-off or receipt documents are created — stock is reconciled.

In ERPJS, this process takes hours instead of days — and gives exact numbers instead of “plus-minus 10%”. Another benefit — partial inventory: you can check one category or zone without stopping the whole warehouse.

How to reserve stock for a customer order?

Reservation is the difference between “item is in stock” and “item is available for sale”. You received an order for 50 units with shipment tomorrow — these 50 units must be reserved so another manager doesn’t sell them today.

In Excel, reservations lead to two problems: either nobody tracks them (and double-sales occur), or they’re tracked in a separate column everyone forgets to update.

In ERPJS, reservations are automatic:

• Customer places an order → system reserves the needed quantity with “reserved” status.
• Available stock for new sales = Physical stock − Reserved.
• After shipment, the reservation is released, stock is reduced.
• If the order is cancelled — the reservation automatically returns to available stock.

The “Shortage” report shows how much stock is missing to fulfill all active orders — and needs to be reordered. This is especially useful for stores with pre-orders and online sales, where 1-3 days pass between order and shipment.

How do serial numbers help in service and retail?

Serial numbers are needed where every unit is unique: electronics, appliances, auto parts, warranty-covered furniture. ERPJS tracks serial numbers as a separate accounting layer: for every number, you can see when it arrived, from which vendor, at what price, when and to whom it was sold.

Practical use cases:

• Warranty. Customer brings an item with a claim — by serial number, you see when it was sold and whether warranty is still active.
• Batch recall. Vendor reports a defect in batch #X — you find all customers who bought items from that batch in seconds.
• Service intake. The technician receives a device for repair — the serial number is already in the database with full history.
• Theft prevention. Every unit has a unique ID — writing it off “into the shadows” is harder.

How to transition from Excel to product accounting software?

You don’t have to transition in one day. For a store or warehouse with up to 1,000 SKUs, a realistic plan is 2-3 weeks:

Week 1. Reference data. Upload the product catalog (units, categories, barcodes), counterparties (vendors, customers), and warehouses. ERPJS supports Excel import, so existing data doesn’t need manual entry.

Week 2. Starting balances and current operations. Run inventory, enter real balances into the system. From this point, run all receipts and shipments through the new system, in parallel with Excel.

Week 3. Full switchover. After a week of parallel work, compare reports. Usually at this stage, it’s already clear where Excel had “gaps”. Turn off Excel for current accounting, keep only as an archive.

How to choose an accounting system that fits your business — see our guide with 7 selection criteria. And for how product accounting affects financial results, see our article on financial accounting.

Frequently asked questions