Formula Help - Inventory Allocation & Distribution

[ricafonyat]

Dear all,
My goal is to distribute the inventory of items in the months from the start date according to the velocity (From column Jun.24 to column Nov.24).
For example:
If I have 60 items in stock and their sales velocity is 3 and the start sales date is Sep.24, the formula should allocate 20 items in the months from Sep to Nov.
If the start sales date is less than or equal to the item, it can be allocated in the month.

Which excel formula will work in this case?

Base Month= jun/2024

Any help will be appreciated

Item	Price	Stock	Start Date	Velocity	jun/2024	jul/2024	ago/2024	sep/2024	oct/2024	nov/24
Pen	2,25	10	jan/2024	1	10
Pencil	1,80	20	mar/2024	2	10	10
Eraser	0,85	30	jun/2024	2	15	15
Notebook	4,30	60	sep/2024	3				20	20	20

 

[iliace]

Are the start dates and month headers actual dates? If so, it's something like (assuming sep/2024 is column I and Notebook is row 5)

I5:  =(I$1>EOMONTH($D5,-1))*(I$1<=EOMONTH($D5, $E5-1)) * ($C5/$E5)

 

[ricafonyat]

Are the start dates and month headers actual dates? If so, it's something like (assuming sep/2024 is column I and Notebook is row 5)

I5:  =(I$1>EOMONTH($D5,-1))*(I$1<=EOMONTH($D5, $E5-1)) * ($C5/$E5)

Yes, Start Dates and Month Headers are actual dates.

 

[Cubist]

A minor adjustment is needed to the formula above to account for the start dates before June 2024.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Item	Price	Stock	Start Date	Velocity	6/1/2024	7/1/2024	8/1/2024	9/1/2024	10/1/2024	11/24/2024
2	Pen	2,25	10	1/1/2024	1	10	0	0	0	0	0
3	Pencil	1,80	20	3/1/2024	2	10	10	0	0	0	0
4	Eraser	0,85	30	6/1/2024	2	15	15	0	0	0	0
5	Notebook	4,30	60	9/1/2024	3	0	0	0	20	20	20
Sheet3

Cell Formulas
Range	Formula
F2:K5	F2	=(F$1>EOMONTH(MAX($D2,$F$1),-1))*(F$1<=EOMONTH(MAX($D2,$F$1), $E2-1)) * ($C2/$E2)

 

[ricafonyat]

Yes, Start Dates and Month Headers are actual dates.

Are the start dates and month headers actual dates? If so, it's something like (assuming sep/2024 is column I and Notebook is row 5)

I5:  =(I$1>EOMONTH($D5,-1))*(I$1<=EOMONTH($D5, $E5-1)) * ($C5/$E5)

Here is the print of spreadsheet.
The provided formula is partially working.
In cell F2 it should be showing the result of 10, just as cells F3 and G3 should show the result of 12.

 

[iliace]

My solution did not take the base month into consideration, but I think Cubist's suggestion addresses this.