fbpx Skip to content

Four Benefits of Scenario Modeling in Excel

insightsoftware -
January 27, 2022

insightsoftware is a global provider of reporting, analytics, and performance management solutions, empowering organizations to unlock business data and transform the way finance and data teams operate.

Optimization And Combination Benefits Of Scenario Modeling In Excel

Every smart business relies on planning, forecasting, and scenario modeling to:

  • Gain a better understanding of what the future might hold
  • Set a viable strategy for the organization
  • Determine which actions to take in both the short and long terms.

Even during times of relative stability, these analytical tools are valuable in helping guide important decisions. As the business climate grows more volatile, planning, forecasting, and scenario modeling become essential.

Group Meeting Around A Table

It is difficult to predict future circumstances. Inflation is on the rise. Hiring workers is challenging for many businesses. Supply chains throughout the world are under stress. Technology continues to spur innovation, creating opportunities for some and threats for others. When the ground seems to be shifting beneath your feet, scenario modeling can provide valuable insights to inform important strategic and tactical decisions.

Rapid change creates a mandate for agility, calling on business leaders to do everything they can to foresee potential threats and opportunities on the horizon. Whether impending changes take the form of economic uncertainty, major weather events, shifts in the political landscape, or legal and regulatory changes, it pays to spend some time understanding the potential outcomes and how they might play out in the context of a broader set of all possible outcomes.

Microsoft Excel is a very popular tool for performing that kind of analysis because it is flexible and offers a wide range of functions, enabling analysts to develop highly sophisticated models to predict outcomes based on various assumptions. As a standalone tool, Microsoft Excel does come with some limitations, however. More robust alternatives allow finance teams to collaborate around a shared platform, with a single source of truth that incorporates all planning data and allows for modeling an unlimited number of scenarios quickly and easily.

Four Key Benefits of Scenario Modeling

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

Understand Your Best Case and Worst Case Scenarios

A very common methodology used in scenario planning starts with laying out a best, worst, and  likely case estimate. Consider a situation in which your company is evaluating the possibility of opening a new retail location. If the store is a resounding success, what will the financial impact be on your company? The answer requires an understanding of the capital investments needed (including leasehold improvements), advertising promotion, personnel expenses, and other costs; as well as an estimate of gross sales and margins.

In your best case scenario model, you will go with high estimates with respect to margins and sales revenue, and lowball the cost estimates. Pair this with a worst case model in which you do the opposite. These two scenario models will stake out a range within which your new retail location is likely to perform.

It’s common to create a “likely case” scenario that functions as a mid-point between the two extremes. It is often easier to arrive at a high estimate and a low estimate and build your likely case around the midpoint between those two than it is to determine the most likely outcome from scratch. 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.

Reading A Document

Once you have developed best case, worst case, and likely case scenarios; you can begin to model different variations on those. For example, your outcomes could look very different depending on whether you rent retail space for your new store or purchase a building. Furthermore, you may wish to look at the differences between a costly space in a prime location versus less expensive real estate in a location that draws a bit less traffic.

Expect the Unexpected

Today’s business environment is rife with change. Whether it’s a shortage of raw materials, high levels of inflation, regulatory change, or challenges in hiring qualified workers, business leaders must prepare to respond quickly to a wide range of factors that influence their business success.

The point of scenario modeling 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 prompt discussion and consideration of potential business constraints or accelerators the organization could face.

In the case of potential crisis scenarios (weather events, economic turbulence, or serious supply chain disruptions), scenario modeling prompts leaders in an organization to explore the things that could potentially happen, to respond effectively to the situations that ultimately do happen.

Make Better Decisions

Scenario modeling helps senior managers formulate strategic decisions based on quantifiable information. There are numerous examples of how this practice supports key strategic decisions.

Imagine an early-stage software company whose product can work just as well for smaller organizations as for larger ones. Pricing in that situation may be 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.

On which types of customers should a hypothetical software company focus? 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.

Leaders in the hypothetical software company might build two distinct scenario models, one focusing exclusively on selling to large customers, and another in which the company sells only to smaller customers. Of course, the company might choose to model a third scenario in which they address a mix of those two customer audiences. Unless or until the company develops scenario models around these various options, it  will be difficult to make a well-informed decision about where to focus company resources and energy.

Scenario modeling helps management to explore available 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.

Risk Mitigation

Scenario modeling helps companies to better understand their risk exposure. Consider 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, or if the availability of certain construction materials is limited? Could investors potentially 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 changes have on the hypothetical construction company?

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

Best Practices in Scenario Modeling

To get the most out of your scenario modeling efforts, keep these best practices in mind.

Choose Your Variables Wisely

In the context of scenario modeling, a variable simply refers to an important factor that could change the course of your business. Usually these are associated with “what if” questions, such as “What if actual sales fall short of the forecast (or significantly exceed the forecast)?”

Start with a fairly limited set of important variables that could have a big impact on your business. Consider the way businesses typically assess risk: the formula combines the probability of a given event (that is, how likely it is to occur) and its impact (that is, how severe the consequences would be if it comes to pass). You should choose your “what if” variables based on a similar type of analysis. Which factors are most likely to impact your business and have significant ramifications?

Reading A Document

Focus on the Right Level of Granularity

Develop detailed scenario models, but don’t go overboard. There is a trade-off between granularity and flexibility. The more detailed your scenario models get, the more work you must do to create and update them. If you’re using the right tools, you can automate much of that process, but the general principle is to make your models as simple as possible while still providing useful analysis of potential scenarios.

Do Not Limit Your Models with Incomplete Data

Many companies rely solely on ERP data, which includes historical sales transactions, for example. Because scenario modeling is ultimately about predicting the future, it pays to look at near-term sales pipeline data from the CRM system as well. That data may point to potential trends that could impact the accuracy of your predictions.

Automate When Possible

In most organizations, Microsoft Excel is the default choice for data analysis, including scenario models. Its inherent flexibility makes it an almost ideal tool for this purpose. Unfortunately, Microsoft Excel doesn’t quite hit the mark for truly granular planning functionality.

Limitations of Excel Scenario Modeling

Scenario modeling is an extraordinarily valuable undertaking, but it requires the right expertise and sufficient commitment. To do it efficiently and accurately, business leaders should approach scenario modeling with a clear plan and the proper technology.

Many users encounter limitations with Microsoft Excel because their spreadsheets lack direct links back to live data from their ERP system or from other business information systems  in use.

Consider a typical financial analysis process. An analyst 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 the company’s near-term sales prospects.

The problem is that as soon as the analyst collects and imports that information into Microsoft Excel, it is almost immediately out of date because it represents a static picture of the business. Unless you refresh and update the information in your scenario model, you will be building that model on outdated assumptions.

That might not necessarily be a problem in the days or weeks after you develop a scenario model, but the fact that the information becomes increasingly outdated over time is a significant problem. Scenario models built with static information lose their validity quickly.

Another issue that arises with scenario modeling in Microsoft Excel is that, because you must create and maintain each model manually, there is a limit to the number of scenarios you 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 Microsoft 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, anytime 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: