cover.eps

Title page image

Financial Modeling in Excel® For Dummies®

To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Financial Modeling in Excel For Dummies Cheat Sheet” in the Search box.

Introduction

I discovered financial modeling in Microsoft Excel when I worked in investment banking in London (as most young Aussies do). Back then, it wasn’t even called “financial modeling,” but I was hooked. Since those days, I’ve devoted my entire career to working in Excel and building models for the purpose of business cases, reports, budgets, and dashboards. I’ve worked with hundreds of clients in many different countries to help build their models for them or train them on how to build their own. Financial modeling in Excel takes me all over the world and I hope that it brings you the same fun and excitement!

About This Book

I wrote this book based on the experiences I’ve had with the many insightful people I’ve trained or worked with over the years. I cover the tools and techniques that are the most commonly needed for building models. This book is aimed at people who have a good smattering of Excel knowledge but want to improve their skills to perform better in their current roles or to get better jobs.

After reading this book, you’ll know exactly what a financial modeler does and how to apply the principles of financial modeling to your work. You may not call yourself a “career” financial modeler. Instead, you might think of yourself as a “casual” modeler — maybe it’s a side interest for you, or it’s just one part of your job. But after reading this book, you may be bitten by the modeling bug and want to pursue a full-time career in this field!

You don’t have to read this book from cover to cover — feel free to jump around and read the sections that are of most interest to you! In most cases, I demonstrate the tools and techniques covered by applying them to a simple model — usually what I would expect to be just part of a full financial model. In Part 3, you create three full financial models from start to finish. I encourage you to read this book with Excel open and not too far away because you’ll want to try out many of the exercises and techniques described in these pages.

Foolish Assumptions

I assume just a few basic things about you. It goes without saying that you’re highly intelligent because you recognize the value of having financial modeling skills. But I also assume that you have the following:

  • A PC with a relatively recent version of Excel installed: The screenshots and instructions in this book relate to Microsoft Excel 2016 and its capabilities. If you’re using a Mac, or a previous version of Excel, you might find some of the instructions slightly different, but you should be able to find your way around.
  • A working knowledge of Excel and a use for it: I don’t assume that you’re an Excel expert, but you should at least know your way around and perhaps have created at least a few basic calculations before.
  • Some kind of financial background: You know what a set of financial statements looks like, you know what revenue is, and you know how interest calculations work. Some of the complexities are explained in this book, but I assume that these kinds of basic financial concepts are not entirely new to you.

Icons Used in This Book

This book is jam-packed with tips, tricks, warning, and ways to work smarter, faster, and more accurately.

tip Anything marked with the Tip icon will make your financial modeling quicker or easier.

remember If I mark it with the Remember icon, it’s really, really important and you should pay special attention.

warning When you see the Warning icon, you know that I’m trying to save you the pain and agony of making a mistake (one that I’ve probably made many times myself).

technicalstuff I get very excited when talking or writing about financial modeling, so sometimes I get a little technical on you. Anything marked with the Technical Stuff icon isn’t essential to your understanding of the surrounding text.

Beyond the Book

In addition to the material in the print or e-book you’re reading right now, this product also comes with some access-anywhere goodies on the web. Check out the free Cheat Sheet for ten Excel functions that you absolutely need to know, tips on what to look for when auditing someone else’s financial model, and the best keyboard shortcuts for financial modelers. To get this Cheat Sheet, simply go to www.dummies.com and type Financial Modeling in Excel For Dummies Cheat Sheet in the Search box.

You can also go to www.dummies.com/go/financialmodelinginexcelfd for Excel files you can use to follow along with the exercises and examples in this book, as well as the completed versions of the financial models you build in Part 3.

Where to Go from Here

If you’re just getting started and want to find out what all the fuss is about financial modeling, start at Chapter 1 and read on from there. If you’re more technical and you want to get into something practical, Part 2 is a great place to start. Have a go at some of the shorter examples before getting started with the longer case studies in Part 3.

If you enjoy this book, I’d like to invite you to connect directly with me online through LinkedIn and other social media platforms. Search for the Financial Modeling in Excel LinkedIn group to join more than 40,000 other modelers and get involved in the active discussions! You can also subscribe to hear more about the world of financial modeling at www.plumsolutions.com.au/news, and I’d love to meet you at one of my upcoming events, or Financial Modelers’ Meetups soon!

Have fun, and happy modeling!

Part 1

Getting Started with Financial Modeling

IN THIS PART …

Explore the practical uses and examples of financial modeling.

