Turnover figures

Interpreting figures

Category: SQL

Introduction🔗

There is a saying: "What you cannot measure, you cannot improve." And because almost al companies want to improve there profit, there is a large incentive to steer on turnover figures. Unfortunately a lot of reporting or 'BI' tools present the data in a way that still takes a lot of cognitive effort for managers to interpret. this post will show anonther way to display the data.

Allthough we will focus on turnover/prognosis figures, the same method is valid for other things that you'd want to improve. When you think about it, you can measure everything. Say you want to improve the 'cleaness' of a factory. You can make dayly pictures of 'defects' and count the pictures that were reported per day.

Prognosis🔗

It is most likely that there is a kind of planning/estimate/prognosis/budget/targe present. I a lot of companies this is done by the sales department, adding 10% to last years sales figures, then a bit of massaging for the workingdays in the month, then a bit of magic for the startup/shuthown of series, then a bit of bluff for the maintenance schedule. Finaly there might be a table like:

MonthWorking daysProcess AProcess BProcess C
12677502300050000
22470002100045000
32780002400050000
42572502250045000
52780002500050000
62677502300050000

Nice round figures, are sucked-out-of-a-thumb(tm). The good thing is that there is a real change that this will be the official plan that every body agrees upon. CEO, Sales, Production, and CFO. Even the working days that are 'possible' in that month known. That is a good basis, Production and maintenance can focus their schedule to meet these targets. We can also calculate the target for every working day.

 Target per monthTarget per day
MonthWorking daysSales Process ASales Process BSales Process CSales TotalTarget Process ATarget Process BTarget Process CTarget Total
12677502300050000 80750 298 885 1923 3106
22470002100045000 73000 292 875 1875 3042
32780002400050000 82000 296 889 1852 3037
42572502250045000 74750 290 900 1800 2990
52780002500050000 83000 296 926 1852 3074
62677502300050000 80750 298 885 1923 3106

Please note that the table above has 3 process columns and one total column. Instead of process, this could also be a product group, sales person, factory, producton line, department, geographical region etc.

Sales report🔗

The ERP system can probably export a sales report per day. If we are lucky we can find the sales per process and the total per day in one report.

DateProcess AProcess BProcess CTotal
2018-03-180.000.000.000.00
2018-03-194332.004332.004332.004332.00
2018-03-204419.004419.004419.004419.00
2018-03-214506.004506.004506.004506.00
2018-03-224593.004593.004593.004593.00
2018-03-234680.004680.004680.004680.00
2018-03-24586.34586.34586.34586.34
2018-03-250.000.000.000.00

A bit of improvement?🔗

Great we got some figures, The question is "How are we doing right now?" Are we on track? do we need to layof staff?

No worries, we know the target data for the month, we know the daily sales figures, we know the working days in the month. Maybe a quick chat/bribe with the BI team of our IT department can help us a bit.

 SalesTarget
DateSales Process ASales Process BSales ProcesS CSales TotalTarget Process ATarget Process BTarget Process CTarget Total
2018-03-180.000.000.000.000.000.000.000.00
2018-03-19444.001264.002624.004332.00296.30888.891851.853037.04
2018-03-20453.001293.002673.004419.00296.30888.891851.853037.04
2018-03-21462.001322.002722.004506.00296.30888.891851.853037.04
2018-03-22471.001351.002771.004593.00296.30888.891851.853037.04
2018-03-23480.001380.002820.004680.00296.30888.891851.853037.04
2018-03-2460.15173.31352.89586.340.000.000.000.00
2018-03-250.000.000.000.000.000.000.000.00

Ok, now we can compare the real data with the target data. But still, it take a lot of cognitive workload to see if we are doing ok. We could improve stuff:

  • We can use colors: 'green' if 'sales > 1.1' * target, 'yellow' if > 0.95 * target, else red.
  • We can put the sales and target column next to each other.
  • We can round the figures, so that we do not need to parse the 'cents'.

At this point a lot of Reporting engins and BI Teams make a chart. One chart can explain more then a thousand words.

A lot of thees charts look like this.

ugly_graph

Here are a few observations:

  • Maybe last week was super good, or super bad, We still do not know how we are doing this week, month, this year.
  • May we only invoice weekly or monthly, may be we do overtime in the weekends to make up for the month.
  • The target is the same for every day except for the weekends, but it the weekends there could be turnover due to overtime.
  • A lot of reporting engines get the x-axis wrong, when the query has 'gaps' in the dates.
  • Did they pre-invoice stuff, just to bumb the turnover figures for that month?
  • How were the figures of previous month

The visual status.🔗

In the ideal situation we want to see, at glance, how are we performing every day, during the weeks, during the month, during the year. This is possible when we create 3 graphs, each with

  • a period of some weeks (3-5, or since the starting of the year)
  • the full range on the y axis (starting at 0, or the most negative value in every graph)
  • the target figures as a filled area
  • the sales figures as dot (or as a histogram)
  • the values for the y axis need to be the cummulative values for that period.

better_YTD better_MTD better_WTD

This way it is quite clear how the performance is.

  • Are the dots above the filled area? Good.
  • Else: Bad

In the examples below you can see quite wel that:

  • the last 3 weeks were sequential: 'ok', 'bad', 'cathing up',
  • the previous month was ok, this month is lagging behind, but following the trent,
  • the year is good and we are following the trend.

It seems that there is no immediate reason to panic.

Remarks🔗

  • It gets very ugly if you add multiple processes in the same chart. So it is necesary to browse through 'Process A, 'Process B', 'Process C' and the total manually.
  • The figures used for this blogpost are totally faked.