Scenario Analysis

City Financial Models • 24 April 2020

Financial Modelling Scenario Analysis

With the unprecedented Covid-19 pandemic and increased uncertainty around the Brexit outcome, it is very important to be able to run a number of scenarios from your financial models.

For example, how could sales forecasts be affected by any further negative impact on consumer spending or how may the costs of imported goods rise with any weakening of Sterling vs the Euro.

There are a number of approaches to creating scenario analysis in Excel to anticipate a range of potential Brexit impacts.

• Use the Scenario Manager tool that comes with Excel.

• Manually change an input value and record the results.

• Copy the model several times and save a new version each time.

• Create multiple input sheets within the model.

• Use Excel Data Tables.

Each approach has its advantages and disadvantages:

The Scenario Manager tool is quite restricted in its functionality and the scenarios aren’t stored in the workbook, so aren’t very transparent.

Manually changing an input value needs to be repeated for every update of the model and it is time consuming and inefficient.

Saving new versions of the model is prone to consistency errors and it is also time consuming and inefficient.

Creating multiple inputs sheets within a model can make the model excessively large and requires discipline to keep all the input sheets in the same format after any updates.

Data Tables need a dynamic link between the input and output, can be confusing initially and can dramatically slow large models. However, they are also a very powerful analytic tool.

We have used all these approaches and are happy to advise on the best approach for your financial model.

< Older Post Newer Post >

Spill Error - Why doesn’t my Index Match work in Excel 365.

by City Financial Models • 27 May 2020

This something we are starting to see as we work across a number of different versions of Excel. Recently, Microsoft rolled out something called dynamic array formulas in Excel 365, which is a substantial change from previous versions. Dynamic array formulas automatically spill in to neighbouring blank cells. They have also added some very powerful new functions such as FILTER, UNIQUE, XLOOKUP and XMATCH. Even better there is no need to enter array formulas with control + shift + enter (CSE) as in other versions. However, because of the way dynamic arrays work, there will be instances where models built in other versions will have formulas which unintentionally now try to spill or populate into neighbouring cells when opened in Excel 365. Hence the Spill error. To overcome this, the @ character is added automatically to stop formulas unintentionally spilling multiple results into neighbouring cells. Because dynamic arrays are only available in the subscription version at the moment and not in the other versions, these powerful new functions may cause some compatibility issues. In general, the @ character should overcome the compatibility issues with models build in other versions to work in Excel 365, however the dynamic array functions at present will not work in non-subscription versions so should be used with caution if you aren’t sure of your final audience.