{"id":14782,"date":"2026-11-18T16:00:00","date_gmt":"2026-11-18T16:00:00","guid":{"rendered":"https:\/\/cashflowinventory.com\/blog\/?p=14782"},"modified":"2026-11-13T07:42:20","modified_gmt":"2026-11-13T07:42:20","slug":"reorder-point-excel-template","status":"publish","type":"post","link":"https:\/\/cashflowinventory.com\/blog\/reorder-point-excel-template\/","title":{"rendered":"Reorder Point Spreadsheet: ROP and Safety Stock for Every SKU at Once"},"content":{"rendered":"<p>A reorder point Excel template works out, for each SKU on its own row, the stock level at which you should place the next order. The formula is reorder point = average daily sales \u00d7 lead time + safety stock. Safety stock is calculated from how much your daily sales swing, how much your supplier&#8217;s lead time swings, and the service level you choose. Excel&#8217;s NORM.S.INV function turns that service level into a Z score.<\/p>\n<p>This page gives you a spreadsheet that does this for your whole catalogue at once. Each row takes five inputs and returns a safety stock, a reorder point, a &#8220;Reorder&#8221; or &#8220;OK&#8221; flag and the cash tied up in safety stock. Every formula is explained below so you can check it or rebuild it yourself.<\/p>\n<p>You also get a worked example with eight products, a step-by-step routine for filling in the sheet, and the mistakes that quietly break reorder points. The last section shows where the same logic lives in Cash Flow Inventory.<\/p>\n<div class=\"download-section\">\n<p class=\"download-title\"><strong>Free download: Reorder point and safety stock template<\/strong><\/p>\n<p>An Excel file (.xlsx, 12 KB) with the formulas already in place and the example from this guide filled in. It opens in Excel, Google Sheets (File \u203a Import), Numbers and LibreOffice. No sign-up needed.<\/p>\n<p><a class=\"btn btn-gold\" href=\"https:\/\/cashflowinventory.com\/blog\/wp-content\/uploads\/cfi-guides\/reorder-point-excel-template\/reorder-point-and-safety-stock-template.xlsx\" download>Download the spreadsheet<\/a><\/p>\n<\/div>\n<div class=\"takeaways-section\">\n<h2>Key takeaways<\/h2>\n<ul>\n<li>Reorder point = average daily sales \u00d7 lead time + safety stock. Work it out per SKU, never for the catalogue as a whole.<\/li>\n<li>Safety stock = Z \u00d7 \u221a(lead time \u00d7 sales std dev\u00b2 + daily sales\u00b2 \u00d7 lead time std dev\u00b2). If you ignore supplier delays, you can underestimate it by half or more.<\/li>\n<li>Set the service level by SKU. High-value, slow items at 98\u201399% can tie up more cash than everything else combined.<\/li>\n<li>Compare the reorder point with stock on hand plus stock on order, not with on-hand stock alone.<\/li>\n<li>Recalculate monthly, and before seasonal peaks, from clean sales history that excludes days when you were out of stock.<\/li>\n<\/ul>\n<\/div>\n<h2>The reorder point and safety stock formulas, explained<\/h2>\n<p>A reorder point has two parts. The first part covers what you expect to sell while you wait for the delivery. The second part, safety stock, covers what you might sell on a bad day or if the supplier is late. If you want the background first, read our guides to the <a href=\"https:\/\/cashflowinventory.com\/blog\/reorder-point\/\">reorder point definition and formula<\/a> and to <a href=\"https:\/\/cashflowinventory.com\/blog\/safety-stock\/\">safety stock and its formulas<\/a>.<\/p>\n<p class=\"formula-line\"><code>Lead-time demand = average daily sales \u00d7 lead time (days)<\/code><\/p>\n<p class=\"formula-line\"><code>Safety stock = Z \u00d7 \u221a(lead time \u00d7 std dev of daily sales\u00b2 + average daily sales\u00b2 \u00d7 std dev of lead time\u00b2)<\/code><\/p>\n<p class=\"formula-line\"><code>Reorder point = lead-time demand + safety stock<\/code><\/p>\n<p>Here is what each part means:<\/p>\n<ul>\n<li><strong>Average daily sales (d):<\/strong> units sold per day over a recent, representative window, usually 60\u201390 days.<\/li>\n<li><strong>Std dev of daily sales (\u03c3d):<\/strong> how much daily sales vary around that average. In Excel, use STDEV.S on a column of daily totals. See the <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/stdev-s-function-7d69cf97-0c1f-4acf-be27-f3e83904cc23\" target=\"_blank\" rel=\"noopener\">Microsoft reference for STDEV.S<\/a>.<\/li>\n<li><strong>Lead time (L):<\/strong> the average number of days from placing the order to having stock you can sell, not just to dispatch. Our article on <a href=\"https:\/\/cashflowinventory.com\/blog\/lead-time\/\">what lead time includes<\/a> covers the hidden days, such as customs and goods-in checks.<\/li>\n<li><strong>Std dev of lead time (\u03c3L):<\/strong> how much that delay varies. Use 0 for a supplier who has never been late.<\/li>\n<li><strong>Z:<\/strong> the number of standard deviations of cover that matches your chosen service level. In Excel, <code>=NORM.S.INV(0.95)<\/code> returns about 1.645. See the <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/norm-s-inv-function-d6d556b4-ab7f-49cd-b526-5a20918452b1\" target=\"_blank\" rel=\"noopener\">Microsoft reference for NORM.S.INV<\/a>.<\/li>\n<\/ul>\n<p>The square root combines two independent sources of risk: demand noise during the lead time, and the lead time itself running long. The method assumes these behave roughly like a normal distribution. The <a href=\"https:\/\/www.itl.nist.gov\/div898\/handbook\/\" target=\"_blank\" rel=\"noopener\">NIST Engineering Statistics Handbook<\/a> explains that assumption well. It holds best for steady sellers, and later sections cover what to do when it does not.<\/p>\n<h3>What the service level actually means<\/h3>\n<p>A 95% service level here is a cycle service level. It means that in 95 out of 100 replenishment cycles, you do not run out before the delivery lands. It does not mean 95% of customer orders are filled. Choosing it per product is a commercial decision, and our guide to <a href=\"https:\/\/cashflowinventory.com\/blog\/desired-service-level\/\">setting a desired service level<\/a> walks through the trade-offs.<\/p>\n<h2>What is in the reorder point Excel template<\/h2>\n<div class=\"sheet-spec\" id=\"ss-reorder-point-excel-template-1\" data-sheet-spec>\n<div class=\"sheet-spec__intro\">\n<p>The file has three sheets.<\/p>\n<\/div>\n<div class=\"sheet-spec__sheets\">\n<div class=\"sheet-spec__sheet\" id=\"ss-reorder-point-excel-template-1-s1\" data-sheet-name=\"ROP by SKU\">\n<p role=\"heading\" aria-level=\"3\" class=\"sheet-spec__name\" id=\"ss-reorder-point-excel-template-1-s1-h\"><span class=\"sheet-spec__icon\" aria-hidden=\"true\"><\/span>ROP by SKU <span class=\"sheet-spec__kind\">sheet<\/span><\/p>\n<p class=\"sheet-spec__lead\">The main sheet, <strong>ROP by SKU<\/strong>, has one row per product with these columns:<\/p>\n<div class=\"sheet-spec__scroll\" tabindex=\"0\" role=\"region\" aria-label=\"ROP by SKU sheet: columns and formulas\">\n<table class=\"sheet-spec__table\" role=\"table\">\n<caption class=\"screen-reader-text\">Columns of the ROP by SKU sheet: header, what it holds, the example in row 2 and the formula<\/caption>\n<thead role=\"rowgroup\">\n<tr role=\"row\">\n<th scope=\"col\" role=\"columnheader\" class=\"sheet-spec__c-col\">Col<\/th>\n<th scope=\"col\" role=\"columnheader\">Header<\/th>\n<th scope=\"col\" role=\"columnheader\">What it holds<\/th>\n<th scope=\"col\" role=\"columnheader\" class=\"sheet-spec__c-ex\">Example <span class=\"sheet-spec__row\">row 2<\/span><\/th>\n<th scope=\"col\" role=\"columnheader\">Formula <span class=\"sheet-spec__row\">row 2<\/span><\/th>\n<\/tr>\n<\/thead>\n<tbody role=\"rowgroup\">\n<tr role=\"row\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>A, B<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>SKU<\/strong> and <strong>Product<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> Typed.<\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__cell\">A<\/span> KW-PAN-24<br \/><span class=\"sheet-spec__cell\">B<\/span> Non-stick frying pan 24 cm<\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell is-empty\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__dim\" aria-label=\"no formula\">\u2014<\/span><\/td>\n<\/tr>\n<tr role=\"row\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>C\u2013G<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Avg daily sales<\/strong>, <strong>Std dev daily sales<\/strong>, <strong>Lead time (days)<\/strong>, <strong>Lead time std dev (days)<\/strong>, <strong>Service level<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> Your inputs. Enter the service level as a percentage, such as 95%.<\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__cell\">C<\/span> 6<br \/><span class=\"sheet-spec__cell\">D<\/span> 2.50<br \/><span class=\"sheet-spec__cell\">E<\/span> 14<br \/><span class=\"sheet-spec__cell\">F<\/span> 3<br \/><span class=\"sheet-spec__cell\">G<\/span> 95.0%<\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell is-empty\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__dim\" aria-label=\"no formula\">\u2014<\/span><\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>H<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Z score<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">Calculated<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__dim\" aria-label=\"empty\">\u2014<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"H2\">=NORM.S.INV(G2)<\/code><\/span> <\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>I<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Safety stock<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> Rounded up because you cannot hold part of a frying pan.<\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__dim\" aria-label=\"empty\">\u2014<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"I2\">=ROUNDUP(H2*SQRT(E2*D2^2+C2^2*F2^2),0)<\/code><\/span> <\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>J<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Lead-time demand<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">Calculated<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> 84<\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"J2\">=C2*E2<\/code><\/span> <\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>K<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Reorder point<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">Calculated<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__dim\" aria-label=\"empty\">\u2014<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"K2\">=ROUNDUP(J2+I2,0)<\/code><\/span> <\/td>\n<\/tr>\n<tr role=\"row\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>L, M<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>On hand<\/strong> and <strong>On order<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> Typed from your stock records.<\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__cell\">L<\/span> 95<br \/><span class=\"sheet-spec__cell\">M<\/span> 0<\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell is-empty\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__dim\" aria-label=\"no formula\">\u2014<\/span><\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>N<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Stock position<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">Calculated<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> 95<\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"N2\">=L2+M2<\/code><\/span> <\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>O<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Action<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">Calculated<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__dim\" aria-label=\"empty\">\u2014<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"O2\">=IF(N2&lt;=K2,\"Reorder\",\"OK\")<\/code><\/span> <\/td>\n<\/tr>\n<tr role=\"row\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>P<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Unit cost<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">You type it<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> 9.50<\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell is-empty\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__dim\" aria-label=\"no formula\">\u2014<\/span><\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>Q<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Safety stock value<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">Calculated<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__dim\" aria-label=\"empty\">\u2014<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"Q2\">=I2*P2<\/code><\/span> <\/td>\n<\/tr>\n<tr role=\"row\" class=\"is-calc\">\n<th scope=\"row\" role=\"rowheader\" class=\"sheet-spec__col\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Column <\/span>R<\/th>\n<td role=\"cell\" class=\"sheet-spec__h\"><strong>Safety cover (days)<\/strong><\/td>\n<td role=\"cell\" class=\"sheet-spec__w\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">What it holds<\/span> <span class=\"sheet-spec__dim\">Calculated<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__x\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Example, row 2<\/span> <span class=\"sheet-spec__dim\" aria-label=\"empty\">\u2014<\/span><\/td>\n<td role=\"cell\" class=\"sheet-spec__fcell\"><span class=\"sheet-spec__lbl\" aria-hidden=\"true\">Formula<\/span> <span class=\"sheet-spec__fx\"><code class=\"sheet-spec__f\" data-cell=\"R2\">=IFERROR(I2\/C2,0)<\/code><\/span> <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"sheet-spec__foot\">\n<p class=\"sheet-spec__fill\"><strong>Filling down:<\/strong> each formula is shown for the first row of data. Copy it down the column and the spreadsheet moves the row numbers for you (B2 becomes B3, B4 \u2026); references with $ signs stay fixed.<\/p>\n<\/div>\n<div class=\"sheet-spec__outro\">\n<p>The <strong>Z lookup<\/strong> sheet lists common service levels next to their Z scores, so you can see how quickly cover grows as you approach 100%. The <strong>Summary<\/strong> sheet counts SKUs to reorder and totals the units and money held as safety stock. If you also need a general stock list with in and out movements, pair this file with our <a href=\"https:\/\/cashflowinventory.com\/blog\/inventory-spreadsheet-template\/\">inventory spreadsheet template with reorder alerts<\/a>.<\/p>\n<\/div>\n<p class=\"sheet-spec__dl\"><a class=\"btn btn-gold\" href=\"https:\/\/cashflowinventory.com\/blog\/wp-content\/uploads\/cfi-guides\/reorder-point-excel-template\/reorder-point-and-safety-stock-template.xlsx\" download>Download the spreadsheet<\/a><span class=\"sheet-spec__dlmeta\">.xlsx, 12 KB \u00b7 formulas and the worked example filled in \u00b7 Excel, Google Sheets, Numbers, LibreOffice<\/span><\/p>\n<\/div>\n<h2>Worked example: reorder points for eight SKUs<\/h2>\n<div class=\"example-section\">\n<p><strong>Example: a shop selling kitchenware online and from one high-street store.<\/strong> The business is hypothetical, and the figures are illustrative. The owner pulled 90 days of daily sales per SKU and the last eight purchase orders per supplier. Cast iron and knives come from a distant importer with long, uneven lead times. Tea towels and mugs come from a reliable local wholesaler.<\/p>\n<div class=\"table-responsive\" tabindex=\"0\" role=\"region\" aria-label=\"Table: SKU (scrolls sideways)\">\n<table>\n<thead>\n<tr>\n<th>SKU<\/th>\n<th>Product<\/th>\n<th>Avg daily sales<\/th>\n<th>Std dev daily sales<\/th>\n<th>Lead time (days)<\/th>\n<th>Lead time std dev<\/th>\n<th>Service level<\/th>\n<th>Z<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>KW-PAN-24<\/td>\n<td>Non-stick frying pan 24 cm<\/td>\n<td>6<\/td>\n<td>2.5<\/td>\n<td>14<\/td>\n<td>3<\/td>\n<td>95%<\/td>\n<td>1.645<\/td>\n<\/tr>\n<tr>\n<td>KW-KNF-8<\/td>\n<td>Chef&#8217;s knife 8 in<\/td>\n<td>3<\/td>\n<td>1.8<\/td>\n<td>21<\/td>\n<td>4<\/td>\n<td>95%<\/td>\n<td>1.645<\/td>\n<\/tr>\n<tr>\n<td>KW-MUG-4<\/td>\n<td>Mug set of 4<\/td>\n<td>4<\/td>\n<td>2<\/td>\n<td>7<\/td>\n<td>0<\/td>\n<td>90%<\/td>\n<td>1.282<\/td>\n<\/tr>\n<tr>\n<td>KW-TOW-2<\/td>\n<td>Tea towel 2-pack<\/td>\n<td>10<\/td>\n<td>4<\/td>\n<td>5<\/td>\n<td>1<\/td>\n<td>95%<\/td>\n<td>1.645<\/td>\n<\/tr>\n<tr>\n<td>KW-CAST-28<\/td>\n<td>Cast iron casserole 28 cm<\/td>\n<td>0.8<\/td>\n<td>0.9<\/td>\n<td>30<\/td>\n<td>6<\/td>\n<td>98%<\/td>\n<td>2.054<\/td>\n<\/tr>\n<tr>\n<td>KW-SPAT-S<\/td>\n<td>Silicone spatula<\/td>\n<td>12<\/td>\n<td>5<\/td>\n<td>10<\/td>\n<td>2<\/td>\n<td>99%<\/td>\n<td>2.326<\/td>\n<\/tr>\n<tr>\n<td>KW-SCALE-D<\/td>\n<td>Digital kitchen scale<\/td>\n<td>2<\/td>\n<td>1.2<\/td>\n<td>14<\/td>\n<td>0<\/td>\n<td>95%<\/td>\n<td>1.645<\/td>\n<\/tr>\n<tr>\n<td>KW-JAR-500<\/td>\n<td>Glass storage jar 500 ml<\/td>\n<td>8<\/td>\n<td>3<\/td>\n<td>10<\/td>\n<td>2<\/td>\n<td>90%<\/td>\n<td>1.282<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Take the frying pan as the worked line. Demand noise over the lead time is 14 \u00d7 2.5\u00b2 = 87.5. Lead-time noise is 6\u00b2 \u00d7 3\u00b2 = 324. Their sum is 411.5, and its square root is 20.29. Multiplied by 1.645, that gives 33.37, which rounds up to a safety stock of 34. Lead-time demand is 6 \u00d7 14 = 84, so the reorder point is 84 + 34 = 118.<\/p>\n<div class=\"table-responsive\" tabindex=\"0\" role=\"region\" aria-label=\"Table: SKU (scrolls sideways)\">\n<table>\n<thead>\n<tr>\n<th>SKU<\/th>\n<th>Safety stock<\/th>\n<th>Lead-time demand<\/th>\n<th>Reorder point<\/th>\n<th>On hand<\/th>\n<th>On order<\/th>\n<th>Stock position<\/th>\n<th>Action<\/th>\n<th>Unit cost<\/th>\n<th>Safety stock value<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>KW-PAN-24<\/td>\n<td>34<\/td>\n<td>84<\/td>\n<td>118<\/td>\n<td>95<\/td>\n<td>0<\/td>\n<td>95<\/td>\n<td>Reorder<\/td>\n<td>9.50<\/td>\n<td>323.00<\/td>\n<\/tr>\n<tr>\n<td>KW-KNF-8<\/td>\n<td>24<\/td>\n<td>63<\/td>\n<td>87<\/td>\n<td>60<\/td>\n<td>40<\/td>\n<td>100<\/td>\n<td>OK<\/td>\n<td>14.00<\/td>\n<td>336.00<\/td>\n<\/tr>\n<tr>\n<td>KW-MUG-4<\/td>\n<td>7<\/td>\n<td>28<\/td>\n<td>35<\/td>\n<td>50<\/td>\n<td>0<\/td>\n<td>50<\/td>\n<td>OK<\/td>\n<td>6.20<\/td>\n<td>43.40<\/td>\n<\/tr>\n<tr>\n<td>KW-TOW-2<\/td>\n<td>23<\/td>\n<td>50<\/td>\n<td>73<\/td>\n<td>40<\/td>\n<td>0<\/td>\n<td>40<\/td>\n<td>Reorder<\/td>\n<td>2.40<\/td>\n<td>55.20<\/td>\n<\/tr>\n<tr>\n<td>KW-CAST-28<\/td>\n<td>15<\/td>\n<td>24<\/td>\n<td>39<\/td>\n<td>20<\/td>\n<td>12<\/td>\n<td>32<\/td>\n<td>Reorder<\/td>\n<td>32.00<\/td>\n<td>480.00<\/td>\n<\/tr>\n<tr>\n<td>KW-SPAT-S<\/td>\n<td>67<\/td>\n<td>120<\/td>\n<td>187<\/td>\n<td>210<\/td>\n<td>0<\/td>\n<td>210<\/td>\n<td>OK<\/td>\n<td>1.80<\/td>\n<td>120.60<\/td>\n<\/tr>\n<tr>\n<td>KW-SCALE-D<\/td>\n<td>8<\/td>\n<td>28<\/td>\n<td>36<\/td>\n<td>30<\/td>\n<td>0<\/td>\n<td>30<\/td>\n<td>Reorder<\/td>\n<td>7.50<\/td>\n<td>60.00<\/td>\n<\/tr>\n<tr>\n<td>KW-JAR-500<\/td>\n<td>24<\/td>\n<td>80<\/td>\n<td>104<\/td>\n<td>110<\/td>\n<td>0<\/td>\n<td>110<\/td>\n<td>OK<\/td>\n<td>3.10<\/td>\n<td>74.40<\/td>\n<\/tr>\n<tr>\n<td><strong>Total<\/strong><\/td>\n<td><strong>202<\/strong><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<td><strong>4 to reorder<\/strong><\/td>\n<td><\/td>\n<td><strong>1,492.60<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Z is shown to three decimals here. The sheet uses the full value from NORM.S.INV, and after rounding up the results are the same. Amounts are in your own currency.<\/p>\n<\/div>\n<h3>What the example tells you<\/h3>\n<ul>\n<li><strong>Supplier variability dominates.<\/strong> If you ignore the frying pan&#8217;s three-day lead time wobble, you get 1.645 \u00d7 2.5 \u00d7 \u221a14 = 15.39, which rounds up to 16 units. That is less than half of the 34 the full formula gives.<\/li>\n<li><strong>The knife is not reordered, even though 60 is below 87.<\/strong> The 40 already on order bring its position to 100. If you check on-hand stock alone, you will double-order.<\/li>\n<li><strong>The casserole costs the most to protect.<\/strong> At 98% and 32.00 a unit, its 15 units of safety stock hold 480.00. That is nearly a third of the 1,492.60 total, for a product that sells less than one a day. The spatula sits at 99% for only 120.60.<\/li>\n<li><strong>Safety cover in days is a useful sense check.<\/strong> The pan holds 34 \u00f7 6 = 5.7 days of cover, and the casserole holds 15 \u00f7 0.8 = 18.75 days. When one SKU shows weeks of cover, ask whether its inputs are right.<\/li>\n<\/ul>\n<h2>How to fill in the spreadsheet: step by step<\/h2>\n<ol>\n<li><strong>Export daily sales per SKU<\/strong> for the last 60\u201390 days, with one column per SKU and one row per day. Put a 0 on days with no sales, because blank days inflate the average.<\/li>\n<li><strong>Remove days when you were out of stock.<\/strong> Those zeros show missing stock, not missing demand.<\/li>\n<li><strong>Calculate the average and standard deviation<\/strong> of each column with AVERAGE and STDEV.S, then paste the values into columns C and D.<\/li>\n<li><strong>Measure lead times from real purchase orders.<\/strong> For each supplier, take the last 5\u201310 orders and count the days from the order date to goods available for sale. Enter the average in E and the STDEV.S of those days in F.<\/li>\n<li><strong>Set a service level per SKU.<\/strong> A simple rule: 97\u201399% for traffic drivers and cheap fast movers, 95% for core range, 90% for slow or expensive items and anything easy to substitute.<\/li>\n<li><strong>Enter on-hand and on-order quantities<\/strong> from today&#8217;s records, after any pending adjustments are posted.<\/li>\n<li><strong>Sort by Action, then by Safety stock value.<\/strong> Raise orders for the &#8220;Reorder&#8221; lines, and review any SKU where safety stock value looks out of proportion.<\/li>\n<li><strong>Recalculate monthly<\/strong>, and again about a lead time before any seasonal peak.<\/li>\n<\/ol>\n<div class=\"tip-section\">\n<p><strong>Expert tip:<\/strong> Keep your units consistent. If lead time is in days, sales and their standard deviation must be per day too. Mixing weekly sales with daily lead times is the most common reason a reorder point comes out seven times too high or too low.<\/p>\n<\/div>\n<h2>Common reorder point mistakes the formula will not catch<\/h2>\n<ul>\n<li><strong>Converting a monthly standard deviation to daily by dividing by 30.<\/strong> Variability scales with the square root of time, so a monthly \u03c3 divided by 30 understates daily \u03c3 badly. Calculate \u03c3 from daily figures directly.<\/li>\n<li><strong>Using the worst-case lead time and adding lead-time variability as well.<\/strong> That counts the same risk twice. Use the average lead time in E and let F carry the spread.<\/li>\n<li><strong>Entering 100% as the service level.<\/strong> NORM.S.INV(1) returns #NUM! because full certainty needs infinite stock. Cap it at 99.5%.<\/li>\n<li><strong>Comparing the reorder point with on-hand stock only.<\/strong> Always use on hand + on order \u2212 stock already promised to open orders.<\/li>\n<li><strong>Forgetting your review cycle.<\/strong> The formula assumes you check stock continuously. If you only order on Mondays, see the edge cases below.<\/li>\n<li><strong>Applying one service level to every SKU.<\/strong> As the casserole shows, that pushes cash into the wrong products. Pairing service levels with <a href=\"https:\/\/cashflowinventory.com\/blog\/xyz-analysis-in-inventory-management\/\">XYZ analysis of demand variability<\/a> gives you a defensible rule.<\/li>\n<li><strong>Letting the sheet drift.<\/strong> A reorder point set in spring and never revisited is just a guess with decimals.<\/li>\n<\/ul>\n<h2>Edge cases: slow movers, seasonality, new SKUs and periodic ordering<\/h2>\n<h3>You only place orders once a week or once a month<\/h3>\n<p>If you review stock every R days, a shortage can start just after a review and go unnoticed until the next one. Protect the full lead time plus the review period. Replace E with (E + R) in the safety stock and lead-time demand formulas:<\/p>\n<p class=\"formula-line\"><code>Safety stock = Z \u00d7 \u221a((lead time + review days) \u00d7 std dev of daily sales\u00b2 + average daily sales\u00b2 \u00d7 std dev of lead time\u00b2)<\/code><\/p>\n<h3>Slow and lumpy sellers<\/h3>\n<p>For items selling less than about one a day, such as the casserole, daily sales are mostly zeros with the odd sale. The normal-distribution assumption is weak there. A plain minimum and maximum, or a two-bin rule, is often easier to run, and you should sense-check the sheet&#8217;s answer against your last few months&#8217; sales.<\/p>\n<h3>Seasonal products<\/h3>\n<p>A 90-day window in September knows nothing about December. For seasonal lines, base the average and \u03c3 on the same weeks last year, adjusted for growth. Raise the reorder point at least one lead time before the peak starts, then lower it again afterwards so you do not carry the peak level into a quiet month.<\/p>\n<h3>New SKUs with no history<\/h3>\n<p>Borrow d and \u03c3d from the closest existing product, such as a 28 cm pan for a new 24 cm pan. Use a conservative service level, then replace the borrowed figures after 30\u201360 days of real sales.<\/p>\n<h3>Minimum order quantities and pack sizes<\/h3>\n<p>The reorder point tells you <em>when<\/em> to order, not <em>how much<\/em>. If a supplier only sells in cartons of 24, your order quantity rounds up to cartons, but the reorder point itself stays as calculated. For the quantity, our free <a href=\"https:\/\/cashflowinventory.com\/tools\/reorder-point-calculator.php\">reorder point calculator<\/a> and the EOQ approach help you check one SKU at a time.<\/p>\n<h3>Several locations<\/h3>\n<p>Calculate one row per SKU per location, using each location&#8217;s own sales and the lead time to that location. Pooling sales across branches understates the safety stock each branch actually needs.<\/p>\n<div class=\"checklist-section\">\n<h3>Monthly reorder point review checklist<\/h3>\n<ul>\n<li>Sales export covers the last 60\u201390 days, daily, with stock-out days removed.<\/li>\n<li>Lead times updated from the latest purchase orders, measured to goods available for sale.<\/li>\n<li>Units match: per-day sales with lead times in days.<\/li>\n<li>No service level at 100%; high levels reserved for items that justify them.<\/li>\n<li>On-order quantities current; cancelled purchase orders removed.<\/li>\n<li>Seasonal SKUs switched to last year&#8217;s peak-period figures where relevant.<\/li>\n<li>Top five SKUs by safety stock value reviewed and justified.<\/li>\n<li>Every &#8220;Reorder&#8221; line either ordered or noted with a reason.<\/li>\n<\/ul>\n<\/div>\n<h2>How to do this in Cash Flow Inventory<\/h2>\n<p>The spreadsheet is a good way to understand and sense-check your numbers. Once your catalogue grows, keeping the inputs current by hand becomes the hard part. In Cash Flow Inventory, stock on a product is never typed in. Every receipt, sale, transfer, adjustment and count is a movement in one log, so on-hand figures reflect what actually happened.<\/p>\n<ol>\n<li><strong>Set up the product properly.<\/strong> In Inventory \u203a Products, choose New product. Add the Supplier and Cost, and choose the locations under Stocked at. For things you produce, switch on Made in-house (no supplier) instead. The <a href=\"https:\/\/cashflowinventory.com\/documentation\/guides\/products-inventory\/\">products and inventory guide<\/a> covers every field.<\/li>\n<li><strong>Use Inventory \u203a Planning for the reorder figures.<\/strong> Planning holds each item&#8217;s forecast, reorder point and safety stock, and a nightly planning run saves a plan for each item.<\/li>\n<li><strong>Work from the Restock tab.<\/strong> On Inventory \u203a Products, the Restock tab shows what is low or out, with what to reorder. From a row&#8217;s menu, choose New purchase order to raise it. On a single product page, See what to reorder opens the same restock view.<\/li>\n<li><strong>Refill to a maximum where that suits you.<\/strong> Inventory \u203a Replenishment shows items below their minimum. For items you refill by making, it can order up to the maximum as work orders.<\/li>\n<li><strong>Plan made-in-house items the same way.<\/strong> Production \u203a Demand plans uses the same reorder engine as purchasing. Choose a Demand window and location, then choose Make it on a row to open a work order for the shortfall. The <a href=\"https:\/\/cashflowinventory.com\/documentation\/guides\/production-demand-planning\/\">production demand planning guide<\/a> explains how demand, on hand and on order are combined.<\/li>\n<\/ol>\n<p>Many businesses run the spreadsheet alongside the system for a month or two. They compare the two answers SKU by SKU before trusting either one fully.<\/p>\n<div class=\"download-section\">\n<p class=\"download-title\"><strong>Free download: Reorder point and safety stock template<\/strong><\/p>\n<p>An Excel file (.xlsx, 12 KB) with the formulas already in place and the example from this guide filled in. It opens in Excel, Google Sheets (File \u203a Import), Numbers and LibreOffice. No sign-up needed.<\/p>\n<p><a class=\"btn btn-gold\" href=\"https:\/\/cashflowinventory.com\/blog\/wp-content\/uploads\/cfi-guides\/reorder-point-excel-template\/reorder-point-and-safety-stock-template.xlsx\" download>Download the spreadsheet<\/a><\/p>\n<\/div>\n<h2>Frequently asked questions<\/h2>\n<h3>How do I calculate a reorder point in Excel?<\/h3>\n<p>Multiply average daily sales by lead time in days, then add safety stock: <code>=C2*E2+I2<\/code>. Calculate safety stock with <code>=ROUNDUP(NORM.S.INV(G2)*SQRT(E2*D2^2+C2^2*F2^2),0)<\/code>, where G2 is your service level. Copy both formulas down so every SKU gets its own result.<\/p>\n<h3>What service level should I use for safety stock?<\/h3>\n<p>Most small product businesses use 90\u201395% for the bulk of the range. They go higher only for items whose absence loses the whole basket, or that are cheap to hold. Expensive slow movers rarely justify 98% or more, because each extra point of service costs progressively more stock.<\/p>\n<h3>Why does my safety stock come out higher than the simple formula?<\/h3>\n<p>The simple formula, Z \u00d7 \u03c3d \u00d7 \u221aL, assumes your supplier is never late. Once you add lead-time variability, safety stock often doubles, as the frying pan example shows (16 units versus 34). If your supplier really is that consistent, set the lead time std dev to 0 and the two formulas agree.<\/p>\n<h3>How often should I recalculate reorder points?<\/h3>\n<p>Monthly is a sensible default for most SKUs. Recalculate sooner when a supplier changes, when lead times shift, or about one lead time before a seasonal peak or a planned promotion.<\/p>\n<h3>Does this template work in Google Sheets?<\/h3>\n<p>Yes. Google Sheets supports NORM.S.INV, SQRT, ROUNDUP, IF and IFERROR with the same syntax, so you can upload the file and the formulas carry over unchanged.<\/p>\n<h3>What is the difference between a reorder point and a minimum stock level?<\/h3>\n<p>A reorder point is calculated from sales, lead time and variability, so it moves as those change. A minimum stock level is usually a fixed number someone chose. Many teams use the calculated reorder point as their minimum and review it on a schedule.<\/p>\n<p>A spreadsheet is the right place to learn how reorder points behave. Keeping hundreds of rows current every month is where it starts to cost you time and stock-outs. When you want reorder points, safety stock and restock suggestions worked out from real stock movements, see how <a href=\"https:\/\/cashflowinventory.com\/inventory-management-software.php\">Cash Flow Inventory&#8217;s inventory management software<\/a> handles it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A free spreadsheet that calculates safety stock and a reorder point for every SKU at once. It includes the formulas explained, an eight-SKU worked example, and the edge cases that generic answers skip.<\/p>\n","protected":false},"author":1,"featured_media":14783,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[159,103,72,158,160],"class_list":["post-14782","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-inventory","tag-excel-template","tag-purchasing","tag-reorder-point","tag-safety-stock","tag-service-level"],"jetpack_featured_media_url":"https:\/\/cashflowinventory.com\/blog\/wp-content\/uploads\/cfi-guides\/reorder-point-excel-template\/reorder-point-excel-template.webp","_links":{"self":[{"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/posts\/14782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/comments?post=14782"}],"version-history":[{"count":2,"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/posts\/14782\/revisions"}],"predecessor-version":[{"id":15915,"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/posts\/14782\/revisions\/15915"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/media\/14783"}],"wp:attachment":[{"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/media?parent=14782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/categories?post=14782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cashflowinventory.com\/blog\/wp-json\/wp\/v2\/tags?post=14782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}