Let's Build a Customer Attractiveness Chart - Pt. 2
Share
Having completed the strategic attractiveness analysis phase that we saw in the first part , we must now focus on the quantitative aspects that concern the relationship between company and customer.
For this evaluation it is appropriate to go beyond the mere turnover data developed towards the customer, and to draw up a synthetic economic statement, such as the one in the following figure.
In the example, 13,000 is the turnover that is expected to be generated annually for the customer Carrozzeria 2000 Snc, while the following income statement reports the actual data of the relationships with the same customer, referred to the evaluation date.
The table structured in this way has the advantage of highlighting, in addition to the sales revenue data, other quantities considered more significant for the purpose of calculating the customer's profitability, such as the first and second level contribution margin , up to the net economic result , a real profit for the financial year, but referable to the single commercial position.
The relationship between these quantities and revenues is indicated in column E (% revenues), and offers an indication of the average margin of the customer; in particular, in the example we will use the Net Result of the customer in percentage terms (in the figure 39.7%), a sort of Net Profit Margin applied to the single position so as to estimate, in an immediate and easily comparable way, the profitability .
Repeating the analyses seen so far for the client "Carrozzeria 2000 Snc" on other hypothetical clients, we obtain a table that summarises the most significant numbers for our evaluation.
The numbers structured in this way give us the possibility of representing them graphically through a bubble chart , after having carried out a small transformation, which we will discuss shortly.
The strategic attractiveness score is already ready to be represented in the graph as it is, as it consists of a score ranging from 1 to 10.
Economic-financial attractiveness, on the other hand, also needs to be brought back to a score between 1 and 10.
To do this, a minimum profitability level is identified below which the score is equal to 1, and a maximum profitability level above which the score is equal to 10.
In the example, we will consider the following profitability thresholds:
- Minimum: 10%
- Maximum: 50%
In this way the profitability percentages, through the simple proportion indicated in the formula (see screenshot below), will be converted into scores 1-10 (see cells with red border) ready to be used in the bubble chart.
As you can see from the screenshot, the second customer (row 7) has a score of 10 because the profitability is greater than 50%; instead, to find the P i scores of all the other customers it was necessary to use the following formula:
which in our example becomes more simply:
So for example, for the first customer (row 6) the profitability score to be assigned will be:
With the scores thus obtained, we can now easily construct our bubble chart in Excel, which in this case will look like this:
This type of chart is particularly suitable for visualizing relationships between 3 sets of numerical data, where the first 2 are represented as “bubbles” positioned on the x and y axes, while the third set of data is represented with the size of the bubble itself.
In particular, the graph is constructed in such a way as to represent:
- on the y -axis (on a scale of 1 to 10) the strategic attractiveness of the customer;
- on the x -axis (always on a scale of 1 to 10) its profitability ;
- with the size of the bubbles, the amount of annual turnover that we are able (or estimate) to generate on average with that particular customer.
This gives an extremely significant and easily comparable result at a glance: customers are represented in a sort of matrix with 4 quadrants, based on their level of strategic attractiveness and profitability.
The ideal customer is obviously the one positioned at the top and to the right of the graph; in our case it is the Webmaster Srl customer who, in addition to being extremely attractive on a strategic level and in terms of economic results, also has the merit of generating a significant turnover for our company, much higher than that of other customers who, moreover, are less performing based on the parameters seen so far. Customers of this type are excellent customers, not to be lost for any reason and to be cultivated in every way, as they are the lifeblood of the company.
The worst customer , on the other hand, is the one positioned at the bottom left of the graph itself; in the example, it is Alfa Calcestruzzi Spa, which has such poor strategic and economic appeal profiles that it leads us to even consider terminating the commercial relationship. Customers like this are potentially dangerous, and to continue the relationship, it is necessary to work on the margins in every way. If a customer is not very strategic, in fact, it is difficult to ensure that it improves in this respect; however, we could better manage the costs associated with it, or the time dedicated, or even the complexity in executing the relationship, so as to recover efficiency and, consequently, improve the profitability associated with it. Returning to the graph, it is unlikely that we will see Alfa Calcestruzzi Spa move towards the top of the graph; however, we can implement a whole series of corrective actions to ensure that it moves at least towards the lower right quadrant of the graph, intervening precisely on its margins.