Do you know which calculation method your models are running on?
It is always the first aspect of a model which I check and it is essential that modellers are aware of the settings for any model they might be using. Don’t forget too that Excel will use the calculation method of the first model opened (this has caused many problems to unsuspecting modellers).
Excel has made some recent changes to these methods which modellers should be aware of. These are being rolled out on a gradual basis, so don’t be surprised if your calculation options suddenly look different.
There are two different places to check the calculation methods:
- File -> Options -> Formulas -> Calculation Options
Here you are able to select the following options:
Automatic (always use this unless your model is large and slow).
Partial (this is the one to use if data tables are slowing up your model. It used to be called “Automatic except for data tables” but has now been expanded to include some coding calculations).
Manual (use this if your model is large and slow and takes a long time to calculate).
In addition, it is here that some modellers enable iterative calculation. But please DO NOT use this option for any financial models. This is bad modelling practice, see my previous blogs on circularity. For example, https://camillaculley.com/2021/09/why-i-dont-approve-of-iteration-and-why-it-matters/
- Formulas -> Calculation Options
This area contains the three standard calculation methods described above, but (thankfully) makes no mention of iterative calculations here.
Automatic
Partial
Manual
However, recently an additional option Update Stale Values has been included if Partial or Manual calculation options are used. This, through using a strike-through, shows which cells still need calculating. It is probably a good idea to leave this option on. Note that the “Update Stale Values” Option can only be changed here (not in File -> Options -> Formulas -> Calculation Options). In addition, once your Excel has been updated to include this “Update Stale Values” option, Excel also now makes clear through the use of the “Calculate” message at the left of the status bar when models which contain data tables need to be updated. In the past this could cause problems if modellers were unaware that the data tables had not been updated.
And remember, to calculate a model, press F9.