Update Inventory Status - Need Recommendations

[Marky31mark]

Hello, I am wondering if someone could help me with some suggestions on inventory sheet that I have. Currently, this sheet is distributed to our customers and lets them know if a product is In stock/Out of Stock/Low Stock or Very Low stock. Also, this sheet provides a Available date based on when we expect new product to be received and available.

I have to update this sheet everyday based on our daily inventory. I am looking for a simple suggestions on how I can update this sheet everyday. I am new with my company and this process is very time consuming part of my day.

I am looking for a way to update this sheet based on previous 12 month sales history and current product availability. Because every item sells different, I cant simply say anything under 100 is low stock, because we may only sell 85 a year, so it will have to be item specific. Also, some items sell faster than others, so we may sell out a few times a year. Example the 215088-13SG is one of our best selling items, so I need to make sure when I have this marked as Low Stock or Out of Stock, we have enough time to make sure we order more while still selling our inventory.

Also, we typically try and save a few items for samples etc. (Maybe 10 or 15), so we are not completely out if we need to bring one to a trade show.


Sample Data:
[TABLE="class: cms_table, width: 1595"]
<tbody>[TR]
[TD="colspan: 5"]Most recent updates are highlighted in yellow - 02/06/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Home & Garden Inventory Updates[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item (Column A)[/TD]
[TD]Description (Column (B)[/TD]
[TD]UPC Code (Column C)[/TD]
[TD]Status (Column D)[/TD]
[TD]Available Date (Column E)[/TD]
[TD]12 Months of sales (02/01/2016 to 02/01/2017) (Column F)[/TD]
[TD]Current Available (02/07/2017) (Column G)[/TD]
[/TR]
[TR]
[TD]30421R012[/TD]
[TD]Glass Solar Birdbath[/TD]
[TD]123456789101[/TD]
[TD]In Stock[/TD]
[TD][/TD]
[TD]581[/TD]
[TD]576[/TD]
[/TR]
[TR]
[TD]40326R014[/TD]
[TD]Solar Fountain: Boy & Girl Reading on Bench[/TD]
[TD]123456789102[/TD]
[TD]Out of Stock[/TD]
[TD]2/10/2017[/TD]
[TD]946[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]77336R015[/TD]
[TD]Umbrella Series Solar Fountain: Boy & Girl Piggyback[/TD]
[TD]123456789103[/TD]
[TD]In Stock[/TD]
[TD][/TD]
[TD]132[/TD]
[TD]628[/TD]
[/TR]
[TR]
[TD]50622R018[/TD]
[TD]Country Gardens Solar Birdbath[/TD]
[TD]123456789104[/TD]
[TD]Out of Stock[/TD]
[TD]2/10/2017[/TD]
[TD]977[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]21833R022[/TD]
[TD]Acadia Solar Birdbath[/TD]
[TD]123456789105[/TD]
[TD]In Stock[/TD]
[TD][/TD]
[TD]951[/TD]
[TD]405[/TD]
[/TR]
[TR]
[TD]227115-BM[/TD]
[TD]Birdbath - Blue[/TD]
[TD]123456789106[/TD]
[TD]Out of Stock[/TD]
[TD]3/20/2017[/TD]
[TD]229[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]777104-OR[/TD]
[TD]Birdbath - Orange[/TD]
[TD]123456789107[/TD]
[TD]In Stock[/TD]
[TD][/TD]
[TD]122[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]70757R013[/TD]
[TD]Mosaic Bath with Metal Stand[/TD]
[TD]123456789108[/TD]
[TD]Low Stock[/TD]
[TD][/TD]
[TD]458[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]22372R014[/TD]
[TD]Solar Koi Fountain[/TD]
[TD]123456789109[/TD]
[TD]Out of Stock[/TD]
[TD]3/20/2017[/TD]
[TD]1351[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]21410R01[/TD]
[TD]Sunny Jam 1200[/TD]
[TD]123456789110[/TD]
[TD]In Stock[/TD]
[TD][/TD]
[TD]2055[/TD]
[TD]957[/TD]
[/TR]
[TR]
[TD]215088-13RL[/TD]
[TD]Garden Torch - Red Lava[/TD]
[TD]123456789111[/TD]
[TD]Low Stock[/TD]
[TD]To be discontinued[/TD]
[TD]55[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]215088-13SG[/TD]
[TD]Apollo Ceramic Garden Torch - Sierra Garden[/TD]
[TD]123456789112[/TD]
[TD]In Stock[/TD]
[TD]To be discontinued[/TD]
[TD]8338[/TD]
[TD]1133[/TD]
[/TR]
[TR]
[TD]21801R01[/TD]
[TD]Solar Oxygen Max[/TD]
[TD]123456789113[/TD]
[TD]In Stock[/TD]
[TD][/TD]
[TD]86[/TD]
[TD]687[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DCE6F1"]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]1
[/TD]
[TD]Item
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Status
[/TD]
[TD][/TD]
[TD]12MO sales
[/TD]
[TD]Available
[/TD]
[TD]Low stock at
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]2
[/TD]
[TD]30421R012
[/TD]
[TD][/TD]
[TD][/TD]
[TD]In Stock
[/TD]
[TD][/TD]
[TD]581
[/TD]
[TD]576
[/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]3
[/TD]
[TD]40326R014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Out of Stock
[/TD]
[TD][/TD]
[TD]946
[/TD]
[TD]2
[/TD]
[TD]85
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]4
[/TD]
[TD]77336R015
[/TD]
[TD][/TD]
[TD][/TD]
[TD]In Stock
[/TD]
[TD][/TD]
[TD]132
[/TD]
[TD]628
[/TD]
[TD]627
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]5
[/TD]
[TD]50622R018
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Low stock
[/TD]
[TD][/TD]
[TD]977
[/TD]
[TD]24
[/TD]
[TD]85
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

You could add a column to list where you want each product to go to low stock.
In D2. Change the 15 to the minimum you want to keep on hand. When the number available goes below the number in the low stock it changes to Low Stock

=IF(G2<=[COLOR=#ff0000]15[/COLOR],"Out of Stock",IF(G2>H2,"In Stock","Low Stock"))

 

[Marky31mark]

Thank you for the great suggestion! Very simple and easy to set up.

Again, thanks.

This will make things much easier for me.

 

[Marky31mark]

How can I do this with 3 different levels?

In Stock = 100+
Low Stock = 100
Very Low Stock = 49
Out of stock = 15

I know this is multiple nested If functions, but the logic is screwing me up.

Thanks,
Mark

 

[Scott T]

If this is for all products then

=IF(G2<=15,"Out of Stock",IF(G2>100,"In Stock",IF(G2>49,"Low Stock","Very Low Stock")))

 

[AkaTrouble]

perfect solution from Scott T

just a slightly alternate one to make it easier to see the logic ( I Think)

=IF(A1>100,"In Stock",IF(A1>49,"Low Stock",IF(A1>15,"Very Low Stock","Out Of Stock")))

there is several ways to arrive at desired answer using nested IF options

 

[Marky31mark]

Thank you both very much!