Principles of Financial Modelling

CHAPTER 8Building Robust and Transparent Formulae

INTRODUCTION

This chapter discusses approaches and techniques to build robust and transparent formulae. Whilst mistakes may sometimes be evident by the display of error messages, or by calculated values that are orders of magnitude different to the correct figures (or are clearly incorrect for some other reason), typically they are subtler and may therefore be overlooked.

The first section of this chapter discusses the general underlying factors which often lead to mistakes being made. The second section presents some examples of common mistakes that one often observes in models and their formulae. The third section discusses named ranges, including their advantages and disadvantages when aiming to build transparent and flexible models. The fourth section presents some key approaches that can be used to build and test formulae, and to detect, correct or manage errors.

GENERAL CAUSES OF MISTAKES

Insufficient Use of General Best Practices Relating to Flow, Formatting, Audit Paths

A key general cause of mistakes is simply the insufficient use of best practices. There is a higher risk of introducing errors into models that are excessively complex, contain unnecessary flexibility, are poorly laid out (poor flow, diagonal audit paths, too many worksheets, inconsistent structures across worksheets), have poor formatting, and so on.

Insufficient Consideration Given to Auditability and Other Potential Users

As discussed in Chapter ...