Help anyone???

[Ingrid]

Hi Everyone,

I'd like some help with the following (which I think is quite simpl
but...) I've created the following spreadsheet:

ProductCode PurchaseDate Style Clr InvCd SI/PQ/US
10042S001 01/07/2004 10042S 001 SI 199
10042S001 01/08/2004 10042S 001 SI 250
10042S001 01/09/2004 10042S 001 SI 500
10042S001 01/10/2004 10042S 001 PQ 100
10042S001 01/10/2004 10042S 001 US -449

SI= starting Inventory
PQ= Purchased Qty
US= Units Sold

I would like to add a colomn which states the closing Balance (in qty
but using the FIFO method.

The additional colomn would have to come back with:

ProductCode PurchaseDate Style Clr InvCd ClosingBalance
10042S001 01/07/2004 10042S 001 SI 0
10042S001 01/08/2004 10042S 001 SI 0
10042S001 01/09/2004 10042S 001 SI 500
10042S001 01/09/2004 10042S 001 PQ 100
10042S001 01/09/2004 10042S 001 US 0

Is there a formula for this?

Would appreciate any help,

Thanks,

Ingri

 

[Bob Phillips]

I think we need more details on where those numbers come from, why are the
first two and the last 0?

 

[Ingrid]

The closing Balance qty is 600

199+250+500+100-449

As the FIFO method applies the sold units consists out of 199+25
leaving the remainder for the closing balance hence the zero's fo
01/07/2004 & 01/08/2004. The sold units should be zero on the closin
balance