Pro workbook: Stock, Movements, Reorder, Dashboard, Setup, Formulas. Sample data, real formulas.
| Stock | Stock value | 3,854.20 | ||||||||||||
| One row per item. Yellow cells are yours; value and status are calculated. Quantity = opening + in − out from the Movements sheet. | To reorder | 4 | ||||||||||||
| SKU | Item | Category | Unit cost | Opening | In | Out | On hand | Reorder at | Reorder qty | Value | Status | |||
| BG-001 | Field tote, tan | Bags | 62.00 | 14 | 0 | 4 | 10 | 6 | 12 | 620.00 | OK | |||
| BG-002 | Field tote, black | Bags | 62.00 | 4 | 12 | 9 | 7 | 6 | 12 | 434.00 | OK | |||
| BG-003 | Weekender, tan | Bags | 98.00 | 7 | 0 | 0 | 7 | 3 | 6 | 686.00 | OK | |||
| BG-004 | Messenger, olive | Bags | 74.50 | 2 | 8 | 3 | 7 | 4 | 8 | 521.50 | OK | |||
| WL-001 | Bifold wallet, tan | Wallets | 18.20 | 31 | 0 | 0 | 31 | 15 | 30 | 564.20 | OK | |||
| WL-002 | Card holder, black | Wallets | 9.80 | 12 | 0 | 5 | 7 | 20 | 40 | 68.60 | Reorder | |||
| BT-001 | Belt 32mm, tan, M | Belts | 21.00 | 9 | 0 | 0 | 9 | 8 | 16 | 189.00 | OK | |||
| BT-002 | Belt 32mm, tan, L | Belts | 21.00 | 6 | 0 | 2 | 4 | 8 | 16 | 84.00 | Reorder | |||
| AC-001 | Key fob | Accessories | 4.10 | 58 | 0 | 0 | 58 | 25 | 50 | 237.80 | OK | |||
| AC-002 | Luggage tag | Accessories | 5.60 | 17 | 0 | 4 | 13 | 20 | 40 | 72.80 | Reorder | |||
| PK-001 | Dust bag, cotton | Packaging | 1.35 | 210 | 0 | 0 | 210 | 100 | 200 | 283.50 | OK | |||
| PK-002 | Gift box, large | Packaging | 2.90 | 44 | 0 | 12 | 32 | 50 | 100 | 92.80 | Reorder | |||
| Movements | |||||
| Every unit in or out. SKU is a dropdown from Stock; the Stock sheet adds these up. | |||||
| Date | SKU | Type | Qty | Note | Item |
| 2026-09-01 | BG-002 | In | 12 | PO 1041 | Field tote, black |
| 2026-09-02 | BG-001 | Out | 3 | Web orders | Field tote, tan |
| 2026-09-03 | WL-002 | Out | 5 | Web orders | Card holder, black |
| 2026-09-04 | BG-002 | Out | 9 | Wholesale — Marlow | Field tote, black |
| 2026-09-05 | AC-002 | Out | 4 | Web orders | Luggage tag |
| 2026-09-08 | BG-004 | In | 8 | PO 1042 | Messenger, olive |
| 2026-09-09 | BG-004 | Out | 3 | Web orders | Messenger, olive |
| 2026-09-10 | PK-002 | Out | 12 | Used | Gift box, large |
| 2026-09-11 | BT-002 | Out | 2 | Web orders | Belt 32mm, tan, L |
| 2026-09-11 | BG-001 | Out | 1 | Damaged | Field tote, tan |
| The Item column looks the SKU up on Stock so typos show as 'unknown SKU' in red. | |||||
| Stock reads rows 5–1000 here. | |||||
| Reorder list | |||||||||
| Every item at or below its reorder point, with the quantity to order and what it will cost. Rows that are fine stay blank. | Sort by SKU or filter blanks away if you want a tight list — the rows mirror Stock one-to-one so the formulas stay simple. | ||||||||
| SKU | Item | On hand | Reorder at | Order qty | Unit cost | Order value | Total to order | 1,242.00 | |
| Lines | 4 | ||||||||
| WL-002 | Card holder, black | 7 | 20 | 40 | 9.80 | 392.00 | |||
| BT-002 | Belt 32mm, tan, L | 4 | 8 | 16 | 21.00 | 336.00 | |||
| AC-002 | Luggage tag | 13 | 20 | 40 | 5.60 | 224.00 | |||
| PK-002 | Gift box, large | 32 | 50 | 100 | 2.90 | 290.00 | |||
| Dashboard | |||||
| Value and health by category, from the Stock sheet. | |||||
| Category | Items | Units | Value | To reorder | Share of value |
| Bags | 4 | 31 | 2,261.50 | 0 | 58.7% |
| Wallets | 2 | 38 | 632.80 | 1 | 16.4% |
| Belts | 2 | 13 | 273.00 | 1 | 7.1% |
| Accessories | 2 | 71 | 310.60 | 1 | 8.1% |
| Packaging | 2 | 242 | 376.30 | 1 | 9.8% |
| Total | 12 | 395 | 3,854.20 | 4 | |
| Health | Value | ||||
| Out of stock | 0 | ||||
| Below reorder point | 4 | ||||
| Cost to restock everything | 1,242.00 | ||||
| Most valuable item | Weekender, tan |
| Setup |
| Categories for the Stock dropdown and the Dashboard. |
| Categories |
| Bags |
| Wallets |
| Belts |
| Accessories |
| Packaging |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Stock · In | SUMIFS(Movements!$D$5:$D$1000, Movements!$B$5:$B$1000, $A5, Movements!$C$5:$C$1000, "In") | Units received for this SKU: every Movements row with the same SKU and type In. |
| Stock · Out | Same with "Out" | Units that left. |
| Stock · On hand | E5+F5-G5 | Opening count plus everything in, minus everything out. Do a physical count? Type it into Opening and clear the old movements. |
| Stock · Value | D5*H5 | Unit cost × on hand. |
| Stock · Status | IF(H5<=0,"Out of stock",IF(H5<=I5,"Reorder","OK")) | Nested IF: nothing left → Out of stock; at or below the reorder point → Reorder; otherwise OK. Conditional formatting colours the word. |
| Movements · Item | IFERROR(INDEX(Stock!$B$5:$B$200, MATCH(B5, Stock!$A$5:$A$200, 0)), "unknown SKU") | Looks the SKU up on Stock and shows the item name — or 'unknown SKU' in red if it isn't there, so a typo is caught where it happens. |
| Reorder · every column | IF(AND(Stock!$A5<>"", Stock!$H5<=Stock!$I5), Stock!$A5, "") | Each Reorder row mirrors the Stock row with the same number and shows it only when on hand ≤ reorder point. No FILTER function, so it works in Excel 2016 and Numbers. |
| Reorder · Total to order | SUM(G5:G201) | Order qty × unit cost, summed over the rows that show. |
| Dashboard · Value by category | SUMIF(Stock!$C$5:$C$200, A5, Stock!$K$5:$K$200) | Adds the Value column for rows in this category. |
| Dashboard · To reorder | COUNTIFS(cat, A5, status, "Reorder") + COUNTIFS(cat, A5, status, "Out of stock") | Both flags counted, per category. |
| Dashboard · Most valuable item | INDEX(Stock!B:B, MATCH(MAX(Stock!K:K), Stock!K:K, 0)) | MAX finds the largest value, MATCH its row, INDEX the name. |