Micro- and Macro-Scenario Analysis in Financial Modeling

8min Read

For an organization to grow, it must take on risk. Understanding the overarching risks as well as the ramifications associated with maintaining and growing a business is the foundation of scenario analysis.

Every growth opportunity is weighed against the financial health of an organization. Whether that opportunity is a small cash purchase or a large capital initiative, each opportunity is ideally vetted in proportion to its growth potential.

Scenario Questions

When considering large capital projects or initiatives, organizations need to construct potential scenarios around how those opportunities could play out. Finance professionals should consider asking questions such as:

  • What aspects of my business will this impact?
  • How does this project or initiative affect my financial solvency in the short and long term?
  • What are external factors outside of my control that could impact the ROI of the project or initiative?
  • Are there cascading ramifications to the financial, cultural, and organizational health of my organization due to this opportunity?

In answering any of the above questions, financial scenarios can easily grow beyond best, worst, and expected cases. Many organizations that use spreadsheets for this type of financial modeling will quickly find themselves with hundreds of versions and a host of unanswered questions.

Scenario-Analysis-billiards-table-example-img

Scenario Analysis and Billiards

Scenario analysis is best performed when you have a full-field view of an organization’s financial health. Without the ability to view each aspect of a business, whether internal or external, a given project or initiative may be executed in a way that is harmful to the business. Similar to billiards, if a player cannot see every shot available to them, they may attempt a shot that places them at a disadvantage for the rest of the game.

Independent of any financial modeler’s skill, performing complex scenario analysis across a business’s entire set of financial statements for a large capital project is virtually impossible in spreadsheets. Scenario analysis inside of spreadsheet applications like Excel takes a much more basic form.

When analyzing a capital initiative scenario inside of spreadsheets, most modelers only look at changes in the income statement and the overall solvency of the organization. Modeling the cascading ramifications of a large capital initiative across an entire organization, including cash flow, is incredibly difficult inside of spreadsheet-based financial models. See how Stetson University uncovered cascading ramifications inside of their financial model in Modeling Vs Budgeting: Organizational Interdependency

Micro- and Macro-Scenario Analysis

Projects and initiatives do not happen in isolation. There are always external economic factors that affect large-scale business decisions. For example, credit ratings, bond pricings, and looming recessions all affect the viability of capital projects, initiatives, and purchases. Scenario-based financial models must incorporate external and internal conditions to maintain relevancy to an organization’s financial future.

Scenario analysis is best defined as the process of stress-testing a business’s finances over a series of macro- and micro-scenarios. Macro-scenarios incorporate both external and internal factors into one overarching financial scenario. For example, a macro-scenario for the construction of a new warehouse might include:

  • Purchase price of building materials
  • Cost of hiring a construction firm
  • Building completion timeline
  • Current debt pricing
  • New staff for the warehouse
  • Economic conditions upon completion

Each component of the above macro-scenario are themselves micro-scenarios. For example, the purchase price and quality of building materials can vary between vendors and hiring additional staff can change depending on completion time of the new warehouse as well as a host of additional factors.

If each micro-scenario has three different possibilities, that yields 36 or 729 unique financial scenarios!

Scenario Analysis and Budgeting Applications

Budgeting applications like Anaplan and Adaptive Insights purport the ability to perform scenario analysis. However, their scenario analysis is only on the macro-level without the ability to quickly adapt or alter micro-scenarios or examine interactions in isolation.

The same foundational technology that makes these applications so well-suited to budgeting is what limits their ability to perform in-depth scenario analysis. Budgeting applications are data-oriented, meaning they must copy both the historical and projected data each time a new scenario is analyzed.

Rather than turning micro-scenarios on or off, budgeting applications must create a new set of financial data to view a micro-scenario in isolation. Using our earlier example, let’s say the business wanted to see the financial effects of hiring on the extra staff at their existing warehouse without building the new facility.

In Synario, this type of micro-scenario analysis is simple due to the formula-based nature of the software. Chosen components of the macro-scenario are turned off to isolate the staffing micro-scenario. The integrated financial model then automatically updates to show the effects of additional staffing over a pre-selected time-range.

In budgeting applications, an entirely new copy of the organization’s financial plan needs to be created and a new staffing assumption is then added into the model. That process can be extrapolated when attempting to view an isolated micro-scenario inside of a given macro-scenario. As scenario copies multiply, they take up valuable pre-allotted storage space that can be costly to increase.

Dynamic Comparisons: The Difference Between What if Questions and Scenario Analysis

Let’s say the previous business wants to identify three different ideal financial scenarios out of the 729 possibilities available in the warehouse example. It is vitally important that the three different scenarios are fully vetted, and the strategic impact calculated prior to submitting the proposal to the board.

To establish credibility in a boardroom, the analysis of both the ideal scenarios and the derivation of those scenarios must be reliable. Comparing scenarios and visualizing differences is a key factor in creating insightful dialogue around strategic directions.

The ability to quickly compare scenarios is a requirement when performing scenario analysis. Without a tool that can dynamically compare scenario outputs, scenario analysis becomes static answers to what if questions.

Compare-dynamic-financial-models

Conclusion - Businesses Need Powerful Scenario Analysis

Understanding how a project or initiative will continuously interact with other business processes is the key to a fully comprehensive financial model. Insightful long-term scenario analysis requires the isolation of micro-scenarios and the stress-testing of macro-scenarios. Once various ideal trajectories are identified, they must be compared against each other on both the micro- and macro-scale to understand effects on overall business health.

To get the most accurate view of a business’s financial future, its financial models need to isolate, analyze, and compare micro- and macro-scenarios.