Assignment
The company’s IT system was not brilliant. It was a relatively old system and was not particularly user friendly. Having said that, it did allow sufficient information to be exported into excel to allow pretty detailed margin reports to be produced. It was therefore possible to develop clear pictures of where margin was improving or deteriorating by individual customers and by individual products.
The potential solution was invisible to the finance team. They could not see what was available because they did not have the commercial experience to understand what would be valuable to the Sales Team nor the analytical ability to manipulate large amounts of data.
The first exercise was to export data from the software system which was in effect a dump of sales invoices by order line for every month going back some 15 months. It was also possible to download the FIFO value of the product at the time of the sale.
By manipulating that data, we could rank customers in descending order of sales. This flagged up to the Sales Team what proportion of total sales that each territory and each customer represented. It also identified which products each customer was buying (and thus was not buying).
The data was then flipped to base the analysis on sales by products rather than sales by customers. Total sales were analysed by product group and then individual product to produce a league table of the best-selling products. We could then see which customers were buying the top products (and thus who was not buying the top products).
We then focused on the top 10 customers that represented about 60% of total sales. We worked out the margin (before rebates) being earned by each of the top 10 customers each month.
We could therefore compare the margin earned for each customer with (a) previous months and show whether margin was improving, deteriorating or stable and (b) compare one customer’s margin with the margin earned from the other top 10 customers to see whether there was a gap.
We were then able to go a step further. We broke margin down into the customer’s overall average selling price per unit and overall average FIFO cost per unit. We would therefore see whether margin movements were being caused by changes in selling prices or changes in buying prices.
Where a customer’s overall margin moved adversely, we drilled down to the individual products that they had purchased and did the same exercise for every product that they had purchased. We could therefore pinpoint which product(s) had caused the adverse movement and whether that was down to selling prices or buying prices.
Where we discovered that margin was being impacted due to buying prices, we discovered that (a) the communication system regarding supplier price increases was poor and (b) that the reaction time to debate the implication for sales prices was far too slow.
We then took the overall margin by customer and blended in the impact of the customer’s annual rebate scheme to get to the net overall margin being earned on each customer and the margin from their most frequently purchased products.