Calculating Actual Revenue and Bonus Deductions with FIFO Method

[PhyuLayKhine333]

I have a member deposit table where customers make deposits, and we offer varying bonus percentages. For instance, if a customer deposits 1,000, we provide an additional 200, resulting in a total deposit of 1,200. When customers make purchases, they draw from this 1,200 balance. However, we must record the actual revenue and deduct the extra bonus amounts. Customers deposit at different times, each with a unique bonus percentage. To accurately record actual revenue, we employ the FIFO (First-In-First-Out) method.

For example, if a customer makes an initial deposit of 100,000 with a 20,000 bonus on the first date, followed by another deposit of 100,000 with a 30,000 bonus on the second date, and the customer consumes 130,000, the actual revenue and extra bonus amounts are distributed between the first and second dates. In this scenario, the actual revenue is calculated as follows: (120,000 * (100% / (100% + 20%))) + (10,000 * (100% / (100% + 30%))). The remaining deposit is 120,000 (including the bonus) and  (100,0000-(10,000*(100%/(100%+30%) (excluding the bonus). We want to represent this information with two columns. The calculation example is presented in the table below:

Member Deposit

Date	Transaction Number	Member Card	Deposit	Member Bonus Percentage	Member Bonus Amount	CT Rate	CT Amount	CT Due Date
1/1/2023		100001	100000	20%	20000	3%	3000
2/1/2023		100001	100000	30%	30000	3%	3000
1/2/2023		100002	50000	10%	5000	3%	1500

Member Sales

Sales Invoice	Sales Date	Member Card	Department	Month	CT Due Date	Description	Total Sales	Bank Account	Tax Rate
	2/1/2023	100001		2	3/10/2023		50000		5%
	3/1/2023	100001		3	4/10/2023		70000		5%
	4/1/2023	100001		4	5/10/2023		20000		5%
	4/1/2023	100002		4	5/10/2023		10000		5%

The final result i want to get;

Sales Invoice	Sales Date	Member Card	Department	Month	CT Due Date	Description	Total Sales	Bank Account	Tax Rate	Actual Sales	Remaining Balance(Include Member)	Remaining Balance(Exclude Member)	Member Discount
	2/1/2023	100000		2	3/10/2023		50,000		0.05	41,667	50,000	58,333	8,333
	3/1/2023	100000		3	4/10/2023		70,000		0.05	57,585	80,000	100,748	12,415
	4/1/2023	100001		4	4/10/2023		20,000		0.05	15,385	60,000	85,363	4,615
	4/1/2023	100002		4	5/10/2023		10,000		0.05	9,091	40,000	40,909	909

Could you please help me in solving this problem?

[lbendlin]

if a customer makes an initial deposit of 100,000 with a 20,000 bonus on the first date, followed by another deposit of 100,000 with a 30,000 bonus on the second date

wait, what?  Where did the 30% come from?  What happens when they make a third deposit?