| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
