Which big clients are quietly the expensive ones?
Twenty-seven clients grouped on fees, realization and days to pay at once. Four of the eight biggest fee payers land in the group that pays late at low realization, which no sort of the fee column would ever have shown.
Work Intermediate Clustering free
After you install, this is the model to open.
Which Kinds of Client Do We Actually Have?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Large, expensive to run
- 11 clients $243K fees, 69% realization, 72 days to pay
- Large and clean
- 6 clients $350K fees, 93% realization, 29 days
- Small and clean
- 10 clients $150K fees, 89% realization, 25 days
- What fees alone hide
- 2 of top 4 biggest billers sit in the expensive group
Twenty-seven clients, described by what they bill, what share of recorded time survives to the invoice, and how long they take to pay. With K-Means selected and k set to 3, click Run. The tool normalizes the three columns first, so dollars do not drown out a percentage, and it returns three groups. Eleven clients cluster around $243K of fees, 69% realization and 72 days to pay.
Seven cluster around $336K, 93% realization and 29 days. Nine cluster around $139K, 89% realization and 24 days. Those are three genuinely different businesses: work that is large and expensive to run, work that is large and clean, and work that is small and clean. Now sort the fee column and see what you would have concluded from it. The four biggest fee payers on the sheet are $441K, $418K, $412K and $398K, and two of those four, the $418K and the $398K, sit in the low-realization group at 70% and 72% with sixty days of payment behind them.
Four of your top eight by fees are in that group. No ranking of a single column separates a client that bills $400K at 95% realization from one that bills $400K at 70%, and the second one is doing a third more work for the same money and financing it for two months. Now the part worth knowing before you show anyone the result, because it is not what the Seed box implies.
A blank seed here does not mean fresh randomness on every run: the tool falls back to a fixed default, so the answer above is the answer everyone gets. Type 7 into the Seed box and rerun. You get a different and slightly tighter split: the same eleven slow payers, but the two clean groups come out at six and ten instead of seven and nine, because the $248K account at 91% realization and 34 days moves from the large-and-clean group into the small-and-clean one.
Neither answer is wrong. K-Means finds a good grouping rather than the best one, the starting points decide which good one, and a client sitting between two centers is a judgment the algorithm makes on your behalf rather than a fact it discovered. So set a seed you can write down before the result goes into a pack, and check the borderline rows yourself.
Rerun at k = 2 and k = 4 and see which split you can act on: k = 2 merges the two clean groups and tells you nothing you did not know, and k = 4 splits the slow payers by size into six small and five large, which may or may not be a distinction your firm can do anything about. To use your own book, paste one client per row, keep every column numeric, put the client name outside the range, and widen it.
The model
It arrives on a tab called Template: Which Kinds of Client We Have, carrying these columns:
- Fees billed ($K)
- Realization (%)
- Days to pay
- Client
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Is the bigger fee the better client?Which of Two Clients Should Get the Slot?
- Score four early-warning signals against client renewal with a correlation matrix, so you know which metric actually predicts churn. Free Sheets template.What Moves With a Client Leaving?
- Which stream will move the delivery date?When Does This Engagement Actually Finish?
- How many hours will this scope actually take?What Should This Engagement Cost to Deliver?
- What if the partner with the fee book leaves?What Does Losing One Person Cost?
- A consulting engagement risk register ranking five risks by exposure and by mitigation value, since the two rankings are not the same list. Free template.What Could Go Wrong on This Engagement?
Every model like this one, and the method behind them: Machine learning in Google Sheets.