Cellwise Start free

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.

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
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 · 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.

RowABCDEFGHIJKL
1OrderSKUQtyUnit priceDiscountNet totalSKUItemPriceMin qty
2SO-301CHR-024DSK-01Standing desk$420.000
3SO-302LMP-0312CHR-02Office chair$149.005
4SO-303ARM-043LMP-03Desk lamp$24.5010
5SO-304DSK-011ARM-04Monitor arm$89.0025
6SO-305LMP-0330CAM-05Webcam$59.00
7SO-306HUB-068HUB-06USB-C hub$38.00
8SO-307CAM-056
9SO-308CHR-0210
10SO-309ARM-042
11SO-310HUB-0632
Answers to the sample task, what each returns on the visible sheet, and the hidden checks
Formula in E2Sheet they seeHidden checksResult
=VLOOKUP(C2,$L$2:$M$5,2,TRUE)Approximate match, table locked 10 of 10 rows right right Right on bothSolvedUsed 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 numbersNot 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 wrongNot needed: already wrongNot 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
Each answer's result in every row of the sample
RowQty=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)
240%0%#N/A (wrong)
31210%10%#N/A (wrong)
430%0%#N/A (wrong)
510%0%#N/A (wrong)
63015%15%#N/A (wrong)
785%5%#N/A (wrong)
865%5%#N/A (wrong)
91010%10%10%
1020%0%#N/A (wrong)
113215%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:

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 buyer testCompare plans

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.