Trying to get dates to converge

[MartyCollins]

Hi there I have a simple problem but its tricky to solve,
My work have disabled the XL2bb macros so apologies this is the best I cna come up with.

I have 3 parts that we manufacture, they are all in backlog as we ran out of a RM (raw material)
I am due 5000 units of the RM and when it arrives i need to apportion it so that each part I manufacture has a projected stockout date equal to one another (or as close as reasonably possible)

I normally end up doing this manually.. basically adjusting the QTYS in proposed apportionment until the projected stockout dates are equal one another.

If you paste this into A1 of a workbook and use the following formulas it will be easy to follow.

D2 = B2/C2 and copy down
G2=F2/E2 and copy down
H2 = G2/C2
J2 =TODAY()+(H2*7)-(D2*7) and copy down.
F5 =SUM(F2:F4) (which must sum to less than or equal to 5000, as that is all the raw material I am due)
Thanks in advance,
MArty.

	Backlog	Weekly sales forecast	Backlog in Weeks	RM Usage factor	Proposed apportionment	Units this will make	weeks coverage this will give	Current week	Projected Stockout date
PartA	35​	3​	11.7​	10​	1390​	139​	46.33​	36​	04/05/2024​
PartB	125​	9​	13.9​	5​	2185​	437​	48.56​	36​	04/05/2024​
PartC	550​	25​	22.0​	1​	1425​	1425​	57.00​	36​	07/05/2024​
					5000​
				QTY of RM due	5000​

 

[MartyCollins]

Got the XL2bb working again... so basically i would like a formula to somehow adjust the highlighted QTYs in the proposed apportionment column to consume a max of 5000 between them and for the projected stockout dates to converge as close to one another as possible.

BuildProjection.xlsx
	E	F	G	H	I	J	K	L	M
238		Backlog	Weekly sales forecast	Backlog in Weeks	RM Usage factor	Proposed apportionment	Units this will make	weeks coverage this will give	Projected Stockout date
239	PartA	35	3	11.7	10	1390	139	46.33	04/05/2024
240	PartB	125	9	13.9	5	2185	437	48.56	04/05/2024
241	PartC	550	25	22.0	1	1425	1425	57.00	07/05/2024
242						5000
243
244					QTY of RM due	5000
CAL11

Cell Formulas
Range	Formula
K239:K241	K239	=J239/I239
L239:L241	L239	=K239/G239
M239:M241	M239	=TODAY()+(L239*7)-(H239*7)
H239:H241	H239	=F239/G239
J242	J242	=SUM(J239:J241)