Inventory spreadsheet: a free template with formulas

What your spreadsheet has to answer

This guide is about building the file. For the method behind it (reorder points, safety stock, ABC analysis, shrinkage), start with our guide to inventory management for small businesses.

Whether you use Excel, Google Sheets or LibreOffice Calc, the file should answer four questions without a calculator:

1. How many units of each product do I have right now?
2. What do I need to reorder?
3. What is my stock worth, at cost?
4. What do I make on each unit sold?

The most common mistake is a single tab where someone overwrites the stock number after every sale. After a month, nobody knows where the number came from. The rule that fixes it: you never edit the stock figure, you add movements. The stock recalculates, and the history stays.

The template, ready to copy

Here it is filled in for a small candle business in Portland, Oregon, for October. Costs and prices exclude sales tax.

TAB "Movements" (one row per stock in or stock out)
A: Date  | B: SKU   | C: Type | D: Qty | E: Unit cost | F: Note
Oct 02   | CAN-01   | In      |   60   | 6.50        | Supplier delivery
Oct 03   | DIF-03   | In      |   12   | 9.60        | Supplier delivery
Oct 07   | CAN-01   | Out     |   38   |             | Sales, week 1
Oct 07   | WAX-02   | Out     |   12   |             | Sales, week 1
Oct 07   | DIF-03   | Out     |    8   |             | Sales, week 1
Oct 07   | MAT-04   | Out     |   14   |             | Sales, week 1
Oct 09   | CAN-01   | Out     |    2   |             | Damaged
Oct 14   | CAN-01   | Out     |   30   |             | Sales, week 2
Oct 14   | WAX-02   | Out     |   10   |             | Sales, week 2
Oct 14   | DIF-03   | Out     |    7   |             | Sales, week 2
Oct 14   | MAT-04   | Out     |   12   |             | Sales, week 2

TAB "Products" (one row per item)
A: SKU | B: Item | C: Opening | D: Opening cost | E: In | F: Out | G: Stock | H: Reorder at | I: Avg cost | J: Value | K: Price | L: Unit margin | M: Status
CAN-01 | Soy candle 8 oz  | 40 | 6.00 | 60 | 70 | 30 | 35 | 6.30 | 189.00 | 18.00 | 11.70 | Reorder
WAX-02 | Wax melts 6-pack | 50 | 2.40 |  0 | 22 | 28 | 20 | 2.40 |  67.20 |  8.00 |  5.60 | OK
DIF-03 | Reed diffuser    | 12 | 9.00 | 12 | 15 |  9 |  8 | 9.30 |  83.70 | 26.00 | 16.70 | OK
MAT-04 | Matches tin      | 30 | 1.50 |  0 | 26 |  4 | 10 | 1.50 |   6.00 |  4.00 |  2.50 | Reorder
                                                        TOTAL STOCK VALUE: $345.90

Two set-up tips. Turn each range into a table (Ctrl + T in Excel) so formulas fill down automatically on every new row. And give the "Type" column a dropdown (Data > Data Validation > List: In,Out): one typo such as "in " with a trailing space is enough to break a total.

The formulas, step by step

Every formula below is written for row 2 of the "Products" tab and copied down.

For the soy candles: 40 + 60 − 70 = 30 in stock. The reorder point is 35, so the status reads "Reorder". The reorder point itself is average daily sales × supplier lead time + safety stock; the inventory management guide shows how to set it.

If your spreadsheet uses a European locale, replace the commas between arguments with semicolons.

Reorder alerts in red

A "Reorder" label is easy to miss in a long list. Conditional formatting colours the whole row.

Stock value: the weighted average cost formula

Stock value is quantity on hand times a unit cost. The problem: the candles were bought at $6.00, then at $6.50. Which cost do you use?

Weighted average cost takes the average of all costs, weighted by quantity:

Average cost = (opening qty × opening cost + sum of received qty × their cost) ÷ (opening qty + received qty)

For the candles: (40 × 6.00 + 60 × 6.50) ÷ (40 + 60) = (240 + 390) ÷ 100 = 630 ÷ 100 = $6.30. Value of the 30 left: 30 × 6.30 = $189.00.

Column I does this with SUMPRODUCT, which multiplies quantity by cost for every "In" row of the SKU:

=IFERROR((C2*D2+SUMPRODUCT((Movements!$B$2:$B$1000=$A2)*(Movements!$C$2:$C$1000="In")*Movements!$D$2:$D$1000*Movements!$E$2:$E$1000))/(C2+E2),D2)

IFERROR avoids a divide-by-zero error for a new item with no stock yet. Keep the ranges bounded (rows 2 to 1,000): SUMPRODUCT over whole columns slows the file down.

This is a period average: one cost for the month. A moving average recalculates after each delivery instead: (units on hand × current average + units received × purchase cost) ÷ (units on hand + units received). For the diffusers, received with 12 already on the shelf: (12 × 9.00 + 12 × 9.60) ÷ 24 = 223.20 ÷ 24 = $9.30.

