Multi-Location Inventory (Excel)
When stock sits in more than one place (a main warehouse, a factory store, a branch, a site) a single stock figure is not enough. This workbook keeps stock for every item in up to eight locations from one movement log: a receipt has only a "to" location, an issue only a "from" location, and a transfer has both. The Stock sheet shows each location's quantity, the company total, the value, and which items have reached their reorder level with a suggested quantity to order.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| Item code | Item name | Unit | Main warehouse | Factory store | Delhi branch | Site: Phase 2 | (location 5) | (location 6) | (location 7) | (location 8) | Total | Reorder level | Status | Suggested order |
| RM-001 | MS round bar 20 mm | kg | 3,800 | 660 | 0 | 0 | 4,460 | 1,500 | OK | 0 | ||||
| RM-002 | MS plate 10 mm | kg | 1,060 | 150 | 0 | 0 | 1,210 | 800 | OK | 0 | ||||
| BO-101 | Ball bearing 6205-2RS | Nos | 140 | 40 | 30 | 0 | 210 | 200 | OK | 0 | ||||
| PK-201 | 5-ply corrugated box 600x400x400 | Nos | 80 | 0 | 120 | 0 | 200 | 300 | Reorder | 1,300 | ||||
| SP-301 | Hex bolt M12x50 (box of 100) | Box | 3 | 6 | 2 | 12 | 23 | 10 | OK | 0 | ||||
| CN-401 | Cutting oil 20 L | Can | 3 | 0 | 0 | 0 | 3 | 4 | Reorder | 9 |
What's inside
- Locations sheet: up to 8 warehouse, godown, store or site names, plus your currency code
- Items sheet: item code, name, unit, unit cost, reorder level, maximum level and lead time
- Opening sheet: opening stock of each item in each location
- Movements sheet: date, item code, quantity, from location, to location, reference and note; the type (Receipt, Issue, Transfer) and item name fill in by themselves, 2,000 ready rows
- Stock sheet: quantity per location, total, reorder level, status (Reorder, OK, Negative), suggested order quantity up to the maximum level and stock value
- Value by location at the foot of the Stock sheet
How to use it
- Type your location names on the Locations sheet. They become the column headings everywhere else.
- On the Items sheet enter each item with a short unique code, unit, unit cost, reorder level and maximum level.
- On the Opening sheet type the stock you hold today in each location.
- Record every movement on the Movements sheet. Goods from a supplier: fill only "To". Issue to production or a customer: fill only "From". Moving stock between locations: fill both.
- Open the Stock sheet. Each location's quantity and the total update as you type. Items at or below the reorder level show "Reorder" with a suggested order quantity.
- Count each location once a month and add an adjustment movement (From for a loss, To for a gain) with the note "physical count".
Tips
- Use item codes, not names, in the Movements sheet. Codes avoid the "same item, two spellings" problem.
- A negative quantity in a location means more was issued or transferred out than was there: check the From location of recent movements.
- The reorder level and maximum level are for the whole company. If each location orders on its own, keep one workbook per location instead.
- Reorder level = daily use × lead time in days + safety stock is a good starting point.
Questions and answers
How does a transfer work?
One row with both a From and a To location. It reduces the first location and increases the second by the same quantity, so the company total does not change.
Can I have more than 8 locations?
The free version has 8 location columns. For more, keep a second file for the other locations or ask for a larger version through the contact page.
How is the suggested order quantity worked out?
When the total stock is at or below the reorder level, it is the maximum level minus the total stock. Otherwise it is zero.
Does it handle batches or expiry dates?
Not in this version. Put the batch number in the Note column and filter by it when you need it.