Introduction to Financial Modelling
Principles of Excel as a Modelling Tool
Essential Operations and Short-Cuts
Introduction to Excel Functions
Building Models: Common Structures and Best Practice Principles
Planning Models for Decision Support

12.1 Overview of Issues in Planning Models for Decision Support

When faced with a modelling project, there is often an overwhelming temptation to “dive in” to the model building activities. This temptation can be reinforced by the ease of creating some first results very quickly when using Excel. In addition, the flexibility of Excel may lead one to assume that any adjustments that may later be required could be implemented without significant difficulty.

In fact, although it is of course not necessary to plan every aspect and formula in detail prior to building the model, there are some fundamental issues relating to context and structure that should be considered carefully. If this is not done, then there is a likelihood risk that the resultant model will be far less effective and efficient than it could or should be.

Clearly, some important principles for any model should include that:

  • It must address the decisions or courses of action that are most relevant in the overall situation (especially avoiding the fallacy of choice, where the course of action that would be most appropriate is not even addressed or considered).
  • It must provide the information that a decision-maker will need. This includes the identification and calculation of the key criteria that will be used to evaluate the decision. It also typically requires one to be able to provide sensitivity or scenario analysis for these criteria and for other key outputs.
  • It must be sufficiently flexible to be used and to deal with likely future changes. Some models will need to be updated regularly as new information comes in, or to be able to be reused for similar types of decision situations.
  • It must be sufficiently accurate, and any key simplifying assumptions that are required should be made as explicit as possible.
  • It should be able to be populated with (or at least use in a clear way) any important data or estimates that are available.
  • It should be sufficiently transparent for it to be easily understood, worked with, and updated by anyone who would likely need to do so in the future (not only to the original model builder).

The key contextual issues to consider relate to:

  • Identifying the right decision or action to address. This requires a consideration of the business situation, context for the decision, the alternatives that are available, and the scenarios within each.
  • Establishing the objectives and decision-criteria, as well as the decision process, stakeholders and their likely information requirements.
  • Using the data sets that may be available in the most appropriate way, and in particular considering the extent to which the existence of large data sets should favour a “database” – rather than “traditional” – approach to the modelling (as discussed later). In addition, the nature of any future updates or changes to the data should also be considered.

These issues are discussed in the following sections of this chapter.

In the following chapter, we discuss further issues that more specifically to the design of the model in detail. These include:

  • Model variables and logic. The variables to be used, their relationships and dependencies and the appropriate flow of logic (e.g. which are inputs, which are calculated?)
  • The level of granularity (or detail) required for the variables in the model and for the time axis. (for example, whether the time axis is to be annual or quarterly, or monthly).
  • The nature of the sensitivity analysis that will need to be conducted on the completed model.
  • Layout and structure. This includes issues such as whether multiple worksheets are to be used, and how to link between them. It also covers the implications of the data sets (for example, if one or several databases are to be used as inputs to the model, this will have consequences for the appropriate structure to use). The issue of whether the time axis in a model is along the vertical or horizontal dimension is also to be considered in this category.
  • The Excel functionality that is likely to be required. This includes clarifying whether there are any unusual or challenging aspects that are required of the modelling, and what techniques, tools, skills or additional software may be needed.
  • Achieving the right balance between simplicity and complexity. Simplifications or approximations that may be required to create a model that is “as simple as possible but no simpler”. The model should be appropriate to inform the decision at hand, whilst not containing unnecessary elements nor functionality. Typically, and model is a simplification which makes implicit assumptions or to which restrictions on its validity apply. These should be understood and documented.
  • (Flexibility versus complexity in best practices)
Scroll to Top
error: Alert: Content is protected !!