Practical guide / Multi-Site Stock & Transfers 1.0
Know what is at each place, and what is still on its way.
Link location balances, dated movements and transfers. Stock in transit stays apart from stock available at its destination.
Your first useful result: Name the locations and items Follow the first task
Name the locations and items
Create stable site and item IDs, a target quantity and a unit cost.
Balances join each site/item lane by its stable ID.
Review: Review the labelled results and correct source warnings before acting.
Open each location balance
Enter one Stock Lane for each site/item pair and its opening quantity.
Dated movements alter on-hand quantities; opening balances stay separate.
Review: Review the labelled results and correct source warnings before acting.
Record physical events
Enter dated inbound/outbound movements and separately the dispatched and received transfer dates.
Dispatched transfers leave the origin, and only actual receipt puts them at the destination.
Review: Review the labelled results and correct source warnings before acting.
Check the assumptions.
- Save a working copy. Manual entry, with no automatic sync or external connections.
- Supplied tables have fixed capacities. Extend formulas, tables and validation together before using extra rows.
- Use one currency. Blank means unknown; enter 0 when the amount is known to be zero.
- Resolve the highlighted input rows before relying on the summary.
- Uses exact-mode XMATCH. Requires Excel 2021, Excel 2024 or Microsoft 365; older Excel editions are not supported. Native Excel, Google Sheets and Numbers have not been verified.
- One Stock Lane is one site and item. Opening quantity is as at the period start; do not re-enter that opening in Movements.
- A dispatched transfer leaves the origin and remains in transit until its actual receipt date. Planned and cancelled transfers do not change stock.
- A negative location balance blocks the summary. This register does not book transport, purchase orders or accounting inventory valuation.
Keep a copy you can return to.
Keep the supplied clean and example files unchanged. Save your own working file with a useful name, such as multi-site-stock-2027-home.xlsx, and keep a dated backup before a large edit. A purchased ZIP is the blank source, not a backup of your entries.
If an input or formula is replaced
Undo the edit while it is still available, or return to a known good backup. If you need a fresh clean file, transfer only your records into its marked input cells and follow the setup guide again. Do not paste whole sheets over the supplied formulas, validations or reference lists. Check IDs, units, dates and linked results against the example before relying on the copy.
When the supplied rows are full
30 sites · 100 items · 150 stock lanes · 300 movements · 150 transfers. Calculation ranges and validations have fixed capacities. A new row below a table does not prove that every linked result includes it.
For a separate period, project or volume, preserve the old workbook and start a named clean copy. If records must carry forward, record exactly which inputs you transfer, preserve their references and compare the opening and closing figures. Avoid copying historical activity as both an opening amount and a new movement. There is no automatic migration between files.
Inspect the workbook before you choose.
View its real sheets and a fictional calculation example.
Explore Multi-Site Stock & TransfersWritten for version 1.0. Examples describe the supplied workflow; they are not customer results.