Working on a laptop

Four Best Practices for Scenario Modeling

You’re familiar with the old saying that you should “hope for the best, plan for the worst.” In a rapidly changing world, you can carry that advice a bit further: plan for a range of possible outcomes so that whatever happens, you’ll be in a better position to respond appropriately. Enter scenario modeling.

Scenario modeling is the practice of developing financial models based on several possible outcomes, and developing plans around each of those situations. By thinking through the implications of each scenario, business leaders can thoroughly explore their options in advance and agree on the measures they will take given a specific set of circumstances.

Very often, businesses develop scenarios for “best case”, “worst case”, and a “likely case” outcome, although that isn’t necessarily always the case. Some situations call for a binary view of possible outcomes (for example, pending legislation that could affect sales of your product is generally an either/or scenario in that it passes and becomes law, or it doesn’t). In either case, it will affect your business.

Let’s look at some of the best practices for financial scenario modeling.

1. Choose Your Key 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 might be associated with “what if” questions, such as “What if actual sales fall short of the forecast (or significantly exceed the forecast)?”

It’s best to start small. Begin with a fairly limited set of important variables that could potentially have a big impact on your business. If you are a manufacturer, you may want to explore the effects that just a spike in commodities prices might have on your business. If you run a real estate investment firm, you might consider interest rates, the cost of construction materials, or larger economic indicators that would correlate to higher (or lower) occupancy rates.

A Choice Of Two Ways. Woman At A Crossroads.

When choosing your variables, focus on things that could most dramatically impact your business. Consider the way that 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 would have significant ramifications?

Scenario modeling can get complicated very quickly, so if you’re just getting started with it, you are advised to begin with a single variable. A common example might be sales. What if revenue comes in well under the forecast? What if sales exceed expectations? Typically, this process involves building models to explore outcomes based on these kinds of “best case”, “worst case”, and “likely case” outcomes.

2. Focus on the Right Level of Granularity

How detailed should your scenario models be? The short answer is “as detailed as necessary, but not more so.” Let’s consider the previous example in which sales come in well under forecast or over forecast. What further information will you need that could impact business decisions for each of your three scenarios?

Say, for example, that you sell three different product lines. You manufacture two of those three in-house, which involves extensive supply-chain planning, production capacity planning, and so on. The third product line demands far less in terms of internal resources because production is outsourced to a third-party contractor. If there is little correlation in sales among the three product lines, then it probably makes sense for your financial models to be broken down with details for each of the product lines. After all, a sales shortfall for your outsourced product will have little impact on staffing and production, whereas the other two have far-reaching implications in those areas.

Magnifying Glass

If your sales are primarily based on a single product category that can be treated as a monolithic whole, you are probably better off building a model around aggregate sales. Generally speaking, there is a trade-off between granularity and flexibility. In other words, the more detail your scenario models get, the more work is involved with creating and updating them. If you’re using the right tools, much of that process can be automated, but the general principle should be to make your models as simple as possible while still providing useful analysis of potential scenarios.

3. Mix It Up (Your Data, That Is)

A common problem with scenario modeling is that companies fail to include information from multiple data sources. In many businesses, you can predict near-term sales fairly accurately by looking at sales pipeline numbers from the CRM system. If you are building a scenario model that only includes transactions from the ERP system plus forecasts that were created at the beginning of the year, then you are missing some valuable information that sits in between those two.

Many scenario models even include data from external sources. Commodities futures, for example, might in some cases serve as an effective proxy for understanding trends in the prices of raw materials. Currency futures and other financial instruments, likewise, can shed light on some of the elements that may factor into your models.

4. Automate as Much as 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, though, Excel doesn’t quite hit the mark for truly granular planning functionality.

Excel lacks the ability to easily access real-time business data from a company’s ERP software, CRM system, or other sources. Most financial analysts work around this problem by exporting data from an external system and manually importing it into Excel, but this is generally labor-intensive, and it often introduces errors into the data.


Another limitation of using Excel as a standalone tool for scenario modeling is its lack of integration with budgeting and planning tools. If your business manages its budgets in Excel, you could potentially link your scenario models to those workbooks, but that process is generally unwieldy. Excel also wasn’t designed for effective collaboration and discussion across a wide group of stakeholders, so it cannot host conversations and comments that include a large group of participants.

To do scenario modeling right, business leaders should look to planning and budgeting software that has all the flexibility and functionality of spreadsheet analysis, but which supports near real-time links to ERP, CRM, and other systems that store critical business information. The solution is Bizview from insightsoftware.

With a familiar spreadsheet-like interface, Bizview 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.

08 2021 Webinar Bv August Resource (1)

Bizview: A Robust, Web-based Planning Solution

Download Now: