Excel for warehouse and logistics teams

Q: How do I calculate a reorder point in Excel?

Multiply average daily usage by the lead time in days and add safety stock: =ROUNDUP(usage*lead_time+safety_stock,0). Then flag items with =IF(on_hand<=reorder_point,"Reorder",""). Use the full lead time until stock is usable, including receiving.

Q: How do I calculate inventory turnover in Excel?

Divide cost of goods sold for the period by average inventory value for the same period. With unit-level data, =SUMPRODUCT(units_sold,unit_cost)/SUMPRODUCT(on_hand,unit_cost) gives a quick estimate; average several month-end inventory values for a better one. 365 divided by turnover gives days of inventory.

Q: How do I calculate pick rate?

Divide what was picked (order lines, units or orders, and say which) by the hours spent picking. For a team or a period, divide total picks by total hours rather than averaging individual rates.

Q: What is the difference between safety stock and a reorder point?

Safety stock is the buffer you keep for late deliveries and demand spikes. The reorder point is the level that triggers an order: expected usage during the lead time plus that safety stock.

For warehouse managers, inventory planners and shipping leads who live between the WMS export and the carrier invoice. Excel answers the daily questions fast: what to reorder today, which item will run out before the next truck, how fast stock turns, how much cash sits on the shelf, how productive picking was, and what each order really costs to ship. Every formula below runs on a kitchenware distributor's stock list and last month's floor numbers.

Warehouse and distribution center managers
Inventory planners and buyers
Shipping and logistics coordinators
Operations analysts
E-commerce sellers running their own fulfillment

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

	A	B	C	D	E	F	G	H
1	SKU	Unit cost	On hand	Daily usage	Lead time (days)	Safety stock	Sold (12 mo)	Picker hours
2	KW-1001 Chef knife 8in	14.2	610	18	21	120	6240	312
3	KW-1002 Cutting board	6.75	260	11	14	60	3850	Lines picked
4	KW-1010 Cast iron skillet	19.4	95	6.5	35	70	2310	8420
5	KW-1015 Silicone spatula	1.85	1240	32	10	150	11560	Orders shipped
6	KW-1022 Mixing bowl set	11.3	175	4.2	28	40	1480	3180
7	KW-1030 Measuring cups	2.4	610	14	12	80	5020	Shipping cost
8	KW-1041 Dutch oven	38.5	22	1.8	42	25	640	21940
9	KW-1050 Kitchen scale	9.9	310	7.5	18	50	2690	Delivered on time
10	KW-1062 Spice rack	7.15	540	2.1	20	30	760	3021
11	KW-1070 Apron	3.6	75	3.4	15	25	1220

What Excel does for warehouse and logistics

Set a reorder point from usage and lead time

The reorder point is the stock level at which you place an order so it arrives before you run out: daily usage times lead time in days, plus safety stock for the days that go wrong. Below it, order now.

Use the lead time from order to usable stock on the shelf, including receiving and put-away, not the supplier's ship time. Round up, because you cannot keep half a skillet in reserve.

=ROUNDUP(D4*E4+F4,0)

Result: 298

298 skillets: 6.5 a day x 35 days = 227.5, plus 70 safety stock. There are only 95 on the shelf, so this order is late already.

List everything at or below its reorder point

Instead of a Reorder column you have to scan, FILTER can return the SKUs that need ordering, and TEXTJOIN puts them in one line for the buyer. Both are in Excel 365 and Excel 2021; in older versions, add a helper column with =IF(C2<=D2*E2+F2,"Reorder","") and filter on it.

The reorder point is worked out inside the test, so the list updates the moment usage, lead time or stock changes.

=TEXTJOIN(", ",TRUE,FILTER(A2:A11,C2:C11<=D2:D11*E2:E11+F2:F11))

Result: KW-1010 Cast iron skillet, KW-1041 Dutch oven, KW-1070 Apron

Three SKUs: the skillet, the Dutch oven, and the apron, which has 75 on hand against a reorder point of 76. The apron is fine for now; the other two are not.

Find items that will run out before the next delivery

Days of supply is stock on hand divided by daily usage. Compare it with lead time: if days of supply is shorter than lead time, an order placed today still arrives after the shelf is empty. Those items need expediting, a substitute or a backorder message, not just a purchase order.

This is the check that catches what reorder points miss when an item was forgotten for a few weeks.

=C8/D8

Result: 12.222222222 shows as 12.2 with Number format

12.2 days of Dutch ovens left, with a 42-day lead time. Even ordered today, you would be out of stock for about 30 days.

Measure inventory turnover in dollars

Inventory turnover is cost of goods sold over a period divided by average inventory value. Work in dollars (units times unit cost), not units, so a pallet of spatulas does not count the same as a pallet of Dutch ovens. Divide 365 by the turnover to get days of inventory on hand.

This sample divides by today's stock because that is all there is. For a proper figure, average several month-end values, for example =AVERAGE of the 12 month-end inventory totals; one count can be unusually high or low.

=SUMPRODUCT(G2:G11,B2:B11)/SUMPRODUCT(C2:C11,B2:B11)

