PTA Funds Tracker Excel - Free Template

1. Open the Instructions & Lists sheet and review the editable transaction types, categories, funds, payment methods, reconciliation options, VAT rates and planning references.
2. Go to Transactions and enter a unique Transaction ID, date, description and transaction type for each receipt or payment.
3. Select the relevant category, fund and payment method from the available lists. Add the bank, cash, card or cheque reference so the entry can be traced later.
4. Enter the amount in either Income (£) or Expenditure (£), leaving the other amount empty or at zero. Do not overwrite the calculated Net Movement (£) or VAT Amount (£) cells.
5. Choose the applicable VAT Rate and record who approved the transaction. Mark Reconciled? as Yes only after checking the supporting record against the PTA bank statement or cash documentation.
6. Use the Summary & Dashboard sheet to review total income, expenditure, net funds movement, opening balance, closing balance, average transaction value and unreconciled entries.
7. Check the monthly summary and fund balances before a committee review, then use Notes to record explanations for unusual receipts, payments or corrections.

A PTA treasurer can use this workbook after each fundraising event, bank payment or cash collection. It suits a primary or secondary school committee that needs one transaction record rather than separate notes for events, grants, uniform sales and trips.

Useful after fundraising events

Suppose a Christmas fair produces £1,245 of income on 08/01/2026. The treasurer can enter the event in Transactions, select Income, choose Events and allocate it to General Fund. A later £385 uniform sale can be recorded separately, making it possible to distinguish two sources of money instead of combining them in a monthly total.

That distinction matters when several volunteers handle money. The workbook records Payment Method as Bank Transfer, Card, Cash or Cheque, while Reference provides a place for an event code such as CF-26-01 or US-2601. Approved By and Reconciled? add a practical handover between the volunteer collecting money, the person approving expenditure and the person checking the records.

Useful for restricted activities

A committee planning playground improvements can record a hypothetical £2,500 grant against Playground Fund rather than General Fund. A £1,100 playground supplier payment would then leave £1,400 from that fund before considering other entries. The Fund field keeps that activity separate from a Year 6 Leavers Fund or Trip Fund.

At a weekly meeting, the treasurer can review recent entries and outstanding No values in Reconciled?. At month-end, the Summary & Dashboard sheet provides January to December rows, allowing the committee to compare a particular month’s income and expenditure with the wider year.

Useful for committee reporting

The dashboard is aimed at a committee that needs figures quickly: total income, total expenditure, net funds movement, closing balance, average transaction value and number of transactions. Its three charts use the monthly figures, fund income and current fund balances, so the meeting can discuss movement by month and purpose without rebuilding a report.

The Transactions sheet separates information you type from outputs calculated by Excel. You enter Income (£), Expenditure (£) and VAT Rate; the workbook calculates Net Movement (£) in column K and VAT Amount (£) in column M for rows 4 to 103.

Input controls that reduce drift

Type and status fields use lists rather than unrestricted text. Type is limited by the list containing Income and Expenditure; Fund uses General Fund, Playground Fund, Year 6 Leavers Fund and Trip Fund; Payment Method uses Bank Transfer, Card, Cash and Cheque. This is a sound design choice because General Fund and general fund would otherwise become separate labels in a summary.

Category also uses a controlled list, including Events, Donations, Grants, Uniform Sales, Refreshments, Equipment, Trips and Insurance. Reconciled? uses Yes or No. Amount entry in I4:J103 is restricted to decimal values from zero, which helps prevent text such as £100 cash from entering a calculation field.

What the formulas do

For a hypothetical £600 income and £125 expenditure on one row, column K returns £475 because its formula subtracts expenditure from income. If the row has no Transaction ID, the formula returns a blank, keeping unused rows clear.

VAT Amount uses expenditure multiplied by the selected VAT Rate when expenditure is above zero; otherwise it returns zero. For example, £200 expenditure at the illustrative 5% list value gives £10.00, while £200 at 0% gives £0.00. Treat the listed rates as workbook inputs and confirm any treatment separately rather than treating the template as tax advice.

How the summary is built

Summary & Dashboard totals the Transactions ranges with SUM and uses COUNTIF for transaction and unreconciled counts. Its monthly rows use date boundaries, while the fund section uses Fund values to total income and expenditure. A current balance of £900 is labelled On track when it is above zero and Review when it is not; that label is a workbook check, not a financial judgement.

The most damaging problem in a PTA record is often not a complex formula but an incomplete row. If a £450 trip payment is entered in Expenditure but the Fund field is left blank or assigned to General Fund, the overall expenditure total may still be correct while the Trip Fund balance is wrong. The committee could then approve spending against an inaccurate fund position.

Mixed amounts distort net movement

Each row is intended to represent one transaction, with income and expenditure in separate columns. If a volunteer enters £900 in both columns for a transfer that should have been recorded once, Net Movement becomes £0 rather than the intended £900 movement. On 12 similar entries, that could hide £10,800 of movement in a clearly hypothetical example.

Do not replace the formulas in K4:K103 or M4:M103 with typed figures. If a calculated £475 net movement is overwritten with £500, the row may look plausible but no longer agrees with the £600 income and £125 expenditure behind it. Correct the source amounts instead.

Unreconciled cash is easy to overlook

Cash and cheque entries need a reference and a clear reconciliation decision. Imagine 15 transactions in a month, including three cash collections totalling £620. If those three rows remain marked No, the dashboard’s unreconciled count identifies the issue; changing them to Yes without checking the supporting record removes the warning without resolving the difference.

Wrong dates change the monthly view

The monthly summary uses date ranges for January through December 2026. A £300 event receipt dated 31/03/2026 appears in March, but a mistyped date of 01/04/2026 moves it to April. That can change two monthly totals and the cumulative closing balance even though the annual total remains unchanged.

Make the Transactions sheet part of the PTA’s existing money-handling routine rather than a task saved for the end of term. A treasurer might enter receipts every Friday, update expenditure after the weekly approval meeting and reconcile the bank records before the monthly committee pack is prepared.

Use a fixed review sequence

Work from the source documents in the same order each time: assign the next Transaction ID, enter the date and description, select the fund and category, add the amount, then complete approval and reconciliation. For example, reviewing 12 monthly entries in one sitting is more manageable when each row follows the same sequence.

• Keep descriptions specific, such as Playground contractor payment, rather than Payment.
• Use the Reference field for event, grant, invoice or banking identifiers.
• Review every Reconciled? value marked No before presenting the summary.
• Check that income and expenditure are not both populated for the same ordinary transaction.

Maintain the supporting lists

When the PTA introduces a new activity, update Instructions & Lists and use the relevant list consistently. Planning references such as Events £1,800, Donations £1,200 and Grants £2,500 can provide a committee discussion point, but they are not connected to the transaction formulas in the manifest, so compare them manually with actual results.

Save a dated copy after each month-end review and restrict formula editing to the person maintaining the workbook. The Transactions sheet has 100 prepared rows, so review the remaining capacity before adding a new term’s records.

Recognise when Excel is no longer enough

Consider a more formal finance system when several people edit the file simultaneously, the 100-row capacity is regularly exceeded, or the PTA needs audit trails, bank-feed matching or more detailed permissions. Until then, consistent entry, documented references and a fixed monthly review make this workbook easier to hand over when committee roles change.