sheetsmith

Templates › Business and freelance

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.

Tabs
3
Columns
24
Fill themselves in
23

£7one 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.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

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.

Clients

One row per client. Add a client here first so it appears in the dropdown on the Projects tab. Room for 120 rows.

ColumnTypeWorked out as
Clienttext
Main Contacttext
Emailtext
Phonetext
Client Statuschoice (list)
Standard Hourly Ratecurrency
ProjectsnumberIF(ISBLANK([client_name]), "", COUNTIF([Projects!client], [client_name]))
Total FeescurrencyIF(ISBLANK([client_name]), "", SUMIF([Projects!client], [client_name], [Projects!fee]))
Fees OutstandingcurrencyIF(ISBLANK([client_name]), "", SUMIF([Projects!client], [client_name], [Projects!fee]) - SUMIF([Projects!client], [client_name], [Projects!fee_paid]))
Hours LoggednumberIF(ISBLANK([client_name]), "", SUMIF([Projects!client], [client_name], [Projects!hours_logged]))
Notestext

Projects

One row per project. Fill in the fee, the deadline and the hours as you go, and the timing columns look after themselves. Room for 300 rows.

ColumnTypeWorked out as
Projecttext
Clientchoice (from Clients › client_name)
What It Istext
Starteddate
Deadlinedate
Statuschoice (list)
Feecurrency
Hours Loggednumber
Days To DeadlinenumberIF(ISBLANK([deadline]), "", [deadline] - TODAY())
TimingtextIF(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 ReceivedcurrencyIF(ISBLANK([fee]), "", IF([status]="Paid", [fee], 0))
Fee Still DuecurrencyIF(ISBLANK([fee]), "", IF(OR([status]="Paid", [status]="Cancelled"), 0, [fee]))
Effective Hourly RatecurrencyIF(ISBLANK([fee]), "", IFERROR(ROUND([fee]/[hours_logged], 2), 0))

Summary

The running answers: what is due soon, what is still in progress and the fees you have coming.

MeasureWorked out as
Projects due in the next 14 daysCOUNTIF([Projects!timing], "Due soon")
Projects past their deadlineCOUNTIF([Projects!timing], "Overdue")
Projects in progressCOUNTIF([Projects!status], "In progress")
Projects on holdCOUNTIF([Projects!status], "On hold")
Fees still to come inSUM([Projects!fee_due])
Fees invoiced and awaiting paymentSUMIF([Projects!status], "Invoiced", [Projects!fee])
Fees on work still in progressSUMIF([Projects!status], "In progress", [Projects!fee])
Fees received so farSUM([Projects!fee_paid])
Value of open quotesSUMIF([Projects!status], "Quoted", [Projects!fee])
Total hours loggedSUM([Projects!hours_logged])
Average earned per hourIFERROR(ROUND(SUM([Projects!fee])/SUM([Projects!hours_logged]), 2), 0)
Active clientsCOUNTIF([Clients!client_status], "Active")
Clients on the listCOUNTA([Clients!client_name])
Average project feeIFERROR(ROUND(SUM([Projects!fee])/COUNT([Projects!fee]), 2), 0)