How to find profit for those two tables

[BlindPro]

Hi, I have to tables Sale and buy:

Buy:
InvID | Date | Item | QTY | Price | Total
-------------------------------------------------------------
1 | 1/1 | ItemA | 5 | 40 | 200
-------------------------------------------------------------
2 | 1/1 | ItemB | 5 | 30 | 150
-------------------------------------------------------------
3 | 5/1 | ItemA | 5 | 45 | 225
-------------------------------------------------------------
Total: 575

Sale:
InvID | Date | Item | QTY | Price | Total
-------------------------------------------------------------
1 | 1/1 | ItemA | 4 | 50 | 200
-------------------------------------------------------------
2 | 2/1 | ItemA | 1 | 51 | 51
-------------------------------------------------------------
3 | 2/1 | ItemB | 3 | 45 | 135
-------------------------------------------------------------
4 | 3/1 | ItemA | 1 | 55 | 55
-------------------------------------------------------------
Total: 441



My Question is: how to find profit for this operation?
My Problem is: I have price different price for some items like you seen on ItemA

 

[jdraw]

How do you define profit? Once you figure out exactly what you mean by profit, you should have an algorithm/equation to do the calculation. Is there any "cost" to holding an item for 30 days, 6 months etc

 

[BlindPro]

all data I have is that what I posted, I can find 30 days total for buy and sale, and what in store remained but my problem is with coast change while buying itemA

 

[jdraw]

My first approach (an I'm not an accountant) would be to get the avg buy price and the avg sell price.

In your sample data

you buy 10 ItemA's for 425, so avg is 42.5

you sell 6 ItemA's for 306, so avg is 306/6 = 51 (and you're holding 4 Itema's)

So on the sold Itema's, the avg profit is 51-42.5 = 8.5

 

[Simon_MT]

With ItemA you bought in at 40 & 45 so depending how you treat Stock attrition FIFO then the Unit cost on Sale Invoice 1 & 2 will be 40 whilst Sale Invoice 4 will be 45 so the logical collusion would be to store the Unit Cost at the time of sale.

Simon

 

[BlindPro]

My first approach (an I'm not an accountant) would be to get the avg buy price and the avg sell price.

In your sample data

you buy 10 ItemA's for 425, so avg is 42.5

you sell 6 ItemA's for 306, so avg is 306/6 = 51 (and you're holding 4 Itema's)

So on the sold Itema's, the avg profit is 51-42.5 = 8.5

thanks alot man, it worked

 

[Simon_MT]

There is a number of methods to evaluate cost:

Actual - High priced items
FIFO - First In / First Out
LILO - Last In / Last Out

Technically as the second batch of ItemA was brought after the sales that depleted the entire stock of ItemA, then retroactively average out the cost of a sale of non-existant stock. Although there is no real issue of materiality on averaging the cost - FIFO is a better method because given your example is you replenish after sales.

Well that is the theory.

Simon