Consumption function

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

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<[email protected]> wrote in message
news:[email protected]...
>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
>

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

On 16 Nov 2005 10:55:11 -0800, [email protected] wrote:

>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

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

Ron Rosenfeld wrote:
> On 16 Nov 2005 10:55:11 -0800, [email protected] wrote:
>
> >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
>
> 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


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????

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

On 18 Nov 2005 10:22:21 -0800, [email protected] wrote:

>
>Ron Rosenfeld wrote:
>> On 16 Nov 2005 10:55:11 -0800, [email protected] wrote:
>>
>> >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
>>
>> 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
>
>
>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????

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