Calculation of the lead times

[Spoorthi]

Please help with a formula that can automate the calculation of the lead times.

Please refer to the table shared below -
I have the order flow pattern captured at every 2 hours frequency and the capacity of the client to handle these orders. On any day, pendency orders have to be first allocated post which the current day's orders will be taken up (i.e. 10% of the pendency has to be cleared first and then only 4% can be allocated).

Need help in generating a formula for each of the current order flows to see when will they be allocated (i.e. when will 4% orders which are received between 10 AM - 12 PM, 10% orders which are received between 12 PM - 2 PM be allocated and so on.)

Please help.

Shop Beginning Time	Shop Close Time	Current Order Flow	Capacity in the shop	Pendency from prev. day	Allotment
	Pendency from previous day			10%​
10:00 AM​	12:00 PM​	4%​	0%​	14%​
12:00 PM​	2:00 PM​	10%​	0%​	24%​
2:00 PM​	4:00 PM​	15%​	20%​	19%​
4:00 PM​	6:00 PM​	20%​	30%​	9%​
6:00 PM​	8:00 PM​	35%​	30%​	14%​
8:00 PM​	10:00 PM​	6%​	20%​	0%​
		90%​	100%​

 

[pecoflyer]

Hi
please post a sample sheet with some data and expected results so we can work on it. Thx

 

[Spoorthi]

Hi,
So it's basically to work as -
As capacity till 2 PM is 0%, prev. day's pendency + current orders received till 2 PM will keep piling up. So by 2 PM - we have 24% of the orders pending.

So at 2 PM, when we start assigning (as the capacity is 20%), first 10% (from prev. day's pendency) should get allotted first followed by the other orders if the capacity can accommodate.

So, the capacity of 20% from 2 PM - 4 PM can accommodate 10% (pendency) + 4% (10-12PM) + 6% (12 - 2 PM). the next 30% capacity of 4 - 6 PM should accommodate 4% (12-2 PM) + 15% (2-4 PM) + 11% (4 - 6 PM)

So, 4% orders which have been received between 10 AM - 12 PM will be assigned after 4 hours and so on. Can you please help automate the allotment in the last column.

Thanks.

Shop Beginning Time	Shop Close Time	Current Order Flow	Capacity in the shop	Pendency from prev. day	Allotment
	Pendency from previous day			10%
10:00 AM	12:00 PM	4%	0%	14%	4
12:00 PM	2:00 PM	10%	0%	24%	2
2:00 PM	4:00 PM	15%	20%	19%	2
4:00 PM	6:00 PM	20%	30%	9%
6:00 PM	8:00 PM	35%	30%	14%
8:00 PM	10:00 PM	6%	20%	0%
		90%	100%