Weeks of Supply

[EdE]

How would I go about figuring the weeks of supply of inventory.
I have PN's listed in a2:a10,inventory in b2:b10, weeks listed in c1:bb1, and the weekly demands of each pn are in c2:bb10. I need to know for each part what the weeks of supply are. My issue is that the week where sum of demands is great than inventory level varies based on inventory number.

any ideas?

 

[schielrn]

In this example I will use row 2. So are you saying you want to know what week the sum of row 2 is greater than the inventory number in b2?

 

[EdE]

Yes. I can insert a row above week with the week number if it makes it easier.

 

[schielrn]

I would recommend using condtional formatting and try selecting c2:bb10 starting with c2 as the active cell and go to conditional formatting and put in formula is:

=$B2<sum($C2:C2)

<?xml:namespace prefix = sum($C2 /><sum($C2:C2)< p>
Then format to red or any other background color.

Hope that helps.
</sum($C2:C2)<>

 

[EdE]

Sorry, i didnt make my self clear. I need to know how many weeks of supply i have, whether it is 2 or 6.7. The sum range needs to change, the beginning date with todays date and end date (range) being where the sum from beginning to that particular date is greater than inventory number.

can this be done without Macro?

 

[schielrn]

Ok this is just showing a conditional formatting approach. It would show that you do not have enough inventory to fill week 6 for PN1 and not enough to fill week 9 in PN2. Now if you want a hard coded number where will this formula or VBA go within the sheet?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		WeekNumber	1	2	3	4	5	6	7	8	9	10	11	12
2	PN1	28	8	6	3	4	5	6	9	11	8	9	3	1
3	PN2	75	13	12	6	6	12	9	11	2	12	10	10	13
Sheet1

Hope that helps.

 

[schielrn]

This is the conditional formattign formula starting in C2:

=$B2>=SUM($C2:C2)

 

[EdE]

Formula could go at the end of data range for the row or i could insert a column at C for W.O.S.

 

[schielrn]

This UDF should work for you. Put in the formula at the end of the range like:

=RunOut(B2,C2:BB2)

Function RunOut(invAmt, weekTotals As Range)
Dim c As Range, total As Long, counter As Integer
For Each c In weekTotals
    total = c.Value + total
    If total = invAmt Then
        RunOut = counter + 1
        Exit Function
    ElseIf total > invAmt Then
        RunOut = counter + (invAmt - total + c.Value) / c.Value
        Exit Function
    End If
    counter = counter + 1
Next c
End Function

Hope that helps.