Principles of Financial Modelling

CHAPTER 9Choosing Excel Functions for Transparency, Flexibility and Efficiency

INTRODUCTION

This chapter highlights some key issues relating to the choice of which Excel function(s) or calculation method to use. Very often, although there are many ways in which the same (and correct) figures would result, model builders tend to employ either the first approach that comes to mind, or the one that is the most familiar. Insufficient attention is typically paid to the consideration of the possible options and the advantages or disadvantages of each, as they relate to issue of flexibility, computational effectiveness or transparency.

KEY CONSIDERATIONS

For the purposes of the issues discussed in this chapter, it is important to note that all methods shown will produce a correct numerical result. In other words, the result of the calculations per se is not a consideration; we will take the accuracy as a given (and necessary) element in all methods, and focus on issues relating to the choice of the most appropriate function or approach.

Direct Arithmetic or Functions, and Individual Cells or Ranges?

When dealing with basic arithmetic operations (i.e. addition, subtraction, multiplication, division), it is often worth considering whether either to use Excel's functions, or to perform the operations explicitly. For example, when adding together a set of items, one could:

• Use the SUM function.
• Use the + operator, referring to individual cells.

Where the items that are to be summed ...