Cellwise Start free

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.

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
Every task in the Inventory and purchasing preset, by level and workbook
LevelStockroom inventoryOrders and price list
BasicStock value per SKU
Reorder flag
Total stock value
SKUs out of stock
Item names
Prices
Line totals
Order total
IntermediateAverage 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
AdvancedReorder 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

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.

RowABCDEFGHIJKL
1SKUItemCategoryOn handUnits soldLead time (days)Unit costDaily salesReorder pointOrder qtySales history (days)90
2BX-1012Shipping box, smallPackaging3408107$0.629Safety stock (days)7
3BX-1013Shipping box, largePackaging954507$1.155Order covers (days)30
4TP-2201Packing tapePackaging02705$2.403Slow mover: stock for more than (days)120
5LB-3105Thermal labels (roll)Labels4818010$6.802
6GL-4410Nitrile gloves (box)Safety86014$9.500.66666666667Cost of all orders
7BW-5002Bubble wrap (roll)Packaging155412$18.750.6Value of slow-moving stock
8SC-6120Box cutterTools64921$4.200.1
9LB-3110Shipping labels (pack)Labels012010$11.901.3333333333
10PL-7031Pallet wrap filmPackaging30669$14.250.73333333333
11HV-8800Hi-vis vestSafety41030$7.600
Answers to the sample task, what each returns on the visible sheet, and the hidden checks
Formula in I2Sheet they seeHidden checksResult
=ROUNDUP(H2*(F2+$L$2),0)ROUNDUP, with the safety days locked as $L$2 10 of 10 rows right right Right on bothSolvedUsed locked references ($) correctly
=ROUNDUP(H2*(F2+7),0)The safety days typed in 10 of 10 rows right right Wrong on new numbersNot 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 wrongNot needed: already wrongNot 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
Each answer's result in every row of the sample
RowLead time (days)Daily sales=ROUNDUP(H2*(F2+$L$2),0)=ROUNDUP(H2*(F2+7),0)=ROUND(H2*(F2+$L$2),0)
279126126126
375707070
453363636
5102343434
6140.66666666667141414
7120.6121211 (wrong)
8210.1333
9101.3333333333232323
1090.73333333333121212
11300000

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:

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

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)

Create a free inventory coordinator testCompare plans

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.