Increasing forecasting confidence with sensitivity analysis

Here’s an example of a forecasting analysis I provided for a former client. Assume, for this example, that we were forecasting cell phone expenses and direct labor costs for a manufacturer of industrial fasteners and bolts.

At the outset, cell phone expenses were classified as Level 4: low variability, low impact. After all, rates should be known in advance (allowing for an advance-purchase discount); and HR should also be able to provide headcount, along with hiring/severance expectations.

Both factors are largely controllable. Cell phone expense is not a direct cost, has little impact on strategic decisions, and is unlikely to change from period to period. So, when forecasting this line item, we can probably project it with a high degree of precision – either based upon historical run rates or as the product of future estimates of headcount and monthly rates.

Because of the low variability and low impact, I considered a sensitivity analysis largely unnecessary.

Direct labor costs, on the other hand, were given a Level 1 classification – high variability, high impact – with a view to improving them to Level 2 (low variability, high impact) through better planning and cost control.

Part of the challenge in this case was that the company was spending 22 cents on direct labor for every dollar of sales. I considered this to be high-impact, compared to the company’s peers and across the industry.

Labor rates are generally known in advance, but the volume of hours – time spent on the manufacturing process – was highly variable, because the number and size of projects for future months was constantly changing.

So it was vital to conduct a sensitivity analysis on these costs. The analysis would illustrate at what levels of project volume, and over what period of time, we were likely to see corresponding labor shortages and overtime.

Conducting a sensitivity analysis is the easy part; interpreting its findings and acting upon it is harder. What this sensitivity analysis showed was that certain, known levels of manufacturing activity made labor inefficiencies worse.

One remedy we identified was to allocate labor hours across projects in similar stages of completion. We soon saw that the wide range of unexpected hours – which had led to increased overtime spend and a need for additional labor –narrowed to more manageable levels.

While we could have come to this conclusion without a sensitivity analysis, the tool gave us the insight which helped put the decision into context.

How to do a sensitivity analysis when things get complex

The most common tools for sensitivity analyses are easy to use and can be widely applied. For forecast models that are simple – or where an analyst seeks to analyze a small number of variables – these tools are excellent. However, financial modelers who are accustomed to modeling in traditional spreadsheet software may oversimplify sensitivity analyses based upon the tools conveniently provided.

Because these tools are rudimentary, they quickly become cumbersome when more than two input values vary at the same time – as is the case in most business scenarios. Forecasts are often complex, considering a wide range of simultaneously changing variables. In fact, in my advisory work, financial leaders often deflate basic sensitivity analyses with one question: “What’s the likelihood of that outcome?”

To answer this question, rather than rely on basic tools for advanced forecasting, companies should consider using “probabilistic” sensitivity analysis. This handles uncertainty across all variables, simultaneously.

With this kind of analysis, the extent of the variability must be defined using either known or projected distributions. Known distributions may be based upon historical data or behavior; unknown distributions may be based upon human best guesses, or computer-generated estimates.

Once base parameters are defined, the analysis will show not only the range of possible outcomes when key drivers change, but also the likelihood of those outcomes – addressing one of the core questions decision-makers are likely to ask.

In the direct labor case, for example, we could determine that we were likely to experience a labor shortage in about 25%–30% of manufacturing runs, which meant delays in project completion and an erosion of gross margin. By knowing the likelihood of issues like this, we can determine the extent of the impact and how we wish to remedy it.

Sensitivity analysis: risk of losing sight of key variables

Despite our human abilities and the capabilities of software, analysts should be careful not to overwhelm their planning models or analysis by sensitizing too many variables. If you do this, it’s possible to lose sight of the most important elements. In the example above, related to EBITDA, because sensitizing may have been performed on so many drivers, we may inadvertently minimize the impact of one element (such as sales mix) while unconsciously maximizing others.

Tools such as Monte Carlo can help an analyst determine which variables have the greatest impact on outcomes, allowing the analyst to pinpoint which variables should be sensitized. Instead of merely reporting what the planning models or analyses say, financial professionals can consider sensitivities, understand them thoroughly, and make better recommendations.

And if you know which variables can be controlled and to what extent, you can make recommendations which help the organization stay on track toward the outcomes it wants.