Good inventory management determines success or failure on Amazon. With these Excel templates and formulas, you keep your inventory under control – without expensive software.

Download: You can download all the Excel templates from this article for free at the end.

The Basics of Inventory Planning

Before we dive into the formulas, here are the most important concepts in brief:

Reorder Point (ROP)

The inventory level at which you should reorder.

Safety Stock

Buffer stock for unexpected demand spikes.

Lead Time

Time from order to goods arriving at Amazon.

Days of Supply

How many days will your current inventory last?

The Most Important Excel Formulas

1. Average Daily Sales

The basis of all calculations is your average daily sales. Use the last 30 days for a reliable figure:

Excel formula: Average daily sales
=SUM(Sales_30_Days)/30

Or with weighting of the recent days (important for trends):

Excel formula: Weighted average
=SUMPRODUCT(Sales;Weight)/SUM(Weight)

2. Inventory Coverage (Days of Supply)

How many more days will your inventory last?

Excel formula: Inventory coverage
=Current_Stock/Average_Daily_Sales
Rule of thumb: Your inventory coverage should be at least Lead Time + Safety Stock days. For FBA, you should have at least 30-45 days of coverage.

3. Calculating the Reorder Point

The point at which you have to reorder:

Excel formula: Reorder Point
=Daily_Sales * (Lead_Time + Safety_Stock_Days)

Example: 10 units/day × (21 days Lead Time + 7 days Safety) = 280 units

4. Calculating Safety Stock

Buffer stock based on demand fluctuations:

Excel formula: Safety Stock (simple)
=Daily_Sales * Safety_Factor

The Safety_Factor is typically 0,2-0,5 (20-50% of the lead time requirement).

Excel formula: Safety Stock (advanced)
=NORM.INV(Service_Level;0;1) * STDEV(Daily_Sales) * SQRT(Lead_Time)

5. Optimal Order Quantity (EOQ)

The economically optimal order quantity using the EOQ formula:

Excel formula: Economic Order Quantity
=SQRT((2 * Annual_Sales * Order_Cost) / (Unit_Cost * Holding_Cost%))

The Inventory Planning Template

Here's how to build your Excel template:

Column Contents Formula
A SKU / ASIN -
B Product name -
C Current inventory Import
D Sales 30 days Import
E Ø Daily sales =D2/30
F Lead Time (days) Manual
G Coverage (days) =C2/E2
H Reorder Point =E2*(F2+7)
I Status =IF(C2<H2;"ORDER";"")
J Order quantity =IF(I2="ORDER";E2*45-C2;0)

Traffic Light System for a Quick Overview

With conditional formatting, you can see immediately where action is needed:

Red: Critical
Coverage < Lead Time → Order immediately!
Yellow: Warning
Coverage < Lead Time + Safety Stock → Order soon
Green: OK
Coverage > 45 days → Everything is fine
Excel formula: Conditional formatting rule
=G2<F2 → Rot
=G2<(F2+7) → Gelb
=G2>=45 → Grün

Cash Flow Planning

Inventory planning without cash flow planning is incomplete. Here are the most important formulas:

Calculating tied-up capital

Excel formula: Tied-up capital
=Bestand * Purchase price

Order cost projection

Excel formula: Next order
=Bestellmenge * Purchase price + Shipping Costs + Zoll

Break-even coverage

At what point does revenue cover the order costs?

Excel formula: Break-even days
=Order_Cost / (Daily_Sales * Gewinn_pro_Einheit)

Tips for Automation

Here's how to save time on data maintenance:

Limitation: Excel is good for getting started, but with over 100 SKUs it gets hard to keep track. Beyond this point, a professional tool like Sellercore, which syncs automatically with your Amazon data, becomes worthwhile.

Conclusion

With these Excel formulas and templates, you have a solid foundation for your inventory management:

  1. Calculate your daily sales as the basis for all planning
  2. Define reorder points for each product
  3. Use a traffic light system for a quick overview
  4. Plan your cash flow with the order projections
  5. Automate where possible to save time

Automate inventory planning

With Sellercore, your inventory syncs automatically – without Excel exports.

Try for free

Keep your FBA inventory under control

Sellercore gives Amazon sellers the tools to grow: repricing, ads optimisation, inventory and profit analytics in one place. Try it free today.

Get started free