Separate profit calculation for each transaction

[sa100]

Hello,

I'd like to create a sample Profit table using the attached Sample Sales and Purchase Table. (see attached tables)

Calculation Criteria:

Each sales transaction requires an individual profit calculation.

Profit calculation will be based on the earliest purchase of the corresponding product.

If the quantities don't match, combine or partially combine the two purchase transactions.

I would greatly appreciate your assistance in solving this puzzle. Please note that the sample data includes only two products, but in a real scenario, there may be more than 100 products.

Purchase Table

Code	Confirmation Number	Trade Date	Unit Price	Quantity	Brokerage	GST	PurchaseCost
KLM	217151684	Friday, 26 November 2021	0.37	11555	18.14	1.81	4295.3
KLM	217246886	Friday, 3 December 2021	0.37	13459	18.14	1.81	4999.78
KLM	217735322	Friday, 21 January 2022	0.445	11191	18.14	1.81	4999.95
KLM	217741345	Friday, 21 January 2022	0.43	11717	18.14	1.81	5058.26
KLM	217802780	Tuesday, 25 January 2022	0.39	7641	18.14	1.81	2999.94
KLM	218207447	Monday, 28 February 2022	0.505	9764	18.14	1.81	4950.77
KLM	218433664	Thursday, 17 March 2022	0.5075	5676	18.14	1.81	2900.52
SDA	29254794	Friday, 20 March 2020	0.155	16533	18.14	1.81	2582.57
SDA	22	Monday, 18 January 2021	0.36	2176	0	0	783.36
SDA	213276727	Friday, 29 January 2021	0.915	2175	18.14	1.81	2010.08
SDA	219474324	Friday, 17 June 2022	2.06	4775	18.14	1.81	9856.45

Sales Table

Code	Confirmation Number	Trade Date	Unit Price	Quantity	Brokerage	GST	Total Sale Price
KLM	17425649	22-Dec-21	0.42	25014	18.14	1.81	10485.93
KLM	17736004	21-Jan-22	0.44	11191	18.14	1.81	4904.09
KLM	18081875	17-Mar-22	0.55	10000	18.14	1.81	5480.05
KLM	18095283	18-Mar-22	0.54	9358	18.14	1.81	5033.37
SDA	14227929	7-Apr-21	1.16	10000	18.14	1.81	11580.05
SDA	14339119	16-Apr-21	1.295	6530	22.68	2.27	8431.4
SDA	18573439	30-Mar-22	3.23	4354	18.14	1.81	14043.47

Profit Table

Code	Confirmation Number	Trade Date	Quantity	Total Sale Price	Purcahse Table Confirmation Number	Purchase Table Quantity	Purchase Cost	Overall Purchase Cost	Profit/Loss
KLM	17425649	22-Dec-21	25014	10485.93	(217151684,217246886)	(11555+13459)	(4295.3+4999.78)	9295.08	1190.85
KLM	17736004	21-Jan-22	11191	4904.09	217735322	11191	4999.95	4999.95	-95.86
KLM	18081875	17-Mar-22	10000	5480.05	(part of 217741345)	10000	(0.43*1000+18.14+1.81)	4319.95	1160.1
KLM	18095283	18-Mar-22	9358	5033.37	(part of 217741345, 217802780)	(1717+7641)	(0.43*1717+0+0)+2999.94	3,738.25	1295.12
SDA	14227929	7-Apr-21	10000	11580.05	part of 29254794	10000	(10000*0.155+18.14+1.81)	1569.95	10010.1
SDA	14339119	16-Apr-21	6530	8431.4	(part of 29254794)	6530	(6530*0.155+0+0)	1012.15	7419.25
SDA	18573439	30-Mar-22	4354	14043.47	(part of 29254794,22,213276727)	(3+2176+2175)	(3*0.155+0+0)+783.36+2010.08	2793.91	11249.56

[sa100]

Hi all, I'm eagerly anticipating guidance or a solution regarding my earlier post. thank you

[sa100]

Hi Albert @Anonymous 

Thanks for looking into this

Currently the relationship between Sales and Purchase table is many to many (code).

Otherway is to create a Master table by combining the Sales and Purchase table. Then we can have one to one relationship between Sales and Master; and between Purchase and Master (confirmation number)

Thank you

Regards

Sushil Arora

[Anonymous]

Hi @sa100 ,
I can understand what you mean, can you please provide the relationship between the sales table and the purchase table so that we can help you better

Best regards,

Albert He