Weeks of cover including negatives

brazill23

New Member
Joined
Dec 18, 2018
Messages
11
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 :(:(


Wk1Wk2Wk3WK4WK5Wk6
Forecast

<tbody>
</tbody>
10230522050
Inventory

<tbody>
</tbody>
200190-406040-10
Plannin delivery

<tbody>
</tbody>
00010000

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Dec 18, 2018
Messages
11
Hi Gerald thank you for your respond.
Inventory : actual value - same week forecast.

weeksWk1Wk2Wk3Wk4Wk5Wk6
forecast1020102005060
opening200190170160-40-100
delivery300
weeks of cover3.82.81.8-0.8-1.53.0


<tbody>
</tbody>


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:(:confused:

weeksWk1Wk2Wk3Wk4Wk5Wk6
forecast1020102005060
opening200190170160-40-100
delivery300
weeks of cover3.82.81.8-0.8-1.53.0

<tbody>
</tbody>
 

brazill23

New Member
Joined
Dec 18, 2018
Messages
11
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
Maybe something like this:

ABCDEFGH
1weeksWk1Wk2Wk3Wk4Wk5Wk6
2forecast1020102005060
3opening200190170160-40-90
4delivery300
5weeks of cover3.82.81.8-0.8-1.53
6321008
73.82.81.8-0.2?8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
B7=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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B6{=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))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,393
Messages
5,601,403
Members
414,449
Latest member
Pashtun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top