20,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Make informed business decisions with the beginner's guide to financial modeling using Microsoft Excel Financial Modeling in Excel For Dummies is your comprehensive guide to learning how to create informative, enlightening financial models today. Not a math whiz or an Excel power-user? No problem! All you need is a basic understanding of Excel to start building simple models with practical hands-on exercises and before you know it, you'll be modeling your way to optimized profits for your business in no time. Excel is powerful, user-friendly, and is most likely already installed on your computer--which is why it has so readily become the most popular financial modeling software. This book shows you how to harness Excel's capabilities to determine profitability, develop budgetary projections, model depreciation, project costs, value assets and more. You'll learn the fundamental best practices and know-how of financial modeling, and how to put them to work for your business and your clients. You'll learn the tools and techniques that bring insight out of the numbers, and make better business decisions based on quantitative evidence. You'll discover that financial modeling is an invaluable resource for your business, and you'll wonder why you've waited this long to learn how! Companies around the world use financial modeling for decision making, to steer strategy, and to develop solutions. This book walks you through the process with clear, expert guidance that assumes little prior knowledge. * Learn the six crucial rules to follow when building a successful financial model * Discover how to review and edit an inherited financial model and align it with your business and financial strategy * Solve client problems, identify market projections, and develop business strategies based on scenario analysis Create valuable customized templates models that can become a source of competitive advantage From multinational corporations to the mom-and-pop corner store, there isn't a business around that wouldn't benefit from financial modeling. No need to buy expensive specialized software--the tools you need are right there in Excel. Financial Modeling in Excel For Dummies gets you up to speed quickly so you can start reaping the benefits today!
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 399
Veröffentlichungsjahr: 2017
Ähnliche
Financial Modeling in Excel® For Dummies®
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2017 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2017936812
ISBN: 978-1-119-35754-4; ISBN 978-1-119-35755-1 (ebk); ISBN 978-1-119-35756-8 (ebk)
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.
Table of Contents
Cover
Introduction
About This Book
Foolish Assumptions
Icons Used in This Book
Beyond the Book
Where to Go from Here
Part 1: Getting Started with Financial Modeling
Chapter 1: Introducing Financial Modeling
Defining Financial Modeling
Looking at Examples of Financial Models
Chapter 2: Getting Acquainted with Excel
Making Sense of the Different Versions of Excel
Defining Modern Excel
Recognizing the Dangers of Using Excel
Looking at Alternatives and Supplements to Excel
Chapter 3: Planning and Designing Your Financial Model
Identifying the Problem That Your Financial Model Needs to Solve
Designing How the Problem’s Answer Will Look
Gathering Data to Put in Your Model
Documenting the Limitations of Your Model
Considering the Layout and Design of Your Model
Chapter 4: Building a Financial Model by the Rulebook
Document Your Assumptions
Create Dynamic Formulas Using Links
Only Enter Data Once
Model with Consistent Formulas
Build in Error Checks
Format and Label for Clarity
Chapter 5: Using Someone Else’s Financial Model
Considering Templates for Building a Financial Model
Inheriting a File: What to Check For
Using Audit Tools to Find and Correct Errors
Part 2: Diving Deep into Excel
Chapter 6: Excel Tools and Techniques for Financial Modeling
Referencing Cells
Naming Ranges
Linking in Excel
Using Shortcuts
Restricting and Validating Data
Goal Seeking
Chapter 7: Using Functions in Excel
Identifying the Difference between a Formula and a Function
Finding the Function You Need
Getting Familiar with the Most Important Functions
Being Aware of Advanced Functions and Functionality
Chapter 8: Applying Scenarios to Your Financial Model
Identifying the Differences between Types of Analysis
Building Drop-Down Scenarios
Applying Sensitivity Analysis with Data Tables
Using Scenario Manager to Model Loan Calculations
Chapter 9: Charting and Presenting Model Output
Deciding Which Data to Display
Conveying Your Message by Charting Scenarios
Deciding Which Type of Chart to Use
Dynamic Charting
Preparing a Presentation
Part 3: Building Your Financial Model
Chapter 10: Building an Integrated Financial Statements Model
Getting to Know the Case Study
Entering Assumptions
Calculating Revenue
Calculating Expenses
Building the Income Statement
Building the Cash Flow Statement
Building the Balance Sheet
Building Scenarios
Chapter 11: Building a Discounted Cash Flow Valuation
Understanding How the Discounted Cash Flow Valuation Works
Step 1: Calculating Free Cash Flow to Firm
Step 2: Calculating Weighted Average Cost of Capital
Step 3: Finding the Terminal Value
Discounting Cash Flows and Valuation
Chapter 12: Budgeting for Capital Expenditure and Depreciation
Getting Started
Output 1: Calculating Cash Required for Budgeted Asset Purchases
Output 2: Calculating Budgeted Depreciation
Output 3: Calculating the Written-Down Value of Assets for the Balance Sheet
Part 4: The Part of Tens
Chapter 13: Ten Strategies for Reducing Error
Using the Enter Key
Checking Your Work
Checking It Again
Getting Someone Else to Check Your Work
Documenting Assumptions
Documenting Methodology with a Flowchart
Stress-Testing with Sensitivity Analysis
Conducting a Scenario Analysis
Taking Note of Excel Error Values
Including Error Checks
Chapter 14: Ten Common Pitfalls to Avoid
The Numbers Don’t Add Up
You’re Getting #REF! Errors
You Have Circular References
The Model Has Too Much Detail
The File Size Is Out of Control
Your Model Is Full of “Spaghetti” Links
The Formulas Are Unnecessarily Long and Complicated
No One Is Paying Attention to the Model
You Don’t Want to Let Go
Someone Messes Up Your Model
About the Author
Advertisement Page
Connect with Dummies
End User License Agreement
Guide
Cover
Table of Contents
Begin Reading
Pages
iv
vii
viii
ix
x
xi
1
2
3
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
247
248
249
250
251
252
253
254
255
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
287
288
289
290
291
292
293
294
295
296
297
298
317
318
319
320
321
322
323
324
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.
Anything marked with the Tip icon will make your financial modeling quicker or easier.
If I mark it with the Remember icon, it’s really, really important and you should pay special attention.
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).
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
Exploring the who, what, and why of financial modeling
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.
WHAT IT TAKES TO BE A FINANCIAL MODELER
Someone working with financial models typically has an undergraduate degree in business, finance, or commerce. Additionally, she likely has at least one of the following postgraduate qualifications:
An accountancy qualification, such as CA (Certified Accountant), CPA (Certified Public Accountant), CIMA (Chartered Institute of Management Accountants), ACCA (Association of Chartered Certified Accountants), CMA (Certified Management Accountant), or CIA (Certified Internal Auditor)A Master of Business Administration (MBA) degreeA Chartered Financial Analyst (CFA) designationA Financial Risk Manager (FRM) designationOf course, you don’t need all those letters after your name to build and work with financial models. I know many skilled modelers who come from backgrounds in IT or engineering, or who don’t have any formal qualifications at all. Currently, there is no specific certification qualification for financial modeling professionals — at least nothing that is publically recognized — but I expect this might change in the near future. You can find courses in financial modeling, however. For example, I run a five-day Certificate in Financial Modeling Using Excel course through George Washington University several times a year in Dubai. And I have colleagues who run similar programs. I would classify these kinds of program as short-course vocational training rather than full certification.
If you simply want to list financial modeling as a skill on your résumé, a short course is sufficient (backed up by at least a couple of models you’ve built in the real world). If you’re aiming toward a financial modeling career, you’ll need formal finance qualifications such as those listed here, as well as intense, practical, hands-on work experience.
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.
Chapter 2
Getting Acquainted with Excel
IN THIS CHAPTER
Comparing different versions of Excel
Introducing Modern Excel
Recognizing the pitfalls of using Excel
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.
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.
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.
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.
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.
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.
FIGURE 2-1: Flash Fill.
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.
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.
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.
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!
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).
You should always save your models as XLSX file types, or XLSB if file size becomes an issue.
Defining Modern Excel
Any version of Excel released from Excel 2010 onward is referred to as Modern Excel because it introduced the groundbreaking Power Suite, which consists of Power Pivot, Power Query (now called Get & Transform), and Power View (as well as Power Map and Power BI, which were added later). The introduction of these tools was the most exciting thing to happen in the Excel world since the PivotTable.
Table 2-1 offers a summary of the features of Modern Excel.
TABLE 2-1 Modern Excel Tools
Tool
What It Does
Programming Language
Relevant Version
Power Pivot
Pulls much larger quantities of data than could be handled in standard Excel from different sources and stores it in a highly compressed format. Users can create relationships, perform complex calculations, and display output from different tables into a single-view PivotTable.
DAX
First introduced as an add-in to Excel 2010; native to Excel 2016*
Power Query (Get & Transform)
Extracts data from various sources. The user can cleanse and format the data and save this procedure; the procedure can then be repeatedly performed each time the data is refreshed.
M
First introduced as an add-in to Excel 2010; native to 2016* (when the name changed to Get & Transform)
Power View
Enables animation of charts (for example, showing movement of bubble charts over time).
None
Excel 2013 and 2016 (disabled by default)
Power Map
Allows you to dump some data into a table, containing location names, postcodes, or map coordinates, and Power Map shows your data visually on a map. Very cool!
None
Excel 2013 and 2016*
Power BI
A cloud-based, self-service analytics tool with which you can create dashboards, reports, and visualizations.
None
Desktop version first made available in 2015. Note that Power BI is the only tool mentioned that does not sit within Excel.
* To access these tools, you need Office Professional Plus 2013 or Office Professional 2016. They are not available in the Home & Student Edition.
The self-service BI space, in particular, is growing rapidly, and there are many other pieces of software that can perform similar tasks. In my opinion, these Modern Excel tools are the way to go for handling and visualizing data for the following reasons:
Low cost:
Power BI Pro (with larger data capacity and enhanced sharing capabilities over standard Power BI) comes with a small monthly cost, but the other tools are included with your Excel license.
Familiarity:
Because they’re part of Excel, and mostly use the familiar Excel interface, existing Excel users can get the hang of it more quickly than completely new software — although Power Pivot can take some time to figure out.
Integration:
It’s pretty easy to convince the boss to implement these tools because they’re already part of Excel.
Many financial modelers I know don’t see these new tools as being relevant to them. Sure, they are data analysis tools as opposed to modeling tools, but modelers spend a lot of time extracting, updating, and manipulating data. Power Query, in particular, is a useful tool for performing these tasks more quickly and efficiently.
The Modern Excel tool that is the most likely to be used for modeling is Power Pivot. As a self-service BI product, Power Pivot is intended to allow users with no specialized BI or analytics training to develop data models and calculations, sharing them either directly in Excel or through SharePoint document libraries. You should consider using Power Pivot for the data in your model if any of the following is true:
The data your model is using contains many thousands of rows and your model is starting to slow down, especially when you add formulas.
You use PivotTables or tables extensively.
Your data needs to be sourced from multiple locations.
The disadvantage of using Power Pivot is that, although you don’t need to be a BI specialist to view and edit reports, learning how to build models with Power Pivot is not particularly straightforward, even for advanced Excel users. You can get started on these tools with some free YouTube videos.
As a modeler, you’ll be using Excel all day every day, and you need to keep up to date with all the changes, including the new tools of Modern Excel, because Microsoft releases new updates regularly. Throughout this book, I recommend the use of these tools to access, retrieve, or update the data for your model, or to display the outputs, but in terms of building your financial model, I’ll stick with plain vanilla Excel.
For more information on some of the tools in Modern Excel, check out Microsoft Excel Power Pivot & Power Query For Dummies by Michael Alexander (Wiley).
