Client & Project Tracker
Made for agencies, consultants and freelancers juggling work.
A workbook for tracking clients, the projects running for each one, what is due soon and the fees still to come in. The summary carries a chart that redraws itself as you type.
Of the 24 columns, you fill in 15 and the other 9 work themselves out, and all 14 figures on the summary do too. There is room for 420 rows of your own.
£4.99one payment, yours to keep
- Opens in Excel, Google Sheets and Numbers
- Delivered straight away
- No macros, nothing to install
- Change it free before you buy
Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.
Already have a spreadsheet of your own? Check it for mistakes, free.
- A new row is typed into the Projects tab, one cell at a time, in the white cells a person fills in.
- The shaded cells in that row fill themselves in.
- The figures on the Summary tab change to include the new row.
- The chart, Fees by project status, redraws with it.
Everything inside, before you pay
This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 420 rows. Open any tab to see its columns.
Clients
11 columns
| Column | What goes in it | Worked out as |
|---|---|---|
| Client | Keep each name unique. This is the name projects are matched against.Type any text. | |
| Main Contact | Type any text. | |
| Type any text. | ||
| Phone | Type any text. | |
| Client Status | Pick one: Active, Prospect, Dormant or Past. | |
| Standard Hourly Rate | Type an amount in £. | |
| Projects | Fills itself in. | The number of rows on Projects where Client matches this row's Client. The formulaIF(ISBLANK([client_name]), "", COUNTIF([Projects!client], [client_name])) |
| Total Fees | Fills itself in. | The total of Fee on Projects where Client matches this row's Client. The formulaIF(ISBLANK([client_name]), "", SUMIF([Projects!client], [client_name], [Projects!fee])) |
| Fees Outstanding | Fills itself in. | Worked out from this row's Client, using Client, Fee and Fee Received on Projects. The formulaIF(ISBLANK([client_name]), "", SUMIF([Projects!client], [client_name], [Projects!fee]) - SUMIF([Projects!client], [client_name], [Projects!fee_paid])) |
| Hours Logged | Fills itself in. | The total of Hours Logged on Projects where Client matches this row's Client. The formulaIF(ISBLANK([client_name]), "", SUMIF([Projects!client], [client_name], [Projects!hours_logged])) |
| Notes | Type any text. |
Projects
13 columns
| Column | What goes in it | Worked out as |
|---|---|---|
| Project | Type any text. | |
| Client | Pick one of the Client entries on Clients. | |
| What It Is | Type any text. | |
| Started | Type a date. | |
| Deadline | Type a date. | |
| Status | Pick one: Quoted, In progress, On hold, Delivered, Invoiced, Paid or Cancelled. | |
| Fee | Type an amount in £. | |
| Hours Logged | Type a number. | |
| Days To Deadline | Negative means the deadline has already passed.Fills itself in. | Worked out from this row's Deadline. The formulaIF(ISBLANK([deadline]), "", [deadline] - TODAY()) |
| Timing | Due soon means the deadline falls within the next 14 days.Fills itself in. | Worked out from this row's Deadline and Status. The formulaIF(ISBLANK([deadline]), "", IF(OR([status]="Paid", [status]="Cancelled", [status]="Invoiced", [status]="Delivered"), "Closed", IF([deadline]-TODAY() < 0, "Overdue", IF([deadline]-TODAY() <= 14, "Due soon", "On track")))) |
| Fee Received | Fills itself in. | Worked out from this row's Fee and Status. The formulaIF(ISBLANK([fee]), "", IF([status]="Paid", [fee], 0)) |
| Fee Still Due | Fills itself in. | Worked out from this row's Fee and Status. The formulaIF(ISBLANK([fee]), "", IF(OR([status]="Paid", [status]="Cancelled"), 0, [fee])) |
| Effective Hourly Rate | Fills itself in. | The Fee divided by the Hours Logged. The formulaIF(ISBLANK([fee]), "", IFERROR(ROUND([fee]/[hours_logged], 2), 0)) |
Summary
14 figures
| Figure | Worked out as |
|---|---|
| Projects due in the next 14 days | The number of rows on Projects where Timing is “Due soon”. The formulaCOUNTIF([Projects!timing], "Due soon") |
| Projects past their deadline | The number of rows on Projects where Timing is “Overdue”. The formulaCOUNTIF([Projects!timing], "Overdue") |
| Projects in progress | The number of rows on Projects where Status is “In progress”. The formulaCOUNTIF([Projects!status], "In progress") |
| Projects on hold | The number of rows on Projects where Status is “On hold”. The formulaCOUNTIF([Projects!status], "On hold") |
| Fees still to come in | Every project except those marked Paid or Cancelled. The total of Fee Still Due on Projects. The formulaSUM([Projects!fee_due]) |
| Fees invoiced and awaiting payment | The total of Fee on Projects where Status is “Invoiced”. The formulaSUMIF([Projects!status], "Invoiced", [Projects!fee]) |
| Fees on work still in progress | The total of Fee on Projects where Status is “In progress”. The formulaSUMIF([Projects!status], "In progress", [Projects!fee]) |
| Fees received so far | The total of Fee Received on Projects. The formulaSUM([Projects!fee_paid]) |
| Value of open quotes | The total of Fee on Projects where Status is “Quoted”. The formulaSUMIF([Projects!status], "Quoted", [Projects!fee]) |
| Total hours logged | The total of Hours Logged on Projects. The formulaSUM([Projects!hours_logged]) |
| Average earned per hour | Worked out from Fee and Hours Logged on Projects. The formulaIFERROR(ROUND(SUM([Projects!fee])/SUM([Projects!hours_logged]), 2), 0) |
| Active clients | The number of rows on Clients where Client Status is “Active”. The formulaCOUNTIF([Clients!client_status], "Active") |
| Clients on the list | The number of rows on Clients with Client filled in. The formulaCOUNTA([Clients!client_name]) |
| Average project fee | Worked out from Fee on Projects. The formulaIFERROR(ROUND(SUM([Projects!fee])/COUNT([Projects!fee]), 2), 0) |
More in Business
-
Freelance Invoice & VAT Tracker
Tracks every invoice sent, what is outstanding, what is overdue, and how much VAT is due this quarter, with a reusable client list.
22 figures work themselves out£4.99
-
Small Business Bookkeeping
A simple set of books for a one-person business: record every payment received and every business cost, tag each one with a category, and see totals by category, totals by month and profit so far this year ready for the tax return.
26 figures work themselves out£4.99
-
Job Quotes & Margin Tracker
A workbook for a tradesman: record every quote with its customer, labour hours and rate, materials and quoted price; see the margin made on each job and the win rate on quotes.
28 figures work themselves out£4.99
-
Etsy Seller Profit Tracker
Record every Etsy sale with fees, postage and materials, and see what each order and each item actually earns.
35 figures work themselves out£4.99