Formula for calcing Weeks of Supply

[omarman]

Hi there,

I am looking for a way to calculate WOS so that as a unit sales forecast changes, so does the WOS.

Here is an example with the cells highlighted that I would like the formula to live:

Thanks!!!

 

[Domenic]

Try...

B4, copied across:


=IF(B3 < SUM(B2:$P2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))<=B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2-B3,(B3-(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2))/B2:$P2),IF(B3=SUM(B2:$P2),COUNT(B2:$P2),"N/A"))<?XML:NAMESPACE PREFIX = SUM(B2 /><SUM(B2:$P2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))<=B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2-B3,(B3-(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2)) B2:$P2),IF(B3='SUM(B2:$P2),COUNT(B2:$P2),"N/A"))</p'> </SUM(B2:$P2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))<=B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2-B3,(B3-(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2))>
<SUM(B2:$P2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))<=B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2-B3,(B3-(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2)) B2:$P2),IF(B3='SUM(B2:$P2),COUNT(B2:$P2),"N/A"))</p'>
Note that the formula returns the text value "N/A" when supply exceeds projected sales.
</SUM(B2:$P2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))<=B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2-B3,(B3-(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2))>

 

[p45cal]

omarman, can you explain how you get 3.3 as the result in C4?
I can get 3.4 for wk1 with a udf but I get nearer 3.74 for wk2.

Domenic, I was trying to use your formula as a double check but I suspect the board software has screwed up the formula, probably around the '<' and '>' symbols. Could you repost with code tags to prevent line wrap and add an extra space around such symbols to prevent it from being changed?

 

[Domenic]

omarman, can you explain how you get 3.3 as the result in C4?
I can get 3.4 for wk1 with a udf but I get nearer 3.74 for wk2.

Domenic, I was trying to use your formula as a double check but I suspect the board software has screwed up the formula, probably around the '<' and '>' symbols. Could you repost with code tags to prevent line wrap and add an extra space around such symbols to prevent it from being changed?

Sure, no problem...

=IF(B3 < SUM(B2:$P2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1)) < =B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2-B3,(B3-(SUBTOTAL(9,OFFSET(B2:$P2,,,,COLUMN(B2:$P2)-COLUMN(B2)+1))-B2:$P2))/B2:$P2),IF(B3=SUM(B2:$P2),COUNT(B2:$P2),"N/A"))

By the way, my formula returns 3.73958 for the second week. I assumed that there was an error in the calculation by the original poster.

 

[p45cal]

A UDF solution:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		wk1	wk2	wk3	wk4	wk5	wk6	wk7	wk8	wk9	wk10	wk11	wk12	wk13	wk14	wk15
2	unit sls	52	11	23	35	96	48	74	67	82	35	44	3	74	40	43
3	BOP	10	140	170	198	138	126	128	100	94	186	196	136	124	118	42
4	Dom's WOS	0.192308	3.739583	3.333333	3.256757	1.875	2.059701	1.80597	1.402439	1.342857	4.75	4.813953	3.44186	2.232558	N/A	0.976744
5	my WOS	0.192308	3.739583	3.333333	3.256757	1.875	2.059701	1.80597	1.402439	1.342857	4.75	4.813953	3.44186	2.232558	n/a	0.976744
Sheet

supported by this code in a standard module:

Function WOS(BOP, StartPosn As Range)
Set myRange = Range(StartPosn, StartPosn.End(xlToRight))
Dim lupvals()
ReDim lupvals(1 To myRange.Cells.Count)
i = 1
For Each cll In myRange.Cells
  lupvals(i) = Application.Sum(Range(StartPosn, cll))
  i = i + 1
Next cll
xx = Application.Match(BOP, lupvals)
If IsError(xx) And lupvals(1) >= BOP Then
  WOS = BOP / lupvals(1)
Else
  If xx = UBound(lupvals) And (BOP - lupvals(xx)) > 0 Then
    WOS = "n/a"
  Else
    WOS = xx + (BOP - lupvals(xx)) / myRange(xx + 1)
  End If
End If
End Function

 

[omarman]

Your formula worked!!! Thanks so much Domenic! This is the second time you've saved me!

P45cal thanks for the code! I haven't tried it yet but will do so later this afternoon.

I really appreciate your help everyone!

omarman

 

[omarman]

p45cal,

Tried the function you posted and it worked perfectly.

Thanks!

omarman