Track inventory movement in multiple warehouse locations

Ashley356

Hi,

We would like to track inventory movement between multiple warehouse locations, there is no need to track stock value/sales, just need to track inventory movement between locations and the inventory balance

Warehouse: Warehouse A, Warehouse B

Item: Item A, Item B

Current Stock:

• - Warehouse A, Item A: 15
• - Warehouse A, Item B: 10
• - Warehouse B, Item A: 0
• - Warehouse B, Item B: 0

User Input: Transferring from Warehouse A to Warehouse B: 5 Item A and 10 item B

• Result:
• - Warehouse A, Item A: 15 - 5 = 10
• - Warehouse A, Item B: 10 - 10 = 0
• - Warehouse B, Item A: 0 + 5 = 5
• - Warehouse B, Item B: 0 + 10 = 10

-Thers is also

• - New Purchase -> item will be added to single warehouse only
• - Inventory Disposal -> item will be deducted from single warehouse only

End result needed

• - History data of inventory movement
• - History data of New Purchase and Disposal
• - Stock balance of each item in each warehouse

Is there a simple way to do this? thank you

Devin Lee

Hey@Ashley356

One way you can do it is by using two sheets and using the automation workflow to move rows which will serve as a log while the other sheet uses the log to calculate the totals.

Make a sheet with these columns and then create a workflow that looks like this

Now in the main sheet write the formula into the Wearhouse Quantity to add up the numbers from Move, Purchase, and Disposal. For your example you will need to input 15 and 10 as a Purchase and then input -5, -10, 5, 10 as a Move.

The formula will need to be replaced with references but if it was on the same sheet it would look like this.

=SUMIFS([Move Quantity]:[Move Quantity], Item:Item, Item@row, Wearhouse:Wearhouse, Wearhouse@row) + SUMIFS([Purchase Quantity]:[Purchase Quantity], Item:Item, Item@row, Wearhouse:Wearhouse, Wearhouse@row) + SUMIFS([Disposal Quantity]:[Disposal Quantity], Item:Item, Item@row, Wearhouse:Wearhouse, Wearhouse@row)

My formula ended up looking like this in the end

=SUMIFS({Community Help 2 Range 2}, {Community Help 2 Range 3}, Item@row, {Community Help 2 Range 1}, Wearhouse@row) + SUMIFS({Community Help 2 Range 4}, {Community Help 2 Range 3}, Item@row, {Community Help 2 Range 1}, Wearhouse@row) + SUMIFS({Community Help 2 Range 5}, {Community Help 2 Range 3}, Item@row, {Community Help 2 Range 1}, Wearhouse@row)

To get this to work you will need to input the numbers into the right column and than hit save. It will take a few seconds but it should copy the row and clear the numbers which will also update the Wearhouse Quantity. There might be a fancier way to do the move so you only need one input but this is the simplest way I could think of.