How to Build a Store Stock Allocation Tool in Google Sheets

You can use Google Sheets to suggest how much stock to send from a warehouse to each store. Calculate each store’s stock requirement, compare the combined requirement with the warehouse stock available, and distribute any shortage using an agreed rule. Keep the suggested quantities separate from the quantities a planner approves.

Last updated: September 2026

This guide covers stock allocation between stores, not choosing which location fulfils a customer order. Shopify already supports order routing between fulfilment locations. If that is your problem, start with those routing settings.

Keep the data preparation visible

In our March 2026 merchant interview, an ecommerce director at a skincare brand described the preparation behind replenishment: “I still have to do an export of my sales from Shopify, do an export of the stock the inventory at the 3PL.” The allocation formula starts after that work. Record when each export was taken and map product codes before comparing sales with stock.

Start with two input tables

Use a Warehouse tab with one row per SKU: the product code, warehouse units available to distribute, and the date or source of that stock snapshot. Available units should exclude quantities already committed elsewhere. Do not count an unreceived purchase order as stock you can dispatch now.

Use a Stores tab with one row per SKU and store:

Column	What to enter
A: SKU	The same product code used in Warehouse
B: Store	A unique store name or code
C: Units sold	Sales during the observation period
D: Observation days	Number of days represented by those sales
E: Target cover days	How many days of demand you want to cover
F: Usable stock	Store stock available for sale, excluding reservations, damage and quarantine
G: Confirmed incoming	Receipts due within the coverage period that are not already included in usable stock

Use the same unit of measure throughout. A case of twelve is not one unit if sales and stock are recorded as individual items. This starter model allocates whole individual units; case-pack constraints require an additional allocation rule.

Calculate the requirement for each store

In H2, calculate average daily sales:

=C2/D2

Observation days must be greater than zero. In I2, calculate the whole-unit stock requirement:

=MAX(0,ROUNDUP(H2*E2-F2-G2,0))

For example, a store selling 28 units over 28 days averages one unit per day. With a 14-day coverage target, ten usable units and two confirmed incoming units, it needs two additional units.

This is a simple coverage calculation, not a demand forecast. Sales during a stockout understate demand. Promotions, seasonality and new products may need a planner’s adjustment before allocation. Choose the coverage period to reflect the time until the next replenishment opportunity.

Do not put the transfer you are calculating into “confirmed incoming.” Doing so would subtract the proposed allocation before you have approved it. Count an existing transfer only once.

Allocate scarce warehouse stock

If the warehouse can cover every store’s requirement for a SKU, suggest each store’s full requirement. If not, a simple starting policy is proportional allocation:

Raw allocation = available units × store requirement ÷ total requirement

Suppose three stores need 40, 30 and 30 units. The warehouse has 50 available. Their proportional allocations are 20, 15 and 15 units.

Proportional allocation does not automatically prioritise a flagship store, launch commitment or minimum display quantity. Those are separate business rules. Decide them explicitly rather than silently changing the formula for a few rows.

Round without creating extra stock

Rounding every store’s share independently can allocate more stock than you have. Use the largest-remainder method for whole units:

1. Round each raw allocation down.
2. Subtract the allocated total from the distributable stock.
3. Give the remaining units to stores with the largest fractional remainders, one unit at a time.
4. Use a consistent tie-break rule. The formulas below use row order.

For three stores each needing one unit, with only two available, the raw shares are two-thirds each. Rounding every share up would allocate three units. Largest-remainder allocation produces 1, 1 and 0, with the tie decided by row order.

Never allocate above a store’s calculated requirement. When the total requirement is zero, the suggested allocation should be zero rather than a division error.

Review before creating transfers

Keep three output columns: Suggested units, Approved override and Final units. A blank override uses the suggestion. An override should be a non-negative whole number within the store’s requirement. If a different requirement is justified, review the inputs rather than hiding the change in an unexplained allocation.

For each SKU, check that the final total is no greater than the available warehouse stock. A planner increasing one store’s allocation may need to reduce another’s. Flag duplicate SKU/store rows, duplicate warehouse SKUs, missing products, invalid quantities and zero observation days.

A Shopify apparel founder described fragmented planning in our March 2026 interview: “I probably have like 10 things we're working off of”. That workflow involved production lists and fabric, rather than this warehouse-to-store model. The relevant lesson is to agree which input snapshot and approved plan the team will use; another spreadsheet does not by itself remove the reconciliation work.

Save a dated copy of the approved allocation before creating transfers in your inventory system. The spreadsheet does not reserve warehouse stock or execute movements. If two people allocate the same stock independently, both can produce a valid-looking plan that overcommits the warehouse when combined.

Add the allocation and checking formulas

Name the tabs exactly Warehouse and Stores. On Warehouse, use A for SKU, B for available units, C for snapshot date/source and D for Input check. Enter zero for known zero quantities; a missing input is not the same as zero.

Put this formula in Warehouse D2 and copy it down through D101:

=IF(COUNTA(A2:C2)=0,"",IFERROR(IF(AND(A2<>"",ISNUMBER(B2),B2>=0,B2=INT(B2),COUNTIF($A$2:$A$101,A2)=1),"OK","FIX INPUT"),"FIX INPUT"))

