How to write a treasurer's monthly report
Short answer: Keep one register of every deposit and payment, then build a monthly report from it with SUMIFS: opening balance, money in, money out and closing balance. Finish with a bank match: statement balance, plus deposits not yet cleared, minus payments not yet cleared, should equal your closing balance, so the difference reads 0.00.
Who it is for: volunteer treasurers of a PTA, PTO, booster club, class, club or small group who report to a board or members each month, and keep the records in a spreadsheet.
It is not the right fit if you need:
- payroll, sales tax or tax filing;
- several bank accounts merged into one report;
- an automatic bank feed;
- someone to decide how your group's money should be spent.
This is a record-keeping method. It adds up and presents what your group already did; it is not accounting, tax or legal advice. Your group's own rules and bylaws come first.
What the report must answer
Members usually ask four things: how much came in, how much went out, what we have now, and does that match the bank. A good monthly report answers those four in the first lines, and the detail follows.
Step 1: One register for every transaction
Make a sheet called Register with these headings in row 1:
| Column | Heading | Example |
|---|---|---|
| A | Date | 3 Oct 2026 |
| B | Type | Income or Expense |
| C | Category | Fundraiser, Dues, Supplies, Events |
| D | Description | Fall book fair takings |
| E | Amount | 640.25 |
| F | Cleared bank? | Yes or No |
Rules that save trouble later:
- Every amount is positive. The Type column says which way the money moved.
- Use a dropdown for Type and Category (Data > Data validation), so "Fundraiser" is never typed two ways.
- One row per deposit or payment, matching what the bank shows, not one row per child or per item.
Step 2: The monthly report
Make a sheet called Report. In B1 type the first day of the month you are reporting, for example 1 Oct 2026. In B2 put the last day of that month:
=EOMONTH(B1,0)
Then the four answers:
| Cell | Label | Formula |
|---|---|---|
| B4 | Opening balance | type last month's closing balance |
| B5 | Money in this month | =SUMIFS(Register!E:E,Register!B:B,"Income",Register!A:A,">="&B1,Register!A:A,"<="&B2) |
| B6 | Money out this month | =SUMIFS(Register!E:E,Register!B:B,"Expense",Register!A:A,">="&B1,Register!A:A,"<="&B2) |
| B7 | Closing balance | =B4+B5-B6 |
Step 3: The bank match
The closing balance in your books and the balance on the bank statement are rarely the same number on the same day, because some cheques and deposits have not cleared yet. The bank match shows the difference is explained.
| Cell | Label | Formula |
|---|---|---|
| B13 | Bank statement balance | type it from the statement |
| B14 | Deposits not yet cleared | =SUMIFS(Register!E:E,Register!B:B,"Income",Register!F:F,"No",Register!A:A,"<="&B2) |
| B15 | Payments not yet cleared | =SUMIFS(Register!E:E,Register!B:B,"Expense",Register!F:F,"No",Register!A:A,"<="&B2) |
| B16 | Adjusted bank balance | =B13+B14-B15 |
| B17 | Difference | =B16-B7 |
When B17 is 0, the books and the bank agree. If it is not 0, look for a transaction missing from the register, a typing slip in an amount, or a bank fee you have not entered yet.
Step 4: A line for each category
For the detail under the bank match, list your categories in A20 downward and put this in B20, then fill down:
=SUMIFS(Register!E:E,Register!C:C,A20,Register!A:A,">="&$B$1,Register!A:A,"<="&$B$2)
That gives the board a line for each fundraiser and each kind of cost without anyone adding by hand.
An example (made-up figures)
Opening balance 1,000.00. In October: book fair takings 640.25 (cleared), member dues 150.00 (not cleared yet), supplies 200.00 (cleared), fall social 75.00 (not cleared yet). The bank statement shows 1,440.25.
| Cell | Label | Result |
|---|---|---|
| B5 | Money in this month | 790.25 |
| B6 | Money out this month | 275.00 |
| B7 | Closing balance | 1,515.25 |
| B14 | Deposits not yet cleared | 150.00 |
| B15 | Payments not yet cleared | 75.00 |
| B16 | Adjusted bank balance | 1,515.25 |
| B17 | Difference | 0.00 |
We typed this example into a Google Sheet with the formulas above, and these are the results it gave.
Step 5: Present it
- Put the four answers and the bank match at the top, in that order.
- Print the Report sheet on one page (File > Print, Fit to width).
- Keep each month's report as a PDF in the group's shared folder, so the next treasurer has the history.
Before the meeting, run down the free treasurer monthly report checklist: seven steps on one printable page, from gathering the statements to agreeing the closing balance with the bank.
Common slips
- A date typed as text (for example with dots instead of slashes) is skipped by SUMIFS. Format column A as a date (Format > Number > Date).
- An amount with a minus sign in the register counts backwards. Keep amounts positive and use Type.
- Opening balance typed wrong. It must equal last month's closing balance; check it first when the bank match is off.
Questions people ask
What should a treasurer's monthly report include?
Four answers first: how much came in, how much went out, what the group has now, and whether that matches the bank. The detail by category follows underneath.
Why doesn't my balance match the bank statement?
Usually because some checks or deposits have not cleared yet. The bank match adds the deposits not yet cleared and takes away the payments not yet cleared; when the difference is 0.00 the books and the bank agree.
I don't want to mess up other people's money. How do I catch mistakes?
Keep every amount positive and use a Type column for Income or Expense, use dropdowns for the categories, and read the bank match difference before every meeting. A date typed as text is the most common slip.
Do I need accounting software?
No. For a PTA, PTO, booster club or small group, one spreadsheet with a register, a monthly report and a bank match is enough. It is a record-keeping method, not accounting, tax or legal advice.
If you would rather not build it yourself
We make treasurer report workbooks for PTAs, PTOs, booster clubs and other groups that do all of the above, with a Row check that names any line that does not count, a printable monthly report and a bank match, tested in Microsoft Excel and Google Sheets. The method above works on its own, and it is yours to use either way.
See the PTA and PTO Treasurer Kit
Next: handing over the books, a checklist for when you leave the role or take it on.