Small Business Inventory Spreadsheet with Reorder Alerts

12 tabs, one linked system

1. 1

   Start Here

   How the tabs fit together, the color legend, how the moving weighted average is calculated with a worked example, the reorder rule, a counting routine, printing and Google Sheets notes.

2. 2

   Dashboard

   Inventory value at cost and at retail, active SKUs, units on hand, items to reorder, out of stock, sales, gross profit and margin, shrink, dead stock value and turns per year. Sales and margin by month with a chart, value by location (including stock not at a known location) and category, a data check line for duplicate SKUs and flagged Movements rows, top 10 items by gross profit, and the cash tied up in dead and slow stock.

3. 3

   Settings

   Business details, as-of date, sales period, sales window for days of stock (90 days by default), safety days, slow mover limit, recount limit in dollars, PO tax rate. Up to 5 locations, 20 categories and 12 units.

4. 4

   Suppliers

   Up to 15 suppliers with contact, phone, email, account number, payment terms, order minimum and notes. Items to reorder and estimated order value per supplier.

5. 5

   Items

   Up to 200 SKUs: name, category, supplier, supplier part number, unit, home location, bin, reorder point, reorder qty, case pack, lead time, latest cost, sell price, on order. On hand, average cost, inventory value, margin and status (OK, On order, Reorder, Out of stock, Inactive, Duplicate SKU) calculate.

6. 6

   Movements

   3,000 rows for Receive, Sale, Return, Adjust and Transfer, with dropdowns and a rows-used counter. Each row shows on hand after, average cost after, inventory dollar change, sales and cost of goods sold. A Check column flags a missing SKU, date or location, a location not in Settings, stock going below zero at one location, a row dated before the last one for that SKU, stock below zero, a receipt with no cost and a sale with no price.

7. 7

   Stock on Hand

   Units at each location, units not at a known location, total on hand, on order, average cost, value at cost and at retail, status. Totals at the top.

8. 8

   Reorder

   Every item at or below its reorder point (room for all 200), grouped by supplier: on hand, on order, suggested qty rounded up to the case pack, unit cost, estimated cost, lead time, days of stock. An order summary for up to 15 suppliers, checked against each order minimum. Prints the summary and the first 30 lines on one page.

9. 9

   Purchase Order

   Pick a supplier and a one-page PO fills in: supplier details, your SKU and their part number, qty, unit cost, line totals, 5 extra lines you add by hand, freight, tax, expected date from the lead time, order minimum check and signature line.

10. 10

    Count Sheet

    Pick a location and print a blind count sheet, 40 lines a page: bin (or 'not home' when the item's home bin is at another location), SKU, item, unit and a blank counted qty column, plus lines for items found but not listed and signatures.

11. 11

    Count Entry

    One row per item, so posting the adjustments never shifts a typed count. Type the counted quantities. System qty, variance in units, dollars and percent, Match, Adjust or Recount (over your dollar limit), the adjust qty to log, net and absolute variance and count accuracy.

12. 12

    Stock Health

    Per item: units sold in the window, average daily sales, days of stock, last sale, days since sale, suggested reorder point next to yours, sales, cost of goods sold, gross profit and margin for the period, sell-through, ABC class, and a status: Healthy, Stockout risk, Slow mover, Dead stock, New item, Out of stock.