Profit Calculator

[richard alfred]

Hi

I am trying to create an Excel sheet that can calculate the monthly net profit for a product that I am selling, however, I purchase the item with different quantities and different prices each time.
Below is the table demonstrating the value of the product bought and sold. can anyone help me with the function to solve this problem?

In QTY	Unit Price	Total Price	Month	Sold QTY	Sold Price	Profit
12	65	780	11/2022	1	98
59	74.6	4400	12/2022	9	869
500	85	42500	1/2023	113	13585
200	78	15615	2/2023	338	44142
400	70	28000	4/2023	102	8275
117	68	7956	5/2023	190	16644
				82	9889

 

[awoohaw]

this seems pretty straightforward. But, I suspect you may have left something out.
Are the products different (do you have multiple rows for multiple products)?

Mr excel questions 39.xlsm
	A	B	C	D	E	F	G
1	In QTY	Unit Price	Total Price	Month	Sold QTY	Sold Price	Profit
2	12	65.00	780.00	11/2022	1.00	98.00	-682.00
3	59	74.60	4,400.00	12/2022	9.00	869.00	-3,531.00
4	500	85.00	42,500.00	01/2023	113.00	13,585.00	-28,915.00
5	200	78.00	15,615.00	02/2023	338.00	44,142.00	28,527.00
6	400	70.00	28,000.00	04/2023	102.00	8,275.00	-19,725.00
7	117	68.00	7,956.00	05/2023	190.00	16,644.00	8,688.00
8					82.00	9,889.00
Sheet2

Cell Formulas
Range	Formula
G2:G7	G2	=F2-C2

 

[AhoyNC]

See if this link on using Excel for FIFO helps.

FIFO Calculator — Excel Dashboards VBA

A FIFO calculator using Excel and VBA to calculate cost of goods sold based on fist in first out (fifo) methodology.

www.thesmallman.com

 

[richard alfred]

1	USB Stick
NO:	IN	Sales
In Date	Location	In QTY	Unit price	In Price	Out Date	Out QTY	Sold Price	Profit
1	11/23/2022​	ABC	12	65	780	OTHER			0
2	5/1/2023​	BXD	59	74.58	4400	1/8/2022​	0	0	0
3	23/1/2023	Tara	500	85	42500	1/9/2022​	0	0	0
4	14/2/2023	Extra store	200	78.08	15615	1/10/2022​	0	0
5	20/2/2023	Safwa	400	70	28000	1/11/2022​	1	98.1
6	5/13/2023​	Center	117	68	7956	1/12/2022​	9	869.2
7					0	1/1/2023​	113	13585.6
8					0	1/2/2023​	338	44142.7
9					0	1/3/2023​	102	8275.5
10					0	1/4/2023​	190	16644.7
11					0	1/5/2023​	85	10295.1
12					0	1/6/2023​	0	0
13					0	1/7/2023​	0	0	0
14					0	1/8/2023​	0	0	0
15					0	1/9/2023​	0	0	0
16					0	1/10/2023​	0	0	0
17					0	1/11/2023​	0	0	0

 

[richard alfred]

1	USB Stick
NO:	IN	Sales
In Date	Location	In QTY	Unit price	In Price	Out Date	Out QTY	Sold Price	Profit
1	11/23/2022​	ABC	12	65	780	OTHER			0
2	5/1/2023​	BXD	59	74.58	4400	1/8/2022​	0	0	0
3	23/1/2023	Tara	500	85	42500	1/9/2022​	0	0	0
4	14/2/2023	Extra store	200	78.08	15615	1/10/2022​	0	0
5	20/2/2023	Safwa	400	70	28000	1/11/2022​	1	98.1
6	5/13/2023​	Center	117	68	7956	1/12/2022​	9	869.2
7					0	1/1/2023​	113	13585.6
8					0	1/2/2023​	338	44142.7
9					0	1/3/2023​	102	8275.5
10					0	1/4/2023​	190	16644.7
11					0	1/5/2023​	85	10295.1
12					0	1/6/2023​	0	0
13					0	1/7/2023​	0	0	0
14					0	1/8/2023​	0	0	0
15					0	1/9/2023​	0	0	0
16					0	1/10/2023​	0	0	0
17					0	1/11/2023​	0	0	0

I attached a picture containing more data, I think it has to do something with FIFO as

AhoyNC mentioned​

 

[AhoyNC]

Take a look at these 2 videos (Part 1 & Part 2) and see if this is what you want.

 

[richard alfred]

See if this link on using Excel for FIFO helps.

FIFO Calculator — Excel Dashboards VBA

A FIFO calculator using Excel and VBA to calculate cost of goods sold based on fist in first out (fifo) methodology.

www.thesmallman.com

Thank You AHOYNC this VBA solved my problem unfortunately the VBA doesn't work on my online sheet.