Using Excel for Business Analysis

A Guide to Financial Modelling Fundamentals

Danielle Stein Fairhurst

• Publisher: Wiley
• Year: 2012
• Print ISBN: 9781118132845
• eBook ISBN: 9781118132876
• Edition: 1
• Topic: Business
• Subtopic: Finance
• Index: Business

A clear, concise, and easy-to-use guide to financial modelling suitable for practitioners at every level

Using a fundamental approach to financial modelling that's accessible to both new and experienced professionals, Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals + Website offers practical guidance for anyone looking to build financial models for business proposals, to evaluate opportunities, or to craft financial reports. Comprehensive in nature, the book covers the principles and best practices of financial modelling, including the Excel tools, formulas, and functions to master, and the techniques and strategies necessary to eliminate errors.

As well as explaining the essentials of financial modelling, Using Excel for Business Analysis is packed with exercises and case studies to help you practice and test your comprehension, and includes additional resources online.

• Provides comprehensive coverage of the principles and best practices of financial modeling, including planning, how to structure a model, layout, the anatomy of a good model, rebuilding an inherited model, and much more
• Demonstrates the technical Excel tools and techniques needed to build a good model successfully
• Outlines the skills you need to learn in order to be a good financial modeller, such as technical, design, and business and industry knowledge
• Illustrates successful best practice modeling techniques such as linking, formula consistency, formatting, and labeling
• Describes strategies for reducing errors and how to build error checks and other methods to ensure accurate and robust models

A practical guide for professionals, including those who do not come from a financial background, Using Excel for Business Analysis is a fundamentals-rich approach to financial modeling.

CHAPTER 1

What Is Financial Modelling?

There are all sorts of complicated definitions of financial modelling, and in my experience there is quite a bit of confusion around what a financial model is exactly. A few years ago, we put together a Plum Solutions survey about the attitudes, trends, and uses of financial modelling, asked respondents “What do you think a financial model is?” Participants were asked to put down the first thing that came to mind, without any research or too much thinking about it. I found the responses interesting, amusing, and sometimes rather disturbing.

Some answers were overly complicated and highly technical:

• “Representation of behaviour/real-world observations through mathematical approach designed to anticipate range of outcomes.”
• “A set of structured calculations, written in a spreadsheet, used to analyse the operational and financial characteristics of a business and/or its activities.”
• “Tool(s) used to set and manage a suite of variable assumptions in order to predict the financial outcomes of an opportunity.”
• “A construct that encodes business rules, assumptions, and calculations enabling information, analysis, and insight to be drawn out and supported by quantitative facts.”
• “A system of spreadsheets and formulas to achieve the level of record keeping and reporting required to be informed, up-to-date, and able to track finances accurately and plan for the future.”

Some philosophical:

• “A numerical story.”

Some incorrect:

• “Forecasting wealth by putting money away now/investing.”
• “It is all about putting data into a nice format.”
• “It is just a mega huge spreadsheet with fancy formulas that are streamlined to make your life easier.”

Some ridiculous:

• “Something to do with money and fashion?”

Some honest:

• “I really have no idea.”

And some downright profound:

• “A complex spreadsheet.”

Whilst there are many other (often very complicated and long-winded) definitions available from different sources, but I actually prefer the last, very broad, but accurate description: “a complex spreadsheet.” Whilst it does need some definition, a financial model can pretty much be whatever you need it to be.

As long as a spreadsheet has inputs and outputs, and is dynamic and flexible—I’m happy to call it a financial model! Pretty much the whole point of financial modelling is that you change the inputs and the outputs. This is the major premise behind scenario and sensitivity analysis—this is what Excel, with its algebraic logic, was made for! Most of the time, a model will contain financial information and serve the purpose of making a financial decision, but not always. Quite often it will contain a full set of financial statements: profit and loss, cash flow, and balance sheet; but not always.

According to the more staid or traditional definitions of financial modelling, the following items would all most certainly be classified as financial models:

• A business case that determines whether or not to go ahead with a project.
• A five-year forecast showing profit and loss, cash flow, and balance sheet.
• Pricing calculations to determine how much to bid for a new tender.
• Investment analysis for a joint venture.

But what about other pieces of analysis that we perform as part of our roles? Can these also be called financial models? What if something does not contain financial information at all? Consider if you were to produce a spreadsheet for the following purposes:

• An actual-versus-budget monthly variance analysis that does not contain scenarios and for which there are no real assumptions listed.
• A risk assessment, where you enter the risk, assign a likelihood to that risk, and calculate the overall risk of the project using probability calculations. This does not contain any financial outputs at all.
• A dashboard report showing a balance scorecard type of metrics reporting like headcount, quality, customer numbers, call volume, and so on. Again, there are few or no financial outputs.

See the section on the “Types and Purposes of Financial Models” later in this chapter for some more detail on financial models that don’t actually contain financial information.

Don’t get hung up on whether you’re actually building something that meets the definition of a financial model or not. As long as you’ve got inputs and outputs that change flexibly and dynamically you can call it a financial model! If you’re using Excel to any extent whereby you are linking cells together, chances are you’re already building a financial model—whether you realise it or not. The most important thing is that you are building the model (or whatever it’s called!) in a robust way, following the principles of best practice, which this book will teach you.

Generally, a model consists of one or more input variables along with data and formulas that are used to perform calculations, make predictions, or perform any number of solutions to business (or non-business) requirements. By changing the values of the input variables, you can do sensitivity testing and build scenarios to see what happens when the inputs change.

Sometimes managers treat models as though they are able to produce the answer to all business decisions and solve all business problems. Whilst a good model can aid significantly, it’s important to remember that models are only as good as the data they contain, and the answers they produce should not necessarily be taken at face value.

“The reliability of a spreadsheet is essentially the accuracy of the data that it produces, and is compromised by the errors found in approximately 94% of spreadsheets.”1 When presented with a model, the savvy manager will query all the assumptions, and the way it’s built. Someone who has had some experience in building models will realise that they must be treated with caution. Models should be used as one tool in the decision-making process, rather than the definitive solution.

WHAT’S THE DIFFERENCE BETWEEN A SPREADSHEET AND A FINANCIAL MODEL?

Let me make one thing very clear: I am not partial to the use of the word spreadsheet; in fact you’ll hardly find it used at all in this book.

I’ve often been asked the difference between the two, and there is a fine line of definition between them. In a nutshell, an Excel spreadsheet is simply the medium that we can use to create a financial model.

At the most basic level, a financial model that has been built in Excel is simply a complex spreadsheet. By definition, a financial model is a structure that contains input data and supplies outputs. By changing the input data, we can test the results of these changes on the output results, and this sort of sensitivity analysis is most easily done in an Excel spreadsheet.

One could argue then, that they are in fact the same thing; there is really no difference between a spreadsheet and a financial model. Others question if it really matters what we call them as long as they do the job? After all, both involve putting data into Excel, organising it, formatting, adding some formulas, and creating some usable output. There are, however, some subtle differences to note.

1. “Spreadsheet” is a catchall term for any type of information stored in Excel, including a financial model. Therefore, a spreadsheet could really be anything—a checklist, a raw data output from an accounting system, a beautifully laid out management report, or a financial model used to evaluate a new investment.

2. A financial model is more structured. A model contains a set of variable assumptions, inputs, outputs, calculations, scenarios, and often includes a set of standard financial forecasts such as a profit and loss, balance sheet, and cash flow, which are based on those assumptions.

3. A financial model is dynamic. A model contains variable inputs, which, when changed, impact the output results. A spreadsheet might be simply a report that aggregates information from other sources and assembles it into a useful presentation. It may contain a few formulas, such as a total at the bottom of a list of expenses or average cash spent over 12 months, but the results will depend on direct inputs into those columns and rows. A financial model will always have built-in flexibility to explore different outcomes in all financial reports based on changing a few key inputs.

4. A spreadsheet is usually static. Once a spreadsheet is complete, it often becomes a stand-alone report, and no further changes are made. A financial model, on the other hand, will always allow a user to change input variables and see the impact of these assumptions on the output.

5. A financial model will use relationships between several variables to create the financial report, and changing any or all of them will affect the output. For example, Revenue in Month 4 could be a result of Sales Price × Quantity Sold Prior Month × Monthly Growth in Quantities Sold. In this example, three factors come into play, and the end user can explore different mixes of all three to see the results and decide which reflects their business model best.

6. A spreadsheet shows actual historical data, whereas a financial model contains hypothetical outcomes. A by-product of a well-built financial model is that we can easily use it to perform scenario and sensitivity analysis. This is an important outcome of a financial model. What would happen if interest rates increase by half a basis point? How much can we discount before we start making a loss?

In conclusion, a financial model is a complex type of spreadsheet, whilst a spreadsheet is a tool that can fulfill a variety of purposes—financial models being one. The list of attributes above can identify the spreadsheet as a financial model, but in some cases, we really are talking about the same thing. Take a look at the Excel files you are using. Are they dynamic, structured, and flexible, or have you simply created a static, direct-input spreadsheet?

TYPES AND PURPOSES OF FINANCIAL MODELS

Models in Excel can be built for virtually any purpose—financial and non-financial, business- or non-business related—although the majority of models will be financial and business-related. The following are some examples of models that do not capture financial information:

• Risk Management: A model that captures, tracks, and reports on project risks, status, likelihood, impact, and mitigation. Conditional formatting is often integrated to make a colourful, interactive report.
• Project Planning: Models may be built to monitor progress on projects, including critical path schedules and even Gantt charts. (See the next section in this chapter, “Tool Selection,” for an analysis of whether Microsoft Project or Excel should be used for building this type of project plan.)
• KPIs and Benchmarking: Excel is the best tool for pulling together KPI and metrics reporting. These sorts of statistics are often pulled from many different systems and sources, and Excel is often the common denominator between different systems.
• Dashboards: Popularity in dashboards has increased in recent years. The dashboard is a conglomeration of different measures (sometimes financial but often not), which are also often conveniently collated and displayed as charts and tables using Excel.
• Balanced Scorecards: These help provide a more comprehensive view of a business by focusing on the operational, marketing, and developmental performance of the organisation as well as financial measures. A scorecard will display measures such as process performance, market share or penetration, and learning and skills development, all of which are easily collated and displayed in Excel.

As with many Excel models, most of these could be more accurately created and maintained in a purpose-built piece of software, but quite often the data for these kinds of reports is stored in different systems, and the most practical tool for pulling the data together and displaying it in a dynamic monthly report is Excel.

Although purists would not classify these as financial models, the way that they have been built should still follow the fundamentals of financial modelling best practices, such as linking and assumptions documentation. How we classify these models is therefore simply a matter of semantics, and quite frankly I don’t think what we call them is particularly important! Going back to our original definition of financial modelling, it is a structure (usually in Excel) that contains inputs and outputs, and is flexible and dynamic.

TOOL SELECTION

In this book we will use Excel exclusively, as that is most appropriate for the kind of financial analysis we are performing when creating financial models. I recommend using plain Excel, without relying on any other third-party software for several reasons:

• No extra licenses, training, or software download is required.
• The software can be installed on almost any computer.
• Little training is required, as most users have some familiarity with the product—which means other people will be able to drive and understand your model.
• It is a very flexible tool. If you can imagine it, you can probably do it in Excel (within reason, of course).
• Excel can report, model, and contrast virtually any data, from any source, all in one report.
• But most importantly, Excel is commonly used across all industries, countries, and organisations. What this means to you is that if you have good financial modelling skills in Excel, these skills are going to make you more in demand—especially if you are considering changing industr...

Table of contents

• Cover
• Contents
• Title
• Copyright
• Dedication
• Preface
• Chapter 1: What is Financial Modelling?
• Chapter 2: Building a Model
• Chapter 3: Best Practice Principles of Modelling
• Chapter 4: Financial Modelling Techniques
• Chapter 5: Using Excel in Financial Modelling
• Chapter 6: Functions for Financial Modelling
• Chapter 7: Tools for Model Display
• Chapter 8: Tools for Financial Modelling
• Chapter 9: Common Uses of Tools in Financial Modelling
• Chapter 10: Model Review
• Chapter 11: Stress-Testing, Scenarios, and Sensitivity Analysis in Financial Modelling
• Chapter 12: Presenting Model Output
• About the Author
• About the Website
• Index