Introduction to Financial Modelling

Principles of Excel as a Modelling Tool

Chapter 2: Excel Foundations

8 Topics | 1 Quiz
Chapter 3: Sensitivity Analysis

10 Topics | 1 Quiz
Excel Operations, Structures and Short-Cuts

Chapter 4: Operations and Short-cuts (I)

6 Topics | 1 Quiz
Chapter 5: Common Structures within Financial Models (I) – Arithmetic Based

7 Topics | 2 Quizzes
Chapter 6: Operations and Short-cuts (II)

15 Topics | 1 Quiz
Introduction to Excel Functions

Chapter 10: Dynamic Arrays and Array Functions

4 Topics | 1 Quiz
Applications of Lookup and Reference Functions

Chapter 14: INDEX and XLOOKUPs

5 Topics | 1 Quiz
Planning and Building Models for Optimal Decision Making

Chapter 17: From Planning to Practice

16 Topics
Excel has three “Calculation Options”, which are shown in the image below. These options may be changed under the File>Options>Formulas menu. They will be retained when the workbook is saved.

Automatic is the default (when Excel is first installed). This means that – whenever an input value is changed or formula entered (and when some other operations are performed) – the Excel workbook will recalculate to reflect the effect of these changes i.e. all cells whose values depend on the changed input cells will be updated. (This applies to all dependent cells, whether directly dependent or dependent only via another intermediate variable).

If the Manual option is chosen, then changes to items (such as an input value) will have no effect until the F9 key is pressed (or the equivalent command made e.g. using a macro). The Manual option is useful if one is working with large workbooks whose recalculation is slow, so that one may wish to recalculate it only after several changes or manipulations have been made, and not for each change.

As an example, with the Sales Revenue model used earlier, if one first sets the workbook to Manual calculation, and then change its inputs to zero, then the figures shown for the calculations are incorrect, because they have not been updated to reflect the changed input values (the values of zero are used only to make this clear, the principle would apply generally).

To update the calculations when in the Manual workbook calculation mode, one can press the F9 key. Doing this will result in the calculations being updated as expected:

The option “Automatic except data tables” is useful in large models which may update slowly and for which there are Data Tables (see Chapter on Sensitivity Analysis), in order to allow more efficient working by not waiting for the full set of sensitivities to update each time an input item is changed, whilst nevertheless having the correct figures shown in the main model area.

[Note that the calculations settings relate to the whole workbook i.e. all worksheets. It is possible to invoke settings for individual worksheets by using VBA macros, but this is beyond the scope here, and generally not recommended except in very specialised applications].

This website uses cookies to improve your experience. We'll assume you accept this policy as long as you are using this websiteAcceptView Policy

Scroll to Top
error: **Alert:** Content is protected !!

Login

Accessing this course requires a login. Please enter your credentials below!