Weeks Cover for Inventory

[PatrickBirch]

Hope some can help - My problem is I wish to write a custom function which I can Use to calculate the weeks cover of current inventory based on a sales forecast. the forecast is in weekly buckets so I was thinking using some form of loop statement within the function which basically starts with the inventory figure then subtracts the forecast week by week until the inventory figure is less than the weekly forecast. I should then be able to divide the inventory by forecast and add back the number of loops to give me the weeks stock figure.

I have done this using if statements but it will only allow me to use 10 statements in the function and I need to do this for a full years worth of forecast.

Does anyone have a suggestion as This problem is becoming quite frustrating.

Here's an example of how the spreadsheet looks

<TABLE style="WIDTH: 399pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=532 border=0 x:str><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>Forecast</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>10</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>30</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>50</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>20</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>50</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="Inventory ">Inventory </TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>200</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>190</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>160</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>110</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>90</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>40</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Weeks Cover</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>6.0</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>5.0</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>4.0</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>3.0</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>2.0</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>1.0</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Any help would be greatly appreciated.

Thanks
Patrick

 

[Domenic]

By formula, assuming that A1:H2 contains the data, maybe...

B3, confirmed with CONTROL+SHIFT+ENTER, copied across:

=IF(N(B2),MATCH(TRUE,SUBTOTAL(9,OFFSET(C1:$H1,,,,COLUMN(C1:$H1)-COLUMN(C1)+1))>=B2,0),"")

Hope this helps!

 

[PatrickBirch]

Tried the response but couldn't get that to work - I have copied in the Custom function I currently use (sorry it's a bit messy and long) but what I need to do is be able to adapt this to run for more than the 10 If statements VBA allows me to use - Approx 52 weeks would be better!

Variables are: Inventory = Current stock qty, Forecast = Forecast for week or month (depends on how far out it is), Prod Week = number of weeks the forecast is for Usually 1 or 4 weeks.

Ideally I think some form of loop statement and maybe using array variables would work but I just can't seem to get it right.

Function WEEKSCOVER(Inventory As Long, Forecast As Long, Prodweek As Single, Optional Forecast1 As Long, _
Optional Prodweek1 As Single, Optional Forecast2 As Long, Optional Prodweek2 As Single, _
Optional Forecast3 As Long, Optional Prodweek3 As Single, Optional Forecast4 As Long, _
Optional Prodweek4 As Single, Optional Forecast5 As Long, Optional Prodweek5 As Single, _
Optional Forecast6 As Long, Optional Prodweek6 As Single, Optional Forecast7 As Long, _
Optional Prodweek7 As Single, Optional Forecast8 As Long, Optional Prodweek8 As Single, _
Optional Forecast9 As Long, Optional Prodweek9 As Single, Optional Forecast10 As Long, _
Optional Prodweek10 As Single)

If (Inventory - Forecast) < 1 Then
WEEKSCOVER = Inventory / Forecast * Prodweek

ElseIf (Inventory - (Forecast + Forecast1)) < 1 Then
WEEKSCOVER = Prodweek + ((Inventory - Forecast) / Forecast1 * Prodweek1)

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + (Inventory - (Forecast + Forecast1)) / Forecast2 * Prodweek2

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + (Inventory - (Forecast + Forecast1 + Forecast2)) / Forecast3 * Prodweek3

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + Prodweek3 + (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3)) / Forecast4 * Prodweek4

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + Prodweek3 + Prodweek4 + (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4)) / Forecast5 * Prodweek5

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + Prodweek3 + Prodweek4 + Prodweek5 + (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5)) / Forecast6 * Prodweek6

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6 + Forecast7)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + Prodweek3 + Prodweek4 + Prodweek5 + Prodweek6 + (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6)) / Forecast7 * Prodweek7

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6 + Forecast7 + Forecast8)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + Prodweek3 + Prodweek4 + Prodweek5 + Prodweek6 + Prodweek7 + (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6 + Forecast7)) / Forecast8 * Prodweek8

ElseIf (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6 + Forecast7 + Forecast8 + Forecast9)) < 1 Then
WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + Prodweek3 + Prodweek4 + Prodweek5 + Prodweek6 + Prodweek7 + Prodweek8 + (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6 + Forecast7 + Forecast8)) / Forecast9 * Prodweek9

Else

WEEKSCOVER = Prodweek + Prodweek1 + Prodweek2 + Prodweek3 + Prodweek4 + Prodweek5 + Prodweek6 + Prodweek7 + Prodweek8 + Prodweek9 + (Inventory - (Forecast + Forecast1 + Forecast2 + Forecast3 + Forecast4 + Forecast5 + Forecast6 + Forecast7 + Forecast8 + Forecast9)) / Forecast10 * Prodweek10

End If
End Function

Any ideas?

 

[Domenic]

Tried the response but couldn't get that to work

I can't help you with VBA. Are you interested in a formula solution? If so, you'll need to elaborate on your response.

 

[PatrickBirch]

Thanks for the help, But I was hoping someone would be able to show me how to turn the Custom Function above into a function that would work for more columns than it currently can due to the limitations of the If statements.

 

[PatrickBirch]

Guys,

I'm still stuck with this problem of turning the above code into one that can look further out than the 10 colums it does currently, If anyone has any ideas how this can be done I would really appreciate it as it would solve a problem that's been annoying my workplace for ages?

Thanks
Patrick

 

[PatrickBirch]

I still had this problem so just decided to have another look around here and pulled up my old post above - Revisited domenic advice on the formula and realised I was misunderstanding it back then and the formula does nearly everything I need which is great!!! just one thing missing and not sure if this formula can be adapted to show this.

If I have enough stock to last for 1 and a half weeks cover can the cover repersent this as at present the formula only shows full weeks - ie if I had 1.5 weeks cover it would show as 2 weeks which would overstate my inventory, Ideally I need to show the decimal.

once again thanks Domenic - I should have listened first time and maybe I would have got the answer by now.

 

[Diablo II]

thats a lot have you try-ed using the "case" you can use a lot of them?

 

[Domenic]

Assuming that A2:H3 contains the table, try...

B4, copied across:

=IF(B3 < SUM(C2:$H$2),SUMPRODUCT(--(B3>=SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))))+LOOKUP(0,SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2-B3,(B3-(SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2))/C2:$H$2),IF(B3=SUM(C2:$H$2),COLUMNS(C2:$H$2),"Inventory Exceeds Forecast"))

 

[PatrickBirch]

That formula definately works fine - Thank you.

can it be extended to take in alot more columns - I working on a full year so Ideally I need it to do 52 weeks?