Most treasurers build the spreadsheet on the first night after being elected and regret it by December. This guide builds it once, properly: in about twenty minutes, in Excel or Google Sheets, with formulas that keep the balance honest.
Step 1. Three sheets
- Ledger — one row per child, columns for payments.
- Expenses — one row per purchase.
- Summary — collected, spent, balance.
Step 2. The ledger
Columns: A — child, B — due (RUB), C — paid, D to O — individual payments, P — debt.
- Paid:
=SUM(D2:O2) - Debt:
=MAX(B2-C2,0)(never negative) - Paid flag:
=IF(P2=0,"paid","owes") - How many families have paid:
=COUNTIF(P2:P31,0)
Step 3. The per-child contribution
Put the budget in one cell and the number of children in another, then: =ROUNDUP(budget/children,-1). Rounding up leaves a small surplus that is easy to explain; a shortfall is not.
Step 4. Expenses
Columns: date, what, amount, who bought, link to the receipt photo. Total: =SUM(C2:C200).
Step 5. Summary
- Collected:
=SUM(Ledger!C2:C31) - Spent:
=SUM(Expenses!C2:C200) - Balance: collected minus spent
Step 6. Colour and protection
Conditional formatting with the rule =$P2>0 highlights debtors — but only in your own copy. The parents’ copy should show totals and receipts, not a list of names. Protect the formula cells so a stray click cannot overwrite them.
Step 7. Share without losing control
In Google Sheets: Share → anyone with the link can view. Edit rights belong to the treasurer alone. Keep a dated copy each month.
Why totals fail
- A number typed as text (“1 500 rub.”). Enter 1500 and format the currency.
- A blank row inside the range cuts the sum short.
- Merged cells break copying formulas down.
Or take the finished file
A ready file with formulas: the budget works out the per-child contribution, the payment log tracks who paid, the summary shows the balance and drafts a chat report. Includes a printable sheet with signature columns. Opens in Excel, Google Sheets and LibreOffice — free, no sign-up.
More on the sheets and how to hand over to the next committee: parents’ committee spreadsheet with downloadable Excel template. If you would rather not maintain a spreadsheet, DeliBabki keeps contributions, expenses and receipts in one shared group.
Frequently asked questions
How do I build a class treasurer’s spreadsheet in Excel from scratch?
Create three sheets: “Ledger” (who paid what), “Expenses” (what was spent) and “Summary” (collected, spent, balance). In the ledger the paid total is =SUM(D2:O2) and the debt is =MAX(B2-C2,0); on the summary the balance is collected minus spent. Every step with formulas is above.
Which formulas does a treasurer need?
Five are enough: SUM (paid and spent), MAX (debt never goes negative), IF (a “paid / not paid” flag), COUNTIF (how many families paid) and ROUNDUP (a round per-child contribution).
How do I work out the per-child contribution?
Divide the budget by the number of children and round up to the nearest ten roubles: =ROUNDUP(budget/children,-1). Rounding up leaves a small surplus, which is easier to show in a report than a shortfall.
Excel or Google Sheets for a parents’ committee?
Google Sheets is easier: you can share it as view-only and parents always see current figures. Excel is simpler for printing a signed ledger. Formulas are almost identical; only the argument separator differs: “;” in Russian-locale Excel and “,” in English.
Why does my total not calculate?
Most often a number is stored as text: “1 500 rub.” instead of 1500. Enter a plain number and add the currency through cell formatting. Other causes are a blank row inside the range or merged cells.
No time to maintain a spreadsheet?
Everyone sees their own balance in a shared group. Free, no sign-up.
Create Free Group 🚀