Society maintenance Excel sheet, made for your flats

How to use the maintenance sheet

1. Fill in the names once. On the Collection sheet, add the owner's name and mobile number next to each flat. Put any money a flat owed before the register started under Old dues.
2. Record each payment twice, in a second. Write the amount under the month on the Collection sheet, and one line in Receipts with the receipt number you gave. For UPI, copy the reference number from the bank SMS.
3. Write every expense with its date. The Summary sheet picks up each expense by its date, so the monthly account fills itself.
4. Share the Summary every month. Collected, spent and the balance, month by month. It is the one page residents want to see. Our income and expenditure format shows how to present it.

What the formulas do

• Paid adds up a flat's payments across the twelve months.
• Due so far is the monthly amount times the months that have passed, plus old dues. The number of months sits at the top of the sheet and updates itself from today's date.
• Pending is due so far minus paid. Sort or filter on it to see who to remind.
• Summary takes each month's total from the Collection sheet, adds up the Expense book by date, and carries the balance forward from the opening balance you type.

Where Excel gets hard

A sheet works well for one careful treasurer. It gets hard when the treasurer is travelling, when two people edit two copies, when a resident says “I paid by UPI last week” and the bank statement shows only a name, or when the committee changes and the laptop does not. That is the point where most societies move to software, and the reason Aangan can read this sheet as it is.