Ratio wise R.A. and Final Bills payment amount calculations using Excel

yogendra borse

Current Issues

    Given the complexity of managing pending bills and the distribution of funds, which often arrive in multiple installments, it is imperative to ensure equilibrium across various projects and payments to contractors. To achieve this equilibrium, a prudent approach involves allocating funds proportionately based on the ratio of received funds to outstanding bills.

    Simultaneously, it's crucial to focus on bills that are nearing completion and can be concluded with necessary adjustments. This goal can be accomplished by meticulously analysing the existing bills, utilizing user-friendly tools like Microsoft Excel to facilitate the process.

    Within this article, my endeavour is to elucidate both the methodology of calculating contractor payments in proportion to the funds received, while also addressing the finalization of works that have reached at Final Bill stage and are amenable to conclusion.

Illustrative Example

Grand Received =					900
Expected Payment =					8118.5
Final bill payments =					440.5
R.A. Bill Payments =					7678
Grand for R.A. Bills =					459.5
Sr. No.	Work Name	Work cost	Expenditure	Demand	Bill Stage	Bill to be given
1	A	550	275	275		16.457
2	B	592	296	296		17.714
3	C	160	80	80	Final	80
7	G	748	374	374		22.382
8	H	705	352.5	352.5		21.095
9	I	515	257.5	257.5		15.410
10	J	970	485	485		29.025
11	K	178	89	89	Final	89
12	L	863	431.5	431.5		25.823
13	M	851	425.5	425.5		25.464
14	N	185	92.5	92.5	Final	92.5
15	O	414	207	207		12.388
16	P	815	407.5	407.5		24.387
17	Q	935	467.5	467.5		27.978
18	R	781	390.5	390.5		23.369
19	S	394	197	197		11.789
20	T	813	406.5	406.5		24.327
21	U	509	254.5	254.5		15.230
22	V	181	90.5	90.5	Final	90.5
23	W	177	88.5	88.5	Final	88.5
24	X	994	497	497		29.743
25	Y	717	358.5	358.5		21.454
26	Z	841	420.5	420.5		25.165
27	[	808	404	404		24.177
28	\	697	348.5	348.5		20.856
29	]	844	422	422		25.255

 Sum of Bills to given is 900 which is equal to grand received, at the same time Bills at Final stage are paid full bill to finalise the work.

Formulas Used

Column Information :

Sr. No.              = A

Work Name      = B; Filled Manually

Work cost         = C; Filled Manually

Expenditure      = D; Filled Manually

Demand            = E; Filled Manually

Bill Stage          = F; Filled Manually

Bill to be given = G ; obtained from formula

Formulas :

Grand Received = To be filled Manually

Expected Payment = SUM(E8:E75) 

Final bill payments = SUMIF($F$8:$F$70,"Final",$E$8:$E$70)

R.A. Bill Payments = C3-C4

Grand for R.A. Bills = C2-C4

Bill to be given =IF(F8="Final",E8,($C$6/$C$5)*E8)

        Where, 

                    F8          --> Corresponding Bill Stage

                    E8          --> Corresponding Bill Demand

                    $C$6      --> Grand for R.A. Bills (Calculated from above formula)

                    $C$5      --> Pending R.A. Bill Payments (Calculated from above formula)