Cellwise Hire · Inventory and purchasing preset
Excel test for procurement and purchasing
Candidates price order lines from a supplier list, apply quantity price breaks, catch SKUs that are not on the list, then decide what to order and what the order run will cost. Every answer is checked by what it calculates, on the sheet they see and on hidden versions of it.
Create a free buyer 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: Buyers and purchasing agents, Procurement coordinators and assistants, Purchasing clerks who raise purchase orders, Category and sourcing assistants.
What buyers and purchasing teams actually do in Excel
A buyer's spreadsheet joins two things: what the business needs and what suppliers charge. Order lines carry a SKU and a quantity; the price, the item name and the quantity breaks live in the supplier's list. Pricing the lines correctly, and noticing the item that is not on the list at all, is daily work.
Then comes the decision: which items are at or below their reorder point, how many to order to cover the lead time and the next stretch of sales, and what the whole run will cost before the purchase orders go out.
- Prices and item names from a supplier list by SKU, with an exact match
- Items missing from the list shown as "Not found" and counted, instead of #N/A
- Quantity price breaks with an approximate-match lookup, and net line totals
- Order quantities from reorder points, lead times and days of cover in cells
- The cost of an order run with SUMPRODUCT, and the value of stock that is not moving
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 buyers and purchasing teams, with the real task names and the level each one appears at.
Pricing from a supplier list
Item names and prices pulled in by SKU with VLOOKUP and FALSE, XLOOKUP, or INDEX and MATCH, then quantity times price. Any exact-match method passes; a range without $ that slides as it fills down does not.
- Item namesBasic
- PricesBasic
- Line totalsBasic
- Unit pricesAdvanced
Items not on the list
Order lines with SKUs missing from the price list, shown as "Not found" with IFERROR, IFNA or XLOOKUP's if-not-found argument and counted, and one SKU's units valued at list price with the SKU in a cell.
- Prices, with missing SKUsIntermediate
- Unknown SKUsIntermediate
- Value at list priceIntermediate
Quantity price breaks
Each line gets the discount of the highest minimum quantity it reaches, read from a table with an approximate match instead of nested IFs with the tiers typed in, then net totals and the SKU with the most units ordered. Advanced level.
- Quantity discountAdvanced
- Net line totalsAdvanced
- Most-ordered SKUAdvanced
What to order and what it costs
Reorder points rounded up, an order quantity only for SKUs at or below their reorder point, and the cost of the whole run with SUMPRODUCT. Advanced level.
- Reorder pointAdvanced
- Reorder quantityAdvanced
- Cost of the ordersAdvanced
Lead times and stock checks
A reorder flag against a level in a cell, days of supply rounded down, whether one SKU will run out before its supplier lead time is up, and the value of slow-moving stock.
- Reorder flagBasic
- Days of supplyIntermediate
- Will it run out first?Intermediate
- Slow-moving stock valueAdvanced
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 suggestedPurchasing clerks and assistants: stock value, a reorder flag, stock-outs, and prices and item names looked up by SKU.
- Intermediate · 30 minutes suggestedBuyers and procurement coordinators: days of supply against lead time, units by category, SKUs missing from the price list, and value at list price.
- Advanced · 40 minutes suggestedSenior buyers and category roles: quantity price breaks, reorder points and order quantities, the cost of the order run and slow-moving stock.
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 · Orders and price list · Advanced
Quantity discount (in E2, filled down to E11). Give each order line the discount for its quantity from the price-break table in L1:M5: a line gets the rate of the highest minimum quantity it reaches. One formula, filled down.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Unit price | Discount | Net total | SKU | Item | Price | Min qty | ||
| 2 | SO-301 | CHR-02 | 4 | DSK-01 | Standing desk | $420.00 | 0 | |||||
| 3 | SO-302 | LMP-03 | 12 | CHR-02 | Office chair | $149.00 | 5 | |||||
| 4 | SO-303 | ARM-04 | 3 | LMP-03 | Desk lamp | $24.50 | 10 | |||||
| 5 | SO-304 | DSK-01 | 1 | ARM-04 | Monitor arm | $89.00 | 25 | |||||
| 6 | SO-305 | LMP-03 | 30 | CAM-05 | Webcam | $59.00 | ||||||
| 7 | SO-306 | HUB-06 | 8 | HUB-06 | USB-C hub | $38.00 | ||||||
| 8 | SO-307 | CAM-05 | 6 | |||||||||
| 9 | SO-308 | CHR-02 | 10 | |||||||||
| 10 | SO-309 | ARM-04 | 2 | |||||||||
| 11 | SO-310 | HUB-06 | 32 |
| Formula in E2 | Sheet they see | Hidden checks | Result |
|---|---|---|---|
=VLOOKUP(C2,$L$2:$M$5,2,TRUE)Approximate match, table locked | 10 of 10 rows right right | Right on both | SolvedUsed locked references ($) correctly |
=IF(C2>=25,0.15,IF(C2>=10,0.1,IF(C2>=5,0.05,0)))The tiers typed into the formula | 10 of 10 rows right right | Wrong on new numbers | Not solvedRight on the visible data only. Typed the number 25 into the formula instead of pointing to L5 |
=VLOOKUP(C2,$L$2:$M$5,2,FALSE)Exact match | 1 of 10 rows right wrong | Not needed: already wrong | Not solvedShows #N/A |
=IF(C2>=25,0.15,IF(C2>=10,0.1,IF(C2>=5,0.05,0)))Right on every row today, because it copies the table. When the supplier changes its breaks, the table gets updated and this formula does not. The hidden version has different breaks, so it fails there.=VLOOKUP(C2,$L$2:$M$5,2,FALSE)FALSE only finds a quantity that is exactly 0, 5, 10 or 25. Every other line shows #N/A. A price-break table needs an approximate match.
Row by row
| Row | Qty | =VLOOKUP(C2,$L$2:$M$5,2,TRUE) | =IF(C2>=25,0.15,IF(C2>=10,0.1,IF(C2>=5,0.05,0))) | =VLOOKUP(C2,$L$2:$M$5,2,FALSE) |
|---|---|---|---|---|
| 2 | 4 | 0% | 0% | #N/A (wrong) |
| 3 | 12 | 10% | 10% | #N/A (wrong) |
| 4 | 3 | 0% | 0% | #N/A (wrong) |
| 5 | 1 | 0% | 0% | #N/A (wrong) |
| 6 | 30 | 15% | 15% | #N/A (wrong) |
| 7 | 8 | 5% | 5% | #N/A (wrong) |
| 8 | 6 | 5% | 5% | #N/A (wrong) |
| 9 | 10 | 10% | 10% | 10% |
| 10 | 2 | 0% | 0% | #N/A (wrong) |
| 11 | 32 | 15% | 15% | #N/A (wrong) |
Grading is by result, so XLOOKUP with match mode -1, INDEX and MATCH with match type 1, and LOOKUP all pass. The hidden version changes both the quantities and the breaks.
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 buyer 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 buyers and purchasing teams, two things are worth a look:
- Open the price-break and missing-SKU tasks first. Whether the candidate read the tiers from the table, and whether "Not found" came from a locked range or hid a sliding one, shows how far you can trust their purchase order sheets.
- On the order quantity task, a note such as "Typed the number 30 into the formula instead of pointing to L3" shows who builds sheets where changing the days of cover really changes the order.
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
- Supplier scorecards, spend analysis, quote comparisons and your purchasing or ERP system are not in this test. It tests the Excel work around them.
- Economic order quantity is not in the test either. Our guide to economic order quantity in Excel has a worked example to use in an interview.
- This page and the Excel test for inventory coordinators use the same Inventory and purchasing preset; that page describes it from the stockroom side.
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 a buyer need?
Exact-match lookups (XLOOKUP, VLOOKUP with FALSE, or INDEX and MATCH) to price lines from a supplier list, IFERROR or IFNA for items that are not on it, an approximate-match lookup for quantity breaks, SUMIF and SUMPRODUCT for totals and order costs, ROUNDUP for order quantities, and absolute references so all of it fills down correctly.
Which level should I choose for procurement?
Basic suits purchasing clerks and assistants. Intermediate suits most buyers and procurement coordinators. Choose Advanced for senior buyers, because the quantity price breaks, reorder quantities and the cost of the order run only appear at that level.
Is this the same test as the inventory coordinator test?
Yes, both use the Inventory and purchasing preset: a stockroom workbook and an orders-and-price-list workbook. This page describes what the tasks show about purchasing work; the inventory coordinator page describes stock control.
How long should a procurement 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.
Is it fair to give purchasing candidates an Excel test?
A short test that reflects the real work is a common part of hiring. Give every candidate 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.