This guide adapts the reconciliation process shared by David Ducharme, a charity treasurer and financial planner, during a live Zeffy webinar. We have rewritten it for UK charity treasurers, with Gift Aid, £ figures, and Charity Commission / OSCR / CCNI reporting in mind.
Your Zeffy payout hits your bank account on schedule. The total is correct. But your bookkeeper needs to know: how much of that deposit is merchandise revenue, how much is unrestricted donations, and how much is restricted to the youth programme's emergency fund? And your trustees need it broken down cleanly enough to feed straight into the annual return and Trustees' Annual Report.
The payout report tells you the 'what'. This guide shows you how to get the 'where': a fully itemised, chart-of-accounts-ready breakdown that takes about 20 to 30 minutes per month using nothing but Excel or Google Sheets.
Table of contents
What you'll need
- Access to your Zeffy dashboard (Finances and Payments tabs)
- Your organisation's chart of accounts (or a list of fund categories)
Step 1: Download your payout report
Start in Zeffy by navigating to the Finances tab in the left-hand menu. You will see a list of all payouts your organisation has received.
Click on the specific payout you want to reconcile to open the payout details page. Then click Export to download the report.
This payout report gives you the high-level view: each transaction included in the payout, the donor's name, a transaction ID, and the total amount per transaction. What it does not give you is the line-item detail. If a donor bought a t-shirt and added a £20 donation at checkout, you will see one total, not the breakdown.
Keep this file open. You will come back to it later to check for refunds.
Pro tip: Use Excel's 'select all, then double-click column border' shortcut to auto-fit all column widths. It makes the data much easier to scan.
Step 2: Download your itemised payments report
Now go to the Payments tab in Zeffy. This is where the detail lives.
Before exporting, set your date range wider than you think you need. If you are reconciling a 3 January payout, download data from November through January. Transactions that occurred in late November or December may have been included in this payout, and you want to catch everything.
Click Export in the top right corner. When prompted to choose your export type, select Itemised Payments, not the regular payments report. This is the version that breaks each transaction into individual line items (the t-shirt, the donation, the event ticket).
Select any additional fields relevant to your organisation: donor contact information, custom questions, payment methods, and so on.
Step 3: Filter the data in your spreadsheet
Open the itemised payments export in Excel or Google Sheets. Select the header row and add filters (in Excel: Data, then Filter; in Google Sheets: Data, then Create a filter).
Apply three filters in this order.
Filter 1: Payment method
Remove any payment methods that Zeffy does not pay out. Deselect cheque, cash, and free transactions. These may exist in your data, but they were not part of the electronic payout you are reconciling. If someone posted you a cheque last month, it is in your records but not in this deposit.
Filter 2: Payout date
Select only the payout date you are reconciling. If you are working on the 4 January payout, select only '4/1' (or however the date appears in your export). This is why you cast a wide net with your date range: the filter handles the precision.
Filter 3: Cancelled tickets
Deselect any rows marked as cancelled. This is especially important with recurring annual memberships, which can sometimes generate two entries: one cancelled and one approved. Without removing the cancelled entry, you would count that amount twice.
After applying all three filters, your spreadsheet should show only the transactions that were included in this specific payout, with only the payment methods Zeffy processes, and no duplicate or cancelled entries.
Step 4: Clean and consolidate your data
This is the step that makes the pivot table work. Your goal is to create two clean columns.
- One column with a title for every line item (what the money was for)
- One column with a £ amount for every line item (how much)
The challenge is that donation forms and sales forms store data in different columns.
For sales forms (ticketing, merchandise, events)
These already have a rate title (the name of the item) and an item amount (the price). No changes needed.
For donation forms
Donation forms do not have a rate title or item amount because there is no 'item' being sold. Instead, you will see blank fields in those columns, with the £ amount in the eligible amount column.
Here is how to fix it.
- 1. Filter the rate title column to show only blanks.
- 2. Look at the campaign title column for those rows. It will say something like 'General Donation to Support the Alumni Association'.
- 3. Copy the campaign titles and paste them into the rate title column for those rows. Now every row has a name.
- 4. Do the same for amounts: copy the eligible amount into the item amount column for any rows where item amount is blank.
For auction forms
Auction items have a unique issue: the item amount is the starting price, but the donor paid a different (usually higher) amount after bidding. Filter by the auction campaign name, then replace the item amount with the total amount paid so your numbers reflect actual revenue.
For silent-auction or gala items, the whole amount paid is income to the charity. For Gift Aid purposes, only the portion above the item's fair value can potentially qualify, and only if the donor-benefit rules are met. Refer to HMRC's Gift Aid guidance for the benefit-rule thresholds rather than trying to apply them at this stage.
Flag Gift Aid eligibility as you go
Adding a 'Gift Aid eligible (Y/N)' column at this consolidation stage lets you split the payout into (a) Gift-Aid-eligible donation principal and (b) non-eligible revenue such as ticket sales, raffle tickets, auction lots at fair value, and merchandise. Under HMRC Gift Aid rules, the charity reclaims 25p for every £1 donated by a UK taxpayer who has signed a Gift Aid declaration. Payments for goods or services do not qualify. Flagging eligibility now makes your monthly HMRC Charities Online claim materially faster and prevents over-claiming, which is a common trustee-liability risk. Supporting technical detail is available from the Charity Tax Group.
Verify your total
Select all the values in your item amount column. Check the sum in the bottom-right corner of Excel (or use =SUM in Google Sheets). This number should match your Zeffy payout total exactly. In David's example, the payout was £925, and the itemised total also came to £925.
If the numbers do not match, you likely have a row with a missing amount or an extra row that should have been filtered out. Go back and check your filters.
Step 5: Build a pivot table
Now that your data is clean, it is time to summarise it. First, copy only the visible (filtered) data to a new sheet. This gives you a clean, uninterrupted list that the pivot table can work with.
- 1. Select all visible data in your filtered sheet (Ctrl+A or Cmd+A).
- 2. Copy it (Ctrl+C / Cmd+C).
- 3. Create a new sheet tab at the bottom of your workbook.
- 4. Paste (Ctrl+V / Cmd+V). You should see a clean, continuous list with no hidden rows.
Create the pivot table
- 1. Select all the data on your new clean sheet.
- 2. Go to Insert, then Pivot Table.
- 3. Keep the default setting to place it on a new worksheet.
- 4. In the pivot table field list on the right side of your screen, select fields in this order: Campaign Title (this becomes your top-level grouping), Rate Title (this nests under each campaign), and Item Amount (this goes into the Values area).
Important: The order you select fields matters. Campaign title should be the outermost grouping, with rate titles nested inside it. If the hierarchy looks wrong, drag the fields to reorder them in the Rows area.
Check the value calculation
By default, the pivot table may count items instead of summing them. If you see 'Count of Item Amount' instead of 'Sum of Item Amount':
- 1. Click the dropdown arrow next to the field in the Values area.
- 2. Select Value Field Settings.
- 3. Change from 'Count' to 'Sum'.
You now have a complete breakdown of your payout organised by campaign and line item.
Bonus: If you want to see how many of each item sold (not just the £ total), drag Payment Method into the Values area. It will count the number of transactions, which you can rename to 'Quantity'.
Step 6: Check for refunds
Go back to the payout report you downloaded in Step 1. Add a filter and sort the amount column from largest to smallest, or filter for negative amounts.
If there are no negative amounts, you have no refunds to worry about.
If you do see a negative amount, that is a refund that was deducted from this payout. Since refunds do not appear in the itemised payments export, you need to manually add this as a line item in your reconciliation. Note the amount, the transaction it relates to, and the fund or campaign it should be deducted from.
Gift Aid note on refunds: If the refunded donation had already been included in a Gift Aid claim submitted to HMRC, you will need to adjust the corresponding claim on your next HMRC Charities Online submission. Note the refund date and the original donor so the finance lead can make the adjustment. The Charity Tax Group provides technical guidance on handling refunded Gift Aid claims.
Step 7: Map to your chart of accounts
This is the 'last mile': translating your pivot table into your accounting system's language.
Create a small table next to your pivot table with two columns: Revenue account (from your chart of accounts) and Classification (unrestricted funds, restricted funds, designated funds, or endowment funds, per the Charities SORP).
Go through each line in your pivot table and map it using UK SORP-aligned fund categories (NCVO guidance; Charity Commission):
- A branded tote-bag sale for £15 → Trading income, Unrestricted funds
- An additional £10 donation added at checkout → Donations income, Unrestricted funds (Gift Aid eligible where a declaration is held)
- Annual membership subscriptions of £25 → Membership subscriptions, Unrestricted funds (Gift Aid does NOT apply where the membership confers benefits)
- A raffle ticket at £2 → Trading income (small society lottery), Unrestricted funds (Gift Aid does NOT apply to raffle entries)
David recommends colour-coding each row as you map it. Once everything is highlighted, you know every pound is accounted for. Your mapped totals should equal the payout total.
"When your totals all add up and everything is colour-coded, you know you're good to go. This will make any bookkeeper very happy.", David Ducharme
Feed the figures into your annual return
Reconciled monthly figures roll up directly into the charity's annual return and Trustees' Annual Report and Accounts (TAR). Charities in England and Wales file with the Charity Commission; Scottish charities file with OSCR; charities in Northern Ireland file with CCNI. Reconciling monthly rather than quarterly means year-end filing is a compilation job, not a forensic exercise. That matters, because it is your trustees who are on the hook for the accuracy of those figures.
Make it repeatable
The first time through this process will take the longest, perhaps 45 minutes to an hour as you build your filters and chart-of-accounts template. After that, David estimates 20 to 30 minutes per month.
To speed things up even more:
- Save your template. Create a master spreadsheet with your chart of accounts and classification table already built. Copy it each month and paste in new data.
- Keep your filters consistent. The same three filters (payment method, payout date, cancelled tickets) apply every time.
- Reconcile monthly, not quarterly. It is faster to reconcile one payout at a time than to untangle three months of data.
Beyond manual: automation options
If 20 minutes a month is still more than you would like to spend, David shared that his organisation has automated parts of this process using Excel's built-in VBA and Power Query features. They drop the two Zeffy export files into a designated folder, click 'run', and get the summarised output in about 30 seconds.
For organisations processing thousands of transactions monthly, a more advanced Zapier integration into your accounting system (Xero, QuickBooks Online, Sage, or similar) is also possible. David noted that managing the automation can sometimes require as much effort as doing the work manually. The right level of automation depends on your transaction volume and how often new campaigns are created.
Fund designation in Zeffy
Zeffy's fund designation feature, launched in early 2026, lets donors choose which fund to support directly on donation forms, or lets organisations associate a form with a specific restricted or designated fund on the back end. Fund designations appear in your reporting, making it materially easier to see how donations should be allocated to unrestricted, restricted, or designated funds without the manual splitting steps at reconciliation time.
Frequently asked questions
How long does this process take each month?
The first time through takes 45 minutes to an hour as you set up your filters and chart-of-accounts template. After that, most treasurers find it takes 20 to 30 minutes per month. Reconciling each payout as it arrives (rather than batching them quarterly) keeps the time per payout low.
Does this work in Google Sheets?
Yes. Every step in this guide works in Google Sheets as well as Excel. Use Data, then Create a filter instead of Data, then Filter, and use Insert, then Pivot table to build your summary. The =SUM function works identically in both tools.
What if my payout total does not match my itemised total?
The most common causes are: a row with a missing amount in the item amount column, a cancelled transaction that was not filtered out, or a date-range mismatch that has left a transaction out of your export. Go back through each filter in turn. Check that cancelled entries are deselected and that your date range is wide enough to capture all transactions in that payout cycle.
Can I use this process to pull donor-level reports?
Yes. Instead of grouping the pivot table by campaign title and rate title, rearrange it to group by donor name (first name, last name, or donor ID). This is useful for year-end reporting to trustees, identifying major donors, and preparing figures for your annual return and Trustees' Annual Report. It is also helpful when reconciling your Gift Aid claim records: HMRC requires you to keep Gift Aid declarations and supporting records for at least six years (HMRC Gift Aid guidance).
Do I need any special software or API access?
No. This entire process uses the two built-in Zeffy exports (the payout report from the Finances tab and the itemised payments report from the Payments tab) plus Excel or Google Sheets. No third-party integrations, API keys, or specialist software are required.
What about peer-to-peer fundraising campaigns?
For sponsored fundraising (peer-to-peer) campaigns, the same reconciliation process applies. Filter and map as normal, then group the pivot table by the parent campaign title rather than by individual fundraiser page. This keeps your chart-of-accounts mapping clean and makes it straightforward to report the total raised under each campaign in your Trustees' Annual Report.
How does this reconciliation help with Gift Aid claims?
Flagging Gift-Aid-eligible donations as you reconcile each payout means the monthly figure you submit through HMRC Charities Online matches your books exactly. Only donations from UK taxpayers who have signed a Gift Aid declaration qualify for the 25p-per-£1 reclaim; ticket sales, raffle entries, auction lots at fair value, and membership fees where benefits are conferred do not. Keep the underlying declarations on file for at least six years, as required by HMRC guidance.
What about raffles or small society lotteries?
Raffle ticket revenue reconciles like any other Zeffy line item: filter, map, and summarise as normal. Tag it separately in your chart of accounts, however, because most UK charity raffles are small society lotteries under the Gambling Act 2005, and the local licensing authority requires a return within three months of the draw. Note that Gift Aid does not apply to raffle ticket purchases. See the Gambling Commission's guidance on small society lotteries for the full requirements.
---
David Ducharme is a trustee treasurer and financial planner who presented this reconciliation method during a Zeffy webinar on understanding your payouts.
Zeffy is 100% free for UK charities. No platform fees, no transaction fees, no card fees. Ever.
.webp)
