Timeline Conditions

[Kamran01]

Dear Experts,

I need your help regarding timeline, i have below data from style to last date and i want to calculate day wise quantities according to below criteria.
1.5 Change over days are constant and days required are calculated =4000/800 >> 5 +C/O days >> 6.5 days.

1st Day Plan: 25% of P/D target2nd Day Plan: 50% of P/D target
3rd Day Plan: 75% of P/D target
rest all days: 100% of P/D target
Last day:Remaining Balance Percentage from plan qty

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Style #[/TD]
[TD]Plan Qty[/TD]
[TD]P/D Tgt.[/TD]
[TD]C/O Days[/TD]
[TD]Days Req.[/TD]
[TD]First Plan[/TD]
[TD]Last Date[/TD]
[TD]19/4[/TD]
[TD]20/4[/TD]
[TD]22/4[/TD]
[TD]23/4[/TD]
[TD]24/4[/TD]
[TD]25/4[/TD]
[TD]26/4[/TD]
[TD]27/4[/TD]
[TD]29/4[/TD]
[TD]30/4[/TD]
[/TR]
[TR]
[TD]AVA-0652NM[/TD]
[TD]4000[/TD]
[TD]800[/TD]
[TD]1.5[/TD]
[TD]6.50[/TD]
[TD]19/4[/TD]
[TD]26/4[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD]600[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AVA3501F1[/TD]
[TD]1200[/TD]
[TD]900[/TD]
[TD]1.5[/TD]
[TD]2.8[/TD]
[TD]27/4[/TD]
[TD]30/4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]225[/TD]
[TD]450[/TD]
[TD]525[/TD]
[/TR]
</tbody>[/TABLE]

 

[proficient]

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][th]

P​

[/th][th]

Q​

[/th][th]

R​

[/th][th]

S​

[/th][th]

T​

[/th][th]

U​

[/th][th]

V​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#BFBFBF]

Style #​

[/td][td=bgcolor:#BFBFBF]

Plan Qty​

[/td][td=bgcolor:#BFBFBF]

P/D Tgt.​

[/td][td=bgcolor:#BFBFBF]

C/O Days​

[/td][td=bgcolor:#BFBFBF]

Days Req.​

[/td][td=bgcolor:#BFBFBF]

Repeat / New​

[/td][td=bgcolor:#BFBFBF]

First Plan​

[/td][td=bgcolor:#BFBFBF]

Last Date​

[/td][td=bgcolor:#BFBFBF]

25-Jul​

[/td][td=bgcolor:#BFBFBF]

26-Jul​

[/td][td=bgcolor:#BFBFBF]

27-Jul​

[/td][td=bgcolor:#BFBFBF]

29-Jul​

[/td][td=bgcolor:#BFBFBF]

30-Jul​

[/td][td=bgcolor:#BFBFBF]

31-Jul​

[/td][td=bgcolor:#BFBFBF]

1-Aug​

[/td][td=bgcolor:#BFBFBF]

2-Aug​

[/td][td=bgcolor:#BFBFBF]

3-Aug​

[/td][td=bgcolor:#BFBFBF]

5-Aug​

[/td][td=bgcolor:#BFBFBF]

6-Aug​

[/td][td=bgcolor:#BFBFBF]

7-Aug​

[/td][td=bgcolor:#BFBFBF]

TOTAL​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]AVA-0652NM[/td][td]

5000​

[/td][td]

1200​

[/td][td]

1.5​

[/td][td]

5​

[/td][td]New[/td][td]

25-Jul​

[/td][td]

31-Jul​

[/td][td]

300​

[/td][td]

600​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

500​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

5000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]AVA3501F1[/td][td]

6000​

[/td][td]

1200​

[/td][td]

1.5​

[/td][td]

5​

[/td][td]Repeat[/td][td]

1-Aug​

[/td][td]

7-Aug​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

700​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

500​

[/td][td]

​

[/td][td]

6000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]TOTAL[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

300​

[/td][td]

600​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

1200​

[/td][td]

500​

[/td][td]

0​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Scott T]

How exactly to you come up with these numbers?
you have a start date of 8/1 but it starts on 7/31?

 

[proficient]

Good question, since formula depends on start date but we need to make a function that will look into repeat/new cell and based on that will start first output... May be for that we need to design a function that if the previous style is same as current and the last output of that style is under capacity (not at 100%) so to complete the capacity it will start from the same date.

 

[Scott T]

What happens if it is not under capacity? If row 5 did not start on Jul 31 but on Aug 1(the listed start date) then using the 25%,50%,100% you would be 300 short on Aug 7 the planed last date.

 

[proficient]

Let me tell you the concept behind it, since we are working in garment industry so if the style is same no changeover will take place.

If the style is same, it will cover the capacity on the same date, for instance, If last style ended on 26-July with 700 and its actual capacity was 1200 so the current style output will be starting from the same date with 500 units in order to cover the capacity. for this you need to put a condition in last output date that if current style is repeat (you can check this in column G that either style is new or repeat) and the last output of previous style is under capacity like despite having 1200 unit capacity we produced only 700 units on that day so it will start from the same day with 500 units. there are two criteria...

 

[proficient]

Bump!!!!