Cellwise Hire · Inventory and purchasing preset
Excel test for inventory coordinators
Candidates work in a stockroom inventory: stock value, a reorder flag, daily sales and days of supply, a reorder point with safety stock, order quantities and slow-moving stock. Then they look up prices by SKU from a second table. Every answer is checked on hidden versions of the data with different stock, sales and settings.
Create a free inventory coordinator testSee a sample task
Free for your first 3 candidates. The Inventory and purchasing preset is preselected for you.
The test at a glance
- Workbooks
- Stockroom inventory + Orders and price list
- Tasks
- 8 hands-on tasks at the level you pick
- Levels
- Basic, Intermediate or Advanced
- Time
- You choose; 20 to 40 minutes suits most
- Grading
- By result, on the visible sheet and 2 hidden versions
Good for: Inventory coordinators and inventory control clerks, Purchasing and procurement assistants, Stockroom and warehouse leads, Operations assistants who place supplier orders.
What inventory coordinators actually do in Excel
Inventory software records what came in and what went out. Excel is where someone decides what to order. An inventory coordinator exports stock on hand and recent sales, works out how many days each SKU will last, compares that with the supplier's lead time and writes the purchase list before the morning call.
The numbers that drive it sit in a few input cells: how many days of sales history, how many days of safety stock, how many days an order should cover. When purchasing changes the safety stock from 7 days to 10, every reorder point should move with it. A sheet with 7 typed into each formula quietly keeps ordering too little.
- Stock value per SKU and in total, for month-end and insurance
- Reorder flags and days of supply from on-hand and sales history
- Reorder points from daily sales, lead time and safety stock, rounded up
- Order quantities, and what the order run will cost
- Finding slow-moving stock that ties up cash, and pricing order lines by SKU
What the test checks
The Inventory and purchasing preset uses two workbooks: Stockroom inventory (packing and warehouse supplies, one row per SKU: units on hand, units sold over the sales history period, supplier lead time in days and unit cost) and Orders and price list (customer orders on the left, the price list on the right. The order lines only have a SKU code and a quantity). Here is what that covers for inventory coordinators, with the real task names and the level each one appears at.
Stock value and stock-outs
On hand times unit cost down a column, the total, and a count of SKUs with nothing on the shelf. COUNTIF on a 0, not COUNTBLANK, because an empty bin still holds a number.
- Stock value per SKUBasic
- Total stock valueBasic
- SKUs out of stockBasic
Reorder flags and days of supply
A reorder flag against a level in a cell, locked with $ so it fills down. Then daily sales from the sales history length in a cell, days of supply rounded down, a "No sales" guard instead of #DIV/0!, and whether one SKU will run out before its lead time is up.
- Reorder flagBasic
- Average daily salesIntermediate
- Days of supplyIntermediate
- Will it run out first?Intermediate
Reorder points and order quantities
Daily sales times lead time plus safety stock days, rounded UP with ROUNDUP or CEILING, then an order quantity only for SKUs at or below their reorder point, and the cost of the whole order run with SUMPRODUCT. Advanced level.
- Reorder pointAdvanced
- Reorder quantityAdvanced
- Cost of the ordersAdvanced
Slow-moving stock
The value of stock that would last longer than a set number of days, including SKUs with stock and no sales at all, and units sold by category with the category in a cell.
- Slow-moving stock valueAdvanced
- Units sold in one categoryIntermediate
Prices and SKUs from a second table
The second workbook is order lines with only a SKU and a quantity. Item names and prices come from a price list with an exact-match lookup, SKUs missing from the list show "Not found", and quantity-discount tiers use an approximate match.
- Item namesBasic
- PricesBasic
- Prices, with missing SKUsIntermediate
- Unknown SKUsIntermediate
- Quantity discountAdvanced
Every task in this test, by level
| Level | Stockroom inventory | Orders and price list |
|---|---|---|
| Basic | Stock value per SKU Reorder flag Total stock value SKUs out of stock | Item names Prices Line totals Order total |
| Intermediate | Average daily sales Days of supply Units sold in one category Will it run out first? | Prices, with missing SKUs Unknown SKUs Units of one SKU Value at list price |
| Advanced | Reorder point Reorder quantity Cost of the orders Slow-moving stock value | Unit prices Quantity discount Net line totals Most-ordered SKU |
Each test uses one level: four tasks from each workbook.
Which level to choose
- Basic · 20 minutes suggestedStockroom and inventory clerks: stock value, a reorder flag, stock-outs, and exact-match price lookups.
- Intermediate · 30 minutes suggestedInventory coordinators: daily sales and days of supply from input cells, run-out checks, missing SKUs and units by SKU.
- Advanced · 40 minutes suggestedInventory planners and purchasing leads: reorder points with safety stock, order quantities and cost, slow movers, discount tiers.
A real task from this test
This is one of the tasks in the Inventory and purchasing preset at the Advanced level, on the sheet candidates see. The answers below were run through the same grader that marks real tests: on this sheet, and on two hidden versions with different data and an extra row inserted.
Sample task · Stockroom inventory · Advanced
Reorder point (in I2, filled down to I11). For each SKU, the reorder point is daily sales (column H) times the supplier lead time in days plus the safety stock days in L2, rounded up to a whole unit. One formula, filled down the column.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | Units sold | Lead time (days) | Unit cost | Daily sales | Reorder point | Order qty | Sales history (days) | 90 |
| 2 | BX-1012 | Shipping box, small | Packaging | 340 | 810 | 7 | $0.62 | 9 | Safety stock (days) | 7 | ||
| 3 | BX-1013 | Shipping box, large | Packaging | 95 | 450 | 7 | $1.15 | 5 | Order covers (days) | 30 | ||
| 4 | TP-2201 | Packing tape | Packaging | 0 | 270 | 5 | $2.40 | 3 | Slow mover: stock for more than (days) | 120 | ||
| 5 | LB-3105 | Thermal labels (roll) | Labels | 48 | 180 | 10 | $6.80 | 2 | ||||
| 6 | GL-4410 | Nitrile gloves (box) | Safety | 8 | 60 | 14 | $9.50 | 0.66666666667 | Cost of all orders | |||
| 7 | BW-5002 | Bubble wrap (roll) | Packaging | 15 | 54 | 12 | $18.75 | 0.6 | Value of slow-moving stock | |||
| 8 | SC-6120 | Box cutter | Tools | 64 | 9 | 21 | $4.20 | 0.1 | ||||
| 9 | LB-3110 | Shipping labels (pack) | Labels | 0 | 120 | 10 | $11.90 | 1.3333333333 | ||||
| 10 | PL-7031 | Pallet wrap film | Packaging | 30 | 66 | 9 | $14.25 | 0.73333333333 | ||||
| 11 | HV-8800 | Hi-vis vest | Safety | 41 | 0 | 30 | $7.60 | 0 |
| Formula in I2 | Sheet they see | Hidden checks | Result |
|---|---|---|---|
=ROUNDUP(H2*(F2+$L$2),0)ROUNDUP, with the safety days locked as $L$2 | 10 of 10 rows right right | Right on both | SolvedUsed locked references ($) correctly |
=ROUNDUP(H2*(F2+7),0)The safety days typed in | 10 of 10 rows right right | Wrong on new numbers | Not solvedRight on the visible data only. Typed the number 7 into the formula instead of pointing to L2 |
=ROUND(H2*(F2+$L$2),0)ROUND instead of ROUNDUP | 9 of 10 rows right wrong | Not needed: already wrong | Not solved |
=ROUNDUP(H2*(F2+7),0)Every row is right today, because L2 says 7. Change the safety stock to 10 days and these reorder points do not move. The hidden version uses different safety days, so this answer fails there and the report names L2 as the cell it should have pointed to.=ROUND(H2*(F2+$L$2),0)ROUND drops anything under half a unit, so a reorder point of 11.4 becomes 11. Order at 11 and the last bit of lead-time demand is not covered. Reorder points round up.
Row by row
| Row | Lead time (days) | Daily sales | =ROUNDUP(H2*(F2+$L$2),0) | =ROUNDUP(H2*(F2+7),0) | =ROUND(H2*(F2+$L$2),0) |
|---|---|---|---|---|---|
| 2 | 7 | 9 | 126 | 126 | 126 |
| 3 | 7 | 5 | 70 | 70 | 70 |
| 4 | 5 | 3 | 36 | 36 | 36 |
| 5 | 10 | 2 | 34 | 34 | 34 |
| 6 | 14 | 0.66666666667 | 14 | 14 | 14 |
| 7 | 12 | 0.6 | 12 | 12 | 11 (wrong) |
| 8 | 21 | 0.1 | 3 | 3 | 3 |
| 9 | 10 | 1.3333333333 | 23 | 23 | 23 |
| 10 | 9 | 0.73333333333 | 12 | 12 | 12 |
| 11 | 30 | 0 | 0 | 0 | 0 |
The typed 7 is the mistake to watch for in inventory sheets: it looks finished and gives the right numbers until someone changes the policy. Grading on hidden data with a different safety stock is what catches it.
Results calculated by the Cellwise formula engine. The hidden versions stay on our server, so this page shows only whether each answer passed them.
See the screen your inventory coordinator candidates work in: try a 3-minute sample test and read the report you would get. No signup.
What the report shows
When a candidate finishes, you get an overall score and every task: solved or not, the exact formula they wrote, the time spent and the number of tries, plain-English notes, and a replay of every edit with the sheet as it looked at each step. For inventory coordinators, two things are worth a look:
- Open the reorder-point and order-quantity tasks first. The formula shows whether they read lead time and safety stock from cells and rounded up, or typed the policy into every row.
- Notes such as "Typed the number 90 into the formula instead of pointing to L1" tell you who will keep a reorder sheet working when the sales history period or the safety stock changes.
You can also add 8 multiple-choice knowledge questions at the same level, scored separately. Candidates never see their score.
What this test does not cover
- It does not test an inventory or ERP system, cycle counts, barcode scanning or warehouse layout. It tests the Excel work around them.
- Economic order quantity, ABC classification, FIFO valuation and demand forecasting are not in this test. Our guides to EOQ, ABC inventory analysis and FIFO valuation have checked examples to use in an interview.
Pricing
Free for your first 3 candidates, no card needed. Then Starter is $79 a month or $790 a year for 25 completed candidates a month, and Agency is $199 a month or $1,990 a year for unlimited candidates. Only candidates who complete a test count.
Founding price: locked for as long as you stay subscribed (first 10 customers)
FAQ
What Excel skills does an inventory coordinator need?
Stock value and totals, COUNTIF and SUMIFS by category or status, IF for reorder flags, days of supply with a guard for items with no sales, reorder points rounded up with ROUNDUP, exact-match lookups by SKU (XLOOKUP, VLOOKUP with FALSE or INDEX and MATCH), SUMPRODUCT for order costs, and reading settings such as safety stock and lead time from cells instead of typing them into formulas.
Which level should I choose for an inventory role?
Basic suits stockroom and inventory clerks. Intermediate suits inventory coordinators who work out days of supply and what to order. Choose Advanced if the person will set reorder points, order quantities or review slow-moving stock, because those tasks only appear at that level.
How long should an inventory Excel test take?
Each test has 8 tasks across two workbooks. We suggest 20 minutes for Basic, 30 for Intermediate and 40 for Advanced, plus about 8 minutes if you add the optional knowledge-check questions. You can set anything from 5 to 120 minutes.
Does it use a particular reorder point formula?
The test states its rule in each task: reorder point is daily sales times lead time plus safety stock days, rounded up. Companies use other methods, such as safety stock from demand variability. The test checks whether a candidate can build the rule they are given correctly, with inputs in cells, which carries over to whatever method you use.
Is it fair to give inventory candidates an Excel test?
A short test tied to the work the job really involves is a common part of hiring. Give every candidate for the role the same test and time, follow local hiring law, and keep tests job-related.
Related
Looking to learn Excel yourself? Start the free Cellwise course.