Inventory management in Excel or another spreadsheet is a good way to get started when the range is manageable and you want more control over purchasing than the webshop’s stock count gives you. With one row per product and a few formulas, the spreadsheet can show which products need ordering now and how many days the stock will last. It requires no special software, and the setup below works in Excel, Google Sheets and most other spreadsheets.
The columns in a simple inventory sheet
Create one row per product and these columns. The formulas are written for row 2 and copied down.
| Column | Content | Formula in English Excel |
|---|---|---|
| A | Product number | |
| B | Name | |
| C | Supplier | |
| D | Stock now | |
| E | On the way | |
| F | Sales last 90 days | |
| G | Sales per day | =F2/90 |
| H | Lead time in days | |
| I | Buffer days | |
| J | Safety stock | =G2*I2 |
| K | Reorder point | =G2*H2+J2 |
| L | Days of cover | =IFERROR((D2+E2)/G2,"") |
| M | Order | =IF(D2+E2<=K2,"Order","") |
| N | Suggested quantity | =IF(M2="Order",K2+G2*30-D2-E2,0) |
English Excel and Google Sheets in English use commas between arguments. In some language versions, such as Danish Excel, the functions have local names and the arguments are separated by semicolons.
What the formulas do
Sales per day is the sales over the last 90 days divided by 90. Buffer days are a simple way to set safety stock, where you decide to keep a few days of extra sales in stock. The more precise method uses the variation in sales and a service level and is described in the guide on safety stock.
The reorder point is the sales during the lead time plus the safety stock. When stock plus goods on the way falls to or below the reorder point, column M shows “Order”. Column N suggests ordering up to the reorder point plus 30 days of sales, so there is roughly a month until the next order. To learn more about choosing the quantity, read the guides on reorder point and order quantity.
An example with three products
| Product | Stock | On the way | Sales 90 days | Sales per day | Lead time | Buffer days | Safety stock | Reorder point | Days of cover | Order | Suggestion |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Coffee pot | 140 | 0 | 270 | 3 | 21 | 7 | 21 | 84 | 46.7 | 0 | |
| Travel mug | 50 | 0 | 180 | 2 | 28 | 7 | 14 | 70 | 25 | Order | 80 |
| Coffee filter | 30 | 100 | 450 | 5 | 14 | 5 | 25 | 95 | 26 | 0 |
The travel mug has 50 units and a reorder point of 2 × 28 + 14 = 70, so it needs ordering. The suggestion is 70 + 2 × 30 − 50 − 0 = 80 units. If the supplier sells in cartons of 12, it is rounded up to 84 units with =CEILING(N2,12).
The coffee filter has only 30 units in stock but 100 units on the way, so stock plus goods on the way is 130 and therefore above the reorder point of 95. That is why it is important to keep the column for goods on the way up to date, otherwise you end up ordering the same goods twice.
How to keep the spreadsheet up to date
- Export stock and sales from the webshop on a fixed day, for example every Monday, and paste the figures into columns D and F.
- Enter ordered quantities in column E when a purchase order is sent, and remove them again when the goods have been received.
- Correct the lead time when a supplier repeatedly delivers later than agreed.
- Sort by column M or L, so the products that need ordering or have the fewest days left are at the top.
The limits of inventory management in a spreadsheet
A simple inventory spreadsheet has some limits that are worth knowing:
- Sales are an average of the last 90 days, so season and promotions are not included. A Christmas product in October gets a reorder point that is far too low. See the guide on seasonality.
- Days when the product was out of stock pull the average down, so popular products that often sell out are ordered too little.
- The figures are only as recent as the latest export, and goods on the way have to be maintained by hand.
- When several people work in the same sheet, it is easy to overwrite a formula or a quantity.
- With several hundred products and many suppliers, the sheet becomes heavy to work in and takes time every week.
When to move on from the spreadsheet
It makes sense to switch when the weekly update takes more than an hour or two, when errors in the sheet have led to stockouts or double orders, or when season and long lead times make average sales too imprecise. lesslater fetches sales and stock automatically from PrestaShop, WooCommerce or Shopify and makes a purchase suggestion per product that accounts for season, stockout days, lead time and goods on the way. See more under purchasing.