Help with Inventory Sheet

Hi to all members,

I need some help with my inventory sheet I have regarding stock that I have in my warehouse. I have for example the following information :

Cadbury Wholenut Chocolate 30 x 80 grams x 3 , meaning that I have 3 boxes of 30 Cadbury chocolate bars in each box making a total of 90 units.

I have my main warehouse and a store that I would like to keep track of stock levels.

For example , i sell 2 chocolate bars from the shop. Every time I sell an item I will enter the amounts sold, and this should deduct from stock levels.


What I would like to know is whether or not there is a way for me to know that I have 2 closed boxes {which will mean 60 units} for example in my warehouse, and at the shop I have a box that has the remaining difference which means 28 units left. So the output will more or less look like :

Cadbury Whole Nut Chocolate 80gr Initial Stock 3 box = 90 Units Item Sold @ Shop 2 units WAREHOUSE : 2 Boxes 60 Units Shop 1 Box 28 Units

Thank you
Sotiris

Yes. You could create a sold items table with columns: location, productID, and units sold . So if you sold 2 boxes of 30 cadburies each and pulled it from the warehouse then record would look like: warehouse, cadburdID#, 60 units. This would decrement from your inventory so you could filter your this product from your starting inventory table and use a totatal inventory table to keep track of the difference. I'd also have a table for the productID that would detail the product-- units per pack etc. Then you'd be able to decrement from your inventory and use the mod() formula to track "leftovers". So if you sold 2 full boxes of 30 units a piece you would see a total of 60 sold mod(60,30)=0. If you sold 1 full container and then 28 from another then you would have sold, mod(58,30)= 28 individual items and have 30-28= 2 leftover in stock. You should take y looking for excel templates online for inventory spreadsheets to find other (and probably better) solutions.

phattails said:

Yes. You could create a sold items table with columns: location, productID, and units sold . So if you sold 2 boxes of 30 cadburies each and pulled it from the warehouse then record would look like: warehouse, cadburdID#, 60 units. This would decrement from your inventory so you could filter your this product from your starting inventory table and use a totatal inventory table to keep track of the difference. I'd also have a table for the productID that would detail the product-- units per pack etc. Then you'd be able to decrement from your inventory and use the mod() formula to track "leftovers". So if you sold 2 full boxes of 30 units a piece you would see a total of 60 sold mod(60,30)=0. If you sold 1 full container and then 28 from another then you would have sold, mod(58,30)= 28 individual items and have 30-28= 2 leftover in stock. You should take y looking for excel templates online for inventory spreadsheets to find other (and probably better) solutions.

Click to expand...

Thank you ... will look into this