Inventory Tracker

About this template

Three sheets, one entry point

Stock overview is the item ledger, Stock movements is the transaction log, and Reorder rules holds the policy table. You only type into the movements sheet plus safety stock, opening stock and unit price on the ledger; stock in, stock out, on hand, stock status and stock value are all formulas. Replace the sample header first: as-of date 2026-06-30, warehouse HQ Warehouse 1, owner Logistics Team, Lee Su-min (a placeholder name).

Filling it in

1. Rows 13-26 of the ledger take item code (series-sequence, like A-1010), item name, spec, unit, safety stock (F), opening stock (G) and unit price (L). The sample carries ten items, from A4 copy paper to cleaning detergent.
2. Log every movement on rows 5-40 of Stock movements: date, type, item code, item name, quantity, unit price, supplier or requesting department, owner. The item code has to match exactly across the two sheets, because the ledger matches on it.
3. Leave columns H, I, J, K and M of the ledger alone. Typing over a formula quietly drops that item out of the summary.
4. When a physical count disagrees with on hand, adjust the opening stock in column G. On hand is opening + in − out, so there is nowhere else to correct it.
5. Anything flagged Below safety stock or Out of stock goes to a purchase order within the deadline in the rules sheet: two business days and same day respectively.

Worked examples

• Log 2026-06-22, Stock in, B-2010, Packing box, 240 at 850 and the B-2010 row gains both stock in and on hand — that single line is what keeps its status at Normal rather than Below safety stock.
• Toner A-1020 has a safety stock of 12; opening 9 plus 24 in minus 27 out leaves 6 on hand, so its status cell reads Below safety stock. With three such items plus one out of stock in the sample, the alert at the top reads “Reorder review needed: 4 cases”.

How the formulas work

• Stock in and stock out are =SUMIFS(‘Stock movements’!$F$5:$F$40,’Stock movements’!$D$5:$D$40,$B13,’Stock movements’!$C$5:$C$40,”Stock in”) — the item code comes from the row, but the type is the literal string.
• On hand is =G13+H13-I13 and the status is =IF(J13<=0,”Out of stock”,IF(J13<F13,”Below safety stock”,”Normal”)). Note that the rules sheet also defines an Overstock band (on hand above safety stock × 3), but no formula ever writes it — you have to watch for that one yourself.
• The alert is =IF(D9+E9>0,”Reorder review needed: “&(D9+E9)&” cases”,”No issues”), counting only the two shortage statuses.
• On the movements sheet the amount is =F5*G5 on the filled rows and =IF(F23=””,””,F23*G23) on the blank ones, and the footer reads =COUNTIF(C5:C40,”Stock in”)&” in / “&COUNTIF(C5:C40,”Stock out”)&” out”.

Common mistakes

• Inventing movement types. The criteria are exactly Stock in and Stock out, so a row marked “Return” counts for neither. Record a return as an offsetting Stock in line rather than a negative quantity.
• Adding movements below row 40, outside the formula range.
• Leaving safety stock blank. With column F empty every item reads as Normal and the reorder alert never fires.
• Typing the counted figure straight into on hand, which deletes the formula and freezes that item from the next month on.