VThe Vera Standard

What is a simple spreadsheet layout for the treasurer's books?

Many small organizations keep their books in a spreadsheet. A few clear sheets, the same columns every month, and a check against the bank are enough.

Short answer: Use one sheet for money in, one for money out, one for fund balances and one for the monthly bank check. Give each row a date, who, what, fund, amount and a reference. Add formulas for totals, lock old months, back up each month, and have a second person look at the checks.

This is not tax, legal or accounting advice. It describes common practice. Where a rule depends on law, ask your adviser, and follow your own organization’s rules and your bank’s requirements.

The steps

  1. Start with a blank file for the year

    Name it clearly with the organization and the year. Keep last year's file closed and in the archive.

  2. Make the money-in sheet

    Columns: date, source, fund, method, reference (deposit slip or report), amount. One row per gift or deposit line.

  3. Make the money-out sheet

    Columns: date, paid to, what for, budget line, fund, method, check number, amount, approved by.

  4. Make a list of funds and budget lines

    A small sheet listing every fund and budget line, so the others use the same words.

  5. Use drop-down lists

    Use drop-down lists for fund and budget line so spelling is consistent. Mistakes in spelling split totals.

  6. Add totals

    Use simple sum formulas by month and by fund. Keep formulas easy to read.

  7. Make a fund balance sheet

    For each fund: opening balance, money in, money out, closing balance. Check that the funds add up to the bank.

  8. Add the bank check sheet

    Bank balance, plus deposits not yet in, minus checks not yet cleared, compared with your balance. See monthly bank reconciliation.

  9. Keep a notes column

    Use it to record anything unusual, such as a refund or a correction.

  10. Never delete a row

    If you make a mistake, add a correcting entry with a note. Old rows stay so the trail is clear.

  11. Save and back up each month

    Save a copy named with the month. Keep another copy on a separate drive or service the organization controls.

  12. Share view access

    Give one other officer view access to the file, and ask them to check it against the bank each month.

A tiny money-in sheet (illustration, not real data)

DateSourceFundRefAmount
5 JanSunday countGeneralSlip 101$900.00
5 JanSunday countMissionsSlip 101$152.00

Protect the file

Keep a password on the file or the folder, and know who has it. Do not email the whole file around. Keep giver names out of any copy the board sees.

When a spreadsheet is not enough

As the organization grows, a spreadsheet can become hard to check. If errors are common or the treasurer is overwhelmed, ask the board whether it is time for something better.

Check your formulas

Once a quarter, add up a column by hand and compare it with the formula. A small check finds the broken formula before it matters.

Handing over the file

When the treasurer changes, the spreadsheet is part of the handover. Include a short page that explains each sheet, what the columns mean, and how the monthly check works. Hand over the password through the board and not by email. Keep the archive of past years in the same place, labeled by year, so the new treasurer can look back when a question comes up from the board or a funder. Test the instructions by asking someone else to follow them once.

Questions people ask

Which spreadsheet program should we use?

Any that you and your successors can use. Choose one that several people already know.

Can we keep the file in the cloud?

Many do. Use an account the organization owns, give each person their own login, and ask your adviser about any rule on records.

How do we stop mistakes?

Use drop-down lists, check totals against the bank, and have a second person review it monthly.

Related guides