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:

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

<tbody>
</tbody>

 

[Scott T]

	A	B	C	D	E	F	G	H
1	Item			Status		12MO sales	Available	Low stock at
2	30421R012			In Stock		581	576	70
3	40326R014			Out of Stock		946	2	85
4	77336R015			In Stock		132	628	627
5	50622R018			Low stock		977	24	85

<tbody>
</tbody>

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!