10 steps to display revenues and earnings in one chart
This is a short 10 step guide for how to display the earnings and revenues of a business in the same chart. This solution enables interchart comparability and a great/ unique way to visualise performance. The example is done in Excel but the method can be used in any other usecase, too.
- Get your data of revenue/sales and earnings.*
For revenues as well as earnings can be used different figures. For example: net sales, operating revenues, revenues of one business line etc. or EBT, EBIT, EBITDA etc..
2. Calculate the profitability for each pair of data.
“m” is defined as the variable for the profitability in this article, because the profitability is sometimes called margin, too. The calculation for it is:
m = earning / revenue
3. Arithemtic mean of the profitability (margin)
To calculate the “average” margin in our database, the arithmetic mean is used.
The formula for the arithmetic mean is: m = sum of profitability / sum of terms
4. Calculate the reciprocal of m
“M” is the reciprocal of m.
Formula: M = 1 / m
5. Calculate the range for each
“highRev” and “highEarn” are the highest data points in their database.
“lowRev” and “lowEarn” are the lowest data point in their database.
“diffRev” and “diffEarn” is the range in those databases.
Formulas:
highRev — lowRev = diffRev
highEarn — lowEarn = diffEarn
6. Transform the range into steps
“S” is the number of steps that has to be displayed by both axes. It is an extra unit to make the ranges, calculated before, comparable.
Rates for transformation:
Rate for revenue data: 1S = 1€
Rate for earnings data: 1S = 1€*M
S = diffRev + diffEarn * M
7. Determine the minimum and maximum values of each axis
“minRev” and “minEarn” are the minimum values displayed on the axis.
“maxRev” and “maxEarn” are the maximum values displayed on the axis.
maxRev = highRev
minRev = highRev-S
minEarn = lowEarn
maxEarn = lowEarn+S*m
8. Comfort zone
This step is not necessary to create the graph itself but it is recommendable as it improves the legibility of the whole picture.
The comfort zone is some extra space on the top and the bottom of the graph to make it look not that squeezed. The size of the comfort zone depends on the values in the database as well on a personal taste. It is implemented by widening the range that is displayed in the axes in the same ratio than the axes were scalled before.
“c” is the comfort zone that we want to add, in Euros.
So the new formulas are:
maxRev = highRev + c
minRev = highRev-S-c
minEarn = lowEarn-(c*m)
maxEarn = lowEarn+(S*m)+(S*m)
9. Create the chart
The chart has to be created with the given databases and a second axis. Afterwards the minimum and maximum values of the axis have to be adjusted manualy. Fill in minRev, maxRev, minEarn and maxEarn.
10. I am sorry, there are only 9 steps.
The whole explanation of the method and the idea behind it can be found here.
*The data used in this example is from the following sources: 1., 2.
