Analytics Calculations: Dynamic Reorder Points
josephmckeown
Posts: 44Moderator, Lightspeed Staff moderator
Keeping the right level of Inventory to meet sales demand is a complicated process. Analytics gives you the ability to set priorities and take measured actions as required.
Knowing how much inventory to order needs to look at: expected demand for products, current availability of products, expected availability of products, and any discrepancies.
As I mentioned in the discussion on best practices, asking for help is one of the best ways to find the best approach for calculations. This method of following inventory movement and from it harvesting action points is very much inspired by Tomer Shavit's work.
If we open up the report, we'll see a metric called "days to sell out", this is looking at the average sale intensity over the past 90 days, comparing to the current level of inventory, and identifying how many days' worth of inventory is available.
So now, how much should we re-order?
The default filter for "days to sell out" on the report is less than 7, but we can change the filters on this report to look at more considerations...
Let's start by changing the "days to sell out" filter from 7 to 60, giving us a wide view of products that have upcoming action points...
So this gives us a working list of products that we expect will need some attention in the next 60 days. Now, seeing the measures for "Quantity on Hand" and "Days to Sell Out", let's calculate the average daily sales volume.
To do this, we divide the quantity on hand, by days to sell out...
Which produces a number for us, recreating the expected daily sales amount.
Now, our question becomes, how much do we re-order?
To answer this question, we need to ask another:
If we want sixty days worth of inventory, then we will need to multiply the expected daily average by 60. If we want 14 days worth of inventory, we need to multiply the expected daily average by 14, and so on. (Choosing how many days' worth of inventory you want to have in stock involves asking some more questions: are there fixed costs with each individual shipment? (if so, ordering for greater timeframes brings these costs down), are there extra costs in storing inventory that isn't selling yet (if so, order for shorter timeframes brings these costs down). This will vary between Vendors and Categories. In Analytics, we can account for many of these...
Let's say we want 30 days of Inventory in stock. We'll create our desired level by multiplying our "Daily Average" by 30, starting with brackets:
Then copying our calculation for the average daily volume...
...then pasting into our 30-day calculation...
This gives us a number for our desired 30-day inventory level...
Now, in accordance with our best practices, let's save this report.
So now we know what our desired inventory level is, but we have figured out how much to order. This becomes simple, we subtract our available Inventory from our desired inventory level.
So we'll start a new calculation, using brackets...
Copying our calculation for desired inventory level...
...pasting...
...then subtracting the quantity on hand...
and saving...
So now this highlights the difference between the desired amount and the available amount, or, the amount required!
We can now sort the table by this amount, bringing up the products that require the greatest amount re-ordered...
Calculations Home
Knowing how much inventory to order needs to look at: expected demand for products, current availability of products, expected availability of products, and any discrepancies.
- Current availability can be measured by "Quantity on Hand"
- Expected availability can be measured by "Quantity on Order"
- Expected demand can be created from "Days to Sell out" on the Low Stock Report
Credit where Credit is due.
As I mentioned in the discussion on best practices, asking for help is one of the best ways to find the best approach for calculations. This method of following inventory movement and from it harvesting action points is very much inspired by Tomer Shavit's work.
If we open up the report, we'll see a metric called "days to sell out", this is looking at the average sale intensity over the past 90 days, comparing to the current level of inventory, and identifying how many days' worth of inventory is available.
So now, how much should we re-order?
The default filter for "days to sell out" on the report is less than 7, but we can change the filters on this report to look at more considerations...
Let's start by changing the "days to sell out" filter from 7 to 60, giving us a wide view of products that have upcoming action points...
So this gives us a working list of products that we expect will need some attention in the next 60 days. Now, seeing the measures for "Quantity on Hand" and "Days to Sell Out", let's calculate the average daily sales volume.
To do this, we divide the quantity on hand, by days to sell out...
Which produces a number for us, recreating the expected daily sales amount.
Now, our question becomes, how much do we re-order?
To answer this question, we need to ask another:
How many days worth of Inventory do I want?
If we want sixty days worth of inventory, then we will need to multiply the expected daily average by 60. If we want 14 days worth of inventory, we need to multiply the expected daily average by 14, and so on. (Choosing how many days' worth of inventory you want to have in stock involves asking some more questions: are there fixed costs with each individual shipment? (if so, ordering for greater timeframes brings these costs down), are there extra costs in storing inventory that isn't selling yet (if so, order for shorter timeframes brings these costs down). This will vary between Vendors and Categories. In Analytics, we can account for many of these...
Let's say we want 30 days of Inventory in stock. We'll create our desired level by multiplying our "Daily Average" by 30, starting with brackets:
Then copying our calculation for the average daily volume...
...then pasting into our 30-day calculation...
This gives us a number for our desired 30-day inventory level...
Now, in accordance with our best practices, let's save this report.
So now we know what our desired inventory level is, but we have figured out how much to order. This becomes simple, we subtract our available Inventory from our desired inventory level.
So we'll start a new calculation, using brackets...
Copying our calculation for desired inventory level...
...pasting...
...then subtracting the quantity on hand...
and saving...
So now this highlights the difference between the desired amount and the available amount, or, the amount required!
We can now sort the table by this amount, bringing up the products that require the greatest amount re-ordered...
Calculations Home
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Lightspeed Analytics and reporting consultant
Lightspeed HQ