Template
Warehouse Inventory Tracking Spreadsheet:Stock-by-Location Columns, SUMIFS On-Hand and Reserved Formulas, Reorder Points, Cycle Count Dates, Georgia's January 1 Inventory Return and a Filled Savannah HVAC Parts Sheet
Warehouse inventory tracking spreadsheet: stock by location, on hand, reserved and reorder point formulas, last count dates and a filled Savannah parts sheet.
A warehouse inventory tracking spreadsheet keeps one row per item per stock location, with the quantity on hand, the quantity reserved for scheduled jobs, the quantity available, a reorder point and the date of the last count. Every quantity is computed from a log of receipts, checkouts, returns, transfers and count adjustments. The filled sheet tracks an HVAC company's two parts warehouses in Savannah and Pooler, Georgia.
The layout is one of the free field service templates kept by FieldRanger, field service software that runs inside HubSpot. The column and formula tables below set out the template, and the page offers it as a free .xlsx download with no sign-up: the Stock columns start in row 4 of its Template sheet. Its cost columns also produce the January 1 inventory figure Georgia asks for on its business personal property return.
Workbook tabs and the record each holds
Quantities are never typed into the Stock tab. A typed on-hand figure is correct until the next checkout nobody records; a figure summed from the log can be traced to each movement that made it.
| Tab | One row per | Purpose |
|---|---|---|
| Items | SKU | Description, return line, unit, delivered unit cost, lead time |
| Stock | SKU and location | On hand, reserved, available, reorder point, count status, extended cost |
| Moves | Movement | Receipts, checkouts, returns, transfers and count adjustments, each signed |
| Reservations | Job and SKU | Parts held for a scheduled job until they are checked out |
| Counts | Count | System quantity, counted quantity and the variance |
| Jan 1 | Return line and location | Cost of inventory owned on January 1 |
Stock tab columns and formulas
Headers sit in row 3 and the first item in row 4. The formulas are shown for row 4 and copy down. Each SKU stocked in two warehouses has two rows, one per location.
| Column | Header | Formula or entry |
|---|---|---|
| A | SKU | Typed |
| B | Location | Typed: SAV or POO |
| C | Bin | Typed: aisle, shelf, position |
| D | Description | =XLOOKUP(A4,Items!$A:$A,Items!$B:$B,"") |
| E | On hand | =SUMIFS(Moves!$F:$F,Moves!$B:$B,A4,Moves!$C:$C,B4) |
| F | Reserved | =SUMIFS(Reservations!$E:$E,Reservations!$C:$C,A4,Reservations!$D:$D,B4,Reservations!$F:$F,"Open") |
| G | Available | =E4-F4 |
| H | Daily use | =-SUMIFS(Moves!$F:$F,Moves!$B:$B,A4,Moves!$C:$C,B4,Moves!$D:$D,"Checkout",Moves!$A:$A,">="&TODAY()-90)/90 |
| I | Lead time, days | =XLOOKUP(A4,Items!$A:$A,Items!$F:$F) |
| J | Safety stock | Typed, in units |
| K | Reorder point | =ROUNDUP(H4*I4+J4,0) |
| L | Reorder flag | =IF(G4<=K4,"Reorder","") |
| M | Last count | =MAXIFS(Counts!$A:$A,Counts!$B:$B,A4,Counts!$C:$C,B4) |
| N | Count class | Typed: A, B or C |
| O | Days since count | =TODAY()-M4 |
| P | Count flag | =IF(O4>=XLOOKUP(N4,{"A","B","C"},{30,90,180}),"Count due","") |
| Q | Unit cost | =XLOOKUP(A4,Items!$A:$A,Items!$E:$E) |
| R | Extended cost | =E4*Q4 |
| S | Return line | =XLOOKUP(A4,Items!$A:$A,Items!$C:$C) |
SUMIFS "adds all of its arguments that meet multiple criteria" and accepts "up to 127 range/criteria pairs" (Microsoft Support, SUMIFS function, checked November 13, 2026). Column E uses two pairs, SKU and location, which is what separates stock in Savannah from the same SKU in Pooler. MAXIFS "returns the maximum value among cells specified by a given set of conditions or criteria" and needs Excel 2019 or later (Microsoft Support, MAXIFS function, checked November 13, 2026); in column M it returns the latest count date for that SKU at that location.
Available, not on hand, drives the reorder flag. A dispatcher promising a part for Thursday's job needs the quantity nobody else has claimed, so column L compares G with the reorder point.
Moves, reservations and counts logs
The three logs are append-only. A mistaken row is corrected by a new row with the opposite sign, so the history behind every on-hand figure stays visible.
| Tab | Columns | Formula |
|---|---|---|
| Moves | A Date · B SKU · C Location · D Type (Receipt, Checkout, Return, Transfer out, Transfer in, Count adjustment) · E Quantity · F Signed quantity · G Job number · H Technician or vendor · I Reference (PO, transfer or count number) | F2: =IF(OR(D2="Checkout",D2="Transfer out"),-E2,E2) |
| Reservations | A Job number · B Scheduled date · C SKU · D Location · E Quantity · F Status (Open, Issued, Cancelled) | Status typed; Issued when the part is checked out against the job |
| Counts | A Count date · B SKU · C Location · D System quantity · E Counted quantity · F Variance · G Counted by | F2: =E2-D2, posted to Moves as a Count adjustment with the same sign |
A transfer between warehouses is two Moves rows with the same reference: a Transfer out at the sending location and a Transfer in at the receiving one. Total stock across both warehouses does not change, and each location's on-hand figure does.
Reorder points and count intervals
The reorder point is the use expected during the supplier's lead time plus a safety stock: daily use times lead time days, plus the buffer, rounded up to a whole unit. Daily use in column H comes from the last 90 days of checkouts at that location, so a capacitor drawn 3.2 times a day in Savannah and 1.4 times a day in Pooler gets two reorder points.
Count intervals follow an A, B, C class set by the company (sample policy): A items, the high-cost or high-use parts, are counted every 30 days; B items every 90; C items every 180. A count due flag in column P lists the bins for the week's cycle count, and the variance from each count posts back to Moves.
Filled warehouse inventory sheet: HVAC parts in Savannah and Pooler, Georgia
The company runs a main parts warehouse in Savannah (SAV) and a smaller one in Pooler (POO), both in Chatham County. The sheet is read on November 13, 2026, so TODAY() returns that date. The company, SKUs, quantities, costs, use rates and the count policy are samples; the return rules in the next section come from the Georgia Department of Revenue.
| SKU | Location, bin | On hand | Reserved | Available | Daily use | Lead time | Safety | Reorder point | Reorder | Last count | Days | Class | Count | Unit cost | Extended |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CAP-4505 dual run capacitor 45/5 MFD | SAV, A-03-2 | 46 | 6 | 40 | 3.2 | 3 | 8 | 18 | Oct 30, 2026 | 14 | A | $14.80 | $680.80 | ||
| CAP-4505 dual run capacitor 45/5 MFD | POO, B-01-1 | 11 | 4 | 7 | 1.4 | 3 | 4 | 9 | Reorder | Sep 18, 2026 | 56 | A | Count due | $14.80 | $162.80 |
| CON-2P40 contactor, 2-pole, 40 A | SAV, A-03-4 | 22 | 2 | 20 | 1.1 | 3 | 4 | 8 | Oct 30, 2026 | 14 | A | $18.60 | $409.20 | ||
| IGN-HSI hot surface ignitor | SAV, A-05-1 | 9 | 3 | 6 | 0.8 | 5 | 3 | 7 | Reorder | Aug 21, 2026 | 84 | B | $27.40 | $246.60 | |
| FLS-UNI flame sensor | POO, B-02-3 | 15 | 0 | 15 | 0.5 | 5 | 3 | 6 | Jun 4, 2026 | 162 | C | $9.75 | $146.25 | ||
| FLT-16251 pleated filter 16x25x1, case of 12 | SAV, C-10-1 | 30 | 12 | 18 | 2.0 | 7 | 6 | 20 | Reorder | Oct 2, 2026 | 42 | B | $38.40 | $1,152.00 | |
| CPM-120 condensate pump, 120 V | POO, B-04-2 | 4 | 1 | 3 | 0.3 | 5 | 2 | 4 | Reorder | Jul 15, 2026 | 121 | C | $52.90 | $211.60 | |
| TAPE-FOIL foil tape, 2.5 in x 60 yd | SAV, D-01-1 | 26 | 0 | 26 | 0.9 | 7 | 4 | 11 | May 20, 2026 | 177 | C | $11.20 | $291.20 |
Four rows reorder and one row is due for a count. The filter row shows why reserved stock matters: 30 cases sit on the shelf, but 12 are held for maintenance visits booked from November 16 to 20, and the 18 left fall under the reorder point of 20. The Pooler capacitor row needs both a count and an order, and the count comes first: an order placed on a wrong on-hand figure inherits the error.
| Date | SKU | Location | Type | Quantity | Signed | Job | Technician or vendor | Reference |
|---|---|---|---|---|---|---|---|---|
| Nov 10, 2026 | CAP-4505 | POO | Checkout | 1 | -1 | J-5750 | T. Greene | |
| Nov 11, 2026 | FLT-16251 | SAV | Receipt | 24 | 24 | Distributor | PO-3184 | |
| Nov 12, 2026 | CAP-4505 | POO | Checkout | 1 | -1 | J-5758 | T. Greene | |
| Nov 12, 2026 | IGN-HSI | SAV | Checkout | 1 | -1 | J-5761 | K. Brooks | |
| Nov 12, 2026 | IGN-HSI | SAV | Return | 1 | 1 | J-5761 | K. Brooks | Wrong model |
| Nov 13, 2026 | TAPE-FOIL | SAV | Transfer out | 4 | -4 | TR-0112 | ||
| Nov 13, 2026 | TAPE-FOIL | POO | Transfer in | 4 | 4 | TR-0112 |
The ignitor checkout and return on November 12 net to zero, and both rows stay. Deleting them would hide a wrong-part trip that cost a second visit to job J-5761.
Georgia January 1 inventory return from the spreadsheet
Georgia taxes business inventory as personal property. Property tax returns are due "between January 1 and April 1" (O.C.G.A. 48-5-18), and taxes apply to property "owned on January 1 for the current tax year" (Georgia Department of Revenue, Property Tax Returns and Payment, checked November 13, 2026). Businesses report inventory on Schedule B of Form PT-50P.
The form's instructions set the spreadsheet's cost rules (Form PT-50P, Tangible Personal Property Tax Return and Schedules, revised January 18, 2017):
- "Inventory should be reported at 100% cost on January 1", and cost includes "freight in, overhead or burden". The Items tab's unit cost is therefore the delivered cost, freight included.
- "'LIFO' is not acceptable." The sheet values each unit at its delivered cost, which fits the form's Actual or FIFO methods.
- "Do not make any deductions for anticipated mark-down or shrinkage." A count variance is recorded through the Moves tab, not deducted as a percentage.
- Goods held but not owned, such as consigned stock, are listed separately with the name and address of the legal owner, so consigned SKUs carry an owner column and stay out of the totals.
- "If inventory is less than the previous year an explanation for the decrease should be submitted." The Jan 1 tab keeps the prior year's totals beside the current ones.
Schedule B lists inventory by line, including Merchandise, Spare Parts and Supplies. The sample maps repair parts to Spare Parts, filters and pumps sold with maintenance to Merchandise, and shop consumables to Supplies (a sample mapping; the county assessor decides disputed lines). On-hand at January 1 comes from the Moves tab with a date limit: =SUMIFS(Moves!$F:$F,Moves!$B:$B,A4,Moves!$C:$C,B4,Moves!$A:$A,"<="&DATE(2027,1,1)). The Jan 1 tab then totals cost by return line and location with SUMIFS on columns R, S and B.
| Return line | Savannah | Pooler | Total |
|---|---|---|---|
| Spare Parts | $1,336.60 | $309.05 | $1,645.65 |
| Merchandise | $1,152.00 | $211.60 | $1,363.60 |
| Supplies | $291.20 | $0.00 | $291.20 |
| Total inventory | $2,779.80 | $520.65 | $3,300.45 |
Some counties exempt part of this value. Georgia's freeport exemption, where the local government has adopted it, can reach "goods, wares, and merchandise of every character and kind constituting a business's inventory" at Level 2, at "20, 40, 60, 80 or 100 percent of the inventory value". Applications go to the Board of Tax Assessors in the return period, and late applications "can receive a partial exemption for that year up to June 1" (Georgia Department of Revenue, Freeport Exemption, checked November 13, 2026). Whether Chatham County's adopted levels reach a service company's parts is a question for the county's assessors; the sheet supplies the cost by line either way.
Inventory, equipment and overtime spreadsheets compared
Inventory is consumed. A capacitor installed on job J-5750 does not come back, and its record is a count that falls. An equipment tracking spreadsheet follows the items that do come back, such as a recovery machine or a vacuum pump, by holder and service date. The technicians whose names fill the Moves tab are paid from timesheets that a tracking overtime spreadsheet turns into weekly and daily overtime. The warehouse inventory tracking guide covers perpetual and periodic methods, bin codes and record accuracy in more depth than this template.
Warehouse inventory management software and the sheet
The sheet works only while every checkout is typed. A technician who takes three capacitors at 6:45 a.m. and logs none of them leaves column E three units high until the next count finds the gap.
In FieldRanger, technicians check parts and materials out of a specific warehouse, so stock is counted where it actually sits. Inventory alerts give low-stock warnings and reorder reminders, and the parts and materials used on each job are logged against it for job costing. Revenue reports show job revenue and margin by customer or service type, and any report downloads as CSV or Excel, which can feed the Jan 1 tab. The warehouse inventory management software page describes the checkout.
Each technician's truck can be its own stock location, so the capacitors that ride in a van are counted there rather than in Savannah or Pooler. Equipment is checked in and out by scanning its QR code on a phone. FieldRanger does not prepare Georgia's PT-50P; the exported counts and costs are the source for Schedule B.
Frequently asked questions
How to track warehouse inventory in Excel?
Keep a Moves tab with one signed row per receipt, checkout, return, transfer and count adjustment, and a Stock tab with one row per SKU per location. On hand is =SUMIFS(Moves!F:F,Moves!B:B,SKU,Moves!C:C,location), available is on hand less open reservations, and a reorder flag compares available with the reorder point.
How is a reorder point calculated in an inventory spreadsheet?
Daily use times the supplier's lead time in days, plus safety stock, rounded up to a whole unit. In the sample, a capacitor used 1.4 times a day with a 3-day lead time and 4 units of safety stock reorders at 9.
What is the difference between on hand and available inventory?
On hand is everything on the shelf. Available is on hand less the quantity reserved for scheduled jobs; the sample filter row has 30 cases on hand, 12 reserved and 18 available.
How does a Georgia business report inventory for property tax?
On Schedule B of Form PT-50P, filed with the county between January 1 and April 1, at 100% delivered cost on January 1, without deductions for anticipated mark-down or shrinkage. LIFO is not acceptable.
Is there a free warehouse inventory tracking spreadsheet template for Excel?
Yes. The page offers a free .xlsx download with no sign-up: the Stock columns on a Template sheet from row 4, the Items, Moves, Reservations, Counts and Jan 1 tabs, and a Sample sheet with the Savannah and Pooler rows.
The Pooler capacitor row before December
Bin B-01-1 in Pooler gets counted first, on November 16, because its count is 26 days overdue and its 7 available capacitors sit under a reorder point of 9. If the count holds at 11, the order goes in the same day and arrives within the 3-day lead time; if it does not, the variance posts to Moves and the reorder point is checked again. The Jan 1 tab runs on January 2, 2027, and the PT-50P goes to Chatham County before April 1. FieldRanger's plans are on pricing, and Start free trial runs 14 days without a card; the other spreadsheets in this set are listed in the free field service templates.
FieldRanger runs field service inside HubSpot
Estimates, scheduling and dispatch, a technician app and invoicing on the HubSpot records sales already keeps. Every plan starts with a free trial.
Book a demo