ExcelBanter - View Single Post

On 18 Nov 2005 10:22:21 -0800, wrote:

Ron Rosenfeld wrote:
On 16 Nov 2005 10:55:11 -0800, 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 befo Sales in A; Stock remaining in B, and use this
array formula which shows when the stock became 0:

=MIN(IF(B1:B50,B1:B5))/(MIN(IF(B1:B50,B1:B5))-
MAX(IF(B1:B5<0,B1:B5)))+MATCH(MIN(IF(B1:B50,
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