Thanks to Nick Williams of Acuity Training (a provider of Excel courses in London and Manchester, UK) for providing this helpful series of posts on using Excel for financial modelling.
Introduction to Financial Modeling in Excel
Who is the article aimed at?
This series of five articles is designed for anyone who wants to learn how to build and / or manipulate financial models built in Excel.
It is designed for people with no background in finance.
What is a financial model?
A financial model allows you to input a set of assumptions about a company’s future and will project the company’s future financial performance from them.
As a minimum this is usually in the form of an income statement, balance sheet and cash flow statement. In addition they also often include calculation sheets and also sheets of further analysis and graphs.
What is a financial model used for?
Using Excel to create financial models is everyday work for finance professionals as they are the basis for most financial decisions.
Potential uses of financial models include:
- Business Valuation:
When buying, selling or floating a business on a stock market a financial model will be used to help determine what a fair value for the company would be.
- Debt Affordability:
When a business borrows money from a bank a financial model will be used to look at how affordable the proposed debt will be in future under a number of different scenarios.
- Strategic Decision Making:
A financial model will be a key part of all strategic decision making. They give decision makers a clear projection of the likely financial implications of the strategic decisions that they are looking at. This might include geographic expansion of the business, entering new product lines, divestment of an existing asset or business unit.
- Cashflow & Capital Budgeting:
Most businesses have a cashflow forecast that they update either monthly or quarterly. This allows them to project their future cash balances and manage their cash accordingly.
This will also feed into the decision making process for future capital expenditure.
- Scenario planning:
Strategic planners will frequently use a financial model to run scenarios which look at how future changes in the business environment might impact the future profitability of the company.
Handle With Care
“The first principle is that you must not fool yourself and you are the easiest person to fool” – Richard Feynman.
Financial models need to be handled with care. The financial crisis of 09 proved this. Just because a financial model forecasts the future one way, that doesn’t mean that that is how the future will turn out.
They are based on data which is at best an educated guess as to how the future might turn out. Your output is only as good as the quality of your inputs, the old computer science maxim of “Garbage in Garbage out” applies.
Equally a financial model can be a very useful business tool when handled correctly.
If you remember that a financial model is nothing more than simplified projection of one potential future you’re unlikely to go too far wrong.
How This Article Will Work
This is a multi-part series of articles.
In it we’re going to initially build a very basic financial model, for a small manufacturing company. The aim of this is to get you comfortable with how to set up and organize a basic 3-statement financial model.
It will lead you through this process step-by-step explaining each step along the way. Article 2 explains how you need to set up your spreadsheet and Article 3 leads you through the individual steps to then populate your model.
Once we have created a very simple model we will then look at running scenarios on our model (article 4) and then how to increase the depth of the model to incorporate more complexity (article 5). Article 5 looks at things like incorporating stock / inventory, dividends and fixed assets additions into your financial model.
Best Practices in Financial Modeling
There are a number of cardinal rules to financial modelling.
Large financial models can be difficult and confusing to navigate and edit which frequently leads to mistakes. If you obey the rules they will help you to avoid the vast majority of the problems
- Grid Integrity
Maintaining grid integrity will make your model far quicker and simpler to navigate.
Grid integrity means that in all the sheets of your workbook the same column will refer to the same year. So for example if column C refers to year 2017 on one sheet then column C in all sheets should refer to year 2017.
- Clear Labeling & Standard Presentation
Your presentation should be consistent throughout your model and make it clear to someone who is new to the model what each cell refers to and contains.
Make it clear what a cell refers to all of your sheets, and within your sheets all of your rows and columns, should be clearly labeled with applicable headings.
For data input, it’s important to make it clear what a cell contains. It is usual for all cells containing formulas to be in a standard black font while all hard coded inputs are in a blue font with a yellow background.
In addition, all cells should be formatted appropriately (i.e. using currency, % symbols etc) and unnecessary blank rows and columns should be removed for clarity.
- Clearly Document Assumptions & No Hard Coding
All assumptions should be gathered onto one sheet the ‘Inputs’ or ‘Assumptions’ sheet. These assumptions can then be linked to from other parts of the model.
This makes it very clear what the underlying assumptions used in your model are. It also allows people to amend the assumptions quickly and simply if they would like to.
Assumptions should be listed in a logical order and labeled clearly with units where appropriate.
Hard coding, especially if subsequent changes are made by someone other than the model creator, can lead to very misleading results. If you must hard code a value within a formula, use an in-cell comment (Shift + F2) on the cell describing what the amount relates to.
- Consistent Formulas
Each row in your model should contain only one consistent formula.
The formula should be the same throughout the row and not change between cells other than for relative cell references. If the formula changes in the middle of a row this will not be apparent to anyone else and this is a frequent source of errors in models.
- Keep Formulas Simple
Avoid complex formulas. These are difficult to get right and audit and complicated to edit.
Break down complex calculations into several separate steps instead. This makes it easier to check your model is working as expected. While you may currently remember what the calculations are doing, it will help you in the future to understand how you broke it down (as well as help other users of the model).
- Never Round Numbers
Use Excel to round the numbers for presentation.
Do not round numbers. There is no need and this will introduce errors into the calculations which can be magnified in large models where assumptions have multiple calculations carried out on them.
- Use Checksums
Models get complex very quickly. Putting checks in place helps to catch errors in the model.
For example, use sum check formulas to ensure that your balance sheet balances.
Other check sums that are often used are:
- Is the sum of the deprecation on an asset and its current NAV the same as its cost price, a calculation that must be correct no matter the age of the asset, and
- Is the difference between two balance sheets equal to the net profit / (loss) for the relevant period?
For some tips on Error Checking and Data Monitoring, check out the video lesson!