Part Expiration Forecast Question

[CatFather]

Hello there, I was hoping to get some tips or advice on highlighting parts that are about to expire in a certain time frame based on average time to sell (in this case based on the last 3 months).

Example:
Part number NA-04220-S1A has $23K worth of parts that are going to expire in the next 6 months. Based on the 3 month sales average, it will take 7.5 months to sell through all of the parts so I would need to highlight that it won't sell in time.

Part number NA-04020-X1ASP based on the average will sell through in 3.4 months so I would need to highlight that it will sell in time.

Thanks in advance and please let me know if you need me to further clarify!

Capture.PNG

[vba_php]

well, in a case like this, here is my recommendation (others might say differently):

you need a part number column, a column for the money worth of parts, column for the total time to expiration, one possibly for the sales average (you have not specified the source of that number), and a simple IF() function that compares that average's result of 7.5 months to 6 months. someone else asked a question similar to this earlier today or yesterday. I don't believe you can highlight cells using formulas and functions in the interface. but I may be wrong. you need code. and the line to color the shade is:

Please Login or Register  to view this content.

where the #'s and 0's inside the [] enclosures are representative of the HTML color code grid numbers.

[lordfa9]

what's the level of precision you are going for?

If its just a blanket statement like the one in your OP (My stock of X qty needs 7.5 weeks to sell through and it will expire in 6) you can just do a SUM of the stock that is say going to expire in Aug to Nov 2020 and then see if the sum of your sales in May to Jul 2020 meets or exceeds that

If you want to know precisely the lot/batch of stock that is in danger you have to track by week not month