Help anyone???
 

Hi Everyone,

I'd like some help with the following (which I think is quite simple
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,

Ingrid


--
Ingrid
------------------------------------------------------------------------
Ingrid's Profile: http://www.excelforum.com/member.php...o&userid=27386
View this thread: http://www.excelforum.com/showthread...hreadid=469098

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

--
HTH

Bob Phillips

"Ingrid" wrote in
message ...

Hi Everyone,

I'd like some help with the following (which I think is quite simple
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,

Ingrid


--
Ingrid
------------------------------------------------------------------------
Ingrid's Profile:
http://www.excelforum.com/member.php...o&userid=27386
View this thread: http://www.excelforum.com/showthread...hreadid=469098

The closing Balance qty is 600

199+250+500+100-449

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


--
Ingrid
------------------------------------------------------------------------
Ingrid's Profile: http://www.excelforum.com/member.php...o&userid=27386
View this thread: http://www.excelforum.com/showthread...hreadid=469098