Four Benefits of Scenario Modeling in Excel

insightsoftware -
July 15, 2021 by insightsoftware

Global provider of enterprise software solutions for the Office of the CFO to connect to & make sense of data in real time, driving financial intelligence across the organization.

Four Benefits Of Scenario Modeling Feature

There’s an old saying in the business world that “All forecasts are wrong.” There’s another adage, often repeated by military leaders, that says “no plan of battle ever survives first contact with the enemy.”

Despite these limitations, every smart business relies upon planning, forecasting, and scenario modeling to establish reasonable parameters for understanding what the future might hold, setting a strategy for the organization, and determining which actions to take in both the short and long terms.

Unfortunately, there are a number of situations which business leaders simply cannot predict. In mid- to late 2019, for example, no one expected that a year later, businesses would shut down, supply chains would be disrupted, and demand curves would undergo dramatic shifts across virtually every industry.

What If

There are numerous other circumstances, though, that are predictable. Demand for a company’s products, though sometimes difficult to estimate, is likely to fall within some well-defined range. Inflation and consumer spending, likewise, will probably fluctuate within a few points above or below the historical average. Major weather events, shifts in the political landscape, or legal and regulatory changes can all prompt some level of speculation as to likely outcomes in the context of a broader set of all possible outcomes.

After the world-changing events of 2020, business leaders are more interested than ever in exploring these kinds of possibilities, modeling best case and worst-case scenarios, asking “what if?” questions, and building contingency plans to make their businesses more agile and responsive.

Microsoft Excel is, of course, a very popular tool for that kind of analysis. It’s extraordinarily flexible and its wide range of available functions allow analysts to develop highly sophisticated models to predict outcomes based on various assumptions. As a standalone tool, Excel does come with some limitations, however.

A better solution is to use a tool that enables you to work with a shared, single source of truth for your planning data, model an unlimited number of scenarios quickly and easily, and work within an environment that is as familiar and flexible as Excel.

Before exploring the best alternative to Excel scenario modeling, let’s explore some of the key benefits of scenario modeling, in general.

Benefit 1. Understand the Best Case, Worst Case, and Everything in Between

One common methodology used in scenario planning is to establish best case, worst case, and likely (or base case) estimates of any given number.

Consider sales forecasts, for example. There are multiple ways to arrive at an estimate of next year’s sales. In all likelihood, you will break that down by product line, region, business unit, or other meaningful measures typically defined by internal boundaries within the organization. For each of those component values, it is usually easier to arrive at a high estimate and a low estimate than it is to determine the most likely outcome. Psychologically, many people are reluctant to arrive at a “likely outcome” estimate at all, because it represents a kind of commitment to a number that is rarely accurate.

Best Case

Scenario modeling based on high, low, and probable outcomes generally works better than a single estimate because it defines the likely upper and lower limits of what could happen.

That, in turn, becomes the basis for building more detailed models around each of those three scenarios. High sales numbers, for example, may necessitate higher resources in production, or may prompt leaders to think about outsourcing some aspects of production and fulfillment. Lower-than-expected sales numbers, in contrast, might lead leaders to consider how to adjust marketing strategy to drive higher revenue.

Benefit 2. Learn to Expect the Unexpected

The whole point of scenario modeling, of course, is to speculate about various possibilities that might or might not come to fruition. Merely by asking the question “Which potential scenarios should we be prepared for?”, business leaders will prompt discussion and consideration of potential business constraints or accelerators that the organization could face.

In the case of potential crisis scenarios (weather events, economic turbulence, or serious supply chain disruptions), scenario modeling in Excel or other spreadsheet tools will help business leaders explore the things that might happen so that they can respond to the situations that do happen. Likewise, there may be potential opportunities scenarios that businesses should prepare to respond to as well. Examples include an unexpected spike in demand or a key competitor going out of business.

There are, of course, situations that present both crisis and opportunity. We know of a manufacturer of retail store fixtures, for example, whose orders plummeted following the initial closures of early 2020. The manufacturer responded by shifting to the production of acrylic barriers for cashier stations, customer service desks, and private offices, where it was impossible to avoid person-to-person altogether. The company acted quickly and decisively, and in the process, it turned the COVID crisis into an opportunity to help solve a problem for customers, keep workers employed, and remain profitable.

Benefit 3. Make Better Choices

