Chapter Overview

Chapter 1 1.    Financial Modelling definition
2.    Data analysis
3.    Excel training
4.    Excel alternatives
5.    Excel skills
6.    32-bit
7.    64-bit
At a basic level, a financial model is really just a complex spreadsheet that contains inputs and outputs in a dynamic way. However, not every spreadsheet could be called a financial model.

Although this book is about how to use Excel in the context of business analysis, consider that there are many other available modelling tools besides Excel. Excel is the most commonly used software for this type of analysis, and in terms of building your skill set, improving your Excel skills will always stand you in good stead for a career in finance.

Having good technical Excel skills is not the only attribute of a good financial modeller. Modellers will have different skills in varying degrees. In this chapter we also discuss convenient ways to improve your financial modeling skills.


Chapter 2 1.    Model design
2.    Version control
3.    Project planning
4.    Design layout
5.    Model structure
6.    File structure
7.    Model build
8.    Workbook anatomy
9.    Golden rules
10.   Worksheets
Considering the layout and design of a financial model is an important part of the model build. It takes a unique mixture of logic, clarity of thought, and graphic layout skills by the financial modeller to build a well-designed model, and this often proves to be difficult to implement in practice.

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.


Chapter 3 1.    Best practice
2.    Assumptions documentation
3.    Hyperlinks
4.    Linking
5.    Bad habits
6.    Consistent formulas
8.    Formatting
9.    Labelling
10.  Financial modelling standards
By following the key principles of financial modelling best practice outlined in this chapter, your model is easier to navigate and check, and much more likely to be robust, accurate, reliable, and error-free.

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.


Chapter 4 1.    Circular reference
2.    Iterative calculation
3.    Error check
4.    Stress-test
5.    Methodology documentation
6.    Logic
7.    Sense-checking
8.    Links
9.    Named Ranges
10.  Formula error
This chapter contains a collection of techniques and strategies commonly used in financial modelling.

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.


Chapter 5 1.    Excel version
2.    Excel 2013
3.    Excel for Mac 2011
4.    Functions
5.    Nested Formulas
6.    Cell referencing
7.    Mixed referencing
8.    Absolute and relative referencing
9.    Named ranges
10.  Excel Shortcuts
This chapter introduces the more basic tools and functions that are commonly used in financial modelling, such as basic functions, cell referencing, and named ranges.

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.


Chapter 6 1.    SUMIF, COUNTIF
3.    Nested functions
4.    Excel dates
5.    Net Present Value (NPV)
6.    Internal Rate of Return (IRR)
7.    Regression analysis
8.    Loan calculations
9.    Interest calculations
Whilst there are hundreds of functions that can be used in a model, this book lists only the ones you are most likely to come across, and that you will find most useful when building models.

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.


Chapter 7 1.    Conditional formatting
2.    Custom formatting
3.    Currency symbols
4.    Sparklines
5.    Icon sets
6.    Colour scales
7.    Password protection
8.    Bulletproofing
9.    Data validation
10.  Form controls
As simple as it may sound, good formatting is an important part of building a financial model. Of course, the accuracy of the model and its calculation are most important, but having a model that looks good and is easy to use certainly adds to the integrity and usability of the model.

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.


Chapter 8 1.    Hiding and grouping
2.    Hiding worksheets
3.    Array formulas
4.    Goal seek
5.    PivotTables
6.    Slicer
7.    Structured Reference Tables
8.    Macros
9.    Visual Basic for Applications (VBA)
10.  Model complexity
In this chapter, we look at the basic, commonly used tools of hiding and grouping. We then cover some more advanced tools such as array formulas, goal seeking, PivotTables, and macros.

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.


Chapter 9 1.    Escalation
2.    Growth rate
3.    Nominal and effective rate
4.    Cumulative calculations
5.    Payback period
6.    Weighted average cost of capital (WACC)
7.    Tiering table
8.    Modelling depreciation
9.    Straight-line depreciation
10.  Break-even analysis
The first part of this book covers the tools, functions, and techniques that financial modellers need to know. In this chapter, we focus on the use of these tools in a financial modelling context.  Once you know how to use many of the useful tools, functions, and features of Excel, we take a look at actually using these tools to solve common problems, calculations, and situations encountered in financial models.

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.


Chapter 10 1.    Inherited model
2.    Model audit
3.    Formula auditing
4.    Quality assurance (QA)
5.    Excel file size
6.    Model performance
7.    Excel memory
8.    QA procedure
9.    Error-checking tools
10.  Evaluate formula
Most modellers prefer to build their own models from scratch, but in practice this rarely happens.  A modeller is more likely to be handed someone else’s model to validate, modify, and make their own.

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.


Chapter 11 1.    Stress-testing
2.    Scenario analysis
3.    Sensitivity analysis
4.    What-if analysis
5.    Data validation drop-down box
6.    Combo drop-down box
7.    Data table
8.    Scenario manager
9.    Advanced conditional formatting
Any good financial model will usually contain scenario and sensitivity analysis functionality, at least to some degree. Scenarios are an important part of financial modelling, and in fact are often the whole point of creating the financial model in the first place.

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.


Chapter 12 1.    Model presentation
2.    Charting tips
3.    Which chart to choose
4.    Line chart
5.    Bar chart
6.    Bubble chart
7.    Pie chart
8.    Combo chart
9.    Waterfall chart
10.  Dynamic range names
Once a model has been built, we then need to display its results clearly and concisely to get its message across, via a written report or oral presentation. The model output is just as important as the rest of the model building process, because there is no point in having fantastically built model that none of decision makers know about! However, this final stage of the model-building process is sometimes not given as much focus by many modellers, particularly those whose skills are often more analytical than visual.

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.