Formula to allocate available stock to an outstanding order report

[dwarden]

Hi

I have an outstanding order report listing all outstanding order lines. I want to be able to allocate available stock to order lines, allocating to the oldest order frist.

My proposal is to have one sheet listing outstanding order lines (Sheet 1). The a second sheet to hold a stock list with available quantity (Sheet 2). I need a formula to allocate available stock until the stock is depleted. If no stock the result would be 0.

Sheet 1 (oustandind order lines)
A1 = Stock ID
B1 = Qty on order
C1 = Original order date
D1 = (Proposed Allocated Qty formula)

Sheet 2 (Stock List)
A1 = Stock ID
B1 = Qty Available


I hope this is clear?

 

[Andrew Fergus]

Hi David

This can be done a number of ways but I have one query : are the outstanding orders in sequence of oldest to newest?

The reason I ask is that if they aren't in sequence and there are 2 orders with the same date competing for the last amount of stock available, whoich order should get what is available?

Andrew

 

[Andrew Fergus]

The following method assumes the data is sorted by date:

   A        B         C          D                   
 1 Stock ID Order Qty Order Date Proposed Allocation 
 2 2        20        1/04/2009  20                  
 3 1        10        1/05/2009  10                  
 4 2        30        20/05/2009 5                   
 5 1        5         25/05/2009 5                   
 6 2        10        25/05/2009 0                   
 7 3        15        25/05/2009 2                   
 8 3        5         25/05/2009 0                   
 9 1        10        26/05/2009 3                   
10 1        16        27/05/2009 0                   
Sheet1 (2)
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
D2:D10  =MAX(MIN(B2,VLOOKUP(A2,$J$2:$K$4,2,FALSE)-SUMIF($A$1:A1,A2,$B$1:B1)),0)
[Table-It] version 09 by Erik Van Geit

I had a simple data table in the range J2:K2 as follows:

  J        K         
1 Stock ID Available 
2 1        18        
3 2        25        
4 3        2         
Sheet1 (2)
[Table-It] version 09 by Erik Van Geit

If the data is not sorted by date then you can use a sumproduct function to work out the quantity allocated so far - but there is an issue if you have 2 orders with the same date competing for the last items in stock - unless you include the order number in another column and stipulate the oldest order always takes priority.

Andrew

 

[sandeshgp23]

Dear Sir,

=MAX(MIN(K2,VLOOKUP(C2,EDA!$A:$E,5,FALSE)-SUMIF($C$1:C1,C2,$K$1:K1)),0)
Thanks very much. Above mentioned formula works fine. But I want to consider another thing in the same formule ie. Currently available e stock. Giving u an example below , what I want exactly :

Suppose Order Qty of Stock ID "A" is 10 against one order and 5 against second order , so total outstanding Qty is 15.
New stock arrived for this is 10 which will be served to 1st order of 10 qty but I also have some stock available ie. 5 in another column.

No I want formula to allocate this stock (5 Qty) first and remaining 5 from new stock. So that remaining 5 (new stock) to the other order.

Hope I have explained what i want.
Please suggest.

Regards,
Sandesh
sandeshgp23@gmail.com