Before / AfterBoutique CPA · 9 professionals · 48 clients · ~18.9 K hrs / yr

Thirteen years of daily tabs, in one rollup.

A 9-person boutique CPA firm — 48 high-touch clients, mostly HNW families and their related entities — tracks ~18,900 hours a year the way it has tracked them since 2011: one sheet per working day, by hand, in a workbook with a tab for every date. Every January the firm spent an afternoon adding them up. We gave them the rollup that builds itself as they type.

Hours tracked / yr

18,920

Billable hours

15,550

Firm utilization

83%

Clients on the books

Annual reconciliation

4 hrs → 5 min

2024-Annual-Summary.xlsx·live from 247 daily tabs via SUMIFS

updated just now

Hours tracked

18,920

across all 247 daily tabs

Billable hours

15,550

82% of tracked

Firm utilization

83%

15,550 of 18,720 available

Clients rolled up

top 12 below · 36 more in the tail

B7│=SUMIFS(INDIRECT("'"&$A7&"'!D:D"), INDIRECT("'"&$A7&"'!B:B"), Clients[@Client])

By month

billablenon-billable

Mo.

Bill.

Total

Jan

1,140

1,420

Feb

1,355

1,680

Mar

1,940

2,310

Apr

2,090

2,485

May

860

1,080

Jun

910

1,140

Jul

985

1,220

Aug

880

1,095

Sep

1,765

2,110

Oct

2,150

2,520

Nov

770

965

Dec

705

895

Total

15,550

18,920

Tax-season peaks visible without looking: April (1040) and October (10/15 extension). Summer and November fall where you'd expect.

By client · top 12 of 48

remaining 36 clients · 7,300 hrs

Client	Hours	Billable	% of book
Client A	2,680	2,210	14.2%
Client B	2,285	1,930	12.1%
Client C	1,345	1,120	7.1%
Client D	1,275	1,055	6.7%
Client E	980	810	5.2%
Client F	780	650	4.1%
Client G	620	515	3.3%
Client H	505	415	2.7%
Client I	365	305	1.9%
Client J	305	255	1.6%
Client K	260	215	1.4%
Client L	220	180	1.2%

Top 2 clients carry 26% of the book. The long tail of 36 smaller clients is where partner capacity goes unnoticed — the rollup makes that visible for the first time.

Master · 247 daily tabs · 48 clients · last edit by S. Morrison · 9:41aAuto-calc: ON

How we built it

No new software. One workbook. Formulas where the afternoon used to be.

Kept the daily-sheet habit

Partners still enter time on a daily sheet. We didn't ask them to change a muscle memory from 2011. But each sheet is now a validated template — typed columns, named ranges — not a free-form grid.

Structured inputs

Client column bound to a single-source roster sheet via data validation. Time column coerced to decimal hours. Costs column tagged reimbursable / non. Wrong inputs can't get in.

SUMIFS over named ranges

Annual Summary is a single sheet that reads every daily tab via SUMIFS + INDIRECT, grouped by month and by client. Add a new day tab and the rollup picks it up without being touched.

Client + month views

Hours by client, hours by month, billable-vs-non split on both axes, utilization against firm capacity. The four questions partners actually ask in January — answered as the year happens, not at the end of it.

Excel did the work. No Python, no BI tool, no migration.

Stack: Excel (O365) · SUMIFS + INDIRECT + XLOOKUP formulas · named ranges · data-validated dropdowns · one master client roster. The partners did not need to learn anything new. The engagement was four working sessions and a weekend of QA.

Results · first full year

The afternoon nobody wanted disappeared.

Partners kept their daily-sheet habit. The summary builds itself. Come January there's no reconciliation, only a sanity check — and the firm sees where hours are going by client and by month as it happens, not in arrears.

18,920hrs

Auto-tallied, zero manual cross-check

4 hr→ 5 min

Annual reconciliation, partner-side

83%

Firm utilization, visible in real time

Clients rolled up by hours and month

Have a version of this in your own business?

Book a free 30-minute call. We'll walk what you're doing now and show you what's fixable.

Book a call See all case studies