Get to know Excel and identify the issues and risks for its use in building financial models.

Document and plan your model’s layout and design.

Learn important guidelines to follow when building your financial model.

Find your way around an inherited financial model, and audit and check its output for accuracy.

Chapter 1

Introducing Financial Modeling

IN THIS CHAPTER

check Exploring the who, what, and why of financial modeling

check Investigating different types of models

The demand for financial modeling skills has increased exponentially in recent years and many job listings for finance positions now include “financial modeling” as a core skill. If you’re reading this book, you’ve probably already discovered how important this skill is, and you know that learning financial modeling will increase your employability in finance or financially focused fields.

In this chapter, I define financial modeling — what it is, who uses it, and why it matters. I also show you some examples of financial models. If you’re brand-new to financial modeling, this chapter is a very good place to start.

Defining Financial Modeling

Before you dive into how to use Microsoft Excel to create financial models, you need to know what financial modeling is, who uses financial models, and why financial modeling matters. In this section, I fill you in.

What it is

When I teach a course on basic financial modeling, I always ask my students for their definitions of the term financial model. Most of them come up with long-winded descriptions using terms like forecast and cash flow and hypothetical outcomes. But I don’t think the definition needs to be that complicated. A financial model is a tool (typically built in Excel) that displays possible solutions to a real-world financial problem. And financial modeling is the task of creating a financial model.

You may have thought that a financial model was basically just an Excel spreadsheet, but as you know, not every spreadsheet is a financial model. People can and do use Excel for all kinds of purposes. So, what makes a financial model distinct from a garden-variety spreadsheet? In contrast to a basic spreadsheet, a financial model

  • Is more structured. A financial model contains a set of variable assumptions — inputs, outputs, calculations, and scenarios. It often includes a set of standard financial forecasts — such as a profit-and-loss statement, a balance sheet, and a cash flow statement — which are based on those assumptions.
  • Is dynamic. A financial model contains inputs that, when changed, impact the calculations and, therefore, the results. A financial model always has built-in flexibility to display different outcomes or final calculations based on changing a few key inputs.
  • Uses relationships between several variables. When the user changes any of the input assumptions, a chain reaction often occurs. For example, changing the growth rate will change the sales volume; when the sales volume changes, the revenue, sales commissions, and other variable expenses will change.
  • Shows forecasts. Financial models are almost always looking into the future. Financial modelers often want to know what their financial projections will look like down the road. For example, if you continue growing at the same rate, what will your cash flow be in five years?
  • Contains scenarios (hypothetical outcomes). Because a model is looking forward instead of backward, a well-built financial model can be easily used to perform scenario and sensitivity analysis. What would happen if interest rates went up? How much can we discount before we start making a loss?

More broadly, a financial model is a structure (usually in Excel) that contains inputs and outputs, and is flexible and dynamic.

Who uses it

Many types of people build and use financial models for different purposes and goals. Financial models are usually built to solve real-world problems, and there are as many different financial models as there are real-world problems to solve. Generally, anyone who uses Excel for the purpose of finance will at some point in his career build a financial model for himself or others to use; at the very least, he’ll use a model someone else created.

Bankers, particularly investment bankers, are heavy users of financial models. Due to the very nature of financial institutions, modeling is part of the culture of the company — the business’s core is built on financial models. Banks and financial institutions must comply with current regulatory restrictions, and the tools and controls in place are forever changing and adapting. Because of the risk associated with lending and other financial activities, these institutions have very complex financial modeling systems in place to ensure that the risk is managed effectively. Anyone working in the banking industry should have at least a working knowledge of spreadsheets and financial models.

Outside the banking industry, accountants are big users of financial models. Bankers are often evaluating other companies for credit risk and other measures. An accountant’s models, however, are often more inward looking, focusing on internal operations reporting and analysis, project evaluation, pricing, and profitability.

Why it matters

A financial model is designed to depict a real-life situation in numbers in order to help people make better financial decisions.

Wherever there are financial problems or situations in the real world that need solving, analyzing, or translating into a numerical format, financial models help. Sometimes it’s just an idea or a concept that needs to be converted into a business case or feasibility proposal. A skilled financial modeler can put substance to the idea by augmenting the details enough to get a working model upon which decisions can be made, investor funds can be gained, or staff can be hired.

For example, financial models can help investors decide which project to put their money into, an executive track which marketing campaigns have the highest return on investment, or a factory production manager decide whether to purchase a new piece of machinery.

Looking at Examples of Financial Models

