Branch Transfer Planning Worksheet
Dead stock at one branch and stockouts at another is the classic multi-location distributor problem. Moving stock between branches is often cheaper than buying new, but only if you know how much to move. This worksheet calculates that quantity per item and totals the value so you can approve the plan quickly.
| Item | From branch | To branch | On hand at source | Source minimum to keep | On hand at destination | Destination daily usage | Target days of supply | Unit cost | Suggested transfer | Transfer value | |
|---|---|---|---|---|---|---|---|---|---|---|---|
230 | $1,564 | ||||||||||
85 | $293 | ||||||||||
1,185 | $213 | ||||||||||
20 | $38 | ||||||||||
0 | $0 |
The suggested transfer is the lesser of what the destination needs to reach its target days of supply and what the source can release above its minimum. Factor in freight cost, vendor pack sizes and special-order allocations before creating the actual transfer orders in your ERP.
Stock balancing recommendations
Optional: we can outline a read-only report that suggests inter-branch transfers from your inventory by location data.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
For each item, enter the source and destination branch, on-hand quantities, the minimum the source should keep, the destination's daily usage, and the target days of supply you want at the destination. The sheet suggests a transfer quantity and its value. Add rows for every imbalance you find in your inventory by location report, review totals, then export to Excel and use it to create transfer orders in Prophet 21, Eclipse or your own ERP.
The logic behind the suggestion
The destination need is daily usage times target days of supply, less what it already has, floored at zero. The source availability is its on-hand less the minimum it must keep, also floored at zero. The suggested transfer is the smaller of the two, rounded up to whole units. This protects the source from being stripped of stock and keeps the destination from receiving more than it will use within the target period.
When to transfer versus buy
Compare the freight and handling cost of the transfer with the saved purchase and the cost of a lost sale. Small, cheap items rarely justify a dedicated truck, while high value items usually do. Consider consolidating transfers onto scheduled delivery routes. If a branch regularly needs stock from another, that is a sign the stocking levels at each location need review, rather than a permanent transfer habit.
- Weigh freight and handling against purchase cost
- Use existing delivery routes where possible
- Check for open orders or allocations on source stock
- Review stocking levels if the same item keeps moving
Common mistakes
Ignoring open allocations at the source can leave a branch short on stock it has already promised. Transferring to fix a symptom, such as a stockout caused by a wrong order point, keeps repeating the problem. Not accounting for in-transit quantities causes duplicate transfers. Finally, avoid moving slow items just to make a location look cleaner, since you may simply move the dead stock rather than reduce it.
Frequently Asked Questions
How do I choose target days of supply?
Base it on the item's lead time, review cycle and variability. A reasonable starting point is the replenishment cycle plus a safety margin. Fast items might target a few weeks, slow items a month or more, depending on cost to hold.
Why keep a minimum at the source?
Without it, a transfer could strip the source branch and create a stockout there. The minimum should cover the source's own lead time demand and any allocated or reserved quantity.
Does this create transfer orders?
No. It is a planning worksheet that exports to Excel. You still create the transfer orders in your ERP, which will handle in-transit status, costing and receiving.
Should I include in-transit stock?
Yes. Add in-transit quantity to the destination on-hand, or you will plan duplicate transfers. Likewise, subtract allocated or reserved quantities from the source on-hand.
If you want transfer suggestions generated daily from live inventory by location, we can build a read-only recommendation view over your ERP data for your inventory team to approve.
Related Tools
Prophet 21 Inventory Replenishment Calculator
Work out order point, safety stock, EOQ and a max level for a distribution item, then compare against what your P21 replenishment parameters say.
Supply Chain & InventoryDistribution ERP Fill Rate Calculator
Calculate line, unit and complete-order fill rate from your ERP counts and see the revenue and gross margin put at risk by unfilled lines.
EpicorEclipse ERP Cycle Count Procedure Template
A fill-in cycle count procedure for a distribution branch: scope, ABC frequencies, tolerances, recount and adjustment approval, ready to print or share.
Supply Chain & InventoryDistributor Rebate Tracking Worksheet
Track vendor rebate programs by period: purchases, rebate rate, amount earned, claimed and received, and what is still outstanding.
More Supply Chain & Inventory tools
See all- Distribution KPI Formula Reference
- Distributor Pricing and Rebate Terms Glossary
- Distributor EDI X12 856 ASN Skeleton Generator
- Distributor ABC Inventory Classification Worksheet
- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distributor Slow-Moving Inventory Review Worksheet
- Distributor Vendor Performance Scorecard Worksheet
- Distributor GMROI Calculator
- Distributor Backorder Cost and Fill Rate Calculator