E
esbee
I have data ,columnwise, as follows:
A: Date, B: ItemName, C
urchase or Sale, D:Qty, Eer Unit Rate, F:Value (
Rate* Qty).
Using FIFO (First-in-First-Out) method I want to match Sales against
corresponding ( item wise) purchases. A simple sort could have solved the
problem. But,it is not enough for the following reasons:
i) Purchases and sales may not always match, as quantity purchased and qty
sold could differ ( sold will be lower). For the quantity sold, I want to
arrive at profit or loss and carry forward the balance for comparison with
future sales.
ii) Carried forward balances should remain distinct for matching with sales
of equal qty in future. If fresh purchases were there, they should be kept
separately as for profit or loss I do not want to mix one lot of purchase
with the other.
iii) Against Sales, which should be the basis for matching, more than one
lot of purchase could be matched ( but shown separately in one row below the
other), but if the last lot of purchase would take the total of purchase to
more than the quantity of sales, only the difference should be shown and the
balance should be carried forward.
If data on one sheet and results based on the above are shown on another
sheet, it will be better. I do not want to cut or remove any of the data
from the original database.
I tried several formulae, but failed to solve it. Doing this manually after
sorting the data itemwise, purchase, sale wise and then manually comparing
and arriving at the difference is very laborious. Is there a short cut ?
What kind of functions or programme I can use. Can any one help ? I shall
be very thankful for the help or even an attempt to help.
A: Date, B: ItemName, C
urchase or Sale, D:Qty, Eer Unit Rate, F:Value (Rate* Qty).
Using FIFO (First-in-First-Out) method I want to match Sales against
corresponding ( item wise) purchases. A simple sort could have solved the
problem. But,it is not enough for the following reasons:
i) Purchases and sales may not always match, as quantity purchased and qty
sold could differ ( sold will be lower). For the quantity sold, I want to
arrive at profit or loss and carry forward the balance for comparison with
future sales.
ii) Carried forward balances should remain distinct for matching with sales
of equal qty in future. If fresh purchases were there, they should be kept
separately as for profit or loss I do not want to mix one lot of purchase
with the other.
iii) Against Sales, which should be the basis for matching, more than one
lot of purchase could be matched ( but shown separately in one row below the
other), but if the last lot of purchase would take the total of purchase to
more than the quantity of sales, only the difference should be shown and the
balance should be carried forward.
If data on one sheet and results based on the above are shown on another
sheet, it will be better. I do not want to cut or remove any of the data
from the original database.
I tried several formulae, but failed to solve it. Doing this manually after
sorting the data itemwise, purchase, sale wise and then manually comparing
and arriving at the difference is very laborious. Is there a short cut ?
What kind of functions or programme I can use. Can any one help ? I shall
be very thankful for the help or even an attempt to help.