Optimise workbook (not Modano-specific)

Hi Tarjei,

From my experience, there are 2 common types of performance issues experienced in Excel:

1. Memory issues resulting from the size of the files and the data within them; and
2. Performance issues resulting from things like the use of volatile functions (such as OFFSET) and lookup functions (e.g. SUMIF).

For the first type of issue, if you're building workbooks large enough to hit memory limits, you've got a justifiable case for upgrading to 64-bit hardware and software. You may still hit Modano memory limits in extreme cases even when using the 64-bit version of Windows and Office, but in most cases this is the point at which I've always considered simpifying the model scope, or switching to a database-style program instead of Excel.

For the second type of issue, the main side effect is very slow calculation speed, which can drive me crazy. I've most commonly seen this resulting from the use of a huge number of lookup functions, often converting data from one periodicity to another. This can be tough to avoid, but one approach might be to separate your analysis into separate workbooks if you can't get around using these functions, so that you can open them independently when 'using' the model rather than 'developing' it.

It says something about Moore's Law that, other than the large file allowances of the 64-bit versions of Excel, the calculation speed of Excel hasn't dramatically changed for well over a decade, from my experience. Very frustrating stuff.

Having said that, one thing I regularly tell even large clients is that by the time you're having issues with Excel performance, you've probably got bigger issues with the scope of your model...

Hi Tarjei, 

Having experienced this exact issue last year where I was having memory issues I decided to break the model into multiple workbooks.

This doesn however introduce new challenges regarding linking across workbooks, but if you use the Export module this can actually work quiet well as you can export the entire 3 statement financials. I did this for an entire gym network (12 in total) with a few consolidated group entities ensuring there are error checks along the way of course including # total type error checks for example sum of the total assets in the balance sheet across all periods from the underlying workbook financial statements to the consolidated work book for each gym. Each Export Module was aligned to an underlying Gym model which also had an Export Module which was effectively replicated in the Import Module for the parent.

I believe there are also other ways to do this, which I havent explored to date. 

Mike does have a point re scope of the model and I often find that sometimes with excessive scope creep the model becomes all singing and dancing when really it was only built for a lullaby. A good discipline is to clearly define the scope of the model and push back or create a new one for a different purpose. In the case of a large PPP transaction which I did recently I had little room to maneuver on scope so I was forced down the path of multiple workbooks which many advisors, banks and auditors were able to use and check...albeit they also complained that it was too large but didnt have memory issues at least. 

Good luck !

Cheers