How sensitive is your model to impacts?
1 July | by Carl Seidman and Lance Rubin
Introduction to the co-author
Carl Seidman, founder of Seidman Global LLC and Seidman Financial, is a trusted business advisor specializing in financial planning & analysis (FP&A), business strategy, and finance transformation.
He coaches and advises FP&A professionals at Fortune 500 corporations and middle-market companies, helping establish greater uniformity in practices and process.
At the same time, he brings finance professionals greater control over their careers by helping them build their competencies while eliminating time-wasting activities and mistakes.
His strategic finance training curriculums and FP&A development programs are among the most in demand in North America and are frequently delivered by leading financial seminar companies.
Carl occasionally serves as a CFO advisor to a select number of lower mid-market and entrepreneurial businesses throughout the United States and Europe.
Carl is a CPA and has earned other professional credentials including CIRA, CFF, CFE, and AM (Accredited Member in business valuation).
He has a master’s degree in accounting and bachelor’s degree in finance and economics. He lives in Chicago with his wife and twin sons.
Why did Carl select the topic and why is he passionate about it?
When you build a forecast or analysis, you’re seeking an answer by doing your best with the information you’ve got.
Rarely do you have the perfect set of assumptions/data and almost never have an unlimited amount of time to find the answer in order to make critical decisions.
Instead, you have to make the best determinations possible with the limited information and time you have.
Sometimes it goes well, and sometimes you get burned, badly.
But that’s exactly why I love business finance and strategy – decisions are based upon logic, emotion, and best guesses – not necessarily exact truths. Isn’t that the reality in life as well as business?
The reason I chose to write about sensitivities is for these very reasons.
Because we don’t have perfect information, it’s wise to consider a lot of different assumptions that could be true and then adapt accordingly.
It’s important to contemplate a range of possibilities rather than just commit to one. That’s what sensitivities encourage: examining the range of outputs based upon a range of assumptions.
Topic and context in 3 sentences
Sensitivities are all about seeing how changes in an identified variable impact certain outputs and outcomes.
When probabilities and elements of uncertainty (which are always present) are overlaid onto your analysis, sensitivities can tell a whole lot about your business environment and not just about the numbers.
When you thoroughly understand how financial (and even non-financial) variables impact each other, you can far more effectively manage your business.
If you had to teach this topic in a class to school kids what key tips would you give them to focus on
While I don’t teach school-age kids, I do facilitate live training and development programs for up to 3,000 entry-level, middle-management and senior-level strategic finance and FP&A professionals each year.
At its core, a sensitivity analysis is a basic concept. It considers the impact of certain variables or drivers on identified outputs.
In other words, it helps us see how one factor in a business impacts another. Kind of like how if you eat too much food you can get a stomach ache, or if you spend too much time in the water your skin starts to go wrinkly.
For example, if a company wants to calculate or gauge how likely it is to hit a target for operating profit six months into the future, it must understand the drivers and inputs that influence that measure.
Operating profit, which is calculated far down the profit and loss statement, is the result of subtracting operating expenses from gross profit. Further, gross profit is the result of subtracting direct costs from revenue.
Thus, if the company were to perform a sensitivity analysis on operating profit, it should seek to understand how variability of revenues, direct costs, and operating expenses play a role.
Within each of these vital P&L categories are other dimensions such as product mix, customer mix, pricing, currency exchange, timing, variable costs, fixed costs, and operating assumptions.
To run a sensitivity on operating profit, we could in effect sensitize any one or all of these other dimensions. The result may be to illustrate that operating profit increases by $13,500 for every $10,000 improvement in labor efficiency (captured within direct costs).
Average customer lifetime tenure (captured within multi-period revenue) may increase 14 months, with a standard variability/deviation of 3.4 months, for every 80 hours spend on customer servicing (captured within opex).
Normal approaches to forecasting may not lend this insight whereas sensitivity analysis often will.
The mechanics of conducting a sensitivity analysis is the easy part. There are plentiful software platforms that can perform it with ease. However, it is the interpretation of the analysis finding that is most challenging and also most valuable.
If, for example, we determined that labor efficiencies of just a couple percentage points can result in $10,000 in its savings, and that this level of savings results in a $13,500 contribution to operating profit, the company would seemingly be motivated to act upon this improvement.
As an example, how could a company go about improving labor efficiencies?
It may come in the form of manufacturing process improvement, tooling, scheduling effectiveness, training, and manufacturing automation among others.
On the customer servicing side, we may determine that 80 hours of incremental time are not all equal. At a cost of $2,200 per year per customer, only certain checkpoints and conversations are found to result in longer tenure.
While finance leaders and analysts alike can deliver the financial implications of the sensitivity analysis, they must collaborate with those in operations and customer service to make the financial realization a reality and extract real value from decision making.
Here is where many organizations struggle. There is often a disconnect between finance and operations in that operations don’t regularly or thoroughly contemplate the financial impact of improvements or inefficiencies. While those in manufacturing, at face-value, are keenly aware of the consequence of labor inefficiencies, many in manufacturing aren’t going to be able to quantify the impact.
On the flip side, in the lower-to-mid tiers of the finance function, it is often less common for financial professionals to be commercially savvy and therefore able to effectively make these recommendations without a significant understanding of the manufacturing process. Particularly in mid-sized and small organization, this task often falls to senior finance leaders, which isn’t the best use of their time and a lost opportunity for the finance analyst to grow.
Therefore, it’s vital for autonomy to be granted to finance staff and enable them to coach those in operations to understand how their process improvement ultimately carries over to the financial side of the business.
The Mechanics of Sensitivity Analysis
As mentioned previously, sensitivity analyses can be conducted with relative ease across many financial and analysis software platforms.
Perhaps the easiest mechanical tool is using the data tables tool in Microsoft Excel or self-referencing IF statements.
For forecast models that are straight-forward and simple or where the analyst is tasked with analyzing just a small number of variables, these tools are sufficient and easy to work with. However, analysts should be wary to rely too much on these tools as they can also be overly simplistic.
Businesses are complex and need to consider simultaneously changing variables. Because these tools mentioned above are so basic, they may quickly become too manual or cumbersome when more than just a handful of input sensitivities are desired.
In more complex business decisions with, dozens or even hundreds of variables, as is the case in many business scenarios, these tools are quickly outgrown.
As a consultant and advisor, when I work with companies on planning, it is not good enough to merely provide a sensitivity analysis of a single variable or a small handful of variables.
Instead, I’m required to understand the more complicated context and how multiple variables interact. In fact, in my advisory work, a question I’m commonly asked is: “what’s the likelihood of that outcome?” The basic tools and models built by many financial analysts will not be able to offer much insight.
Instead of giving credence to basic tools for advanced forecasting, companies should explore employing more probabilistic sensitivity analyses, which can quickly and simultaneously contemplate uncertainty and variability across inputs.
Probabilistic sensitivity analysis will demonstrate not only the range of possible outcomes given changes in key drivers, but also the likelihood of those outcomes being realized, thus again addressing one of the core questions decision-makers are likely to ask: “what’s the likelihood of that outcome?”
In the two straight-forward examples above, we did not apply any probabilities to the sensitivity analysis outcomes. In other words, any analyst would easily conclude that the company should invest substantial funds into improving labor efficiency and customer services.
However, once we go deeper into probabilities and uncertainty, we can more definitely quantify how much expense is worthwhile and where.
Let’s assume for a moment that our analysis determined that the company was likely to experience a labor shortage in approximately 45%-50% of manufacturing runs.
This, ultimately meant delays in project completion, an erosion of gross margin, and ultimately a diminishing of operating profit.
By knowing the probability, or likelihood of this issue, we can determine the extent of the impact and how we wish to remedy. We may determine that a reduction of labor shortage frequency from 45%-50% down to 20%-25% comes at an incremental labor cost of $215,000 but ultimately brings the company incremental profit of $85,000.
In my experience, it is not usually sufficient to present our findings without providing further color into likelihood, impact, risk, and uncertainty. Stakeholders expect and deserve more.
Despite our human abilities and the capabilities of software, and despite my inference that Excel’s data tables are often overly simplistic, analysts should be careful not to overwhelm their analysis by sensitizing too many inputs.
In doing so, it becomes easy to overwhelm the analysis and lose sight over which elements are most important to the business.
In the example above related to operating profit, I’d suggested product mix, customer mix, pricing, currency exchange, timing, variable costs, fixed costs, and other operating assumptions could all play into changes in operating profit.
If I were to perform sensitivities on all these factors, I may overcomplicate my analysis and lend too much weight to one factor while minimizing others inadvertently.
Look out for some of the upcoming articles on more advanced techniques for scenario analysis and simulations.
What practical steps can people take now to learn more?
In my consulting experience, I’ve sometimes elected to employ tools that can help companies determine which inputs have the greatest impact on outcome and to what extent.
One easy-to-use and common approach is to conduct a waterfall analysis.
A waterfall analysis illustrates which drivers have the greatest impact on an output. It essentially illustrates the ‘bridge’ between a starting point and ending point.
A second approach is to use Excel’s built-in data table and self-referencing IF approaches. They’re very easy to learn and can be utilized effectively by beginning and expert modelers alike.
Another useful visual is a tornado chart which, like the waterfall, looks at key underlying drivers and the impact eac of those have on a particular target outcome like valuation.
When conducting sensitivity analyses, it is vital to select those inputs that have the greatest amount of variability and/or impact to determine which deserve the most attention from a management perspective.
Recognize that many analysts myopically view sensitivities merely as a mechanical exercise in displaying how a range of inputs impacts outputs. The greatest value in sensitivity analysis is understanding what the results mean and how to influence performance.
For the benefit of non-financial professionals and financial leaders alike, we should be able to provide them with deep insights into the drivers and risks in the business.
We should educate them on how the targets and objectives of the business will be impacted by certain variable. And we should further be able to convey the extent to which we can control, or not control, activities related to those variables.
I am not a fortune-teller and cannot know what the future will hold for most of the companies I work with. I can assist them in their planning and forecasting efforts but those plans and forecasts are going to be wrong to a certain extent.
Despite my confidence in their incorrectness, we can still heavily rely upon their ‘directional correctness’ to aid in our decision-making.
This is the essence of sensitivity analysis – being able to identify the impact, quantify this impact in terms of value and risk, and make confident recommendations accordingly.
Where are good places (links) to find out more on the topic
This is a video Carl did about 4-5 years ago for the American Management Association for a course I’ve delivered on an off for them for about 10 years.
Here are is a webinar Carl did with one of his partners on FP&A (with a little bit of discussion on sensitivities) about 4 years ago
Carl is always publishing new content on his LinkedIn profile and websites. Here are links to those:
How important is this skill in the context of learning Financial Modeling?
Sensitivity analysis is paramount to learning financial modeling.
In forecasting models, you’re conducting an analysis of the future based upon assumptions you believe will be true.
Some of those assumptions are going to have strong bases and others may not.
And ultimately, both ways, you’ll be wrong.
But just because you’re wrong doesn’t mean your model can’t be ‘directionally correct’.
Would you rather have a model with one set of assumptions and zero idea as how changing assumptions will impact your results?
Or alternatively, would you prefer a model with several sets of assumption and a thorough understanding of how changing assumptions will impact your results?
The former is a reactive mindset whereas the latter is about agile decision-making and where professional modelers perform at their best and show their real value.
Large multi-million dollar deals are done on Wall Street and around the world based on financial modeling. If we are able to capture even a small portion of that value internally within our finance teams it will be game changing.
How does all this disruption, AI and automation talk impact this topic
While I haven’t yet seen it robustly implemented, machines should be able to evaluate a range of options provided through a sensitivity analysis and recommend decisions related thereto.
We need to be cautious, however, in that machine-learning is far off from being able to emulate human decision-making and also infer and influence other humans to perform.
In the not-so-distant-future, we should see more sensitivity analytics and simulations facilitated by AI that has historically been done by humans, but we are a few years off from being able to remove the human completely from the process.
If you want to find out more and follow the rest of the article series be sure to download the Financial Modeling App
If you want to find more information on financial modeling and content visit the Model Citizn website.