Inventory Management Spreadsheet: Template and Setup Guide

Quick answer: An inventory spreadsheet tracks each product's SKU, cost, quantity on hand and reorder point in one row, with a separate log of stock movements. Formulas calculate stock levels, flag items to reorder using reorder point = (daily usage × lead time) + safety stock, and total inventory value at cost.

Before paying for inventory software, many small businesses start with a spreadsheet, and for good reason: it is free, flexible and easy to understand. Built carefully, it can tell you what you have, what it is worth and when to reorder. Built carelessly, it becomes a list of numbers nobody trusts. This guide gives you a column-by-column inventory management spreadsheet template for Excel or Google Sheets, the formulas for reorder points and safety stock, a FIFO costing example, a setup checklist and the mistakes to avoid.

A note on scope: our free invoice generator creates PDF invoices in your browser. It does not track stock or connect to a spreadsheet, so you will update inventory separately when you buy and sell.

How an inventory spreadsheet is structured

A reliable setup uses three tabs rather than one long list:

1. Items (master list): one row per product with its fixed details, current stock level and reorder settings.
2. Movements (stock log): one row per transaction: purchases received, sales shipped, returns and count adjustments. You never overwrite quantities; you add rows.
3. Purchases (optional): purchase orders placed and their expected delivery dates, so you do not reorder something already on its way. A purchase order template keeps those orders consistent.

Keeping a movement log is the key design choice. If you just type a new quantity into a cell each time, you lose the history and cannot find out where stock went missing.

Inventory management spreadsheet template: the columns

Set up the Items tab with these columns. The formulas assume data starts in row 2 and that the Movements tab has the date in column A, SKU in column B, type (IN or OUT) in column C and quantity in column D.

Column	Heading	What to enter or the formula
A	SKU	A unique code for each product, such as CND-LAV-200
B	Item name	Plain description
C	Category	Product group, for filtering
D	Supplier	Main supplier name
E	Unit cost	Cost per unit at your chosen costing method
F	Quantity on hand	=SUMIFS(Movements!D:D,Movements!B:B,A2,Movements!C:C,"IN")-SUMIFS(Movements!D:D,Movements!B:B,A2,Movements!C:C,"OUT")
G	Average daily usage	Units sold over the last 90 days divided by 90
H	Lead time (days)	Days from placing an order to receiving it
I	Safety stock	Buffer quantity (see formulas below)
J	Reorder point	=G2*H2+I2
K	Reorder quantity	How many you normally order at once
L	Status	=IF(F2<=J2,"REORDER","OK")
M	Stock value	=E2*F2
N	Last counted	Date of the last physical count

Then add totals beneath the table, for example =SUM(M2:M200) for total stock value and =COUNTIF(L2:L200,"REORDER") for the number of items needing an order. Apply conditional formatting to column L so REORDER cells turn red.

For average daily usage in column G, if your Movements tab records dates in column A, you can calculate the last 90 days with =SUMIFS(Movements!D:D,Movements!B:B,A2,Movements!C:C,"OUT",Movements!A:A,">="&TODAY()-90)/90. The same formulas work in both Excel and Google Sheets. If you are new to building sheets, our walkthroughs of making an invoice in Excel cover the basics of formulas and formatting.

Reorder point and safety stock formulas

Reorder point

Reorder point = (average daily usage × lead time in days) + safety stock

It answers the question: how low can stock fall before I must order to avoid running out while I wait for delivery?

Safety stock, simple method

Safety stock = (maximum daily usage × maximum lead time) − (average daily usage × average lead time)

This covers a bad week where demand spikes and the supplier is slow at the same time. It needs only your sales history and delivery records.

Safety stock, statistical method

Safety stock = Z × standard deviation of daily demand × square root of lead time

Z reflects the service level you want; a commonly used value is about 1.65 for roughly a 95% chance of not running out during the lead time. In a sheet: =1.65*STDEV.S(range_of_daily_sales)*SQRT(H2). This method is more precise but assumes demand is reasonably steady and your data is reliable.

Worked example: setting a reorder point

An illustrative example for a shop selling a lavender candle. Over the last 90 days it sold 1,800 units. The supplier usually delivers in 7 days but has taken up to 10. The busiest day saw 30 units sold.

• Average daily usage: 1,800 ÷ 90 = 20 units
• Lead-time demand: 20 × 7 = 140 units
• Safety stock (simple method): (30 × 10) − (20 × 7) = 300 − 140 = 160 units
• Reorder point: 140 + 160 = 300 units

When the Quantity on hand cell drops to 300 or below, the Status column switches to REORDER. If the shop instead used the statistical method with a daily standard deviation of 6 units: 1.65 × 6 × √7 ≈ 1.65 × 6 × 2.646 ≈ 26.2, rounded up to 27 units of safety stock, giving a reorder point of 140 + 27 = 167. The gap between 300 and 167 shows how much the method choice matters; the simple method is cautious, which ties up more cash in stock.

