Petty Cash Log Template in Excel: Imprest Float, Vouchers, Running Balance and Reconciliation
How to set up a petty cash log template in Excel: an imprest float, numbered vouchers, a running balance, category totals for replenishment, cash counts with over/short tracking, the bookkeeping entries and simple controls, with a worked two-week example.
Quick answer: a petty cash log is a running record of the small cash payments made from an office cash box. Each line holds the date, voucher number, description, expense category, amount paid out, any money added and the running balance:
=PreviousBalance+MoneyIn-MoneyOut
Most businesses use an imprest system: the box starts with a fixed float (say $300), and when it runs low you top it back up by exactly the amount spent, backed by receipts. At any moment, cash in the box + receipts = float. The template below does the math, totals spending by category for your books and flags any shortage. The example figures are illustrative.
Step 1: Set the float and the rules
- Float amount: enough for two to four weeks of small purchases. Too large invites misuse; too small means constant top-ups.
- One custodian: one person holds the key and signs out cash; a second person approves top-ups.
- Per-purchase limit: for example, nothing over $50 from petty cash; bigger purchases go through normal payment channels.
- A receipt or voucher for every payment, numbered in sequence.
Write these rules on the template’s first sheet so they travel with the file.
Step 2: Log columns and formulas
| Column | Content | Formula / tip |
|---|---|---|
| A Date | Date of payment | Data validation: dates only |
| B Voucher # | PC-001, PC-002… | Sequential; gaps are a red flag |
| C Paid to / description | “Post office – parcel to client” | |
| D Category | Postage, Office supplies, Cleaning, Meals, Travel… | Drop-down list from a Categories table |
| E Money out | Amount paid | |
| F Money in | Float or top-up | |
| G Balance | Running balance | First row =F2-E2, then =G2+F3-E3 |
| H Receipt? | Yes / No | Highlight “No” in red |
| I Approved by | Initials |
Format the range as an Excel table so new rows pick up the formulas automatically. A check that the balance never goes negative: =IF(MIN(G:G)<0,"CHECK: negative balance","OK").
Worked example: a $300 float over two weeks
| Date | Voucher | Description | Category | Out | In | Balance |
|---|---|---|---|---|---|---|
| Mar 1 | — | Float established | — | 300.00 | 300.00 | |
| Mar 2 | PC-001 | Post office, parcel to client | Postage | 18.40 | 281.60 | |
| Mar 3 | PC-002 | Printer paper and pens | Office supplies | 42.75 | 238.85 | |
| Mar 5 | PC-003 | Cleaning supplies | Cleaning | 23.10 | 215.75 | |
| Mar 6 | PC-004 | Coffee for client meeting | Meals | 16.50 | 199.25 | |
| Mar 9 | PC-005 | Parking, bank visit | Travel | 12.00 | 187.25 | |
| Mar 11 | PC-006 | Courier, signed documents | Postage | 35.00 | 152.25 |
Total paid out: $147.75. The log says $152.25 should be in the box.
Step 3: Count the cash and reconcile
Before each top-up (and occasionally by surprise), count the cash and compare:
| Item | Amount |
|---|---|
| Float | $300.00 |
| Receipts / vouchers since last top-up | $147.75 |
| Expected cash (float − receipts) | $152.25 |
| Cash counted | $151.25 |
| Over / (short) | ($1.00) |
Formulas: expected cash =Float-SUMIFS(Log[Out],Log[Date],">"&LastTopUp); difference =CashCounted-ExpectedCash. Small differences happen (rounding, a coin given as change). Record them on a separate “Cash over/short” line so the log stays honest. Repeated or growing shortages are a signal to tighten controls.
A simple count sheet with denominations helps: list $20, $10, $5, $1 bills and coins, enter the quantity of each, and total with =SUMPRODUCT(Denominations,Quantities).
Step 4: Replenish the float
Under the imprest method, the top-up equals the receipts plus any shortage, so the box returns to exactly $300: here $147.75 + $1.00 = $148.75. Record it as a new line with that amount in the Money in column and the voucher batch reference (for example, “Top-up, vouchers PC-001 to PC-006”).
Category totals for the top-up come straight from the log:
=SUMIFS(Log[Out],Log[Category],"Postage",Log[Date],">"&LastTopUp)
| Category | Total |
|---|---|
| Postage | $53.40 |
| Office supplies | $42.75 |
| Cleaning | $23.10 |
| Meals | $16.50 |
| Travel | $12.00 |
| Cash over/short | $1.00 |
| Top-up | $148.75 |
Step 5: Bookkeeping entries
With the imprest method, the petty cash account in your books stays at the float amount; the expenses are recorded when you replenish:
- Setting up the fund: debit Petty cash $300, credit Bank $300.
- Replenishing: debit each expense category (Postage $53.40, Office supplies $42.75, Cleaning $23.10, Meals $16.50, Travel $12.00) and Cash over/short $1.00; credit Bank $148.75.
- Changing the float (for example, raising it to $400): debit Petty cash $100, credit Bank $100.
If you keep a double-entry ledger, post these entries in your general ledger template; in a simpler cash-based setup, record the top-up as categorized expenses in your bookkeeping template. Meal and entertainment expenses may have special tax treatment, so keep the business purpose on the receipt and check with your tax adviser.
Controls that keep petty cash clean
- Lock the box and limit who can access it.
- No IOUs or personal loans from petty cash.
- Staple receipts to numbered vouchers and file them by top-up batch.
- Have someone other than the custodian do occasional surprise counts.
- Review the category totals monthly: if one category grows steadily, move it to a company card or a supplier account.
- Protect the formula columns so only the input cells can be edited; here’s how to protect formulas in Excel.
Common mistakes
- Topping up with a round number instead of the exact amount spent, which breaks the imprest check.
- Missing receipts with no voucher explaining the payment.
- Mixing personal reimbursements with petty cash purchases.
- Recording expenses twice: once from the log and again from the bank top-up.
- Never counting the cash: the log is only as good as the reconciliation.
Frequently asked questions
How big should the petty cash float be?
Look at a month of small cash purchases and set the float to cover two to four weeks of them. Many small offices start with a few hundred dollars and adjust after a couple of months.
Is a petty cash log the same as a cash book?
They’re related. A cash book records all cash receipts and payments of a business; a petty cash log covers only the small-expense fund. Very small businesses that handle most sales in cash often keep a full cash book instead.
Can I use this template in Google Sheets?
Yes. SUMIFS, SUMPRODUCT, MIN and IF work the same; replace table references with normal ranges.
For the full picture of your records, read small business bookkeeping. More templates are in Bookkeeping & income tracking.