Excel template
Keep track of every voucher
Download a free Excel workbook that records each voucher you sell, each partial redemption and the balance that remains, and flags mistakes as you type.
Download
Download voucher trackerWhat is inside
Staff guide: four steps
- Selling a voucher: on Vouchers, add a row with a new ID such as V001, today’s date and the value, for example 50.
- Redeeming: on Redemptions, add a row with a new transaction ID such as R001, the voucher ID, the date and the amount used. A guest can use a voucher in several visits.
- Checking a balance: type the voucher ID into the look-up box on Summary. The balance comes from the redemption log; never type it yourself.
- Fixing a mistake: correct the cell in its row. If a redemption was entered by mistake, clear that row. Do not enter negative amounts.
What the checks catch
Every row gets OK or the first problem found: duplicate voucher or transaction IDs, unknown voucher IDs, zero or negative amounts, amounts with more than two decimals, text where a date belongs, a redemption before the issue date, and a redemption above the remaining balance. An over-redemption is never cut off: the balance shows below zero and is flagged. While anything is flagged, the summary says its totals are not reliable.
Pasting skips Excel’s input checks, but not the check columns: they recalculate on whatever lands in the row. Sheets are protected without a password only to stop accidental edits of calculated columns; that is not a security feature.
Worked example
| Entry | Amount | Balance of V001 |
|---|---|---|
| V001 issued | €50.00 | €50.00 |
| R001 redeemed | €12.50 | €37.50 |
| R002 redeemed | €7.50 | €30.00 |
| A further €30.01 | flagged | −€0.01, “Exceeds the remaining balance” |
Questions
Does it work in Google Sheets or Numbers?
We have tested it in Microsoft Excel. It uses only common functions, but we have not yet verified other programs, so please check the Example sheet after opening it there.
Do I need guests’ names?
No. A voucher ID is enough. The optional note is for your team, for example “gift card, printed”.
Is this enough for my bookkeeping?
It is a tracking aid, not an accounting system. How vouchers are recorded for VAT and bookkeeping is a question for your tax adviser.
What if I need more than 500 vouchers?
Start a new copy of the file, for example one per year, and carry open balances over as new vouchers with a note.