Spreadsheets

Society maintenance collection tracker sheet for a small RWA

A treasurer's sheet for a thirty-flat society: flat-wise monthly receipts and arrears, an expense tab for security and repairs, and a monthly fund balance.

Editorial illustration for Society maintenance collection tracker sheet for a small RWA

A thirty-flat society can run its maintenance accounts on three tabs: a Collection grid with one row per flat and one column per month from April to March, an Expenses log for security, cleaning and repairs, and a Fund tab that works out each month's closing balance. Arrears come from one subtraction per flat.

What does the Collection tab look like?

ColumnHeadingWhat it holds
AFlat101, 102 and so on
BOwner or contactName only; keep numbers off shared copies
CMonthly due (₹)From the society's approved rate
D to OApril to MarchAmount received in each month
PTotal received=SUM(D2:O2)
QDue till date=C2*Settings!$B$1
RArrears (₹)=Q2-P2

Settings!B1 holds the number of months billed so far in the financial year. =MOD(MONTH(TODAY())-4,12)+1 returns 1 in April and 12 in March, or you can type the number yourself. Positive arrears mean the flat owes money; a negative number shows an advance paid.

How do I track the society's own expenses?

On the Expenses tab use Date in A, Head in B, Amount in C, Paid to in D and Voucher number in E. Heads might be Security, Housekeeping, Repairs, Electricity for common areas and Lift service. Give each payment a voucher number and keep the bill itself in a folder under the same number. Match every bank debit to a voucher.

How do I get the monthly fund balance?

On the Fund tab put the first of each month across row 1, from B1 for April. Collections are the column total from the Collection tab, so for April it is =SUM(Collection!D2:D31). Expenses use SUMIFS:

=SUMIFS(Expenses!$C$2:$C$500, Expenses!$A$2:$A$500, ">="&B$1, Expenses!$A$2:$A$500, "<"&EDATE(B$1,1))

Closing balance is opening balance plus collections minus expenses, and next month's opening is this month's closing.

What does one month look like with numbers?

Say the monthly due is ₹2,500 for each of 30 flats, so ₹75,000 is collectable. Twenty-seven flats pay, so ₹67,500 is collected. Expenses are security ₹28,000, cleaning ₹12,000 and repairs ₹6,500, a total of ₹46,500. With an opening balance of ₹52,000, the closing balance is 52,000 plus 67,500 minus 46,500, which is ₹73,000. The three unpaid flats show ₹7,500 of arrears for the month.

What should the treasurer do each month?

  1. Record every receipt against the right flat on the day it arrives, with the receipt number and mode: UPI, cheque or cash.
  2. Match the bank statement to the collection and expense tabs and make the closing balance equal the bank balance, after allowing for cheques not yet cleared.
  3. Mark the fully paid flats and prepare a short arrears list.
  4. Share a summary with residents, showing totals rather than flat-by-flat personal details unless your bye-laws say otherwise.

Handle part payments and advances plainly. If a flat pays ₹5,000 in June against two months of ₹2,500 dues, enter ₹5,000 in the June column; the arrears formula works on totals, so it still comes out right. For a cheque that bounces, subtract the amount from that month with a note, rather than deleting the original entry, so the history stays honest.

What are the limits of a sheet?

A sheet works well at this size, but it has no audit trail and anyone with edit access can overwrite figures. Keep one editor, lock the formula cells, and keep a dated backup each month. Rules for how an association keeps accounts, and audits, vary, so check your bye-laws and ask your auditor what format they expect. Before the year end, tidy the arrears list so each balance carries forward correctly into the next April column.

Keep a receipt book with numbered copies for cash and cheque payments, and enter the receipt number on the sheet, so a resident who disputes a payment can be answered in a minute.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports