How to speed up your models: Part 3

How to speed up your models: Part 3

By

Tuesday 6th April 2021

This article is Part 3 in the series “How to speed up your models”; Part 1 and Part 2 are available on the Mazars Financial Modelling website. This final part will focus on some commonly used Excel functionality which can slow down financial models and practical tips to minimise the impact on calculation speed while still including the desired functionality. In terms of calculation speed, sometimes what you don’t include in a model can matter just as much as what you do!

External links

In Microsoft’s own words…

“Avoid inter-workbook links when it is possible; they can be slow, easily broken, and not always easy to find and fix.”

There’s not much more to say about external links other than to avoid using them whenever possible. If there are inputs to the model which should come from another Excel file, then these inputs should be brought in via pasting values into the model and updating when needed. This will also prevent inputs accidentally being updated via the source file and will ease the process of sharing the model between different teams and organisations who may not have access to the source file.

Note there are numerous ways an external link can unintentionally be created within a file, of which we have listed the most common types in the table below. To check if you have any external links in your workbook you can go to Data > Queries & Connections > Edit Links. If this option is greyed out the file does not have any external links.

TypeCauseSolution
Links in formulaePasting links instead of pasting valuesSearch within the entire workbook for “.x”
Links in named rangesCopying in sheet(s) from other workbooksOpen the name manager and sort the “Refers to” column to find names referring to other workbooks
Links in chart dataCopying in chart(s) from other workbooksGo to “Select data” for each chart and check if any data is being sourced from an external file

Because Excel attempts to automatically update external links and notifies the user via a pop-up whenever the Excel file is opened, these unintentional links should be removed to improve both performance and the user experience.

Data tables

Data tables can be a powerful way to compare key outputs between multiple scenarios or quickly analyse sensitivities, however they can become a significant drag on calculation speed. The most common way to use data tables in a modelling context is to compare the outputs for a range of scenarios concurrently, although when used in this way the model effectively needs to recalculate every scenario each time the model is recalculated, which clearly will have an impact on calculation speed, and therefore should be avoided where model performance is an issue.

If a model must include a data table this performance drag can be reduced by changing the calculation options to “Automatic except for data tables”, which will prevent the non-active scenarios from being recalculated unless the mode is manually recalculated. Note however that this approach will not always be accurate, for reasons explained below.

In a model with optimisation macros (e.g. debt sizing), the data table will not be able to run the macro independently for each scenario and will therefore apply the live case results to all scenarios in the model, which may lead to incorrect scenario outputs. For this reason and due to the potential performance impacts a data table is often not the best solution for scenario output tables, at least within project finance models. In these instances, our recommended approach is to use a VBA macro to iterate through each scenario, run the required optimisation macros and paste the key scenario outputs into the outputs table. The end result will look the same but won’t have the calculation impacts or optimisation risks of a data table.

Charts

You can do a lot using Excel charts however this blessing can also sometimes be a curse from a performance perspective. Sometimes modellers go over the top including too much data, excessive formatting or simply including far more charts than necessary. We provide recommendations in each of these areas below, however the key idea is to just keep it simple when it comes to charts.

Including too much data:

If there are five or more different data series in a chart it’s probably too much; consider combining series or excluding irrelevant data. Also consider the periodicity of your data, you can usually achieve the same impact with annual data that you do with monthly or quarterly, with only a fraction of the data points required.

Excessive formatting:

Many of the fancier chart formatting options such as 3D charts and gradients are much more computationally intensive and usually make charts harder to understand, not easier. Chart formatting should be simple and effective; focus on presenting each chart without the distractions of superfluous features, too many colours or distracting fonts and not only will your model run faster but model users will be able to understand it faster.

Including too many charts:

Think carefully about your audience and what information they really need. Do not include extra charts just for the sake of it; the goal should be to make the summary page as short as possible (i.e. usually one to two A4 pages, depending on your audience). You may also want to consider replacing some charts with sparklines, which are much more economical from both a calculation and space perspective, and much faster to create.

Conditional formatting and data validation