When you then consider the benefits that a financial model can bring, it’s difficult not to get carried away thinking of the application potential of a financial model! When you understand the principles of financial models, you can begin to look at the most common scenarios in which a model would be implemented.

There are a variety of categories of financial models:

  • Project finance models: When a large infrastructure project is being assessed for viability, the project finance model helps determine the capital and structure of the project.
  • Pricing models: These models are built for the purpose of determining the price that can or should be charged for a product.
  • Integrated financial statement models (also known as a three-way financial model): The purpose of this kind of model is to forecast the financial position of the company as a whole.
  • Valuation models: Valuation models value assets or businesses for the purpose of joint ventures, refinancing, contract bids, acquisitions, or other kinds of transactions or “deals.” (The people who build these kinds of models are often known as deals modelers.)
  • Reporting models: These models summarize the history of revenue, expenses, or financial statements (such as the income statement, cash flow statement, or balance sheet).

Modelers generally specialize in one or two of these model categories. You’ll see some overlap between each type of model category, but most models can be classified as one model type.

In this section, I show you some examples of scenarios and places in which these categories of financial models can come in handy, along with the functions and characteristics of each.

Project finance models

Loans and the associated debt repayments are an important part of project finance models, because these projects are normally long term, and lenders need to know whether the project is able to produce enough cash to service the debt. Metrics such as debt service cover ratio (DSCR) are included in the model and can be used as a measure of risk of the project, which may affect the interest rate offered by the lender. At the beginning of the project, the DSCR and other metrics are agreed upon between the lender and borrower such that the ratio must not go below a certain number.

Pricing models

The input to a pricing model is the price, and the output is the profitability. To create a pricing model, an income statement (or profit-and-loss statement) of the business or product should be created first, based on the current price or a price that has been input as a placeholder. At a very high level:

  • Units × Price = Revenue
  • Revenue – Expenses = Profit

Of course, this kind of model can be very complex and involve many different tabs and calculations, or it can be quite simple, on a single page. When this structure model is in place, the modeler can perform sensitivity analysis on the price entered using a goal seek (see Chapter 7) or a data table (see Chapter 8).

Integrated financial statement models

Not every financial model needs to contain all three types of financial statements, but many of them do, and those that do are known as integrated financial statement models. You may also hear them referred to as “three-way financial models.” The three types of financial statements included in an integrated financial statement model are the following:

  • Income statement, also known as a profit-and-loss (P&L) statement
  • Cash flow statement
  • Balance sheet

From a financial modeling perspective, it’s very important that when an integrated financial statement model is built, the financial statements are linked together properly so that if one statement changes, the others change as well. For an example of how to build an integrated financial statement model, turn to Chapter 12.

Valuation models

Building valuation models requires a specialized knowledge of valuation theory (using the different techniques of valuing an asset), as well as modeling skills. If you’re a casual financial modeler, you probably won’t be required to create from scratch a fully functioning valuation model. But you should at least have an idea of what types of valuation financial models are out there.

Here are three common types of valuation financial models you may encounter:

  • Mergers and acquisitions (M&A): These models are built to simulate the effect of two companies merging or one company taking over the other. M&A models are normally undertaken in a tightly controlled environment. Due to its confidential nature, an M&A model has fewer players than other kinds of models. The project moves quickly because time frames are tight. The few modelers working on an M&A model do so in a concentrated period of time, often working long hours to achieve a complex and detailed model.
  • Leveraged buyout (LBO): These models are built to facilitate the purchase of a company or asset with large amounts of debt to finance the deal, called a leveraged buyout. The entity acquiring the “target” company or asset usually finances the deal with some equity, using the target’s assets as security — in the same way that many home loan mortgages work. LBOs are a popular method of acquisition because they allow the entity to make large purchases without committing a lot of cash. Modeling is an important part of the LBO deal because of its complexity and the high stakes involved.
  • Discounted cash flow (DCF): These models calculate the cash expected to be received from the business or asset a company is considering purchasing, and then discounts that cash flow back into today’s dollars to see whether the opportunity is worth pursuing. Valuing the future cash flows expected from an acquisition is the most common modeling method of valuation. Intrinsic to the DCF methodology is the concept of the time value of money — in other words, that cash received today is worth a lot more than the same amount of cash received in future years. For an example of how to calculate DCF, turn to Chapter 11.

Reporting models

Because they look historically at what occurred in the past, some people argue that reporting models are not really financial models at all, but I disagree. The principles, layout, and design that are used to create a reporting model are identical to other financial models. Just because they contain historical rather than projected numbers doesn’t mean they should be categorized any differently.

In fact, reporting models are often used to create actual versus budget reports, which often include forecasts and rolling forecasts, which in turn are driven by assumptions and other drivers. Reporting models often start out as a simple income statement report, but end up being transformed into fully integrated financial statement models, pricing models, project finance models, or valuation models.

Chapter 2

Getting Acquainted with Excel

IN THIS CHAPTER

check Comparing different versions of Excel

check Introducing Modern Excel

check Recognizing the pitfalls of using Excel

check Exploring alternatives to Excel

For most people, Microsoft Excel and financial modeling go hand in hand. Given the title of this book, it should come as no surprise to you that I assume you’ll be using Excel. In order to build a financial model, you need at least a working knowledge of Excel. So, before jumping into the details of financial modeling, I’m going to introduce you to the tool you’ll be using, Microsoft Excel.

Almost every financial model you’ll come across will make use of Excel to some extent, but alternatives to Excel do exist, as do add-ins to improve Excel, both of which I cover in this chapter. Finally, I look at some of the issues and risks related to the use of Excel, just so you know what to expect.

Making Sense of the Different Versions of Excel

Every few years, Microsoft brings out a new version of Excel. For users who are comfortable with the way their version of Excel works, these changes are often met with apprehension or dismay. But for avid Excel fans like me, each new release is a cause for excitement! I’m always eager to find out what new tools and features have been introduced to improve the process of building financial models in Excel.

Although major changes have been applied to Excel over the past few versions, the changes are less relevant for financial modelers than they are for some other folks. Why? Because many of the new features are visual, and financial modeling relies less on visual features and more on links and formulas, which haven’t changed.

warning Some new functions have been introduced in recent versions of Excel. If you build a model that contains these new functions and a user opens it in a previous version of Excel, he’ll get a #N/A error. I recommend avoiding new functions when you’re building a financial model, unless you’re sure that anyone who needs to use your model will be using the same version of Excel as you.

tip If you’re not sure whether you’ve used any functions or features not available in previous versions of Excel, use the Inspect Workbook tool (see Chapter 5) to find out.

And if you’re not sure which version of Excel you’re using, open Excel and choose File ⇒   Account ⇒   About Excel. At the top of the dialog box that appears, you’ll see the version number. If that doesn’t work, then you’re probably using a very old version; choose Help ⇒   Resources ⇒   About.

A rundown of recent Excel versions

In this section, I walk you through some of the features introduced in recent versions of Excel. Although these lists are not exhaustive, they are the features you’re most likely to use for the purposes of financial modeling and analysis.

tip If you have Excel on an Office 365 subscription plan, you get new features as soon as they roll out with each update, instead of having to wait for the next version of Excel.

Microsoft Excel 2016

In Excel 2016, the following features were added:

  • The Tell Me What You Want to Do box was added to the Ribbon. This box is a very user-friendly way of finding your way around Excel.
  • The following new charts were added: Waterfall, Treemap, Sunburst, Histogram, Box & Whisker, and Funnel. These new charts are a welcome addition to Excel and make it very easy to display the results of your financial model. But remember that if you insert any of these new charts into your model and a user opens it in a previous version of Excel, the charts won’t be available — they’ll only be able to see a blank white box.
  • Power Query was changed to Get & Transform. It’s on the Data tab on the Ribbon. In prior versions of Excel, Power Query had to be installed as a free downloadable add-in, but Get & Transform comes standard.
  • Forecast Sheet was added. It’s a very powerful way of forecasting using historical data.
  • The following new functions were introduced. Note these functions are only available in Excel 2016 to Office 365 subscribers:

    • TEXTJOIN: Use this function to link the text in ranges of cells together. This is one of my favorite new functions because you can now string entire ranges of cells together, instead of linking them individually as you had to do with the ampersand (&) or the CONCATENATE function.
    • CONCAT: Use this function to link the text in individual cells together. This was called CONCATENATE in previous versions. You can also use the ampersand (&) instead of CONCAT or CONCATENATE.
    • IFS: Use this function if you have multiple conditions to include in a single cell. This function makes using a nested IF function much easier.
    • SWITCH: Use this function to look up a list of values and return a matching result in a single cell.
    • MAXIFS: Use this function to calculate the maximum value that meets specific criteria.
    • MINIFS: Use this function to calculate the minimum value that meets specific criteria.

    remember Even though these new functions might come in handy, they won’t work properly if the person opening your model is not using Excel 2016 or later.

