Introduction to Financial Modelling
Principles of Excel as a Modelling Tool
Excel Operations, Structures and Short-Cuts
Introduction to Excel Functions
Applications of Lookup and Reference Functions
Planning and Building Models for Optimal Decision Making

1.7 The Skills Sets Used in Financial Modelling

The examples shown in the previous sections were very simple. Nevertheless, they illustrate some of the underlying skills that a well-rounded financial modeller needs. These include:

  • The ability to analyse the business context.
  • Some knowledge of core aspects of decision sciences.
  • A solid understanding of general economics, and of financial measures and criteria.
  • A good knowledge of the specific area of application, or of the behaviour and relationships in the situation.
  • Sufficient knowledge of Excel functionality and functions.
  • The ability to use Excel best practices.
  • The skills to design models around sensitivity analysis principles.
  • The ability to manipulate data, conduct statistical analysis and integrate datasets.
  • A knowledge of automation and the use of algorithms, VBA and macros.

These are briefly described in more detail below, whilst the materials of the overall CertFM Program is structured to develop these skills and knowledge in an effective and focussed way.

Contextual Analysis

For a model to be planned, built and used in the most valuable way, a modeller will need to be able to analyse and interpret the context of the decision. This may require for example the ability to take into account core aspects of the strategy, competitive position, macro-economic or political context of a business: These factors would likely impact the possible decision options and their variations, and therefore have implications for the model design and use.

Decision Sciences

Decision science is the study of the ways that decisions are made. It is a very large topic, and in a sense modelling is simply a sub-set of this (as the aim of models is to support the development and selection of appropriate courses of action).

From a modelling perspective, key aspects of decision sciences that one needs knowledge of include the processes by which decisions can be made, the challenges to making good decisions, and same ways to deal with such challenges. An understanding of these can help to ensure that the right models are built and which provide output that is as valuable as possible to the final decision process.

Decision sciences also includes the criteria by which decisions can be made. In general, this requires a knowledge of economic criteria and the concepts behind these.

There is also an important role for sensitivity and scenario analysis, since decision-makers will typically want to consider these within the process. More generally, the presence of risks/uncertainties may need to be taken into account. Further, there may be optimisation aspects (in terms of making the best decision from several or many possible ones): At least in theory, the presence of risk/uncertainty and optimisation possibilities are inherent in almost all situations, so in a sense their inclusion in the analysis should be a fundamental part of “trying to model reality”. In practice, such factors may be complex to treat in full detail, and decision-makers may wish for simpler forms of analysis (such as sensitivity analysis). However, when de-emphasising risks/uncertainties or optimisation aspects of the analysis, one needs to take care to not oversimplify the analysis either (“as simple as possible, but no simpler).

General Economics and Financial Concepts

A good knowledge of economics and finance is required in order to understand, implement and interpret the economic analysis required to support the decision, to ensure the robustness and validity of such analysis. Some concepts are more or less ubiquitous or very frequently needed (such a calculating and forecasting growth, measuring returns, discounting and present values), whereas others are specific to an area of application (see below).

Specifics of the Application: Behaviours and Relationships

Whilst some models may require only a knowledge of general or common topics in economics and finance (e.g. such as growth, discounting, etc.), other models have a specific component that may require additional knowledge. For example, topics such as cash flow valuation, financial statement modelling, project finance, optimisation, options and derivates or statistical analysis each have specific features or behaviours that a modeller will need to be aware of. In some cases – notably those related to corporate finance – the Excel aspects of the models is in principle very straightforward, with the application knowledge being the more challenging part of the overall modelling.

Excel Knowledge and Best Practices

(On the assumption that the modelling platform is Excel), the knowledge of Excel needs to be sufficient for the situation being addressed: Although quite limited Excel is needed for simple models, very quickly it often becomes clear that a wide knowledge allows for better models to be built and for more complex situations to be dealt with. In the CertFM Program, the essential aspects of of Excel are covered in the course I.2 Essentials of Excel for Modelling, whilst a huge range of topics are covered at the first relevant points throughout the other courses.

In addition to knowing what is possible with Excel, one also needs to use this knowledge to create models which are “as simple as possible, but no simpler”. This includes the appropriate design and layout of the model and its logic flow, as well as the disciplined and skilled us of formatting, choice of functions, and so on. The use of best practices does not (in principle) change the numerical results of a model (although they reduce the chance of error). However, they enhance transparency and thereby make a model more credible and ultimately more useful in decision support. Throughout the CertFM Program, we aim to generally show models that are in-line with best practice principles, as well as pointing out variations of the interpretation of the principles. The course II.1 Model Planning, Principles and Best Practices has a detailed discussion about many best practice principles.

Data Analysis and Manipulation

Models may need to be driven by (or linked to) large datasets or databases, requiring integration, manipulation, cleaning or transformation of the input data or output calculations. As a minimum (almost always), some form of statistical analysis may be needed to determine important factors, or to calculate input values (e.g. historic growth rates). In more advanced applications, data sets may need to be live-linked into models, or the model is itself essentially a data-driven predictive or probabilistic one.

Automation and Macros

Models often require some degree of automation. In some cases, the use of automation allows one to work more efficiently, whereas in other cases it is a fundamental requirement. In fact, knowledge of VBA macros is often indispensable, and there are even some quite basic applications where basic knowledge of VBA would be ideal. Indeed, one can make a good “theoretical” argument that VBA macros should be considered as so fundamental that they should be covered early in the CertFM Program (Level I or Level II), since there are potential uses in many of the applications that are discussed in these Levels. However, for practical and presentational reasons, we cover these in the first Level III course i.e. in III.1 Automation and Algorithms with and VBA Macros.

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