UDF weeks of inventory calculation - lost on how to create this lo

[zeroscout]

Hello all, I am trying to create a function to calculate the weeks of
inventory based on future sales, actuals for historical and forecast for
future.

I can create a row in the excel report that will show actuals through the
latest reported week and forecast, so the function can use the same row of
data for sales.

My goal is to create a true weeks of inventory calculation, not based on the
inventory divided by the average sales over a time period, but how many weeks
it would take to deplete the inventory reported based on actual sales or
forecast.

For the first product, the inventory is in row 21, first weeks reported
starting in column G, and the sales data is in row 17.

If the inventory of the first week to calculate is in G21, then I would want
to begin the weeks of inventory calculation using the sales starting in the
following week, cell H17

My thought of how this would work would be a loop that counts how many weeks
the sum of 100% of those weeks sales is less than the inventory.

Example would be in week of 12/30/07, the inventory reported was 4,573. The
sales in the next week, 1/6/08 = 548, 1/13/08 = 407, 1/20/08 = 503, 1/27/08 =
617, 2/3/08 = 575, 2/10/08 = 444, 2/17/08 = 515, 2/24/08 = 478, 3/2/08 = 483.

There fore, if the loop was functioning as I envision it, the count of the
loop would be 9, and since these weeks only add up to 4,570, there would be a
balance of 3 left to decrement from the inventory.

The next step in the function would take the balance and divide it into the
sales of week 3/9/08, which are 250. This is equal to 0.012.

At the end of this step, the function would add to loop count and the last
step together to calculate the actual weeks of inventory to be 9.012.

Thank you for taking the time to read the narrative above. Here is the
function I have written so far. I am confident that if I was aided in the
first step of the loop, I could complete the function. Any and all help is
greatly appreciated.

Public Function WKSINVsim02(ByVal invST As Single) As Single
Dim invLP As Integer, invINC As Single, posCL As Integer

''invST is starting inventory quantity
''invLP is counter
''invINC is inventory loop quantity
''posCL is pos cell in loop

invLP = 0
invINC = 0
posCL = invST.Offset(-4, 0).Value

Do While invINC < invST
invINC = invINC + posCL
invLP = invLP + 1
posCL = cell.posCL.Offset(0, 1).Value
Loop

WKSINVsim02 = invLP

End Function

 

[Patrick Molloy]

pass into this function just the column of sales (548,407,503,617,575,444
....) and the stock amount (4573)

Function Inventory(table As Range, stock As Long)
Dim cell As Range
Dim weeks As Double
Dim total As Long
For Each cell In table.Cells
If (total + cell.Value) <= stock Then
weeks = weeks + 1
total = total + cell.Value
Else
weeks = weeks + (stock - total) / cell.Value
Exit For
End If
Next
Inventory = weeks
End Function

 

[jamescox]

OK, I'm going to talk a bit about how I see what you are trying to do.

You have a cell with the actual inventory in it and below that cell
(maybe right up against it, maybe with some rows with blanks or text or
whatever) you have a series of rows with actual weekly sales of the
item. All these numbers (inventory and past sales) are in the same
column. Let's plan on there being some rows between the inventory value
and the first of the weekly sales because that's the more general case
and if you can get this to work with those rows, you can get it to work
without those 'extra' rows.

This means that your function will have to have the location of the
cell with the inventory in it and the cell at the top of the set of
weekly sales, so the function will have to be passed these two
locations, as ranges.

So far the function looks like

Public Function WOI(rInv As Range, rWks As Range) As Double

End Function

where WOI stands for Weeks Of Inventory, rInv is the cell (say J3) that
holds the inventory and rWkS is the cell at the top of the Weekly Sales
(say J7) and the value that needs to be returned is a single or double -
I'll go with a double.

You were onto something with the idea of using Offsets, but Offsets
need to be from Ranges, not variables like your invST (an integer, I
believe).

If we're going to be looping, we are probably going to need a counter
or index variable. If we are going to be keeping a running total of
weekly sales, we probably need something to store that in and we may
need something to hold the value of the inventory. Let's Dim a few
things in the function and do some initializations

Public Function WOI(rInv As Range, rWks As Range) As Double