Microsoft Excel 2013

In Excel 2013, the following features were added:

  • Flash Fill was introduced. Flash Fill is a handy tool that picks up on the pattern of what you’ve entered.

    To use Flash Fill, start typing an abbreviated version of your data in the column directly next to it, as shown in Figure 2-1. Based on the pattern of what you’ve typed, a grayed-out version of suggested text is displayed. Press Enter to accept this data. If you’d like to try this out for yourself, you can download File 0201.xlsx from www.dummies.com/go/financialmodelinginexcelfd. Open it and select the tab labeled 2-1.

  • The Combo Chart was introduced as a standard chart. Combo Charts display a line chart and a bar chart on two different axes. For example, you might choose to show customer numbers on one axis and profitability on the other, as shown in Figure 2-2.
  • Multiple monitors were made easier to work with because the interface changed so that you can have two separate files open and view them side by side. In the past, you would have had to open a completely new session of Excel to do this, so you couldn’t link between files. Whether you link between files or not, having large and/or multiple monitors is definitely recommended for large and complex models!
  • Fifty new functions were introduced, enhancing the already abundant function set. Most of the new functions are used for statistics, trigonometry, and engineering, but here are a few that you might find useful for financial modeling:

    • PDURATION: Use this function to return the number of investment periods required for the invested amount to get to the specific value.
    • IFNA: Use this function to suppress an #N/A error only.
    • ISFORMULA: Use this function to return the value TRUE if the cell contains a formula. This function is similar to the ISERROR, ISNUM, and ISTEXT functions.

    remember Even though these functions might come in handy, they’ll return an error if the person opening your model is not using Excel 2013 or later.

image

FIGURE 2-1: Flash Fill.

image

FIGURE 2-2: Combo Chart.

Microsoft Excel 2010

At first glance, there were no obvious changes introduced in Excel 2010, but this upgrade was actually deceivingly radical because it was the first version to introduce the Power Suite of tools, now called Modern Excel. Additionally, two other features made an appearance for the first time:

  • Slicers: Slicers are a great way of filtering PivotTables.
  • Sparklines: Sparklines are tiny charts in a single cell. They’re a great way of displaying trends in a small space.

Figure 2-3 shows an example of a PivotTable with a slicer on the left in column A and a series of sparklines in column D. When you select one of the regions shown in the slicer, the data for the PivotTable filters to show only that selection. Additionally, sparklines in column D show the trend over a 12-month period of that line item.

image

FIGURE 2-3: Slicer and sparklines.

These two features, although not related, work together so that when Africa is selected, for example, the total profit and loss numbers for Africa show only in column C, and the 12-month trend for Africa in the sparklines show only in column D. Both slicers and sparklines were particularly useful additions for building dashboards.

remember The space in which slicers and sparklines are built will simply show as blank areas if the file is opened in Excel 2007 or earlier.

Microsoft Excel Online

You can use Excel online through a web browser with Microsoft Excel Online. Microsoft Excel Online is completely free, works on any browser, and is useful for shared files and collaborating with others. It’s basically a stripped-down version of Excel.

warning Microsoft Excel Online is only sufficient for a casual user of Excel, not for a financial modeler. You need a desktop version of Excel in order to work through the steps in this book.

Focusing on file formats

Another thing that you may need to consider when working with different versions of Excel is the file type. Way back in Excel 2007, the file formats were changed from XLS to XLSX. The XLSX file format is more secure, faster, and more compact than XLS files. Also, XLS files are also limited to 65,000 rows, which sounds like a lot, but XLSX files can handle up to a million rows.

Although the XLSX file type has been around for many years, Excel files that have been downloaded from another system are sometimes automatically saved as XLS files. If you have Excel 2007 or later, you can save the file as XLSX by choosing File ⇒   Save As, and changing the file type from Excel 97–2003 to Excel Workbook.

You might also run into the XLSM file format. Those files contain macros, which contain executable code. If you’re using macros, Excel will prompt you to save the file as XLSM. And if you accidently save a file with macros in it as XLSX, all the macros will completely disappear!

tip The XLSB file format is a binary file format and is even more compressed than XLSX, making the file size even smaller (which means the files open and save much faster than other file types). It has the added advantage of supporting macros. The only disadvantage is that XLSB files can’t be read by other databases and software, including other cloud-based spreadsheet programs (although that’s not usually an issue for financial models).

remember You should always save your models as XLSX file types, or XLSB if file size becomes an issue.