Purpose
By the end of this lesson, you will be able to build formulas for tracking inventory levels, reorder points, and basic operational efficiency metrics.
Lesson Explanation
A basic reorder alert flags an item for reordering when its current stock falls below a defined minimum threshold: =IF(B2<C2,"Reorder","OK"), where B2 is current stock and C2 is the minimum threshold for that specific item.
Days of inventory remaining estimates how long current stock will last based on a recent average daily usage rate: =B2/C2, where B2 is current stock and C2 is average daily usage – useful for prioritizing which items need attention soonest, since a low absolute stock count isn’t alarming if daily usage is also very low.
Inventory turnover measures how many times inventory is sold and replaced over a period: =D2/AVERAGE(B2:C2) (cost of goods sold divided by average inventory, where B2 and C2 are beginning and ending inventory for the period) – a low turnover number can indicate slow-moving stock tying up cash unnecessarily.
A combined reorder priority formula can weigh multiple factors at once: =IF(AND(B2E2),”Urgent Reorder”,IF(B2E2), distinguishing critical fast-moving shortages from lower-priority ones.
Practice Questions
1. Current stock for an item is in B2 (45 units), and its minimum reorder threshold is in C2 (50 units). Write a formula that flags this item as “Reorder” or “OK.”
View Answer
=IF(B2<C2,"Reorder","OK") (which would return "Reorder" since 45 is less than 50).
2. Current stock for an item is 200 units (B2), and its average daily usage is 5 units per day (C2). Write a formula calculating days of inventory remaining.
View Answer
=B2/C2 (which would return 40 days).
3. Two items both have 100 units in stock. Item A has average daily usage of 2 units; Item B has average daily usage of 20 units. Which item needs attention sooner, based on days of inventory remaining, and why?
View Answer
Item B – despite having the same current stock, its much higher daily usage means it will run out much sooner (5 days remaining, versus 50 days for Item A).
4. Cost of goods sold for a period is $120,000 (D2), beginning inventory was $30,000 (B2), and ending inventory was $20,000 (C2). Write a formula calculating inventory turnover for the period.
View Answer
=D2/AVERAGE(B2:C2) (which would return 4.8, meaning inventory turned over roughly 4.8 times during the period).
5. A specific product shows a low inventory turnover number compared to other products. What does this lesson say this might indicate?
View Answer
Slow-moving stock that is tying up cash unnecessarily, compared to faster-turning products.
6. An item is below its reorder threshold (B2<C2) but also has very low sales velocity, well under the urgency threshold. Using the combined priority formula from this lesson, would this item be flagged as "Urgent Reorder" or simply "Reorder"?
View Answer
Simply “Reorder” – it meets the below-threshold condition but not the high-sales-velocity condition required for “Urgent” status.
7. Write the combined reorder priority formula, where B2 is current stock, C2 is minimum threshold, D2 is a sales velocity metric, and E2 is a velocity threshold for urgency.
View Answer
=IF(AND(B2E2),”Urgent Reorder”,IF(B2<C2,"Reorder","OK"))8. Why does this lesson recommend considering BOTH current stock level AND usage rate, rather than flagging reorders based on stock level alone?
View Answer
A low absolute stock count isn’t alarming on its own if daily usage is also very low – usage rate provides essential context for how urgent a given stock level actually is.
9. An operations manager notices one product has a very high inventory turnover number compared to company average. What does a HIGH turnover generally suggest, in contrast to the low-turnover concern raised in this lesson?
View Answer
The product is selling and being restocked relatively quickly, which is often a positive sign (though extremely high turnover in some contexts could also suggest insufficient safety stock, depending on the business).
10. A warehouse tracks 500 different SKUs, each with different reorder thresholds and usage rates. Why would a single combined formula (rather than manually checking each item) be especially valuable at this scale?
View Answer
Manually checking 500 individual items for both stock level and usage rate would be impractical; a formula applied down the entire list can instantly flag the specific items needing attention, at any scale.
11. An item shows 15 days of inventory remaining, which seems low, but further investigation reveals this item has a lead time of only 3 days for reordering. Why might this context change how urgently this item should actually be treated?
View Answer
If new stock can arrive quickly (a short lead time), a relatively low days-remaining figure may be far less urgent than the same figure would be for an item with a much longer reorder lead time.
12. Why might inventory turnover be a more useful metric for identifying “cash tied up in slow-moving stock” than simply looking at total inventory dollar value alone?
View Answer
A high total inventory value could belong either to genuinely healthy, fast-selling stock or to slow-moving stock – turnover specifically measures the RATE of movement, directly identifying whether that inventory value is efficiently cycling through or stagnating.