Why your Excel stock register is always wrong, and how to fix it

Why is an Excel stock register always wrong?

An Excel stock register is wrong because it is a second record typed by hand after the real event. The invoice goes to the customer, the truck leaves, and someone is supposed to update the sheet later. Every gap between the event and the typing is a chance for the numbers to drift.

The sheet is not the problem on its own. Many small businesses run for years on one. The problem is that nothing forces the sheet to agree with the invoices, the bills and the godown. When a number is wrong, there is no trail to say which row broke it.

Below are the five most common failure modes, each with a small example from Lakshmi Steel Furnitures, the fictional Coimbatore business used across these guides.

Failure 1: sales are entered late, or not at all

When sales are typed into the register at the end of the day, the register is wrong for the whole day and stays wrong for every sale that gets missed.

On a busy Saturday the Ukkadam store sells 5 steel racks across three invoices. The salesperson updates the sheet for two of them before closing. On Monday the sheet shows 5 more racks than the shelf, and a customer is promised stock that is not there.

Failure 2: the invoice and the stock live in different files

If the invoice is made in one program and stock is kept in another, nothing links the two, so a cancelled or edited invoice never reaches the stock sheet.

Invoice INV-2026-27-0219 to Ravi Stores is made for 4 racks, then cut to 3 when the customer changes the order. The billing software is corrected. The stock sheet still shows 4 out. The register is now one rack short of reality, and the error will show up only at the next count.

Failure 3: the average cost is never recalculated

Most stock sheets carry one fixed rate per item, so stock value goes stale the moment a purchase comes in at a new price.

Lakshmi Steel had 34 steel racks at ₹3,000 each. It then bought 20 more from Tamil Nadu Steels at ₹3,324. The sheet kept ₹3,000 as the rate.

The same stale rate also understates cost of goods sold on every rack sold, so the margin looks better than it is. The weighted average cost guide shows the formula step by step.

Failure 4: godown transfers are agreed by phone

A transfer agreed on a call is recorded on one side, both sides, or neither, depending on who remembers.

The Ukkadam store calls the main godown for 10 racks. The godown manager reduces his column. The store never adds them. The total across both columns is now 10 racks short, though not a single rack has left the business.

Failure 5: nobody owns the count

When no one person is responsible for a warehouse's numbers, differences found at a count are fixed by overwriting the cell, and the reason is lost.

At the half-yearly count, the main godown has 3 racks fewer than the sheet. Someone types the counted number over the old one. Was it a missed sale, damage in loading, or theft? The sheet cannot say, so the same loss happens again before the next count.

What does a stock register need that Excel does not enforce?

A reliable stock register is a movement ledger: every change in quantity is a row that points to the document that caused it, and the balance is the sum of those rows.

How do you fix an Excel stock register?

You fix it by changing where stock numbers come from, not by adding more columns. Four principles cover the five failures above.

1. 1

   Stock moves only when a document posts

   A sale reduces stock because the invoice posted, and a purchase increases it because the bill posted. No one edits a quantity directly. This closes failures 1 and 2.

2. 2

   Use one cost method and let it run

   Pick weighted average with your accountant and apply it on every receipt. The rate is calculated, never typed. This closes failure 3.

3. 3

   Correct stock with a count adjustment and a reason

   When the count differs, post an adjustment that records the counted quantity and why (count difference, damage, theft). The old number stays in the history. This closes half of failure 5.

4. 4

   Give each warehouse one owner

   One named person answers for each godown's count and approves transfers out of it. Transfers are posted as documents, so both sides move together. This closes failure 4 and the rest of failure 5.

Before switching anything, it helps to run a quick check on the sheet you have today.

• Every item has one name and one unit (box or piece, not both)
• Each "Out" entry can be traced to an invoice number
• Each "In" entry can be traced to a supplier bill
• The rate column changed after the last purchase at a new price
• Every godown-to-godown move appears in both columns on the same date
• The last count difference has a written reason
• Each warehouse has one person who signs off its count

If more than two of these fail, the sheet is costing you more time than it saves. Two related guides go deeper: negative stock and how to fix it covers what happens when the register goes below zero, and stock audit and physical count shows how to run a count in a day and post the differences.

How does SimpleERP keep the stock register right?

In SimpleERP, stock moves only from posted documents: supplier bills, invoices, credit and debit notes, adjustments, transfers and work orders. Posting an invoice writes the stock issue, cost of goods sold, the receivable and GST in one step, so the sale and the stock can never disagree.

• Weighted average cost per product across the organisation, with quantities kept per warehouse.
• Transfers between warehouses move both sides in one document.
• Stock adjustments in count mode: enter what you counted, pick a reason, and the difference posts.
• Posted documents cannot be edited; a cancellation reverses them, and the audit history shows who did what.
• The stock screen shows total stock value beside the Inventory account balance and the difference between them, as a monthly self-check.
• Opening stock comes in by product, by adjustment, or by a product CSV import.

See the inventory management page for the stock screens. If you are planning the move off spreadsheets, the switching from Tally and Excel guide covers what to move and when, and moving from Tally or Excel shows how SimpleERP fits that switch.