Tool to calculate stock movement

[Williamc92]

So I have a table with multiple columns, each representing a store. The rows are individual products while the value is the discrepancy of stock ie we are missing 5 stock (-5). Now I want some sort of tool that can calculate that if I’m missing 5 in one store, I can move 5 from another branch to fill that gap. Any ideas in the easiest way to generate this? This

 

[Col Delane]

Hi William and welcome to the Forum

Please describe what you mean by a "tool that can calculate that if I’m missing 5 in one store". Are you looking for a relatively simple "technique" or a more expansive spreadsheet application that will take your source data and calculate/report stuff and provide a means to record your corrective stock transfers?

My simple solution is this:
1. Change the layout of your Product (down page) x Store (across page) table into a database/list like table that has just four columns, viz:

Product, Store, Qty, Source
1234, Paris, -5, Stocktake
1234, NYC, 3, Stocktake
1234, Perth, 4, Stocktake
​

Status options could be "Stocktake" and "Adjustment" (or abbreviations thereof like ST & Adj)
If you have lots of products the table will be quite wide, but it is better/easier to look at multiple numbers for the same category (i.e. Product) in vertical alignment rather than horizontal.

2. Then create a Pivot Table (probably best on another sheet) that summarises the discrepancy data.

• Row label1 = Store, Row label2 = Source, Column label = Product, Values = Sum of Qty
• Change the Field Settings for Store (click the drop down for Store in the Row Labels box of the Pivot Table Field List dialogue) to uncheck the box that says "Display subtotals at the top of each group" found in the Layout & Print tab.

Now you can see the results for each product by store, and work out which stock transfers ("adjustments") are required to eliminate the shortfalls in those stores with deficits.

• Enter each of these transfers as new rows in the data source table, tagging them as "Adjustments" so that you can separate these from the original position if necessary.
• Refresh the Pivot Table to see the adjusted results.
• You can use the filter under the dropdown for Store when you mouse-over that field in the "Choose fields to add to report" box at the top of the Pivot Table Field List dialogue to dynamically restrict the Pivot Table display to Stocktake only, Adjustments only, or no filter for All, as required.

I hope this helps.