About Us

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date & Time	mully	New Users to Excel	4	May 23rd 05 11:56 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc	Elijah-Dadda	Excel Worksheet Functions	0	March 5th 05 03:31 AM
Conversion	SVC	Excel Worksheet Functions	9	February 28th 05 02:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE.	Robert AS	Excel Worksheet Functions	4	December 2nd 04 10:49 AM
Find a Function to use accross different worksheets	R. Hale	Excel Worksheet Functions	3	November 25th 04 07:07 AM

Menu

About Us