Tool to calculate stock movement

Williamc92

New Member
Joined
Feb 18, 2019
Messages
1
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
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.:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,643
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top