Weeks of cover including negatives

[brazill23]

Hi All,
Im trying to resolve below issue form a while. Currently im trying to build forecast vs stock calculations to show me weeks of cover. Im not looking for average but to calculate in Wk2 i have stock enough for ... weeks with the forecast value as .... Also include negative stock (to be use in my later calculations). Can someone help me to resolve this as I have spend fare too much time


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]WK4[/TD]
[TD]WK5[/TD]
[TD]Wk6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="width: 103"]Forecast[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]230[/TD]
[TD]52[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="width: 103"]Inventory[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]200[/TD]
[TD]190[/TD]
[TD]-40[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="width: 103"]Plannin delivery[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, welcome to the board.

What exactly do you want the results to be ?
And how do you derive those results ?
Also, what do you mean by negative stock ?

 

[brazill23]

Hi Gerald thank you for your respond.
Inventory : actual value - same week forecast.

[TABLE="width: 500"]
<tbody>[TR]
[TD]weeks[/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[TD]Wk5[/TD]
[TD]Wk6[/TD]
[/TR]
[TR]
[TD]forecast[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]200[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]opening[/TD]
[TD]200[/TD]
[TD]190[/TD]
[TD]170[/TD]
[TD]160[/TD]
[TD]-40[/TD]
[TD]-100[/TD]
[/TR]
[TR]
[TD]delivery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]weeks of cover[/TD]
[TD]3.8[/TD]
[TD]2.8[/TD]
[TD]1.8[/TD]
[TD]-0.8[/TD]
[TD]-1.5[/TD]
[TD]3.0

[/TD]
[/TR]
</tbody>[/TABLE]


Calculation: Opening - same week Forecast: 200-10 and next week opening is 190 - 20 , next week 170 ect. opening 160 demand 200 weeks are on negative as not enough stock.

result: For how many weeks 200 will least, next cell for how many weeks 190 least ect

Negatives - if forecast is higher then opening stock (example with 120-200) I want to see how many weeks we are short. The logic in this is to be able to calculate how much stock I need to deliver to bring again back to 3 Weeks worth of stock. please see example below (Weeks of cover are just random as Im looking formula for it)

Any ideas ? Im sorry if its not clear one of those hard ones to explain

[TABLE="width: 500"]
<tbody>[TR]
[TD]weeks[/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[TD]Wk5[/TD]
[TD]Wk6[/TD]
[/TR]
[TR]
[TD]forecast[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]200[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]opening[/TD]
[TD]200[/TD]
[TD]190[/TD]
[TD]170[/TD]
[TD]160[/TD]
[TD]-40[/TD]
[TD]-100[/TD]
[/TR]
[TR]
[TD]delivery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]weeks of cover[/TD]
[TD]3.8[/TD]
[TD]2.8[/TD]
[TD]1.8[/TD]
[TD]-0.8[/TD]
[TD]-1.5[/TD]
[TD]3.0[/TD]
[/TR]
</tbody>[/TABLE]

 

[brazill23]

I was trying this but its not calculate correctly

=IF(D8>SUM(D4:$N$4),#N/A,IF(C16=SUM(D12:$N$12),COUNT(D12:$N$12)*4,ROUND((SUMPRODUCT(--(C16>=SUBTOTAL(9,OFFSET(D12:$N12,,,,COLUMN(D12:$N12)-COLUMN(D12)+1))))+ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(D12:$N12,,,,COLUMN(D12:$N12)-COLUMN(D12)+1))-C16-D12:$N12)/D12:$N12)))*4,2)))

 

[Eric W]

Maybe something like this:

	A	B	C	D	E	F	G	H
?

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]weeks[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Wk1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Wk2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Wk3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Wk4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Wk5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Wk6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]forecast[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]opening[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]190[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]170[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]160[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-90[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]delivery[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]300[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]weeks of cover[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-0.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-1.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]-0.2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=IF(B6=0,IF(B3+B4>0,-(B2-B3-B4)/B2,"?"),B6+IFERROR((B3+B4-SUM(OFFSET(B2,0,0,1,B6)))/OFFSET(B2,0,B6),0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]{=IFERROR(MATCH(1,IF(MMULT(IF(COLUMN(B2:$N2)<=TRANSPOSE(COLUMN(B2:$N2)),B2:$N2,0),TRANSPOSE(COLUMN(B2:$N2))^0)<=B3+B4,0,1),0)-1,COLUMNS(B6:$N6))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I used a helper row in C. You can hide it if you want. It basically finds the whole number of weeks of stock available. The row 7 formula calculates the fractional part. For the "positive" weeks (columns B:D), the results match yours. For the first "negative" week (column E), I calculated -.2 instead of your -.8. The way I figure it, you are 40 units short of your 200 forecast, so 40/200 = .2 weeks short. For the next "negative" week, I just use a question mark. The reason being that from a mathematical point of view, calculating percentages from mixed signed numbers becomes fairly nonsensical. If you are looking to at least find how many weeks it will take before the available stock becomes greater than zero, that could be possible, but I had difficulties figuring out how to handle aperiodic deliveries. (For example, should G3 be -90?) And do negative numbers represent back orders, or lost sales?

In any event, take a look and see what you think.