Financial Modeling in Excel For Dummies
Overview
Turn your financial data into insightful decisions with this straightforward guide to financial modeling with Excel
Interested in learning how to build practical financial models and forecasts but concerned that you don’t have the math skills or technical know-how? We’ve got you covered! Financial decision-making has never been easier than with Financial Modeling in Excel For Dummies. Whether you work at a mom-and-pop retail store or a multinational corporation, you can learn how to build budgets, project your profits into the future, model capital depreciation, value your assets, and more.
You’ll learn by doing as this book walks you through practical, hands-on exercises to help you build powerful models
using just a regular version of Excel, which you’ve probably already got on your PC. You’ll also:
- Master the tools and strategies that help you draw insights from numbers and data you’ve already got
- Build a successful financial model from scratch, or work with and modify an existing one to your liking
- Create new and unexpected business strategies with the ideas and conclusions you generate with scenario analysis
Don’t go buying specialized software or hiring that expensive consultant when you don’t need either one. If you’ve got this book and a working version of Microsoft Excel, you’ve got all the tools you need to build sophisticated and useful financial models in no time!
Read More
About The Author
Danielle Stein Fairhurstis a Sydney-based financial modeling consultant who helps her clients create meaningful financial models for business analysis. She is regularly engaged around Australia and globally as a speaker and course facilitator. She received the Microsoft MVP Award in 2021 in recognition of her technical expertise and contributions to the community.
Sample Chapters
financial modeling in excel for dummies
CHEAT SHEET
When you're building financial models in Microsoft Excel, functions are the name of the game. You also need to check your work — and check it again — to make sure no errors slip through the cracks. Finally, to make your work quick and easy, keyboard shortcuts are a lifesaver.Essential Excel functions for building financial modelsThere are now over 400 functions are available in Excel, and Microsoft keeps adding more with each new version of the software.
View Cheat Sheet
View Cheat Sheet
HAVE THIS BOOK?
View Book Companion Site
Access practice tests
Additional Book Resources
Articles from
the book
Excel
Excel is the choice of many financial modelers. Excel is often called the “Swiss Army knife of software” or the “second-best solution” because you can do practically anything in Excel, but it’s not always the best tool for the job. You can write a letter in Excel for example, but Word is a much better tool for that purpose.
Now that you’ve determined your base case assumptions that reflect how you believe the business will perform, you also want to run worst-case and best-case scenarios in your financial model. Not only do you want to see how you believe the business will do, but you also want to see how the business will perform if it does worse than expectations or better than expectations.
Excel
After you’ve finished all the calculations in your financial model, do lots and lots of sensitivity and scenario analyses. Stress-testing with sensitivity analysis will check that the inner workings of the formulas and logic of the model are correct, but how realistic are the assumptions? If the absolute worst happens, what happens to your bottom line?
Excel
The problem that needs to be solved and the output required often influence the layout and design of the financial model. So, it’s not a bad idea to spend some time thinking about the layout before you get started. Not only do the layout and structure of the model relate to the logic and functionality, but they can also influence the look and feel of the model’s interface and how users navigate through the model.
Excel
The major limitation of drop-down scenarios in financial modeling is that you can’t see multiple scenarios side-by-side. To show the cost per customer under different scenarios, you need to change the scenario drop-down box in cell F1 — but you’ll only be able to look at one scenario at a time.To add a data table that will allow you to see the cost per customer of all three scenarios side by side, follow these steps:
Add the three scenario names — “Best Case,” “Base Case,” and “Worst Case” — below the Cost per Customer.
Excel
The output of your financial model may be very detailed and contain a myriad of numbers, colors, and confusing calculations. A common mistake is to try to put as much information as possible into one chart in an attempt to make it look impressive. In reality, the chart just looks cluttered and fails to get the message across.
Excel
When you’ve identified the problem that needs to be solved, it’s very tempting to dive straight in and begin the financial model-building process, but it’s a good idea to stop for a moment to plan the model and determine how the output will look. When it comes to building a financial model, you want to start with the end in mind.
Excel
Who will be viewing or using your financial model in the future? If it’s for only your own use, you should still follow good model design but there’s no need to spend a lot of time on the formatting to make it look cool. You should still add assumptions and source documentation for your own reference even if you know that no one will ever look at it.
Excel
Once you’ve found the business’s FCF, terminal value, and discount rate, it’s time to value the business in your financial model. Follow these steps:
Select cell C21 and enter the formula =SUM(C19:C20); copy this formula across the row.This formula sums cells C19:C20 to arrive at the total cash flows to discount.
When you’re creating charts in financial models or reports, you should still follow best practice and try to make your models as flexible and dynamic as you can. You should always link as much as possible in your models, and this goes for charts as well. It makes sense that when you change one of the inputs to your model, this should be reflected in the chart data, as well as the titles and labels.
Now that you’ve figured out how to fund your business, you need to make future assumptions in your financial model and project how the business will perform. You need to build a separate Assumptions worksheet with your business projections that will drive the rest of the financial model.The Assumptions worksheet already has places for your key business drivers — it’s up to you to input accurate and reasonable assumptions for the business.
Excel
Today well over 400 functions are available in Excel, and Microsoft keeps adding more with each new version of the software. Many of these functions aren't relevant for use in finance, and most Excel users only use a very small percentage of the available functions. If you're using Excel for the purpose of financial modeling, you need a firm grasp on the most commonly used functions, at the very least.
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.
If you're spending a lot of time modeling in Excel, you can save yourself time by learning some keyboard shortcuts. Much of the modeler's skills are about speed and accuracy, and by practicing these shortcuts until they become muscle memory, you'll be a more rapid and accurate modeler.Here's a list of the most useful shortcuts that should be part of your everyday keyboard use if you're a financial modeler:
Editing
Ctrl+S
Save workbook.
Excel
When you're building financial models in Microsoft Excel, functions are the name of the game. You also need to check your work — and check it again — to make sure no errors slip through the cracks. Finally, to make your work quick and easy, keyboard shortcuts are a lifesaver.Essential Excel functions for building financial modelsThere are now over 400 functions are available in Excel, and Microsoft keeps adding more with each new version of the software.
Excel
The term “garbage in, garbage out” is never truer than in relation to financial modeling. You can have the most beautifully laid-out financial model with perfect formatting, a great design, and fabulous-looking charts and scenario tables, but if the inputs that go into the financial model haven’t been verified, the model is useless and you can’t use the outputs.
Excel
When you start getting a fair amount of data in your financial model, it’s pretty easy to end up with an enormous Excel file that takes a long time to calculate, making it difficult to review or share with others.If the large file size is due to a large number of rows (say, more than 100,000), consider using Power Pivot to store the data instead.
Excel
If you’re getting a financial model audited by a third party, one of the many things they’ll check for is the number of unique formulas in the model. A good modeler will create as few unique formulas as possible as he’s building a model.In the following example, the modeler is calculating portfolio returns of different amounts.
Excel
Applying appropriate formatting and labeling to financial models is sometimes neglected by even experienced financial modelers. You need to include as many descriptions and instructions as possible within the model to make it absolutely obvious how to use the model and how the calculations work. Don’t assume that someone using the model will be able to understand what he’s supposed to do with the model, its purpose, and what should be changed when.
Excel
The most commonly used method of building scenarios is to use a combination of formulas and drop-down boxes. In the financial model, you create a table of possible scenarios and their inputs and link the scenario names to an input cell drop-down box. The inputs of the model are linked to the scenario table. If the model has been built properly with all the inputs flowing through to the outputs, then the results of the model will change as the user selects different options from the drop-down box.
Financial modeling is a complicated process. Knowing the general steps before you jump in can be helpful as you build out your model. Here are seven steps to follow when starting to build a financial model:
Design the high-level structure.You won’t know exactly what the layout of the model will be until you actually start building the calculations, but you should have some idea of the tabs.
Excel
Even if you’ve only just started modeling, you’re probably well aware how easy it is to make a mistake in a financial model! There are three ways to prevent errors in a financial model:
Avoid making the mistakes in the first place. In this book, I describe several techniques that you can employ to avoid making mistakes in the first place, such as being consistent with your formulas.
Excel
It is always important to document your assumptions when preparing a financial model. Once you’re happy with your assumptions, you can use them to calculate the revenue of the business for the next year.
Projecting sales volume
You know that your base case assumption is that the cafe will sell 120 cups of coffee per day, so you need to multiply this assumption by the monthly seasonality to arrive at the number of cups sold per day in each month.
Excel
If you need to report sales in your financial model, use SUMIF. SUMIF is similar to COUNTIF, but it sums rather than counts the values of cells in a range that meet given criteria. Following on from the last example, let’s say you want to know how much (in terms of dollar value) in sales were made in each region.
Excel
After you finish building a financial model, you may be tempted to keep it to yourself, because you don’t want anyone to mess up your formulas or use the model inappropriately. Models should be collaborative, but you need to build your model in such a way that it’s easy for others to use and difficult to mess up.
Excel
The COUNT function, as the name suggests, counts. Although this sounds pretty straightforward, it’s actually not as simple as it seems and, for this reason, the COUNT function is not as commonly used as the very closely related COUNTA function.
The COUNT function only counts the number of cells that contain numerical values in a range.
Excel
One tool that’s very useful for financial modeling is goal seek. If you know the answer you want, but you want to know what input you need to achieve it, you can work backward using a goal seek.In order to run a goal seek, you must have
A formula
A hard coded input cell that drives this formula
It doesn’t matter how complicated the model is.
Excel
Scenario Manager is grouped together with Goal Seek and Data Tables in the What-If Analysis section of the Data tab. Being grouped with other tools that are so useful would lead you to believe that Scenario Manager is also a critical tool to know. However, despite its useful-sounding name and the good company it keeps, Scenario Manager is quite limited in its functionality and is as helpful as the name suggests!
Excel
A financial model is usually built in order to answer a question or to solve a problem. For example, the question “Should I purchase this new asset?” could result in a model containing cash flow analysis, which compares the cash flow if the asset is purchased versus if it is not purchased. “How much should I pay for this new asset?
Many financial modelers like to include named ranges in their models. Named ranges are just a way of naming a cell, or a range of cells, to use it in a formula, instead of using cell references.
Understanding why you may want to use a named range in your financial model
You don’t have to include named ranges in a financial model, and some of the best financial models don’t use them at all.
Excel
Financial models are often inherited in the form of templates. Dealing with a model built from a template is quite a different prospect than using a model that has been purpose-built for the job.
Why templates can be appealing in financial modeling
If you describe yourself as a “casual” financial modeler, your usual job might be something else entirely, but part of your professional and personal life means that you need to create a budget or financial statements, or maybe just do some pricing calculations.
Excel
Financial modelers, like anyone working extensively with Excel, are very aware of the inherent risks involved. According to a study by Ray Panko, who is a leading authority on spreadsheet practices, close to 90 percent of spreadsheets contain errors.Some managers treat models as though they are able to produce the answer to all their business decisions and solve all their business problems.
Excel
You know your financial model best. No one is more qualified than you are to talk about your model, so you may be asked to communicate the results of the financial model as a formal presentation to the board or senior management. You need to decide how to communicate your findings in a clear and concise way. Understandably, many detail-orientated modelers find that distilling their 20MB financial models that have taken weeks to build into a ten-minute presentation is difficult!
Excel
Knowing how the discounted cash flow (DCF) valuation works is good to know in financial modeling. The core concept of the DCF is that of the basic finance concept of the time value of money, which states that money is worth more in the present than the same amount in the future. In other words, a dollar today is worth more than a dollar tomorrow.
Before you dive into creating financial models, you need to know what financial modeling is, who uses financial models, and why financial modeling matters.
What is financial modeling?
When asked to define financial model, many people come up with long-winded descriptions using terms like forecast and cash flow and hypothetical outcomes.
Financial modelers know that 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).
Excel
The possibility of error in a financial model is the number-one thing that keeps a financial modeler awake at night. As a modeler, you must have a healthy respect for spreadsheets and their susceptibility to error.Imagine you’re working on an exciting new project. You’ve provided a financial model that’s being used for a project or key function of your business.
Excel
You’ve finished with your financial model and convinced yourself to hand it over to the rest of your team to review or use on a regular basis. Then, one day, you hear the words that strike fear into the heart of every model designer: “Sorry to bother you, but the spreadsheet isn’t working.”You try hard not to panic, but you can’t help it: You break out in a cold sweat.
Excel
If you've been using Excel for a while, you probably prefer to build your own spreadsheets or financial models from scratch. In a corporate environment, however, people rarely get this opportunity. Instead, they're expected to take over an existing model that someone else has built.Maybe you're stepping into a role that you're taking over from someone else and there's an existing financial reporting model that you'll need to update every month.
Load More Articles
Related Books
0:00
0:00
https://cdn.prod.website-files.com/6630d85d73068bc09c7c436c/69195ee32d5c606051d9f433_4.%20All%20For%20You.mp3
Frequently Asked Questions
No items found.
