Tracking customer account profitability
posted 2017.10.22 by Clark Wilkins, Simplexable

Version 16.154 introduced a more comprehensive analysis of customer profitability. We're going to outline this as a specific set of methods, but the general concept could be applied using more basic tools such as Quickbooks, albeit with a lot more work. First, some definitions.

accrual-basis income
The total amount billed on invoices sent to this account. The linkage is established by billing (the invoice) on jobs assigned to this customer account.
accrual-basis costs
The total expenses posted on jobs for this account. The linkage is established by expenses thart are linked to jobs that, in turn, are linked to the custemor account.
accrual-basis profits
accrual-basis income — accrual-basis costs
cash-basis income
The total amount actually received. The linkage is established by recording the payments against specific customer jobs.
cash-basis costs
The total amount actually paid on linked expenses. The linkage is established by expenses thart are linked to jobs that, in turn, are linked to the custemor account.
cash-basis profits
cash-basis income — cash-basis costs
customer account
Each customer in INSIGHT is assigned a unique identity. This ID is then linked to jobs to establish the connection between the account and the work (and billing).
customer profile
INSIGHT allows a customer profile to be further broken down into a number of specific sub-accounts. Initially, this was used to separate service events and correlate them to specific equipment for historical purposes, but this document is going to leverage the concept to show how we can parse a large account into sections and look at the profitability of each one.

To prepare for this analysis, I segmented a large customer account into ~ 12 profiles. By doing this, and reassigning jobs to group the billing and costs into these profiles, I am now able to provide both comprehensive analysis (the entire account) and specific analysis (project-by-project). Consider this chart of accrual-basis profits. Looks pretty good, right?

We can see what looks like a sharp uptake in billing in August 2015, followed by several months of expenses accumulated as the project costs roll in. This is what you might refer to as a “front-loaded” job where the invoicing leads the costs — definitely a more desirable situation from our point of view. Let's look at the cash-basis plot of the same account.

Uh-oh! We can see here that a long slide in actual revenues received causes a slide from +847K in May 2016 to -108K in April 2017. This can be attributed to a few potential causes, among them:

  • If the work is billed all on the front end, both charts 1 and 2 are going to peak early and then tail off. That's not what we see here.
  • The job is billed periodically and accrual basis profits are good, but we're not collecting the invoices. That's what we do see here.

Look again at chart 1 and you see periodic billing. The client account is always well “above water”. In fact, there's a $400K increase in chart 1 from August 2016 to March 2017, but in chart 2, actual profits decrease by $500K. This is what failiure to collect looks like.1 If you're billing, but not collecting, and the expenses continue to pile up and get paid as you can, you are going to run out of cash eventually.

To make it even clearer, we added another chart in a revision of this tool.

Now it really starts to make sense. We're running up massive bills on the client's projects, paying our vendors, but not getting paid ourselves. That $950K deficit in receivables is the root problem here.2,3

Now, we can also “drill down” into a specific equipment profile and look at the performance much closer. This classification requires the following:

  • Setting up customer accounts with profile tracking.
  • Assigning the jobs to correct profiles.

Here's a project that simply went bad almost from the start. You can see that it's very front-loaded on the billing (a good thing), but accumulates a lot of cost overruns.

The cash-basis analysis correlates almost exactly. This is just a deal that went very badly, not a collections problem.

CONCLUSIONS

  • Cash flow problems can arise from failure to colllect as well as overruns.
  • Looking at this analysis in a broad sense helps understanding of the overall client-supplier relationship.
  • Using equipment profiles and drilling down into the sub-accounts is useful in understanding the performance of specific sectors such as Time and Materials vs. Parts.
  1. Another possible reason is complete failure to process payments properly.
  2. As pointed out earlier, you can do this sort of analysis “by hand” if you have a day or two to spare. This one was done in ~ two seconds on INSIGHT.
  3. As we show later in the article, misapplication of payments can skew profile-specific analysis, but here, we are generating a report agains the account as a whole, so even if someone applied a payment against the wrong job balance, as long as they got the right customer account, these numbers are valid.