Identifying Declining Sales by Sku

[JackChang]

All-

I was looking for some suggestions on how I might be able to identify declining sales by sku. I'm trying to see which sku's we should discontinue selling and not reorder. (i.e. they're not selling)

On the file attached, I have monthly data by sku, but rather than going through each row, is there some function/feature in Excel to make it easier? I used a sparkline, so I can put an 'x' next to the ones that are declining, but it's still a manual task especially when I have thousands to review. And then it changes when I add in new monthly data and the process starts all over again.

Anyone have any ideas on how to get to the data I need, without the noise?

Thank you,

[Dave]

Hi JackChang. You would need to specify exactly what the criteria is for identifying when a product is declining (year over year, month by month, season by season, etc). I'm guessing some of the SKU's are seasonal sales so there will be monthly variations. Also, your data has a negative amount for sales? HTH. Dave

edit: forgot to mention that your inventory management practice is likely important (ie. LIFO vs FIFO)

[p45cal]

To get the gradient of the best straight line through the 15 months you could use a formula in row 3 such as:
=LINEST($B3:$O3)
and copy down, but bear in mind that gradient alone isn't a good yardstick for keeping or discontinuing an SKU; the following 2 SKUs have the same negative gradient but you'd probably want to drop SKU1, but keep SKU2.
26186
ps. I assume that blanks in your data mean zero, but to prevent #VALUE! errors you could select the whole data range, press F5 on the keyboard, click Special…, then choose Blanks, click OK, then enter 0 on the keyboard, hold down the Ctrl key and press Enter. This will fill those blanks with a zero value.