Help anyone???

[Ingrid]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ingrid]

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.