Consumption function

A

andrew_berry

I work in stock control and want to add a function in excel which works
out how many weeks stock I have versus sales in a range

eg if stock is 100 and the next 4 weeks sales are 40, 20, 20, 30

then I have 3.66 weeks stock

this is 40+20+20 = 3 weeks

and the remainder 20 as a percentage of 30 = 0.66

Any help would be much appreciated.....

thanks

Andy
 
B

Bernard Liengme

Please rephrase.
If you start with 100 and sell 40+20+20+30 you have -10 in stock!
we want to help!
 
A

andrew_berry

no I don't want to know what is left in stock, I need to know the
number of weeks (or part weeks) that the stock will last. In the
example above this will be 3.66 weeks
 
R

Ron Rosenfeld

I work in stock control and want to add a function in excel which works
out how many weeks stock I have versus sales in a range

eg if stock is 100 and the next 4 weeks sales are 40, 20, 20, 30

then I have 3.66 weeks stock

this is 40+20+20 = 3 weeks

and the remainder 20 as a percentage of 30 = 0.66

Any help would be much appreciated.....

thanks

Andy
Click to expand...

Your rate of decrease is not constant.

I would think that the number of weeks of stock would be the point where the
graph of inventory vs. time crosses the y-axis -- in other words look at the
slope of your inventory curve and, using linear regression, compute the point
at which the curve crosses the 0-value on the y-axis.

The formula for that line would be y=mx+b

If y = 0, then
mx = -b
x = -b/m

LINEST gives you the values for m and b (base 1) so you'd have to subtract 1
from the results to get a base 0 result.

Therefore a formula could be constructed as follows:


Put your weekly sales in A1:A4

B1: 100
B2: =B1-A1

Copy/Drag down to B5 giving the following results:

B1: 100
B2: 60
B3: 40
B4: 20
B5: -10

Then use the following equation:

=-1-INDEX(LINEST(B1:B5),2)/LINEST(B1:B5)

or 3.615 weeks.




--ron
 
A

andrew_berry

Ron said:
Your rate of decrease is not constant.

I would think that the number of weeks of stock would be the point where the
graph of inventory vs. time crosses the y-axis -- in other words look at the
slope of your inventory curve and, using linear regression, compute the point
at which the curve crosses the 0-value on the y-axis.

The formula for that line would be y=mx+b

If y = 0, then
mx = -b
x = -b/m

LINEST gives you the values for m and b (base 1) so you'd have to subtract 1
from the results to get a base 0 result.

Therefore a formula could be constructed as follows:


Put your weekly sales in A1:A4

B1: 100
B2: =B1-A1

Copy/Drag down to B5 giving the following results:

B1: 100
B2: 60
B3: 40
B4: 20
B5: -10

Then use the following equation:

=-1-INDEX(LINEST(B1:B5),2)/LINEST(B1:B5)

or 3.615 weeks.




--ron
Click to expand...


thanks Ron

tried this but does not work with other data eg

stock = 24,717

sales = 11,832 123,377 71,386 31,564

projected stock = 24,717 12,885, -110,492, -181,878

result = 0.64

I estimate this should be 1.1 weeks stock????
 
M

MrShorty

Here's a basic UDF that should do what you want. I haven't extensively
tested it, nor have I included any error handling or declared
variables. This should give you a basic core to work with.

Function stkonhnd(inistk, wklysls As Range)
crntstk = inistk
wks = 0
Do While crntstk > wklysls.Cells(wks + 1, 1).Value
wks = wks + 1
crntstk = crntstk - wklysls.Cells(wks, 1).Value
Loop
wks = wks + crntstk / wklysls.Cells(wks + 1, 1).Value
stkonhnd = wks
End Function

Then in your spreadsheet, call the function in the cell where you want
the result with =stkonhnd(A1,B1:B4).

HTH
 
R

Ron Rosenfeld

thanks Ron

tried this but does not work with other data eg

stock = 24,717

sales = 11,832 123,377 71,386 31,564

projected stock = 24,717 12,885, -110,492, -181,878

result = 0.64

I estimate this should be 1.1 weeks stock????
Click to expand...

Perhaps we have a communication problem.

The function I gave you is for *prediction* purposes. I mistakenly thought
that's what you wanted.

If all you need to know is when the stock ran out, then the only two "stock
remaining" numbers you really need are the two that bracket the week when the
stock ran out.

So set up your data as before: Sales in A; Stock remaining in B, and use this
array formula which shows when the stock became 0:

=MIN(IF(B1:B5>0,B1:B5))/(MIN(IF(B1:B5>0,B1:B5))-
MAX(IF(B1:B5<0,B1:B5)))+MATCH(MIN(IF(B1:B5>0,
B1:B5)),B1:B5,-1)-1

To enter an **array** formula, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the formula.


--ron
 
Top