In Stores, keep the A–G input columns above. Add these columns in the exact order shown. Paste each formula into its row-2 cell, then fill it down through row 201. R is a manual input: leave it blank to accept the suggestion. H and I below replace the simple explanatory formulas earlier in this guide with versions that check the inputs first.

H: Daily sales

=IF(T2<>"OK","",C2/D2)

I: Store need

=IF(T2<>"OK","",MAX(0,ROUNDUP(H2*E2-F2-G2,0)))

J: Warehouse available

=IF(T2<>"OK","",SUMIF(Warehouse!$A$2:$A$101,A2,Warehouse!$B$2:$B$101))

K: Total SKU need

=IF(T2<>"OK","",SUMIF($A$2:$A$201,A2,$I$2:$I$201))

L: Raw share

=IF(T2<>"OK","",IF(K2=0,0,ROUND(MIN(J2,K2)*I2/K2,12)))

M: Base units

=IF(T2<>"OK","",INT(L2))

N: Remainder

=IF(T2<>"OK","",ROUND(L2-M2,12))

O: Tie rank

=IF(T2<>"OK","",COUNTIFS($A$2:$A$201,A2,$N$2:$N$201,">"&N2)+COUNTIFS($A$2:A2,A2,$N$2:N2,N2))

P: Remaining units

=IF(T2<>"OK","",MIN(J2,K2)-SUMIF($A$2:$A$201,A2,$M$2:$M$201))

Q: Suggested units

=IF(T2<>"OK","",M2+IF(AND(I2>M2,O2<=P2),1,0))

R: Approved override

Optional approved quantity, entered manually as a non-negative whole number. Leave blank to use Q.

S: Final units

=IF(T2<>"OK","",IF(R2="",Q2,IF(ISNUMBER(R2),R2,0)))

T: Input check

=IF(COUNTA(A2:G2,R2)=0,"",IFERROR(IF(AND(A2<>"",B2<>"",COUNT(C2:G2)=5,MIN(C2:G2)>=0,D2>0,C2=INT(C2),F2=INT(F2),G2=INT(G2),COUNTIFS($A$2:$A$201,A2,$B$2:$B$201,B2)=1,COUNTIFS(Warehouse!$A$2:$A$101,A2,Warehouse!$D$2:$D$101,"OK")=1),"OK","FIX INPUT"),"FIX INPUT"))

U: Plan check

=IF(T2="","",IF(T2<>"OK","BLOCK: input",IF(COUNTIFS($A$2:$A$201,A2,$T$2:$T$201,"FIX*")>0,"BLOCK: SKU inputs",IF(SUMIF($A$2:$A$201,A2,$S$2:$S$201)>J2,"BLOCK: warehouse exceeded",IF(R2="","OK",IFERROR(IF(AND(ISNUMBER(R2),R2>=0,R2=INT(R2),R2<=I2),"OK","BLOCK: approval"),"BLOCK: approval"))))))

Keep the formula ranges together: this setup allows 100 warehouse SKUs and 200 SKU/store rows. Adding rows beyond those limits requires extending every referenced range. Do not sort a single column on its own; sort whole rows. Row order breaks equal-remainder ties.

The formulas use English function names and comma separators. Regional Sheets settings may require different separators. The calculation and input-check logic was tested in LibreOffice against 50 allocation scenarios and eight invalid-input or approval cases; execution in Google Sheets has not been tested. Before using real stock, enter the examples below and confirm their results.

Check the model with fictional inputs

In Warehouse, enter SKU-A with 50 available units and SKU-B with 2. In Stores, enter three SKU-A rows with different store names, sales of 80/60/60 units, 28 observation days, 14 cover days, and zero usable stock and incoming. Suggested quantities must be 20/15/15.

Then enter three SKU-B rows with different store names, sales of 2/2/2, the same observation and cover days, and zero stock and incoming. The result must be 1/1/0 in row order. Set observation days to zero to confirm the input check fails. Override a SKU-A row to exceed the shared warehouse pool and confirm the plan blocks it. Restore valid inputs before proceeding.

Do not dispatch when any Warehouse input check says FIX, any Stores input check says FIX, or any Plan check says BLOCK. An empty table is not an approved plan.

Where Forthcast fits

Forthcast supports Shopify demand forecasting and replenishment planning. Its forecasts and reorder points are store-wide. It shows stock by location and suggests inter-location transfers, but it does not split an inbound purchase order across warehouses or provide a native Google Sheets integration.

This allocation sheet is a separate planning aid. It does not turn Forthcast into an automated allocation engine. If you need demand forecasts and reorder planning alongside your allocation process, explore Forthcast.

Questions about the allocation sheet

Can I use it without Shopify?

Yes. The calculation uses sales, stock and incoming quantities that you enter. It is independent of your commerce platform and does not connect to it automatically.

Does it move stock or send transfer orders?

No. It suggests quantities and checks the proposed plan. A person approves the plan and creates the transfers in the system used to manage stock.

What if I need case packs or store priorities?

Agree those rules before extending the model. Rounding each store to a case pack after allocation can exceed available stock. Minimum displays, store priorities and packs need to be considered together with the shared warehouse limit.

Is allocating stock the same as routing orders?

No. Stock allocation decides where inventory should go. Order routing decides which location should fulfil an existing customer order. Shopify offers native order-routing rules; this sheet addresses the first job.