Calculate how many items to keep in your inventory pipeline
What is the purpose of the report?
Joe Example sells textbooks to college students on Amazon for the fall semester only. His business is seasonal from May to August. It takes 8 - 10 weeks for Amazon to receive your goods and stock the shelves. How does he keep the shelves fully stocked?
How is the formula calculated?
Here are the numbers:
- Safety stock: If there is a 1 week delay in the pipeline, how many items would we sell in a week?
- Velocity: What time of year is it and how well would this product sell?
- Number in stock: How many items do we have in stock?
Consider this data:
Lead time is 3 months
Service level is .9
Here is the formula:
|Lead Time Demand||16.5||Sum of Jan, Feb, Mar||Summing the forecasts|
|Standard Deviation||.41724||Standard Deviation Jul - Dec||Deviation in past sales|
|Service Factor||1.281552||Inverse of Standard Deviation|
|Lead Time Factor||1.732051||Square root of lead time||Square Root of lead-time to forecast ratio|
|Safety Stock||.926152||Standard Dev * Service Factor * Lead Time Factor|
|Reorder Point||17.42615||Lead Time Demand + Safety Stock||Lead time demand + safety stock|
What does the report look like?
The reorder point changes, based on seasonality. In Connex, you can choose a date range and a product to calculate your re-order point. Here is how it works:
- Connex Inventory will download your sales by item and inventory valuation reports.
- Connex will determine the average sold daily, known as your velocity, based on the date and the sales report.
- Your re-order point is calculated using the lead time and safety stock.
- Connex will calculate the average sold per month.
Connex creates this report:
How do the formulas work?
|Reorder Point||For each month, when does the quantity on hand less than or equal to the days to receive inventory times 30 days?|
|Avg. Sold Monthly||Look at orders from the last 90 days and get average per month and day.|
|Days and Month Supply||Quantity on hand / number of units sold average per month.|
|Average Cost||Pulled from QuickBooks inventory valuation report.|