Course outline / Advanced Excel
Lesson 64 of 75 · Intermediate · 4 exercises
Inventory management
Stock value, reorder flags and days of stock left.
What you'll practise
- Value of all stock
- Time to reorder?
- How many items need reordering?
- Days of stock left
Value of all stock
In cell G2, work out the total value of stock: each quantity (B2:B5) times its unit cost (D2:D5), added up with SUMPRODUCT.
Time to reorder?
In cell G2, show "Reorder" if Paper's stock (B2) is at or below its reorder level (C2), otherwise "OK".
How many items need reordering?
In cell G2, count how many items are at or below their reorder level: add four tests, (B2<=C2)+(B3<=C3)+(B4<=C4)+(B5<=C5).
Days of stock left
In cell G2, work out how many whole days the Pens will last: stock (B4) divided by daily use (E4), rounded down with ROUNDDOWN to 0 places.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.