Conditional formatting and data validation are very useful features of Excel which we should all be utilising as appropriate, however using them a lot will significantly slow down calculation speed. Note also that conditional formatting rules which use formulae are always volatile and therefore are particularly slow. For this reason, the use of these features should be limited, especially in large models. In slow models conditional formatting should be one of the first areas to look to cull functionality, given that it is primarily used for presentation purposes and shouldn’t impact the model calculations.

Note some applications of conditional formatting can be achieved through the non-volatile number formatting functionality instead, if you understand the custom formatting notation Excel uses.

As a simple example, if you want to display positive numbers in green, negatives in red and zero in black you can use the following code:

[Green]0; [Red]0; [Black]0

By default, Excel defines four different types of data for formatting purposes, separated by semicolons in custom number formats. These are:

positive values; negative vales; zero; text

As a simple example, if you want to display positive numbers in green, negatives in red and zero in black you can use the following code:

[Green]0; [Red]0; [Black]0

By default, Excel defines four different types of data for formatting purposes, separated by semicolons in custom number formats. These are:

positive values; negative vales; zero; text

You can define different groupings using the custom number formats. A simple example with groupings of <100 and >=100 is shown below. Note this flexibility will allow custom number formats to replace conditional formatting in some instances.

[Red][<100]0; [Blue][>=100]0

If you want to find out more about how conditional formatting works and what can be done using custom number formats, see the further reading section below which includes some good resources on this topic.

Other potential issues

There are a range of other issues which often occur accidentally in the normal course of modelling which can impact model performance. A number of these are explained below including recommendations on how to avoid or fix them.

End cells:

It is very common for modellers to accidentally extend the used range (the part of the sheet Excel will save) way beyond what is required. A common example is accidentally formatting the far-right column or bottom row on a worksheet, which will cause Excel to save all columns and/or all rows for that sheet, massively increasing the file size. While this may not have a significant impact on calculation speed while the file is open, it will significantly slow down the time it takes to open and save the file and may lead to crashing.

You can check the used range on each sheet by going to the end cell (CTRL + END). It is also advisable to check the file size periodically to see if there is an unexplained jump which might mean the used range has been expanded.

Excessive range names:

Having excessive amounts of named ranges will slow down calculation speed because names are recalculated each time they are referenced by a formula that is recalculated. Note also that although dynamic named ranges can be very useful (created using OFFSET and COUNTA), they are volatile and therefore will impact calculation speed as well.

Periodically check named ranges look ok and there aren’t too many of them in the name manager, (CTRL + F3).

Excessive cell styles:

We are big advocates of using cell styles as a fast and effective way of maintaining formatting consistency and flexibility, however saving too many cell styles in your workbook will impact your model performance. This can easily occur when copying in data or sheets from different workbooks, so be mindful in how you do this.

We recommend copying only formulae or only values for the ranges of cells needed as this won’t bring in the cell styles or formatting associated with those cells. You should also periodically cull excess cell styles from your workbook.

Event-based VBA code and add-ins:

Excessive use of event-based VBA code either in the model itself or within your personal Excel macro’s or addins, or simply using too many addins concurrently can all create performance impacts that can sometimes become significant.

You can check whether VBA is causing calculation impacts by re-opening the file and holding down SHIFT. This disables any macros in the workbook. If you notice a performance improvement you can test which macro or add-in is causing issues through a process of elimination by individually enabling/disabling each until you find the culprit(s). It is also good practice to periodically assess whether you have add-ins which you no longer use and therefore should be removed.

Excessive grouping:

Again, we are big advocates of using grouping to present different levels of information and avoid hiding cells, however using grouping excessively may impact model performance.

Consider how many levels of grouping are really required for your model. Typically, one to two levels will be sufficient for most cases.

Wrapping up

This concludes our series on How to speed up your models. If you have any feedback on this series, or if you think there is anything you think we missed, feel free to contact the author via email at matthias.prosser@gif.mazars.com. If you are interested in learning more about financial modelling we have a wide range of resources on our website including tutorials, webinars and blogs. We have also included a selection of further reading on the topics covered in this post below.

Further reading:

External links:

Conditional formatting:

Charts:

Other:

Request Prices and Discounts