While we cannot go back and re-create the model from scratch, there are several steps we can take to make hybrid Operational-Financial PF models easier and faster to work with.

This issue of speed is not only important because of the adage that time is money, but also because slow Excel files exacerbate the human brain’s already poor error-detection abilities. According to usability-engineering expert, Jakob Nielsen:

• 0.1 second is about the limit for having the user feel that the system is reacting instantaneously

• 1.0 second is about the limit for the user's flow of thought to stay uninterrupted, even though the user will notice the delay.

• 10 seconds is about the limit for keeping the user's attention focused. For longer delays, users will want to perform other tasks while waiting for the computer to finish, i.e., they risk becoming distracted.

Checking the logic and mechanical accuracy of any model requires concentration and attention. This attention evaporates quickly when it takes more than one second to move between worksheets.

As our attention is focused on the “Calculating” message in Excel’s status bar, our cognition is struggling to maintain the last reference we are linking, or the previous two formulas we are stepping through.

This is where we will either invest more energy to check and double check the model, or short of time and budget, we will risk unpleasant surprises down the road.

There are, however, three categories of actions we can take to make life with these messy and slow models easier:

1. Changing how we handle the Excel files we receive.

2. Removing any deadweight from the model.

3. Optimizing how the model calculates and recalculates.

CHANGING HOW WE HANDLE THE EXCEL FILES WE RECEIVE

Very often, when we receive an operational PF model or a hybrid PF model, we double click and grow alarmed as the seconds go by and Excel is still loading the model. We can avoid unnecessary time lost, as well as stress, by developing a few habits when we start working with an unfamiliar PF model.

OPEN THE MODEL IN SAFE MODE

This is done easily enough by holding Ctrl while double clicking the file. Excel starts, but in doing so, it bypasses all the files in the various startup folders (such as XLStart) and skips loading the toolbar file (Excel.xlb).

When you run Excel in Safe Mode, Excel does not open add-ins or other startup files and it does not run any VBA macros that maybe triggered on file open. It is a clean no-frills mode of operation with many options unavailable. This is perfect for the first time you are exploring an unfamiliar PF model.

OPEN THE MODEL IN A NEW INSTANCE OF EXCEL

Very often we will be working with multiple Excel workbooks open. When we open a file that hangs, resorting to killing the culpable Excel process via Task Manager will also close all other workbooks because they occupy the same instance of Excel in the computer’s memory. This is an incredibly frustrating way to lose work done in other workbooks and projects.

Instead, open a new instance of Excel and open the new file from within this new instance. You can open a new instance by holding down Alt as you launch Excel. You can verify that there are two instances running by checking task manager:

The value of this tactic becomes obvious when you have one instance recalculate a huge workbook or calculating sensitivity tables in a complex model: you can observe one instance of Excel consuming CPU cycles at the maximum allowed rate, while you continue working in other workbooks with no interruption or slow down (depending on your PC’s power, of course).

If ever an offending Excel file needs to be shut down because it hangs, only workbooks open in that instance will be closed. Other workbooks will remain unscathed. 

GIVE EXCEL MORE PROCESSING BANDWIDTH

Often, standard issue laptops with corporate IT policies will limit the amount of CPU bandwidth available for a calculation task in Excel. You can observe this as the status bar shows “Calculating” non-stop, but the task manager shows Excel is only using 25% of the CPU. This constraint can be changed through Task Manager by right-clicking on the Excel process and setting the priority to High or Real-time:

This maximizes the amount of processing power available for tasks in Excel such as sheet calculation and the ever-offending sensitivity tables.

REMOVING DEAD WEIGHT FROM THE MODEL

Often, models we receive (or create) are templates or similar models created long ago and recycled over numerous projects. The result is a bloated model with lots of hidden junk (and attendant risks of errors) and potential performance bottlenecks.

EXCESS USED RANGES

Although it may appear that a worksheet’s Used Range is only a few hundred rows and perhaps 100 columns, the last used cell is not just the last cell that contains data or a formula, but also the last cell with any formatting including non-standard column width or row height.

It would suffice to select and change the height of an entire row to suddenly extend the Used Range to over sixteen thousand columns. It is not unusual for a single worksheet whose Used Range includes 1.8 billion empty cells to bloat a .xlsx file with an additional 15 Mb of “data”.

UNUSED NUMBER FORMATS, STYLES, AND NAMES

In some extreme cases, extreme volumes of number formats and styles can not only reduce performance, but impede normal functioning of Excel.

As an example, a .xlsx file that had over fifty-thousand unused number formats and styles held up a project as analysts struggled to understand why worksheets could not be copied to or from the workbook.

Other performance penalties for “hidden” junk are observed in large volumes of Names and Named ranges. Not all names are visible in Name Manager leading to models with thousands of names that contain errors, or links to other workbooks and files, or formulas that are not pertinent. Not only can this junk take up disk space and memory, but Names are calculated every time a formula that refers to them is calculated thus leading to longer calculation times.

Depending on the volume of junk, the clean-up can be done manually. Sometimes, manual clean-up is not possible because user actions in Excel trigger long recalculation cycles. In these cases, the simplest recourse is to do the clean-up via VBA or an Excel Add-in that does housekeeping tasks programmatically.

SAVE AS .XLSB

While not really dead weight, the file structure itself can improve performance by saving it as an Excel binary file, or .xlsb. Data and code stored inside an XLSB file is in binary, while the typical .xlsx format stores data as XML files which take longer to parse on opening. 

Saving a file as .xlsb can lead to load and save times which are twice as fast and the Excel binary file uses noticeably less space. The impact is more dramatic where very large .xlsx files are concerned.

OPTIMIZING HOW THE MODEL CALCULATES AND RECALCULATES

