Team expense tracker for Excel: claims, receipts and who is owed

How to use this template

This tracker is for a team whose members pay for things first and are repaid later: a volunteer crew, a school or youth team, a club committee. It is one claims log and one summary. The formulas below are for Excel, and the same grids work on paper.

1. Log every claim in its own row when the person tells you, with the date, who paid, what for and the amount.
2. Set the status. Needs receipt means the claim is in but has no receipt. Submitted means it has one and is waiting for a decision. Approved means someone with authority said yes. Paid means the person has been repaid.
3. Keep claims without a receipt out of Approved. A claim stays at Needs receipt until the receipt arrives.
4. Read the summary each week and repay what is Approved.
5. Watch Days open. It shows how long each unpaid claim has been waiting.

The template

Grid 1: claims (columns A to E)

Date	Claimed by	What	Amount	Status

Grid 2: follow-up (columns F to H, same rows)

The Row column only matches each line to its claim in Grid 1. It is not a column in the sheet, so Days open is column F.

Row	Days open	Approved by	Paid on
1
2
3
4

Grid 3: summary (columns J to N)

The column headers are the four status names, spelled exactly as they are in the Status cells.

Person	Approved	Submitted	Needs receipt	Paid
Total

The formulas

Put headers in row 1 and the first claim in row 2. These formulas assume no more than 199 claims, which is row 200.

• Status drop-down. Select the Status cells, open the Data tab, choose Data Validation, set Allow to List and type Needs receipt,Submitted,Approved,Paid in Source, without quote marks. The cells then offer the four statuses in a drop-down, so nobody has to type them.
• Days open (F2). =IF(A2="","",IF(E2="Paid","",TODAY()-A2)). Copy it down to row 200, and rows with no claim stay empty. Format column F as Number, because Excel may otherwise show the result as a date.
• Summary (K2). =SUMIFS($D$2:$D$200,$B$2:$B$200,$J2,$E$2:$E$200,K$1). Put each person’s name in column J, copy the formula across to N and down one row for each person, and fill the Total row with a SUM of the cells above it. The ranges must have the same number of rows, and they do.
• Claims waiting more than 14 days (any cell). =COUNTIFS($F$2:$F$200,">14").

A filled example: seven claims

Say a volunteer team pays for supplies and van fuel, and Rob approves claims. The log below is as of March 20, so Days open counts from that date.

Date	Claimed by	What	Amount	Status
Mar 1	Lena	Gloves and bags	42.30	Paid
Mar 3	Marcus	Van fuel	38.00	Approved
Mar 3	Lena	Poster printing	24.50	Needs receipt
Mar 7	Sofia	Water and snacks	31.75	Paid
Mar 9	Marcus	Van fuel	41.20	Approved
Mar 12	Tomas	Cable ties and tape	18.90	Submitted
Mar 14	Lena	Parking at the drop-off	12.00	Needs receipt

Row	Days open	Approved by	Paid on
1	blank	Rob	Mar 8
2	17	Rob	blank
3	17	blank	blank
4	blank	Rob	Mar 15
5	11	Rob	blank
6	8	blank	blank
7	6	blank	blank

The summary adds up each person’s rows by status.

Person	Approved	Submitted	Needs receipt	Paid
Lena	0.00	0.00	36.50	42.30
Marcus	79.20	0.00	0.00	0.00
Sofia	0.00	0.00	0.00	31.75
Tomas	0.00	18.90	0.00	0.00
Total	79.20	18.90	36.50	74.05

The four totals add up to 208.65, the same as the Amount column, so no status was mistyped. The team owes Marcus 79.20 now. Lena has 36.50 held until she brings the two receipts. Two claims, Marcus’s first fuel claim and Lena’s poster printing, have been open 17 days, so the formula for claims waiting more than 14 days returns 2.

What to agree on

Who approves

Name the approver, and the amount above which a second person signs off. Write the name in Approved by when the status changes, so a later question has an answer.

How fast approved claims are repaid

Pick a number of days and write it above the log. The example uses 14, which makes Marcus’s first claim 3 days late. A rule that nobody can see is a rule that gets forgotten, so the days-open column is what makes it work.

The receipt rule

Decide whether a claim can ever be approved without a receipt, and for what amount. If the answer is never, the Needs receipt status does the enforcing. If there is an exception for small amounts, write the amount down.

One keeper

One person owns the file, so there is a single copy and nobody works on a stale one. Everyone else sends rows to that person.

Teams that cover for each other

Some teams have no central pot. Four people share the van fuel one person paid, and the question is only who owes whom. That is a splitting job and not a claim. In EvenOut, add the expense, choose who paid and who shared it, and “Balances” shows you what you owe or are owed with each person. With “Only their own” under “Who can edit”, people change only what they added or paid for.

Costs that come out of a fixed allocation belong with the team budget tracker template, which tracks what is committed, spent and left on each line. For one person’s trip claim on a single form, see the travel expense report form, and for a treasurer’s monthly routine, the club treasurer duties checklist.