Detailed model scenarios help senior managers make effective strategic decisions about the company and where it invests its resources. Consider the example of an early-stage software company whose product can work just as well for smaller organizations as for larger ones. In those kinds of situations, pricing is often very elastic; larger customers pay considerably more for the same product because it generates value for them on a larger scale. Smaller customers benefit less, and therefore pay less.

Which types of customers should a hypothetical software company focus on pursuing? There are several factors that vary greatly for servicing the larger customers versus smaller ones. The sales cycle may be considerably longer and require more effort and expense, for example. Larger customers may demand bespoke features that smaller companies can live without. The two categories of customers may have very different expectations with respect to service levels, which, in turn, can determine the need for additional consulting and implementation staff to serve them.

Board Game Pieces

In one theoretical model, the company might focus exclusively on selling to large customers. In another, the company might sell only to smaller customers.Of course, the company might choose to address a mix of those two categories. Unless or until the company develops scenario models around these various options, it may be difficult to make a well-informed decision about where to focus company resources and energy.

Generally speaking, scenario modeling helps management to explore different options in depth, with full consideration as to the implications each of those options will have across all areas of the company. When they do so, managers are much better equipped to make fully informed decisions.

Benefit 4. Mitigate Risk

Last, but not least, scenario modeling helps companies understand their risk exposure. Consider the case of a construction firm, for example, that has contracted to develop a large-scale residential property (for example, a condominium complex). What will happen if the cost of materials skyrockets (as has happened recently), or if the availability of certain inputs is limited (as has also happened)? Is it possible that investors will walk away and abandon the project? A sharp rise in interest rates, likewise, could lead to a drop in interest from potential condominium buyers. That, too, could lead investors to rethink their commitments. What impact would those kinds of changes have on the hypothetical construction company?

By modeling these kinds of scenarios in advance, business leaders have a much clearer picture of potential areas of risk. That can suggest appropriate risk mitigation measures (such as hedging for price fluctuations) or might even dissuade executives from embarking on certain projects and relationships that present too much risk to their organizations.

Limitations of Excel Scenario Modeling

Scenario modeling is an extraordinarily valuable undertaking. It does require significant effort and expertise, though. To do it efficiently and accurately, business leaders should approach scenario modeling with a clear plan and the proper technology.

As discussed earlier, Microsoft Excel is understandably a very popular tool for scenario planning. However, many users run into limitations because their Excel spreadsheets lack direct links back to live data from their ERP system, or from other business information systems used by the organization.

Consider a typical financial analysis process. An analyst, usually part of the finance team, collects needed information for the process. In many situations, that may include information from the ERP system such as historical sales data, marketing expenses, and the cost of goods sold. The analyst might also be interested in sales pipeline data from the CRM system, with a view to understanding what the company’s near-term sales prospects look like.

The problem is that as soon as that information has been collected and imported into Excel, it is almost immediately out of date because it represents a static picture of the business. Unless you refresh and update that information in your Excel scenario model, you will be building that model on outdated assumptions. While that might not necessarily be a problem in the days or weeks after you develop a scenario model, the fact that the information becomes increasingly stale over time is a significant problem. Scenario models built with static information lose their validity very quickly.

Another issue that arises with scenario modeling in Excel is that, because models have to be created and maintained manually, there is a limit to the number of scenarios finance teams can practically evaluate due to time and resource constraints.

A Better Way Forward for Scenario Modeling

A far more effective strategy is to use a tool that gives you the flexibility and functionality of Excel and offers centralized consolidation of your planning data with near real-time updates. That solution is Bizview from insightsoftware.

With a familiar spreadsheet-like interface, Bizview is a planning solution that offers anywhere, any time access to your planning data in a single repository. Bizview simplifies the planning and scenario modeling process with powerful workflows, automated emails, and multi-level approvals for collaboration with your team. With Bizview, you can quickly and easily create unlimited scenarios, ensuring better decision-making throughout the year.

If you are seeking a way to accelerate your business growth with collaborative and connected planning and modeling tools, insightsoftware recommends Bizview for your needs. Contact us to discuss your scenario modeling strategies and arrange a free demo.

06 2021 Whitepaper 50percentoftime Resource (1)

Recoup 50 Percent of Your Time with the Right Financial Reporting and Planning Tools

Download Now: