Hello,
I am looking for a formula, which can help me to calculate my cost of the goods at time of selling.
Attachment is for refrence.
just for info
a) in coloumn j4 unit of my 1st purchase are sold out and by balance unit is 0, so in coloumn j5 i want to take new cost from the date of purchase from 09/03/21.
b) in coloumn j9 cost of 3 units is average from 28/04/21 (2 @ 673.017) to 12/05/21 (1 @ 676.676)
Hope i could make understand my query with given example.
Thank in advance for any reply/help.
I am looking for a formula, which can help me to calculate my cost of the goods at time of selling.
Attachment is for refrence.
just for info
a) in coloumn j4 unit of my 1st purchase are sold out and by balance unit is 0, so in coloumn j5 i want to take new cost from the date of purchase from 09/03/21.
b) in coloumn j9 cost of 3 units is average from 28/04/21 (2 @ 673.017) to 12/05/21 (1 @ 676.676)
Hope i could make understand my query with given example.
Thank in advance for any reply/help.
Need Formula.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | # | Date | Status | Scrip Name | Qty | Rate | Amount | C.O.G/Unit | Cost of Goods Total | Difference | |||
2 | 01 | 19/11/20 | Buy | Adani Enterprise | 15 | 490.708 | 7,360.63 | 7,360.63 | 0.00 | ||||
3 | 02 | 07/01/21 | Sell | Adani Enterprise | -10 | 502.121 | -5,021.21 | 490.708 | -4,907.08 | 114.13 | |||
4 | 03 | 12/01/21 | Sell | Adani Enterprise | -5 | 517.310 | -2,586.55 | 490.708 | -2,453.54 | 133.01 | |||
5 | 04 | 09/03/21 | Buy | Adani Enterprise | 10 | 673.017 | 6,730.17 | 6,730.17 | 0.00 | ||||
6 | 05 | 28/04/21 | Sell | Adani Enterprise | -8 | 644.594 | -5,156.75 | 673.017 | -5,384.14 | -227.39 | |||
7 | 06 | 12/05/21 | Buy | Adani Enterprise | 11 | 676.676 | 7,443.43 | 7,443.43 | 0.00 | ||||
8 | 07 | 13/05/21 | Sell | Adani Enterprise | -10 | 700.483 | -7,004.83 | 676.676 | -6,766.76 | 238.07 | |||
9 | 08 | 18/10/21 | Sell | Adani Enterprise | -3 | 68.082 | -204.25 | 674.237 | -2,022.71 | -1,818.46 | |||
10 | Total | 8 | 0 | - | 1,560.64 | 0.00 | -1,560.64 | ||||||
Sheet 1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J3:J4,J8:J9,J6 | J3 | =[@[C.O.G/Unit]]*[@Qty] |
K2:K9 | K2 | =[@[Cost of Goods Total]]-[@Amount] |
J10 | J10 | =SUBTOTAL(109,[Cost of Goods Total]) |
K10 | K10 | =SUBTOTAL(109,[Difference]) |
A2:A9 | A2 | =ROW(A1) |
C10 | C10 | =SUBTOTAL(103,[Status]) |
E10 | E10 | =SUBTOTAL(109,[Qty]) |
F10 | F10 | =IFERROR(Cs.Transaction27[[#Totals],[Amount]]/Cs.Transaction27[[#Totals],[Qty]],"-") |
G10 | G10 | =SUBTOTAL(109,[Amount]) |