Charting inventory by expiration date with projected levels based on sales

[zohebnoorani]

I am going to do my best to explain the situation that I have and that I am trying to work through and I am not sure if it is possible, but I am open to suggestions.

I am currently getting a weekly report from our logistics team about our product that we have in stock and which of the 2 warehouses we have it stored. The information tells me the expiration date and quantity expiring on that date.

What I would like to do is graph the quantity of product that is expiring on each date and show based on our current sales rate how our inventory levels look to see when we might have get rid of expired product.

Here is a sample of the date after I used a pivot table

Expiration Date	Quantity
2023-10-15	2880​
2023-10-17	2160​
2023-10-19	23040​
2023-10-20	24082​
2023-10-21	26640​

 

[sijpie]

will this help?

jun23.xlsm
	B	C	D	E
8			Daily sales	8500
9
10	Expiration Date	Quantity	Sales	Expired
11	15/10/2023	2880	8500	0
12	17/10/2023	2160	17000	0
13	19/10/2023	23040	17000	0
14	20/10/2023	24082	8500	1162
15	21/10/2023	26640	8500	19302
Sheet4

Cell Formulas
Range	Formula
D11	D11	=$E$8
E11:E15	E11	=MAX(SUM($C$11:C11)-SUM($D$11:D11),0)
D12:D15	D12	=$E$8*(B12-B11)