Introduction to Financial Modelling

Principles of Excel as a Modelling Tool

Chapter 2: Excel Foundations

8 Topics | 1 Quiz
Chapter 3: Using Sensitivity Analysis

10 Topics | 1 Quiz
Common Modeling Structures (I)

Chapter 4: Core Operations and Shortcuts

7 Topics | 1 Quiz
Further Operations and Shortcuts

Chapter 5: Common Structures in Financial Models (I)

7 Topics | 2 Quizzes
Excel Functions (I): Information and Numerical Aggregation

Chapter 7: Formatting, Presentation and Graphs

7 Topics | 1 Quiz
Excel Functions (II): Conditionality, Advanced Aggregations and Arrays

Chapter 11: Logic and Conditionality

7 Topics
Chapter 12: Conditional Aggregation

4 Topics | 1 Quiz
Chapter 13: Array Functions and Dynamic Arrays

5 Topics | 1 Quiz
Common Modelling Structures (II)

Chapter 14 Measuring and Calculating Growth

6 Topics | 1 Quiz
Excel Functions (II): Lookups and Referencing

Chapter 16: MATCH and XMATCH

5 Topics
Chapter 17: CHOOSE and SWITCH

6 Topics
Chapter 18: INDEX and XLOOKUPs

6 Topics | 1 Quiz
Model Planning and Best Practices

Chapter 19: Model Planning

11 Topics
This could be represented in Excel as:

Note that – for clarity – we have used color-coding, both in the influence diagram(s) and the model: The blue-shaded cells are “inputs”, which are used in calculations, to determine the value of the green-shaded “outputs”. Of course, the Excel worksheet captures the calculation thatÂ (new) Savings result by starting with Salary and subtracting Living Expenses. (This requirement is not explicitly stated on the influence diagram, but is captured using the Excel formula in cell C8 in the image).

(Note: The basic structure of Excel and the creation of simple formulas is no doubt familiar to many readers. However, it is covered in later materials for those not sufficiently familiar with it.)

- One can influence.
- Where precise data is available to be used as model input values.
- Reflect a change in how the overall situation behaves.
- … and so on.

As an example. let us suppose that the Living Expenses are able to be split as:

- Rental costs. These could be a large component of living costs and also are likely to be known explicitly and quite precisely. By breaking these out from the aggregated figure, more focus can be placed on the other items. It may also create more focus on other potentially-important decisions, such as whether the costs could be reduced or renegotiated, and so on.
- Food, entertainment, clothing. These may contain some discretionary components, so that breaking them out may also provide insight into additional actions that could be taken to increase Savings if needed. Further, there may be actual data available (e.g. from recent bank statements) to populate these items, which is therefore possibly more accurate than working only with an aggregate Living Expenses figure (for which no data is directly available, unless it is calculated from the sub-components in any case).
- Future values of some items could be captured through assumptions about inflation (price growth) of each cost item, and of likely salary changes. The values of these assumptions could be supported by actual data where available, such as government data on price inflation and industry data on average salary growth. Also, but not done here, a more detailed time axis (e.g. monthly or quarterly) could be created, so that the time-profile of changes in future values could be captured.
- There may be structural changes in some components that require a different type of calculation. For example, you may currently travel to work on public transport , but next year you plan to go by bicycle.

The revised version of the Savings model could be as follows:

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!