Introduction to Financial Modelling
Principles of Excel as a Modelling Tool
Common Modeling Structures (I)
Further Operations and Shortcuts
Excel Functions (I): Information and Numerical Aggregation
Excel Functions (II): Conditionality, Advanced Aggregations and Arrays
Common Modelling Structures (II)
Excel Functions (II): Lookups and Referencing
Model Planning and Best Practices
1 of 2

1.6 Simple Example II: Personal Savings

It is simple to create an Excel model of the savings-driven vacation decision discussed earlier. For example, if we assume (for even more simplicity) that we have no current savings, then the influence diagram for the model would be simply:

This could be represented in Excel as:

The estimate is that the (new) Savings would be $2000; you can then consider whether this is sufficient and then decide whether to go on vacation or not.

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.)

Adding Detail and Accuracy

The model shown above is so simple that it seems intuitive that one may be able to create more accuracy by adding more features or enhancements. In addition to adding the current level of Savings (if there were any), another simple enhancement could be to break down the structure of the Living Expense items into more detail. For example, one could split out explicitly those line items that:
  • 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:

Therefore, one may be able to conclude that the total Savings are likely to be higher than estimated in the simpler model (i.e. $2840 instead of $2000): Even though Salary is slightly lower than the initial estimate that was made as a direct assumption, the detailed breakdown of costs has highlighted the amount saved by taking the bicycle, even as costs items increase with inflation.

Further enhancements could include adding a granular time axis (e.g. monthly or quarterly). This could help to capture that some items may change in value over the course of the year (such as being fixed for the earlier months before taking a different value in later months. Whether this is worth doings is not a simple question to answer; it needs to be considered on a case-by-case basis, since the extra complexity (and investment) of doing so may not always be worth the additional accuracy achievable (the principle of “diminishing returns” generally applies from some point onwards).

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