Which clients cost money, and who's carrying the load?

The gist

A small CPA firm already tracks the right things. Every time entry, task, and client budget is on record. The trouble is where it lives. It sits in exports from the firm's practice-management software and in separate Excel reports. Today, someone rebuilds those reports by hand.

Each report answers one narrow question. None can answer the question the partners care about most. Who is overloaded, on which clients, and are those clients even profitable?

I joined those same exports into one model and built a working dashboard on top. It has four views, and the matrices and tables click through to the time entries behind them. I put it in front of the partners early, so they could react to something real. The pipeline that will keep it fresh on its own is in build.

You can try the dashboard yourself. It runs on an anonymized copy of the firm's data.

What this solves

The dashboard puts capacity, profit, and client health side by side. Questions that cross reports finally have one place to look.

Pricing with evidence. Every client that loses money traces back to the hours and the people behind the loss. That's the conversation to have before you re-price, re-scope, or let a client go.

Workload with context. The dashboard pairs each person's hours with how they feel. For now, that part is simulated. It also keeps owner and admin time apart from staff time. The point is fairer workload, not surveillance.

Numbers you can check. Every figure traces back to its source. Click a number, see the rows behind it.

Rules as settings. In the demo, the firm's judgment calls are settings, like who counts as a billing role. When the firm learns something, it changes a setting, not a formula.

The problem

The data is fine. Four things get in the way.

Reports that don't talk to each other. Time, budgets, cost rates, and the tax pipeline each sit in their own file. A question that crosses them means cross-referencing by hand. Realistically, it doesn't happen.

Names that don't match. The same client goes by different names in different files. Today, someone keeps a name list by hand every month to line them up. A plain match on names catches only about half the clients.

Snapshots you can't question. The reports can't be filtered, regrouped, or clicked into. When a number looks wrong, the only check is to reopen the raw export.

Hours without context. A utilization number can't tell a strained employee from a thriving one. It also can't tell the cost of running the firm from billable time that slipped away.

Then there's the time. The management reports come out every month. Today, the three report files behind them are rebuilt by hand every week, at about an hour each. Missing data and re-uploads come on top of that.

Workflow

Here's the system I'm building, in plain terms. The demo runs on a snapshot today. The automatic refresh is in build.

Your exports stay the source. I went through all 18 files the firm shared. One time export, with 15,629 entries from January to August, held the truth about time. Several other files were partial copies or hand-built summaries of it. So the system will start from the exports the firm already pulls. The firm keeps its practice-management software.

Why scheduled exports. The software's public API only shares client records. Time, tasks, and invoices come out as report exports. So scheduled exports, sent by email, are the workable path. The API still earns its place. It gives each client a stable ID, and that ID will replace the hand-kept name list.

The firm's rules become settings. Staff groups, client tags, and the rules behind them will live as data. One example: a rule tags clients who have revenue outside this data. The client view hides them by default. Changing or retiring a rule will take one settings change. When someone overrides a rule by hand, their choice will win.

Profit is calculated for you. It comes from the hours, each person's cost rate, and the client's budget. Once the pipeline is running, nobody will reconcile it by hand each month.

Checked on the way in. Every load will be checked and logged. A late or broken file will raise an alert.

Sentiment is simulated for now. The workload view pairs hours with how people feel. Today that part is simulated, and the demo labels it that way. A real employee check-in will replace it later.

What's next

The dashboard works today, on a snapshot. The pipeline that will refresh it on its own is in build. Here's the order:

1. Settle a few questions with the firm. What one invoice code means. Why some roles carry no cost. Whether tax costs are complete.
2. Load the exports on a schedule, with an alert when something's off.
3. Rebuild the model and check it against the demo, number for number.
4. Run the monthly review from the dashboard.
5. Swap the simulated sentiment for a real check-in.

What the demo proves. The firm's existing exports already answer the questions that cross reports. The model holds together, and every number traces to its rows.

What it doesn't prove yet. Automatic refresh, real sentiment, and complete revenue. Some billing happens outside this data. Those will come with the pipeline and the firm's answers.

The goal is simple. Once the pipeline is running, nobody should have to rebuild those three report files by hand.

Fine printNames are fictional and dollar figures are scaled. Hours are real.