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:

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.¹ 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.

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:

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.