ExcelBanter - View Single Post

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