Petty Cash Log in Excel (Free Template + Reconciliation)

How an imprest petty cash fund works

An imprest fund has a fixed amount, here 500.00. At any moment, the cash in the box plus the vouchers for what was spent should add up to exactly that amount. When cash runs low (or at month end), you count the box, write one check for the amount spent, and the fund is back to 500.00. Two journal entries cover the whole cycle:

The Petty Cash account in the general ledger stays at 500.00 the whole time. Individual vouchers are not posted one by one; they are recorded together when the fund is replenished.

Step 1: Set the fund and log every voucher

Enter the imprest amount in C3 and the custodian, the one person responsible for the box, in C4. Every payment gets a numbered petty cash voucher (PC-101, PC-102, …) with the receipt stapled to it, and one row in the log: date, voucher, paid to, description, category and amount in Cash out. The first row brings the opening balance forward.

Category is a drop-down list (Data > Data Validation) pointing at the category table in J7:J12, so every voucher is coded the same way. For a list that grows by itself, use a drop-down list from a Table.

Step 2: Keep a running balance

The balance tells the custodian how much cash should be in the box right now. In H8, filled down:

=H7+F8-G8

Previous balance, plus cash in, minus cash out. H7 is simply the opening amount (=F7-G7).

Step 3: Total the spending by category

The replenishment entry needs one debit per expense account, so total the vouchers by category. In K7, filled down:

=SUMIFS($G$7:$G$19,$E$7:$E$19,J7)-SUMIFS($F$7:$F$19,$E$7:$E$19,J7)

The second SUMIFS subtracts any cash in for the same category, which only happens on the Cash Over/Short line when the box is over. It is the same SUMIFS pattern as in the monthly expense tracker and summing monthly values.

Step 4: Count the cash

On the Reconciliation sheet, count the box by denomination and type the number of each bill and coin in column B. Each amount is =A4*B4, and the total in C14 is the cash counted. Count with a second person present and have both initial the count sheet; surprise counts during the month are a good control too.

Step 5: Reconcile and find the over or short

The reconciliation in A16:C22 proves the fund:

Cash plus vouchers is 0.50 less than the fund, so the box is short 0.50. Rounding to cents in C21 avoids a tiny floating-point difference showing up as a shortage. The log picks the result up automatically in row 18 (=MAX(0,-Reconciliation!C21) in Cash out), so the log balance now matches the count, and K15 shows Ties.

Step 6: Replenish and record the journal entry

Write a check (or make a transfer) for the replenishment needed, cash it, and put the money in the box. Row 19 of the log records it as Cash in, and the balance returns to 500.00 (K16 shows Ties). The entry on the Reconciliation sheet is built from the category totals:

Cash Over and Short is debited for a shortage (an expense) and credited for an overage, which the template handles with =MAX(0,-C21) and =MAX(0,C21). The Petty Cash account itself is not debited or credited; only a change in the size of the fund touches it. The credit to Cash in Bank is the replenishment check, which will appear on the bank reconciliation, and the expenses flow into the monthly P&L.

Petty cash controls in one list

• One custodian per fund, and the box locked when not in use.
• A maximum amount per voucher (for example 50.00); larger payments go through accounts payable.
• Every payment has a numbered voucher, signed by the recipient, with the receipt attached.
• Vouchers are canceled (stamped PAID) when the fund is replenished, so they cannot be used twice.
• Someone other than the custodian does surprise counts and approves the replenishment.
• No IOUs, personal loans or check cashing from the fund.

To flag vouchers above the limit automatically, add a conditional formatting rule based on another cell, such as =$G8>$C$5 with the limit typed in C5.

Frequently asked questions

Final checklist

• The imprest amount in C3 matches the Petty Cash balance in the general ledger.
• Every log row has a voucher number, a category and a receipt.
• The cash count was done with a second person and signed.
• Cash + vouchers = imprest amount, with any difference in Cash Over and Short.
• The replenishment entry shows Balanced and the log ends at the imprest amount.
• Vouchers are stamped PAID and filed with the count sheet.