Whale curve can be easily created in Excel. Most of the Excel users trying to create Excel chart with whale curve are looking at visually analyzing and reporting their customer profitability.
While this chart can be used by anyone in business – most of the users come from companies with large customer base like logistics and manufacturing companies. In addition business professionals who are ABC (activity-based costing) users use the whale chart.
Anyway, what is the whale curve and what are the benefits of using it?
You can use this chart template to visually represent your customer profitability. This means you need to have the profitability for each of your customers. Once you have the data creating the curve is the easy part.
In order to create the curve you need to rank your customers from most profitable to least profitable (with negative profit or loss). The resulting curve shows the cumulative profit (your overall profit) as each customer is added to the curve. In most cases, the most profitable customers create the largest part of the profit – this is where the curve generally rise after which the curve growth is declining and at some point the curve declines as customers with negative profit (loss) are represented on the whale curve.
Looking at the right part of the curve can be alarming because you can visually see the impact of the bottom customers on your overall business profit.
However, at the same time, this is the tricky part for every manager. What do you do to improve the big picture? The first logical step for an outsider would be to fire all the bottom (negative) customers however in most examples this can be a mistake because without those customers the remaining part of the curve will change as well.
Changes of this kind of scale are not made in vacuum without impact on the big picture. Reasons are changes in purchasing power, negotiating power, pricing, volume, economies of scale….
How to Create Whale Curve in Excel?
Download the template and follow the quick instructions
- Gather and organize your customer profit data – the profitability for each of your customers.
- Enter (copy and paste) your customer list in column A in the excel template and the profitability for each customer in the next column (Column B in the template).
- Sort your data by profit descending (click on cell B2 and click the Z-A sort button).
- Column C has the formulas to calculate the cumulative profit for your data. What you need to do is to simply drag or copy the cells in column C to adjust for your customer list and the data will update.
- Adjust the scales in your chart – double click the X axes and adjust the range (min and max values based on your data). Do the same adjustment for the Y axes range for minimum and maximum values. This will give you the right focus and curve for your data.
You can download the Whale Curve Excel Template here
Note: Gross margins and gross profit can be misleading so if your profitability data includes profit per customer at gross level your whale curve does not represent the true net profitability. For best and most reliable customer profit analysis use ABC to calculate the net profit for each customer.