Two ideas come to mind. The first one is simple but not easily expandable if you, say, open a second retail location and want to know how many items are in each of the two locations.
First idea:
Use a table like shown below to replace your IN and OUT tables, assuming they are equivalent to Bought and Sold. It would have a column for each kind of movement of the product.
For your Inventory summary table you would insert two new columns. You would use SUMIFS to gather the various sums needed for each SKU
Qty in Warehouse = (In to Warehouse) + (Retail Shop to Warehouse) - (Warehouse to Retail Shop)
Qty in Retail = (Warehouse to Retail Shop) - (Retail Shop to Warehouse) - (Out from Retail Shop)
Second idea:
Columns E and F are pop up menus. The popup in E is all possible sources, F is all possible destinations. They are identical popups other than "purchased" and "sold".
QTY in a particular location would be calculated like this (in English not formulaic):
Qty in Warehouse = (sum of qty when column F = "warehouse") - (sum of qty when column E is warehouse)
I can provide the actual formulas if what I wrote above doesn't make sense.
With the first idea you could put more than one activity per line (bought some and also moved some to Retail on the same day, for example). It also makes it easy to see at a glance when you moved things from one place to another because each is in a separate column. But, like I said above, it is not easy to expand. The second idea is easy to expand, you just add a new location to the pop-ups, but it is hard to see at a glance what went where when.
