At a glance
Some years ago, when Liam Bastick was working as a consultant in the UK, he was involved in the due diligence and subsequent acquisition of a parcel delivery company in Germany.
To understand the business and to develop some modelling scenarios, Bastick identified the top 10 drivers for the performance of the organisation.
Most of those were standard drivers such as the labour costs and bricks and mortar premises, but the German management team (which was staying on tethered by “golden handcuffs”) added two: the size of the vehicles and the uniforms of the delivery teams.
What the Germans knew, but the British acquirers didn’t, was that German highways have different speed limits for different types of trucks, and this had a significant impact on delivery times and the company’s guarantees of next-day delivery.
Also, the German public like their postmen to be smartly and traditionally dressed, and the preference was for a navy blue uniform with silver buttons. In a competitive market this could be a differentiator in customer retention.
What are “tornado” charts?
All of this went into Bastick’s modelling exercise, for which he used Excel and a two-stage technique for key driver identification he has developed using “tornado” charts and sensitivity analysis.
They are called “tornados” because it is a two-sided bar chart with the two data bars horizontally opposite each other.
The biggest bars are at the top of the chart and the bars progress down to the smallest, creating an outline shape similar to a tornado and making the data easily comparable.
“All companies need to identify what the key drivers of their businesses are, and while many have a gut feel, they often can’t quantify it,” says Bastick.
“Many companies will build a tactical model with up to 300 inputs, but the tornado chart approach is based on the 80/20 rule focusing on drivers that stand out beyond the rest.
“What this exercise does is firstly, without bias, identify the drivers and then adjust the inputs based on real-life assumptions to gain a deeper understanding of the impacts.”
Using Excel to identify key drivers
There is no special Excel button for this; rather, it is an exercise that can be completed from first principles in the Excel bar chart to identify key business drivers and how business performance will change as the drivers vary.
This can be done on Excel 2003 and earlier through a process that begins with selecting the Chart Wizard, or on Excel 2007 and later by initially tapping on the Ribbon, selecting “Bar” from the “Charts” group and “Clustered” on the “2D Bar” section.
Bastick describes a two-step process to the modelling in which a number of identified business drivers are “flexed”, one by one, to see the impact they will have on outcomes.
In the first stage, which Bastick calls the “deterministic stage”, each variable is flexed negatively and positively by the same amount, without bias, regardless of whether this would be achieved in practice.
This identifies which drivers have the largest impact if they are all flexed by the same amount (one at a time) from a base position, and they are all then given a ranking based on the total variation.
The second stage, the “non-deterministic” stage, is a repeat of stage one, but with the flex amounts modified and informed by an inside knowledge of the business and by a reality check.
Foreign exchange rates, for example, may vary by up to 30 per cent in any direction, while fixed costs will swing by a smaller amount, with possibly more likelihood of increased expenditure.
“I see this as a neat process that helps train management to not sweat the small stuff and identify the variables that provide more bang for the buck in the business,” says Bastick, who now operates his own company, SumProduct.
An inexpensive model for modelling
He describes these tornado charts as a way of building a graphical approach to sensitivity analysis, defined as a flexing of one variable at a time to see how changes will impact on key outputs.
Bastick’s tornado charts are horizontal bar charts that reflect how much impact varying a particular input will have an output, delivering both a ranking and a measure of magnitude either in absolute or percentage terms.
All of this can be accomplished, he says, with the use of the almost ubiquitous Excel program and without the need for more expensive modelling tools.
“I really have a bee in my bonnet about the contempt people show for financial modelling,” he says.
“People pay a lot of lip service to financial modelling, but often they don’t use them properly in their decision making.
“I think my approach with tornado charts is an inexpensive and accessible way for many businesses to gain some fresh insights into what is driving their performance, and that has to be something they can constructively use.”
