Managing Received amount in FIFO method

[NaushadAnwar]

Hello Everyone,
we are using simple method of Billing but now we need to add cost based received payment analysis. Scenario is that we issued a bill to company amounting to Rs. 9262/- which included 10% amount (Profit) & COGS sales cost. we need to separate the marginal 10% amount from payments to check how much the company paid billed amount (COGS) and how much marginal profit (Profit). companies also pay lumpsum amount not bill to bill. some calculations have been done in last of the sheet to separate cogs and profit from unit amount but i am confused
with breaking payment amount and in last the balance amount.
pls help.

 

[NaushadAnwar]

Hello everyone help?

 

[NaushadAnwar]

help pls

 

[NaushadAnwar]

water.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	Bill Period	Billed Amt	LPS	Total Bill Amt	Received Amount	10% Service Charges	Water 80%	Sewarage 25%	Conservancy 25%	Fire 30%	Billed (F:K)	Bill cost (G+H)	Bill Profit (C+F+I+J)	Received Payment Breakup Cost	Received Payment Breakup Profit	Balance
3
4	Opening
5	31-Jul-21	12,953		12,953		1,178	6,542	1,635	1,635	1,963	12,953	8,177	4,775					0.00	1177.51	6541.70	1635.42	1635.42	1962.51
6	31-Aug-21	11,419	194	11,613	24,372	1,038	5,767	1,442	1,442	1,730	11,419	7,209	4,404					194.29	1038.11	5767.26	1441.81	1441.81	1730.18
7	30-Sep-21	36,557	3	36,560	36,754	3,323	18,463	4,616	4,616	5,539	36,557	23,079	13,481					0.00	0.00	0.00	0.00	0.00	0.00
8	31-Oct-21	40,057	551	40,609	40,609	3,642	20,231	5,058	5,058	6,069	40,057	25,289	15,320					0.00	0.00	0.00	0.00	0.00	0.00
9	30-Nov-21	58,854	609	59,463	59,463	5,350	29,724	7,431	7,431	8,917	58,854	37,155	22,308					0.00	0.00	0.00	0.00	0.00	0.43
10	31-Dec-21	25,797	892	26,689		2,345	13,029	3,257	3,257	3,909	25,797	16,286	10,403					891.95	2345.22	13028.97	3257.24	3257.24	3908.69
11	31-Jan-22	16,853	400	17,253	43,942	1,532	8,511	2,128	2,128	2,553	16,853	10,639	6,614					0.00	0.00	0.00	0.00	0.00	0.00
12	28-Feb-22	7,389	0	7,389		672	3,732	933	933	1,120	7,389	4,665	2,724
13	31-Mar-22	7,648	111	7,759	15,149	695	3,863	966	966	1,159	7,648	4,829	2,931
14	30-Apr-22	35,001	227	35,229	35,228	3,182	17,678	4,419	4,419	5,303	35,001	22,097	13,132
15	31-May-22	34,094	579	34,673		3,099	17,219	4,305	4,305	5,166	34,094	21,524	13,149
16	30-Jun-22	19,186	520	19,706	54,328	1,744	9,690	2,422	2,422	2,907	19,186	12,112	7,594
17	31-Jul-22	11,538	816	12,353		1,049	5,827	1,457	1,457	1,748	11,538	7,284	5,069
18	31-Aug-22	8,221	186	8,407		747	4,152	1,038	1,038	1,246	8,221	5,190	3,217
19	30-Sep-22	8,891	312	9,204		808	4,491	1,123	1,123	1,347	8,891	5,613	3,590
20	31-Oct-22	7,621	446	8,067	38,082	693	3,849	962	962	1,155	7,621	4,811	3,255
21		342,080	5,847	347,927	347,927	31,098	172,768	43,192	43,192	51,830	342,080	215,960	131,967
Sheet1 (2)

Cell Formulas
Range	Formula
F5:F20	F5	=B5/(1.1)/10
G5:G20	G5	=SUM(B5-F5)/1.8
H5:H20	H5	=G5*0.25
I5:I20	I5	=G5*0.25
J5:J20	J5	=G5*0.3
K5:K20	K5	=SUM(F5:J5)
L5:L20	L5	=G5+H5
M5:M20	M5	=C5+F5+I5+J5
R5:R6	R5	=MAX(MIN(0,SUM($C5:C5)-$E5),C5)
S5:W6	S5	=MAX(MIN(0,SUM(C6,$F6:F6)-$E5),F5)
R7:R11	R7	=MIN(MAX(0,SUM($C7:C7)-$E7),C7)
S7:S11	S7	=MIN(MAX(0,SUM(C7,$F7:F7)-$E7),F7)
T7:W11	T7	=MIN(MAX(0,SUM($C7,$F7:G7)-$E7),G7)
D5:D20	D5	=B5+C5
B21:M21	B21	=SUM(B5:B20)