Dim dSum As Double
Dim dOldSum As Double
Dim dInv As Double
Dim lL As Long

lL = 0
dInv = rInv.Value
dSum = rWks.Offset(lL, 0).Value

End Function

You'll notice that dInv now holds the value of the cell where the
inventory is and dSum holds the first of the weekly sales, the value of
the cell at the top of the weekly sales. Yes, the value of dSum could
have been determined by

dSum = rWks.Value

but the way it was defined above is a foreshadowing of how we will be
getting weekly sales values once inside the while/wend loop - so let's
add that loop now:

Public Function WOI(rInv As Range, rWks As Range) As Double

Dim dSum As Double
Dim dOldSum As Double
Dim dInv As Double
Dim lL As Long

lL = 0
dInv = rInv.Value
dSum = rWks.Offset(lL, 0).Value

'You will stay in this loop until dSum includes one week too many,
so
' save the old sum in dOldSum
While dInv > dSum
dOldSum = dSum
lL = lL + 1
dSum = dSum + rWks.Offset(lL, 0).Value
Wend


End Function


Let's talk through what's going on in the loop. While the sum of all
weeks sales is less than the inventory, the code inside the loop will
execute. First, the previous value of dSum is cached in dOldSum, then
lL is incremented and finally, the next weekly sales value is added to
the previous dSum. As the comment says, this loop will run until it has
added in the statement

dSum = dSum + rWks.Offset(lL, 0).Value

one too many of the weekly sales values. Therefore, we need to use
dOldSum (the last sum of the weekly sales which was less than the
inventory) to calculate what fraction of that last-step-too-far weekly
sales value is needed to bring dOldSum just up to dInv. And here's that
in the function:

Public Function WOI(rInv As Range, rWks As Range) As Double

Dim dSum As Double
Dim dOldSum As Double
Dim dInv As Double
Dim lL As Long

lL = 0
dInv = rInv.Value
dSum = rWks.Offset(lL, 0).Value

'You will stay in this loop until dSum includes one week too many,
so
' save the old sum in dOldSum
While dInv > dSum
dOldSum = dSum
lL = lL + 1
dSum = dSum + rWks.Offset(lL, 0).Value
Wend

WOI = CDbl(lL) + (dInv - dOldSum) / rWks.Offset(lL, 0).Value

End Function

Now there are a few things that need to be tested (I did do a simple
test, just to check my logic) and potential problems to be thought
through:

1) What does the code do if the first weekly sales happens to be more
than the inventory - does the value get calculated correctly?

2) What happens if the loop reaches the bottom of the weekly sales and
dSum is still less than dInv? This one is going to take more thought,
especially if it's possible that there were no sales during a week and
the person who enters the sales decides it's OK to just leave the cell
blank for that week. Since you know more about your specific
application than I do, the ball is in your court for this, but one
possible hint is to check if the value of the weekly inventory is "".

3) Look up Application.Volatile in the help files and decide how you
want the function to recalculate and add, if you decide it's appropriate
a line

Application.Volatile = True after the Dim statements



Finally, you will note that in your code, you looked at the loop
criterion differently than I did - you were, in effect, checking if dSum
was less than dInv. Doing it that way would affect the code in the
function above - just for grins, go ahead and modify it to use that
inequality test, and think through the ramifications - you may find
things get simpler if you do.

To use this UDF, go to a cell of your choice, type in

=woi(

then click on the cell with the inventory in it, say J3, they type a
comma

=woi(J3,

then click on the cell at the top of the weekly sales values, say J7,
then close the parentheses

=woi(J3,J7)

and you should get your answer.


Hope this has helped... :Bgr
--- Automerged consecutive post before response ---
:Bgr

Or, you could just use Patrick Molloy's solution - he got in, worked it
out and posted it while I was still typing mine...

 

[zeroscout]

Hello Patrick,
Thank you, this works very well! I have tested the function with an offset
formula in the report and it works awesome. This will get me through the
current report.

Now I will see if I am able to engineer in a programmatic solution for the
table variable and I will post back my efforts.

Thank you again, this function is a huge help,
Brian

 

[Patrick Molloy]

thanks - we all appreciate feedback - especially the good ones