Build a Simple Stock and Sales Tracker in Google Sheets

A practical spreadsheet workflow that helps small retailers separate incoming stock, sales, expenses and actual closing inventory.

Original ITBank practical guide prepared with AI assistance. Published through owner-authorized automated approval, without independent human editorial review.

The most dangerous number in a small shop may be the stock balance everyone assumes is correct. A notebook says five units remain; a shelf says three; a delivery driver says two are reserved. Before buying complex software, a simple spreadsheet can make these disagreements visible.

Set up four tabs

Create Products, Stock Movements, Sales and Daily Reconciliation. Products should contain a unique SKU, description, unit, supplier and reorder threshold. Movements should record date, SKU, quantity received or removed, reason and a reference number. Sales should record the transaction ID, date, SKU, quantity, selling price and payment method.

Calculate balances from movements

Avoid repeatedly typing a new balance into the Products tab. Instead, calculate each SKU's quantity from opening balance plus received stock minus stock sold, returns to supplier and documented damage. In Sheets, functions such as SUMIF or SUMIFS can aggregate movements; test them with a small sample before relying on the results.

Separate sales from collections

A product sale and a customer payment are not always the same event. Record unpaid invoices or credit sales separately, and reconcile mobile-wallet, bank and cash collections against actual statements. A copied payment screenshot should not be counted as a confirmed receipt.

Reduce errors with simple controls

Use dropdowns for movement reasons, protect formula columns and give each staff member only necessary editing access. Enable version history and save periodic exports in a secure location. If internet access is unreliable, configure supported offline access in advance and test what happens when staff edit the same file from different devices.

Close each day deliberately

Compare physical counts for several fast-moving items against the spreadsheet. Record discrepancies as adjustments with reasons, rather than quietly changing historical transactions. Look at the products below their reorder threshold and ask whether the shop has cash to replenish them.

ITBank takeaway: The goal is not a complicated dashboard. It is one dependable record showing what arrived, what left and what the business can actually account for.

Sources

Reference documentation