The average cost also gives your margin: a candle sold at $18.00 that cost $6.30 leaves $11.70, a 65% margin on the selling price. Our guide on how to calculate profit margin explains margin versus markup.

Weighted average or FIFO?

The rules depend on where you report.

• IFRS (IAS 2): inventories are measured at the lower of cost and net realisable value. For interchangeable items, cost is assigned using FIFO or weighted average. LIFO is not among the permitted formulas.
• UK GAAP (FRS 102, Section 13): FIFO or weighted average are accepted; LIFO is not permitted.
• US tax (IRS Publication 538): FIFO, LIFO (you must file Form 970 to adopt it) and specific identification are all recognised, with inventory valued at cost or at the lower of cost or market. Small businesses under the gross receipts threshold ($26 million average over three years, indexed for inflation) may be exempt from keeping formal inventories.

On the candles, 70 units went out:

The total never changes; only the split between cost of goods sold and closing stock does. When prices rise, FIFO shows higher closing stock and a lower cost of sales. In a spreadsheet, weighted average is simpler: one formula per product, no batch tracking. Whatever the accounting method, perishable goods should still leave the shelf oldest-date first. Your accountant will confirm which method suits your books.

Counting stock: the year-end check

The spreadsheet shows book stock. A physical count shows what is really there. Most shops count everything at least once a year, at year end, so the accounts reflect real stock; in France, for example, the Commercial Code requires traders to check their assets, stock included, by inventory at least every twelve months. Ask your accountant what applies to you.

To prepare, add two columns to "Products": N "Counted", filled in on count day, and O "Variance": =N2-G2. The value of the variance is =O2*I2. If you count 29 candles instead of 30, the variance is −1, or −$6.30. Then log that difference as an "Out" movement (note: "Count adjustment") so the file starts again from the true figure. Write down what you count before looking at the spreadsheet, so the book figure doesn't influence you.

Keep business stock records apart from personal spending: our guide to separating personal and business records explains why and how.

When a spreadsheet stops being enough

A well-built spreadsheet handles a shop with a few dozen to a few hundred SKUs. It starts to strain in five situations:

• Several people edit at once: someone overwrites another person's row, or works from an old copy.
• Several sales channels (shop, website, marketplaces): stock has to drop everywhere the moment an order comes in, which a spreadsheet can't do alone.
• Batches and expiry dates per item: one row per batch quickly makes the file unreadable.
• Overwritten formulas from typing in the wrong column: protect calculated columns (Review > Protect Sheet), but the risk remains.
• Logging at the counter or a market stall: opening a spreadsheet on a phone between customers is awkward.

At that point there are three routes: a point-of-sale system with inventory, dedicated inventory software, or a lighter app for logging movements as the day goes.

Binome360's Shop module keeps your products, stock movements and margins, with stock in and stock out logged by typing or by voice. It doesn't connect to a till or a bank: think of it as a stock notebook you keep by talking, not a point-of-sale system.

Frequently asked questions

Is there a free inventory template for Excel?

Yes. The two-tab layout in this guide works in Excel, in Google Sheets (free with a Google account) and in LibreOffice Calc (free). Copy the column headers and formulas above; you do not need macros.

What is the formula for current stock in Excel?

Current stock = opening stock + stock in − stock out. With a movements tab: =C2+SUMIFS(Movements!$D:$D,Movements!$B:$B,$A2,Movements!$C:$C,"In")-SUMIFS(Movements!$D:$D,Movements!$B:$B,$A2,Movements!$C:$C,"Out").

How do I calculate inventory value?

Multiply the quantity on hand by the unit cost, then add up all items. Use cost (what you paid), never the selling price: the margin only exists once the item is sold.

How often should a small shop count stock?

At least once a year, usually at year end, is the common baseline. In practice, a rolling count works better: best sellers every week or month, slow items every quarter.

In short

A trustworthy inventory spreadsheet rests on one rule: you never type the stock figure, you log movements and let formulas do the rest. With SUMIFS, a red reorder alert and a weighted average cost, you always know what you have, what to reorder and what your stock is worth. First action: create the "Products" and "Movements" tabs tonight and enter your five best sellers with this week's movements.

Sources

• IFRS Foundation, IAS 2 "Inventories" (lower of cost and net realisable value; FIFO or weighted average cost): ifrs.org.
• ICAEW, "FRS 102: Inventories under UK GAAP": icaew.com.
• Internal Revenue Service, Publication 538 "Accounting Periods and Methods" (inventory identification and valuation methods, Form 970, small business taxpayer exception): irs.gov.
• French Commercial Code, Article L123-12 (inventory at least every twelve months): legifrance.gouv.fr.
• Binome360 calculations for the worked example (fictional business), October 2026.

Also available in Français.