That cash trade-off is real. Stock sitting on shelves is money that cannot pay bills, which is why inventory levels feed directly into working capital.

FIFO costing in a spreadsheet

Unit cost often changes between purchases. Your costing method decides which cost flows to cost of goods sold and which stays in inventory. In the US, the IRS describes the accepted methods, including FIFO, LIFO and average cost, in Publication 538, Accounting Periods and Methods, which also covers simplified options some small businesses may qualify for.

Illustrative example: you buy 100 units at $5.00 and later 100 units at $6.00, then sell 150 units.

Method	Cost of goods sold	Ending inventory (50 units)
FIFO	(100 × $5.00) + (50 × $6.00) = $800	50 × $6.00 = $300
Weighted average	150 × $5.50 = $825	50 × $5.50 = $275

The weighted average cost is ($500 + $600) ÷ 200 = $5.50. Both methods account for the full $1,100 spent: $800 + $300 = $1,100 and $825 + $275 = $1,100. In a spreadsheet, FIFO is easiest to manage by recording each purchase as a separate layer (date, quantity, unit cost) and consuming the oldest layer first. Weighted average is simpler: recalculate the unit cost in column E after each purchase.

The ending inventory figure from your inventory spreadsheet feeds the formula beginning inventory + purchases − ending inventory, explained in our guide to cost of goods sold. If the count is wrong, gross profit is wrong too.

Example Movements tab

Here is what a few rows of the stock log look like for the lavender candle. Each row is one event; nothing is ever edited after the fact.

Date	SKU	Type	Quantity	Reference
Oct 1	CND-LAV-200	IN	400	Opening count
Oct 3	CND-LAV-200	OUT	24	Invoice 1042
Oct 4	CND-LAV-200	OUT	60	Invoice 1043 (wholesale)
Oct 6	CND-LAV-200	IN	2	Customer return
Oct 9	CND-LAV-200	OUT	3	Count adjustment: damaged

The quantity formula on the Items tab reads this log: (400 + 2) − (24 + 60 + 3) = 402 − 87 = 315 units on hand. At a reorder point of 300 the status still shows OK, but only 15 units of headroom remain, so an order will be due within a day at 20 units a day. Returns are logged as IN and write-offs as OUT; if you want separate reporting, add RETURN and ADJUST types and extend the SUMIFS accordingly. Teams already working in Google Sheets can keep this log in the same file as their Google Sheets invoice template.

Keeping the sheet accurate day to day

• Log goods in when they arrive, not when ordered, and check quantities against the supplier's delivery note and invoice.
• Log goods out when they ship. Use the invoice or order number as a reference so every OUT row can be traced. A consistent sequence from an invoice number generator makes matching easier.
• Record returns and damaged stock as their own movement types rather than editing old rows.
• Cycle count a handful of items each week and post differences as adjustments with a note.
• Lock formula columns and use data validation for the SKU and movement type so typos cannot break SUMIFS.

Sales invoices are the source document for most OUT rows. Our invoice tracker spreadsheet uses the same structure and can sit in the same workbook if you track billing and stock together.

Inventory spreadsheet setup checklist

• Create Items, Movements and (optionally) Purchases tabs.
• Assign a unique SKU to every product and variant.
• Do an opening physical count and enter it as a single IN movement per SKU dated the start day.
• Enter unit costs using one costing method, and note which.
• Add the quantity, reorder point, status and value formulas, then copy them down.
• Calculate average daily usage and lead time from real records, not guesses.
• Set safety stock per item, higher for best sellers and unreliable suppliers.
• Add conditional formatting for REORDER and data validation for movement types.
• Protect formula columns and keep a dated backup copy each month.
• Schedule weekly cycle counts and a full year-end count.

Common inventory spreadsheet mistakes

• Overwriting quantities instead of logging movements, so discrepancies cannot be traced.
• Valuing stock at selling price rather than cost, which inflates assets and profit.
• Duplicate or inconsistent SKUs, such as "CND-LAV" and "CND LAV", which split stock across two rows.
• Stale lead times and usage figures that make reorder points meaningless after a busy season.
• Skipping physical counts, so theft, damage and errors build up unseen.
• Several people editing without rules, overwriting each other's changes.
• Ignoring stock already on order, leading to double orders.
• Holding too much safety stock, which ties up cash that could support cash flow management.

When to move beyond a spreadsheet

A spreadsheet works well for a modest product range in one location. Consider dedicated inventory or point-of-sale software when you sell across several channels that need synced stock levels, manage many SKUs or variants, need barcode scanning, or have several people updating stock at once. Until then, a disciplined inventory spreadsheet with a movement log, clear formulas and regular counts gives you most of the benefit at no cost. Accurate stock costs also make it easier to work out how to price a product with confidence.