The suggested actions above focus on changes to the typical workflow we use understanding how to improve our workflow through cleaning up messy PF models. But once opened and cleaned of junk, a complex PF model can still be incredibly slow.

This final performance bottleneck is down to calculation and, to the surprise of many, not only is it the formulas that are used, but also the architecture of the model, that can make a dramatic difference in in how response Excel is when working in a PF model. To understand why this is, we must understand how Excel optimizes the calculation tasks it must perform.

EXCEL TRIES TO BE EFFICIENT

Excel is designed to calculate the minimum number of cells possible. It’s smart recalculation engine tracks changes and only recalculates elements (cells, formulae, names, etc.) that have been flagged as needing recalculation because they or one of their precedents have been changed. 

This process relies on Dependency Trees that Excel tracks in order to identify the optimal calculation sequence. The way this works is:

• That each cell/name that is changed is flagged as uncalculated (i.e., to be re-evaluated).

• The worksheet is scanned top-to-bottom and left-to-right looking for references to an uncalculated cell and, in turn, flagging these as uncalculated.

• The scan is repeated until all references have been flagged.

Once the Dependency Trees have been determined, Excel will identify the optimal sequence for calculating the cells that have been flagged. Ideally, all the references in a cell are to cells which have not changes and do not require calculation. A cell that depends on another cell for which calculation has been completed is called a backward reference.

However, a cell that depends on another cell yet to be calculated is call a forward reference. If this reference also depends on a cell which has to be recalculated, the calculation chain grows, as does the calculation time.

Excel uses an iterative process of calculating cells to discover the correct calculation sequence. Because of the complexity of PF models, as well as the very long forecasts (e.g., it is not unusual for hydro-electric project cashflows to be forecast over 100 years), the calculation changes are long and branching.

Surprisingly, there are several user actions beyond the worksheet or workbook recalculation functions that can trigger recalculation including:

• Deleting or inserting a row or column.

• Saving a workbook while the Recalculate before save option is set.

• Double-clicking a row or column divider (in Automatic calculation mode).

• Adding, editing, or deleting a defined name.

• Renaming a worksheet.

• Changing the position of a worksheet in relation to other worksheets.

• Hiding or unhiding rows, but not columns.

These innocent-seeming actions can lead to costly slowdowns as we navigate between worksheets or try to effect some basic changes to the PF model.

In building a PF model, certain choices of functions and how the workbook is structured can have important performance impacts:

VOLATILE FUNCTIONS

The presence of these functions in a cell will always require that cell, and cells referring to it, to be recalculated.

These functions include some of the most popular functions used in PF models: Indirect(), Offset(), Rand(), Now(), and Today(). Using Indirect() or Offset() as part of a lookup will mean every formula that depends on that lookup will be included in the calculation chain, thus causing enormous amounts of unnecessary re-calculation.

CONDITIONAL FORMATS

Any formulae used in a conditional format is effectively volatile because conditional formats need to be evaluated at each calculation. Furthermore, conditional formats seem to be hyper-volatile: they are evaluated each time the cell that contains them is repainted on the screen. If we have received a PF model, it is unthinkable to try to replace the use of volatile functions throughout the model. However, performance can be improved by eliminating conditional formatting which can be done for entire worksheets.

FORWARD WORKSHEET CROSS-REFERENCES

Building on the idea of Forward References (references to cells which have not had their final calculation performed), references between worksheets further penalize performance.

These Forward Worksheet Cross-References can cause uncalculated cells to be evaluated multiple times and thus increase the amount of time spent calculating a workbook. This is exacerbated by the use of circular references which is one of the more frequent strategies used in PF models. 

All is not lost however, as the intersheet dependencies are driven by worksheet name. Much as Excel scans from top-to-bottom and left-to-right searching for references to uncalculated cells, it scans worksheets in alphabetical order. 

This does require knowing the model well enough to judge where most backward references will be found. It will quickly become apparent that the bulk of the operational model will be calculated in final form before finalizing calculations in the financial model. 

Some of the most calculation intensive portions of more mature models (those closer to financial closing) will include sensitivity tables. These can be more easily contained on worksheets which are the most forward cross referenced, meaning they are the absolute last sheets to be scanned and calculated, all of which can be enforced by renaming the worksheets with a “z” prefix.

 HOW IMPORTANT IS THIS SKILL IN THE CONTEXT OF LEARNING FM?

If you want to cut your teeth into the financial modelling sector, then PF modelling (either on the financial close or the operational model) is arguable the best place to learn as the models are so complex.

Until you have built a PF model you really haven’t pushed the boundaries of complex 3-way modelling. The skill of dealing with multiple parties and inputs across a wide range of stakeholders is an equally valuable skill to learn.

HOW DOES ALL THIS DISRUPTION, AI AND AUTOMATION TALK IMPACT THIS TOPIC?

Given the complex nature of Project Financings, PF models were always destined to be large and complex. This complexity is often exacerbated by multiple different people adding to the model in an uncoordinated manner, i.e., ad hoc modelling or modelling without using the same best practice guidance. Because of the high stakes involved, a model which is responsive is critical for eliminating errors and producing the analytic results needed. When confronted with bloated and slow performing PF models, there are a number of straight forward steps (more even than what is offered in this article) we can take every time to reduce lost work, errors, and frustration. 

But for the moment this process cannot be simplified to any AI or disruption. There are ways to build component parts of the PF model quickly using a combination of templates (which are also dangerous) and module components the complexities cannot be removed entirely.

This will mean that for the foreseeable future most of the PF models will continue to be built in Excel perhaps with a few supped-up Add-ins to help us do things faster.

If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App

If you want to find more information on financial modelling and content visit the Model Citizn website.