Run your inventory in Excel properly, and know when it stops being enough.
Almost everyone starts inventory control in Excel, and for many businesses it is enough. Here is how to build it well, with the columns, the safety stock and the reorder point. We also say when it stops working (several shops, seasons, hundreds of products, several people in one file) and how that same spreadsheet becomes the starting point for software that calculates the order.
Book a 30-minute demoHow it works
Read-only connection · Tested on your own data first · Data hosted in the EU · Demos in English or Spanish
The short answer
Inventory control in Excel works with two sheets, movements and products, plus a safety stock and a reorder point per product calculated by formula: average daily sales times supplier lead time, plus the buffer. It falls short with several shops, a strong season, hundreds of products or several people in one file. The same spreadsheet, as it is, is a valid starting point for software that forecasts demand and calculates the order for you.
By Manuel Gómez González, founder of Arume · Reviewed on
What it does
What Excel does well
Current stock per product, a minimum per product, a simple reorder point, and a filtered list of what is below it. With a PivotTable, an ABC classification of your products by sales in ten minutes. For one warehouse, one person and a few hundred products, that is inventory control.
The columns you need
Product code, name, supplier, supplier lead time in days, average weekly sales, current stock, on order, safety stock, reorder point and quantity to order. The last three are formulas. A separate movements sheet holds one row per receipt or issue, so current stock is a sum and not a number somebody overwrites.
When it breaks
When several shops turn one file into five. When the season makes March's reorder point wrong for November. When products pass about 500 and nobody reviews minimums. When three people edit "stock v7 FINAL (2).xlsx". Excel does not learn: it calculates an average.
Your spreadsheet is the starting point
No new format, no retyping. We read your Excel as it is (sales, stock, movements, whatever your columns are), return a data quality report (what is missing, what does not add up, what is duplicated) and start proposing orders from the first week.
What software does that Excel cannot
Forecast demand as a range instead of an average, learn the season of each product from past years, calculate the risk of reaching zero before the order arrives, put the order into money, and look at several shops at once to move stock before buying.
Excel can remain the output
The weekly order proposal can come back as an Excel file: one sheet per supplier with product, quantity and the reason for each line. Many teams prefer that at first, and it makes shadow mode easy to start.
How far does Excel go?
For a business with one warehouse, one person who buys and a few dozen to a few hundred products, Excel is a perfectly valid stock control. You know what you hold, when to order, and you review it yourself. If someone tells you Excel is not professional, be wary: what is professional is that the stock is right, not the tool.
Excel does well what a fixed formula can do: current stock as a sum of movements, a reorder point per product, a list of what is below it, an ABC split with a PivotTable. What it does not do is learn. It does not know you sell triple in November, that your Asian supplier now takes 60 days instead of 40, or that April sales were zero because you had no stock for three weeks and not because nobody wanted the product.
How do you build the sheet: two tabs and ten columns?
The first tab is movements, and it is the more important one. One row per receipt or issue: date, product code, quantity (positive in, negative out), type (sale, purchase, return, stock adjustment) and the shop or warehouse if you have more than one. Nothing is deleted or overwritten; if a count does not match, you add an adjustment row. That way current stock is a sum and you keep a history of what happened on any day.
The second tab is products, one row each, with these columns: code, name, supplier, supplier lead time in days, average weekly sales (calculated from the movements tab over the last three months, or whatever period suits you), current stock, on order, safety stock, reorder point and quantity to order. The last three are formulas. A filter on "quantity to order greater than zero" is your shopping list for the week.
Use table formatting on both tabs so formulas extend as you add rows, and save a dated copy every Friday. It is not elegant, but it lets you go back when somebody deletes something.
What are the formulas for reorder point and safety stock?
The reorder point is the stock level at which to order so that the order arrives before you hit zero: average daily sales times supplier lead time in days, plus safety stock. If a product sells 4 a day, the supplier takes 10 days and safety stock is 15, the reorder point is 4 x 10 + 15 = 55. When current stock plus what is on order drops below 55, order.
Safety stock is the buffer for weeks when you sell more than usual or the supplier is late. The simple version is the gap between peak daily sales and average daily sales, multiplied by lead time: normal is 4 a day, the peak was 7, lead time is 10 days, so the buffer is (7 - 4) x 10 = 30. A finer version uses how much your daily sales vary and the service level you want, such as 95 % or 98 %.
For the quantity to order, set a maximum per product, for example four weeks of average sales plus safety stock. With 4 a day that is 28 x 4 + 15 = 127. If current stock is 50 and nothing is on order, you need 127 - 50 = 77. Round up to the supplier's pack size with CEILING, which Microsoft describes as returning a number rounded up to the nearest multiple of significance: CEILING(77, 12) gives 84, which is seven boxes of 12.
Can Excel forecast demand?
Excel has a FORECAST.ETS function, which Microsoft documents as using the AAA version of the Exponential Smoothing algorithm. It can detect seasonality automatically, and it needs a timeline with a constant step between points, otherwise it returns a #NUM! error. In textbook terms, exponential smoothing forecasts are weighted averages of past observations, with weights that decay exponentially as the observations get older.
That is a real step beyond a plain average, and for a handful of products it works. It is a forecast of one number per series, though. It does not know your stock, your lead time, the days you were out of stock or the cost of the order. With 500 products you would be maintaining 500 forecast columns by hand, and that is where most spreadsheets quietly stop being used.
What are the signs that Excel has become too small?
It is not only the number of products, though that helps. It is that the method stops holding. Several shops or warehouses and a file that has split into five, merged by hand on Monday. A clear season and a campaign that catches you every year with the off-season reorder point. More than a few hundred products with minimums nobody reviews. Several people editing from different places. The one person who knows what to order going on holiday.
The clearest sign: every Monday somebody spends two or three hours crossing sales and stock to decide the orders, and in the end does it by feel, correcting what the spreadsheet says because they know the average is wrong. That work is exactly what forecasting software does by itself, for every product, every week.
How does the software start from your Excel?
There is no migration. You send the spreadsheet (or all five) as they are. During the data review we map the columns once: this is the date, this is the product, this is the quantity, this is the shop. After that they are read automatically from a shared folder. Before calculating anything, we return a data quality report: duplicated or unnamed products, movements without a date, negative stock, months with gaps. The report often reveals things, and it is yours even if you stop there.
With movements and today's stock we rebuild stock for every day backwards, so we know which days you did not sell because the product was missing. Then we learn the rhythm and season of each product (one year is enough to start, two campaigns refine it), calculate the risk of reaching zero with the real lead time of each supplier, and put each order into money. The weekly proposal can return as an Excel file per supplier, with the reason on each line.
Before trusting it, we replay your last months without knowing the future and compare with what you did. If it does not improve, we say so. Then one or two months in shadow mode, and only after that is it used for real. If you later move to Holded, Odoo or Sage, the engine connects to them and nothing learned is lost.
When should you stay in Excel?
With one warehouse, fewer than a few hundred products, a supplier that delivers within days and similar sales all year, stay in Excel. Build it as above, use the formulas for reorder point and safety stock, and review it once a quarter. It is free, you understand all of it, and nobody will sell you something better at that size.
If you are unsure, the free analysis answers it with your own data: send the spreadsheet and we return which products ran out and for how many days, how much money sits in stock that does not move, and whether, at your size, it pays to move on.
Frequently asked questions
Do you give away a free Excel inventory template?
Something more useful than a closed template: the exact columns and formulas, explained on this page. With them you build your own in half an hour and understand each cell, which is why you will still be using it in a year.
How do I calculate the reorder point in Excel?
Reorder point = average daily sales x lead time in days + safety stock. With 4 a day, 10 days and 15 of safety stock, it is 55. When stock plus what is on order falls below 55, it is time to order.
And safety stock?
A simple way: peak daily sales minus average daily sales, times lead time. A finer way uses the variation of your sales and the service level you want, for example 95 % or 98 %.
Do I need macros or VBA?
No, and it is better without. A movements sheet, a products sheet with ten columns and a PivotTable run inventory control without a macro. Macros break first when the person or the Excel version changes. If you feel you need them, Excel is probably getting too small.
Does Google Sheets work as well?
Yes, with the same columns and formulas, and with one advantage: several people can work at once without file versions. We read it just like an Excel file.
Do I have to leave Excel to use your software?
No. At first we read your Excel and the proposal can return in Excel. If you later adopt an ERP, the engine connects to it. If you never want one, the engine can keep the stock itself while you keep viewing what you like in Excel.
What format does my spreadsheet need?
Whatever you have. Somewhere there must be sales with date, product and quantity (and shop, if several), current stock and goods received. Column names do not matter: we map them once. If something is missing, the data quality report says so.