2
Principles of Model Design

2.1 INTRODUCTION

Modeling activity takes place within an overall context and a wider set of business processes. At a high level, the main steps to consider when planning and building a financial model for decision support are:

• Identifying the decision and its structure, options, and criteria.
• Mapping the elements of real-life that should be captured, including the variables and logic flow.
• Building and testing the model.
• Using relevant external data.
• Using the results, including presentation, graphics, sensitivity analysis, reports, and documentation.

This chapter explores these topics, discussing the core principles of each point and the main practical issues. Note that in this chapter, the discussion is still quite generic; in fact, most of the principles apply whether a model is to be built in Excel or in some other platform. However, the rest of the book (from Chapter 3 onwards) is devoted to implementing these within the Excel environment.

2.2 DECISION IDENTIFICATION, FRAMING, AND STRUCTURE

A model is generally used to support a decision process in some way. Therefore, it is important to establish what decision is being addressed, what are the objectives, and what are the constraints or limitations that must be respected.

A common failing of decision processes is known as the "fallacy of choice": This is where what would have been the best decision option is not considered at all. Clearly, for a model to be most useful, it must also reflect the relevant decision and the most appropriate or best option(s).

Generically, one may think of a decision as having a binary structure ("go or no go?"). Most commonly, Excel models reflect this: The model represents the "go" option, whereas the "no go" option is not modeled explicitly (i.e. it is implicitly considered as being neutral or evaluating to zero).

It is also frequently the case that (within the "go" option) there are set of sub-options which each have the same structure. That is, there is only one model, and the sub-options are captured as scenarios (each simply using different input values). If there were major structural differences between the sub-options then a different model would be required for each (and, in that case, they are strictly speaking not sub-options at all). Figure 2.1 illustrates this for the situation discussed in Chapter 1 (see Figure 1.5 and the associated discussion).

Other types of decision structures include allocations or optimizations (e.g. how much capital shall we allocate to project A, and how much to project B?), multiple structurally different options (such as whether to renovate one's house, buy a new car, or go on vacation), and decision sequences (e.g. using a phased approach rather than making a single up-front decision). These may require more advanced models and tools to properly address them. However, the core points are that the appropriate decision needs to be identified and that the model should reflect the structure of the decision situation.

Figure 2.1 Basic "Go/No Go" Decision with Sub-Options

2.3 DECISION CRITERIA AND INFORMATION NEEDS

There are many ways that a decision could be made, or a decision option selected. The least structured is using "gut feel," which is essentially a subjective method. A more robust process is to make the criteria explicit and to evaluate these as objectively as possible (often quantitatively).

In principle it should be self-evident that a model should be designed so that it calculates (or contains) the values of the decision criteria (or metrics) that are to be used by the decision-maker. Figure 2.2 depicts the idealized modeling process. It starts with identifying the decision, with the nature of the decision then determining the decision criteria (metrics). These are used to determine the design requirements, allowing the model to be built so that it evaluates the criteria, with the results used to support the decision.

It is also worth noting that a "gut feel" decision process is often one where the process of decision identification is incomplete and potentially subject to the fallacy of choice. In addition, it may be considered as one in which there is a direct route from decision identification to decision-making (i.e. a route directly downwards from the top-left box to the bottom-left one in Figure 2.2).

Common decision criteria used in economic analysis include measures relating to:

• Breakeven analysis (such as time-to-breakeven and payback periods).

  Figure 2.2 Using the Decision to Design the Model That Supports the Decision

• Returns (such as the internal rate-of-return, the return-on-capital) and net present values).
• Ratios (such as profit/sales, or sales/assets, and so on).

In some cases, one may wish to focus on a specific item only and maximize or minimize this. For example, one may wish to choose the option which has the maximum revenues, that which has the minimum cost, or that with the minimum risk, and so on. Clearly, these criteria could lead to different decision choices. For example, in day-to-day life, the choice to go on the cheapest vacation possible would likely lead to a different selected vacation than if one sought to choose the vacation option by considering both the costs and benefits (such as the quality of the hotel one is staying in). Similarly, in a business context, the option that maximizes revenues may require making significant up-front investments that would not be acceptable if criteria such as profitability or financing constraints were considered.

Note that while one may initially interpret "decision criteria" in a pure economic sense, the term should be thought of in a wider context (i.e. the full information needs of decision-makers). These would typically also include that a sensitivity or scenario analysis (or a full risk assessment) be conducted. That is, one would aim to establish the likely ranges for the decision criteria (such as the range of value for the time-to-breakeven, or for the return-on-capital, and so on). This is discussed further in the next section.

Similarly, in practice, some decision criteria may initially be overlooked when a model is first built: It is possible that the criteria are not understood initially, or that the information needs of decision-makers change over time after some initial results have been reviewed, or that further information about the market or competition has become available, and so on.

Finally, some decision elements (e.g. relating to ethical or moral issues) may not be able to be evaluated by quantitative analysis (i.e. cannot be included in a model). In these cases, some judgment by the decision-maker is likely to be required. However, the core point is that when planning a model, one should take some time to reflect on a wide set of likely decision criteria that may ultimately be needed, and to build the model so that these are evaluated, at least as far as possible.

2.4 SENSITIVITY-BASED DESIGN

Sensitivity analysis is the exploration of the changes that occur to the value of a calculated item when one or more of the input value(s) is changed. It is a key part of decision support, as it can:

• Help to understand the conditions under which a decision makes sense (or not). For example, while a base case may indicate that a "go" decision is preferable (to "no go"), a sensitivity analysis could identify that this is true only if costs do not rise by more than 10%.
• Establish the range of likely outcomes and generally to assess the potential upsides and downsides.
• Identify the relative importance of the key input variables, and hence the effectiveness of potential management actions that could be used to maximize (or optimize) the overall result while mitigating or reducing risk.

A seemingly obvious - but often overlooked - point is that sensitivity analysis should be considered before the model is built (i.e. as a planning and design tool): If it is considered only afterwards, the model may have been built in a way which does not allow the necessary sensitivities to be run! The approach to implementing sensitivity techniques varies according to the stage within the modeling process:

• At the design and planning stage, it revolves around identifying as precisely as possible the sensitivities that will need to be run later. This can help to define the variables that should be included in the model, their roles as inputs or outputs (i.e. the logic flow), as well as the level of detail or granularity that is needed.
• When a model is being built, it can be used to verify and test its general behavior, notably by checking that the relationships that are present in the real-life situation are reflected properly. It can also be used to develop and check complex calculations, by testing their results at various values (ideally a combination of simple values, extreme values, and values which are critical in how the formulas would evaluate).

Figure 2.3 Using a Sensitivity-Based Thought...