Result: 10.393184218 shows as 10.4 with Number format

10.4 turns a year: $270,664.50 of cost sold over 12 months against $26,042.50 of stock on hand. That is about 35 days of inventory.

Put a dollar value on excess stock

Slow movers tie up cash and space. Pick a cover limit (90 days here), then count only the stock above it: on hand minus 90 days of usage, times unit cost, for every SKU with more than 90 days of supply.

SUMPRODUCT handles the test and the math in one formula. A total in dollars is what gets a markdown or a return-to-vendor approved.

=SUMPRODUCT((C2:C11/D2:D11>90)*(C2:C11-90*D2:D11)*B2:B11)

Result: 2509.65

$2,509.65 tied up above 90 days of cover, all in spice racks: 540 on hand at 2.1 a day is 257 days of supply.

Work out pick rate per hour

Pick rate is the work picked divided by the labor hours spent picking. Say which unit you use: order lines, units or orders give very different numbers, and only the same unit compares across shifts and sites.

For a team or a month, divide total lines by total hours. Averaging each picker's rate gives a part-timer's 4-hour shift as much weight as a full week.

=H4/H2

Result: 26.987179487 shows as 27.0 with Number format

27.0 order lines per picker hour: 8,420 lines over 312 hours. Track it weekly; a sudden drop usually points to slotting, replenishment or system problems before people.

Shipping cost per order and on-time delivery

Cost per order is total shipping spend divided by orders shipped. Use the carrier invoices, not the rates you quoted, because surcharges (fuel, residential, oversize, address corrections) land after the fact. On-time delivery % is deliveries on or before the promised date divided by all deliveries, =H10/H6 here.

Track both together. Switching to a cheaper service level often lowers cost per order and on-time % in the same month.

=ROUND(H8/H6,2)

Result: 6.9

$6.90 per order: $21,940 of shipping across 3,180 orders. 3,021 of them arrived on time, which is 95.0%.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

1How many units are on hand across all SKUs?

Your formula for exercise 1

Show answer
=SUM(C2:C11) = 3937. A unit count is useful for space planning; for money, multiply by unit cost.
2What is the total value of the stock on hand?

Your formula for exercise 2

Show answer
=SUMPRODUCT(B2:B11,C2:C11) = 26042.5. SUMPRODUCT multiplies each pair and adds them: $26,042.50 of stock.
3How many days of supply are left for the chef knife (row 2)?

Your formula for exercise 3

Show answer
=C2/D2 = 33.888888889. 610 / 18 = 33.9 days, comfortably longer than its 21-day lead time.
4What is the reorder point for the Dutch oven (row 8), rounded up to a whole unit?

Your formula for exercise 4

Show answer
=ROUNDUP(D8*E8+F8,0) = 101. 1.8 x 42 + 25 = 100.6, rounded up to 101.
5How many SKUs are at or below their reorder point?

Your formula for exercise 5

Show answer
=SUMPRODUCT(--(C2:C11<=D2:D11*E2:E11+F2:F11)) = 3. The double minus turns TRUE and FALSE into 1 and 0 so SUMPRODUCT can add them: 3 SKUs.
6What was the on-time delivery percentage last month?

Your formula for exercise 6

Show answer
=H10/H6 = 0.95. 3,021 / 3,180 = 95.0%.
7What did shipping cost per order, rounded to cents?

Your formula for exercise 7

Show answer
=ROUND(H8/H6,2) = 6.9. 21,940 / 3,180 = $6.90.
8How many days of inventory are on hand, based on 12-month cost of goods sold? Round to 1 decimal.

Your formula for exercise 8

Show answer
=ROUND(365*SUMPRODUCT(C2:C11,B2:B11)/SUMPRODUCT(G2:G11,B2:B11),1) = 35.1. It is 365 divided by turnover, written the other way up: about 35.1 days.

Free templates for warehouse and logistics

Guided project: Picker productivity report

Turn a week of picking hours, lines and errors into pick rates, accuracy, a target check and the team rate.

6 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate a reorder point in Excel?

Multiply average daily usage by the lead time in days and add safety stock: =ROUNDUP(usage*lead_time+safety_stock,0). Then flag items with =IF(on_hand<=reorder_point,"Reorder",""). Use the full lead time until stock is usable, including receiving.

How do I calculate inventory turnover in Excel?

Divide cost of goods sold for the period by average inventory value for the same period. With unit-level data, =SUMPRODUCT(units_sold,unit_cost)/SUMPRODUCT(on_hand,unit_cost) gives a quick estimate; average several month-end inventory values for a better one. 365 divided by turnover gives days of inventory.

How do I calculate pick rate?

Divide what was picked (order lines, units or orders, and say which) by the hours spent picking. For a team or a period, divide total picks by total hours rather than averaging individual rates.

What is the difference between safety stock and a reorder point?

Safety stock is the buffer you keep for late deliveries and demand spikes. The reorder point is the level that triggers an order: expected usage during the lead time plus that safety stock.

Excel for other fields

Updated November 2, 2026