Chapter Overview

In this chapter we cover golden rules for model design and layout, dissection of workbook anatomy, project planning, flow-charting and steps to building a model which will add to the quality and success of the modeling project.

Model design can sometimes be the most difficult part of building a financial model and it is, according to many modellers, one of the most difficult aspects to teach and learn. When building your model, pay close attention to the design layout and ensure that it is clear, coherent, and logically structured.

These guidelines, such as assumptions documentation, linking, only entering data once, ensuring formulas are consistent, and avoiding bad modeling habits, financial modellers can be sure that their models are more robust, have fewer errors, and are easier to audit.

One of the most important principles of best practice is to document assumptions, and this chapter lists different technical methods of assumptions documentation, such as comments, footnoting, hyperlinks, and linked text. We also explored the features and structural attributes of a well-built financial model.

Anyone who has been using Excel for any length of time will know how easy it is to make a mistake, and what a huge effect this can have on their business models. Therefore, knowing and using strategies for reducing errors is an important skill for the Excel modeller to have.

We also look at how to avoid overly long and complex formulas, and how to dissect them if we are unfortunate enough to have inherited a model that includes overly complicated formulas. Linking to external files, building error checks, and dealing with circular references are also issues that are commonly encountered in financial models.

Because there are currently several different versions of Excel available that modellers are likely to be using, we explore the major changes and capabilities between versions.

We also cover functions, such as IF, SUM, AVERAGE, and nesting these, as well as useful shortcuts that will make your model building faster and more accurate. The fundamental techniques of using cell referencing for best practice as well as the use of named ranges are the critical basic knowledge required for financial modellers before moving on to more advanced tools.

The Windows and Mac shortcuts named here will also prove invaluable to increasing your speed, productivity, and accuracy as a financial modeller.

We cover the important aggregation, lookup, referencing, date, and finance functions that, at minimum, are important for a financial modeller to know. Of course, there is a lot more to being a good financial modeller than knowing hundreds of different Excel functions!The most challenging part is to know when to use these functions in your modelling.

When faced with a problem, creating a modelling solution that contains the most appropriate tools and functions is the task faced by the financial modeller. Usually, there are several different possible tools that can be used, and the one that is the most concise, yet easiest to follow, will be the best solution.

When the model builder has obviously taken care to use clear colour coding, borders, and correct formatting will be trusted more, and given more credibility by the user.

There are many Excel tools that can be used to make the model more attractive and friendly to the user.

In this chapter we cover the basic formatting, as well as more complex custom formatting, conditional formatting, and then form controls, which can be built to help the user interact with the model more easily.

Good modellers should set aside a little time during the model build to format colours, borders, and styles to make it more accessible.

As always, we try to make our models as simple as possible, and as complex as necessary. Therefore, if you can create a model using just basic functions and tools, by all means, do so.

If, however, you find that your simple solution does not provide your model with the desired functionality, then you may consider including a more complex nested formula, or even possibly a macro. The best sort of financial models are those that are clearly laid out and use very simple and clearly defined tools—not because we don’t know how to create complex models, but because they are easier to follow.

Some of the practical applications include methods of calculating escalating growth, comparing normal and effective rates, cumulative totals, payback periods, weighted average cost of capital, tiered pricing, depreciation and break-even analysis.

If you are pursuing a career as a financial modeller, you will almost certainly have to calculate escalation at some point, and it is quite likely that you will find yourself needing to use tiering tables, calculate straight-line depreciation, or perform a break-even analysis as part of your financial model.

Although it may initially seem like a simple enough task where the hard work is already done, trying to use, understand, audit, or validate an existing model is one of the toughest modelling tasks.

Because Excel is such a flexible tool, and there are so many different ways to achieve the same end, each modeller will invariably approach a model design in a different way and almost certainly using different formulas.  Trying to understand the way a model has been built can be like trying to get inside another modeller’s mind! This chapter covers many tips and tricks for auditing and rebuilding an inherited model, checking and correcting errors.  It also contains some useful guidelines and templates for the model QA and audit procedure.

This chapter covers the various tools that are available in Excel for scenarios and sensitivity analysis. The drop-down method is the most popular, as that allows for many different scenario inputs as well as outputs. Scenario Manager is surprisingly not a particularly useful tool, and data tables are most useful for sensitivity analysis where there are a maximum of two input variables, and only one output that needs to be shown.

Whilst there are several different scenario tools available, the methodology and logic behind creating the scenarios, which is explored in this chapter, remains the same.

At the end of the model-building process we should spend some time thinking about the results that the viewer of the model needs to see, whether it is a report, chart, or table.

This chapter covers ways to summarise and present this output, including many useful chart layouts, and some tips for communicating both verbally and visually the methodology, assumptions, and results of the model in the clearest and most informative way.