We’ve come a long way since the days when Excel was nothing more than Bob from accounting’s thing. Now, Excel and financial modeling have become entirely new domains, even featuring events as exciting as watching the NBA or the Soccer World Cup. In fact, we are the proud founders and creators of some of the biggest events in this field, including the Financial Modeling World Cup and the Microsoft Excel World Championship.
This article probably won’t make you the next Michael Jordan of financial modeling, but it will help you grasp the best practices to follow and the common pitfalls to avoid when building one.
You can think of a financial model as a fancy calculator in a spreadsheet. It has a set of calculations that represents the financial performance of a business or project. A typical financial model forecasts revenue, costs, cash flows, but the best ones help you answer “what if” questions. It should be designed so that anyone – from an expert to a beginner – can understand the flow of numbers and the story behind them.
When a model is built with care, it can:
- Enhance Decision-Making: A clear model shows how changing one assumption affects outcomes. This helps managers test different scenarios and plan strategies.
- Increase Confidence: A model that is easy to understand and review inspires confidence among its users and stakeholders.
- Save Time: With a robust design, future updates or revisions become much simpler. This is especially important in today’s fast-paced business environment.
Reduce Errors: By following best practices, you lower the risk of mistakes that can lead to inaccurate results and costly decisions.
The Philosophy Behind Good Models
Keeping It Simple
Most financial modeling standards, like the FAST Standard and the ICAEW Financial modeling code, stress the importance of simplicity. A simple model is easier to understand, update, and review. This is like following a recipe: clear, step-by-step instructions make it less likely that you miss an ingredient.
- FAST Philosophy: The FAST Standard is built on the idea that models should be flexible, appropriate, structured, and transparent. In simple words, a model should adapt to changes, match the task at hand, have a clear layout, and show its workings so users can see how conclusions are reached.
- ICAEW View: The ICAEW Financial Modelling Code emphasizes that clarity and ease of use are crucial. It suggests using clear labels, consistent layout, and easy navigation. This helps both the person building the model and those who will review it later.
Design Principles
Good models are built with clear principles:
- Flexibility: The model should be able to adjust when new data or different assumptions are needed.
- Appropriateness: Use the right level of detail for the problem. Too much detail can confuse users, while too little can miss important factors.
- Structure: Organize the model into clear parts, such as inputs, calculations, and outputs. This separation makes it easier to update and troubleshoot.
- Transparency: Each calculation and assumption should be visible. Hiding the details might save space but can make it hard to spot errors.
Best Practices for Building Financial Models
1. Plan Your Model Before You Build It
Before opening Excel, think about what the model needs to do. Ask these questions:
- What is the main purpose of the model?
- Who will use it and what decisions will it support?
- What time period should the model cover?
Planning helps set a clear goal and avoids unnecessary work later. Sketching a rough layout on paper or a whiteboard can help organize your thoughts.
2. Separate Your Model into Logical Sections
A well-organized model has distinct parts. The most common structure is:
- Inputs: These are the numbers or assumptions you start with, like interest rates, sales growth, or cost estimates. Inputs are one of the most important parts of a financial model, if not the most important one, because as the old saying goes: Garbage in, garbage out. The structure and granularity of your inputs will determine the quality and precision of your inputs, and the ease of use and the ease of modification determines the versatility of your model, whether it can forecast numerous scenarios or not. This can be used by decision makers if they want to test other hypotheses than what’s presented or if they want to play advanced integrated scenarios rather than just the traditional best/worst case planning.
- Calculations: This section contains all the formulas that process the inputs to produce meaningful results. Depending on situations, it can be one sheet to multiple sheets. For instance this could include your method of forecasting customers and churn, and these forecasted numbers are used to calculate your revenue on that specific product, which would then be groupped with the other products and summed to get your company’s revenue, which would in its turn compose your P&L statement, and so on and so forth. Decision makers like CEOs and investors do not typically need to look at this part of the model, unless they want explanations of where you got your numbers from.
- Outputs: The results of the calculations are shown here in the form of summaries, charts, or tables. For instance, if you are building a valuation model, this would be the summary sheet that you’d present to investors or stakeholders. It could summarize how much they invest and how much they would get back in return, answer the “what if” questions like what if we demand a return of x% or what if we borrowed more money from this other bank, and so on. These are bite sized information, presented in a clear and well structured way, delivering just the right amount of information so that any decision makers can properly read and interpret them, no matter their financial acumen background. Within the same model, you can have multiple outputs for different users, because for instance, the information your CFO wants to look at might be different from what the shareholders or CEO wants.
Using separate worksheets or clearly marked sections for each group makes the model easier to read and reduces mistakes. The FAST Standard advises separating worksheets into different groups such as Foundation (inputs), Workings (calculations), and Presentation (outputs) to serve different users’ needs.
3. Use Consistent Layouts and Formatting
Keep a consistent design across the model. Each company might have their own design requirements, formatting and color palette, which in most cases follow their brand’s color palette and design identity, but for example:
- Use the same color for cells with the same purpose. For instance, inputs are often in a blue font or have a grey to orange-ish background so users know where to change numbers, cells that have linked value can be in green, scenario values or assumptions have a dashed outline border, and so on and so forth. The importance does not lay in the format you choose, but in the consistency of it and the documentation.
- Keep similar items grouped together and use consistent column headings.
- Freeze key rows like dates or periods or flags, and key columns like account items so that they remain visible when scrolling.
The ICAEW code recommends that a clear sign convention be used throughout the model. A sign convention explains whether a number is positive (money coming in) or negative (money going out). This consistency helps avoid confusion and errors.
4. Make Navigation Simple
Imagine reading a long book with no chapters or table of contents. A messy model can feel the same way. To improve navigation:
- Table of Contents: Create a summary sheet that links to all parts of the model.
- Clear Worksheet Names: Use names like “Inputs,” “Model,” and “Results” instead of generic names like “Sheet1” or “Data.”
- Hyperlinks and Navigation Buttons: Consider adding clickable links that take users to key sections. This is especially helpful in larger models.
According to the ICAEW guidance, easy navigation is not just about looking good—it helps users find the right data quickly, saving time and reducing errors.
5. Keep Formulas Simple and Clear
When writing formulas in Excel:
- Break Down Complex Formulas: If a formula is very long, split it into several steps. For example, instead of one long formula that calculates a net profit, break it into steps like calculating total revenue, total costs, and then subtracting costs from revenue.
- Use Meaningful Names: For cells or cell ranges that are constantly used across the model, instead of using cell references like A1 or B2, use names like “Interest Rate” or “WACC.” This makes the formula easier to understand.
- Limit Nested Functions: Nesting means placing one function inside another. Stop using your nested IFs and INDEXes, these might look like the easy route to go, but in reality, long nested functions just makes your job harder. Right now you might exactly know how it works, but a week, a month or a year from now, you probably won’t without spending a few hours looking at it. Adding to that, you are not the user of the model, so, every other user would need to get a migraine and have to break down your nested functions to actually understand what that cell is for. Keep formulas simple and short, and adopt a more structured approach by breaking calculations into multiple rows or columns, and label them correctly.
The FAST Standard reminds modellers that formulas should be as short and clear as possible. A common saying in the FAST community is “do not write a formula longer than your thumb,” which means that if it takes too long to explain the formula, it is probably too complicated.
6. Use Error Checks and Controls
Even with the best planning, errors can slip in. To catch mistakes early:
- Error Checking: Include a master error check that sums up key numbers and shows a warning if something is off. You could include different types of checks such as a check that verifies if in your balance sheet, Equity + Liability = Assets, or a check that verifies that Debt balance should never be negative (or positive, depending on your signs use) which would translate to reimbursing a debt that is already fully paid.
- Control Sheets: Create a dedicated sheet that lists key checks. For example, check if the sum of percentages adds up to 100%.
- Consistent Testing: Regularly test the model with different scenarios to make sure it behaves as expected.
ICAEW advises that models should be robust and easy to test. This is not just about avoiding mistakes—it is also about giving confidence to users that the model is working correctly.
7. Document Your Work
Documentation is like writing notes in a recipe. It tells the reader what each part of the model does. Good documentation includes:
- Comments in Cells: Use simple text to explain why a number is there or what a formula does, or why a specific cell was hardcoded.
- A User Guide: Create a separate section or worksheet that explains how to use the model. This guide should list key assumptions, the purpose of the model, and instructions for updating it, the color code saying which cells are inputs and can be modified and which ones are calculations or outputs and should not be messed with.
- Version Control: Note when the model was last updated and by whom. This helps keep track of changes and ensures everyone is using the latest version.
Both the FAST Standard and the ICAEW Financial Modelling Code stress the importance of clear documentation. When users can easily understand your model, it becomes much more effective.
8. Use Appropriate Tools and Functions
Excel offers many functions, but using too many can make your model harder to understand. Stick with a limited set of basic functions when possible. For example:
- SUM, AVERAGE, and IF: These are simple and widely understood.
- Cell Anchoring: This technique locks a cell reference in place when you copy a formula. (For example, writing $A$1 instead of A1.)
- Conditional Formatting: Use this to highlight important numbers or errors.
Look up functions: Since the excel update to dynamic arrays, more and more people use the XLOOKUP function because of its simplicity and versatility compared to the classical H and VLOOKUP or the traditional INDEX MATCH. However, make sure all the model users have a version of excel that supports dynamic arrays, because not all versions of excel do.
Common Pitfalls to Avoid
Even experienced modellers can make mistakes. Apart from the classical “Never merge cells” or “Never Hardcode a value that should be calculated”, here are some pitfalls to watch out for:
1. Overcomplicating the Model
A common mistake is trying to include every possible scenario. Instead, focus on what is truly needed. Overly complex models can be confusing and are more likely to contain errors. Simplicity is key.
2. Mixing Inputs with Calculations
Another frequent error is placing input data in the same area as calculations. This can lead to accidental overwrites or errors when updating the model. Always keep your inputs separate from your calculations.
3. Inconsistent Layout and Formatting
Inconsistencies, like changing colors or fonts, can make your model look unprofessional and confuse users. Always use a consistent format throughout your workbook.
4. Hidden Cells or Sheets
While it may seem tidy to hide some rows or columns, hidden cells can hide errors. Reviewers might miss hidden errors, so use hiding sparingly and only when absolutely necessary.
5. Lack of Error Checks
Without built-in error checks, mistakes can go unnoticed. An error in a single cell can affect many parts of the model. Always include checks to catch these issues early.
6. Not Updating Documentation
Documentation should be updated every time the model changes. Outdated notes or guides can mislead users and create confusion.
7. Overuse of Macros and VBA
While macros (small programs within Excel) can automate tasks, they can also add complexity. Use them only when necessary, and always document what they do.
If you or your team need a master class in financial modeling and the best practice to adopt, reach out to us, and our experts will gladly assist you to best fulfill your needs.
Tips for Testing and Maintaining Your Model
Once your model is built, the work is not done. Regular testing and maintenance keep it robust and reliable.
Regular Reviews
Set aside time to review your model:
- Internal Testing: Run through different scenarios to see if the model behaves as expected.
- Peer Reviews: Ask a colleague to review your model. Fresh eyes can catch mistakes you might have missed.
- Audit Trails: Keep track of changes by using version control. This makes it easier to pinpoint when an error was introduced.
Scenario Analysis
One strength of a good financial model is its ability to handle “what if” scenarios. For example, you might change:
- Sales Growth Rates: See how lower or higher growth affects the outcome.
- Cost Assumptions: Test what happens if costs rise unexpectedly.
- Interest Rates: Check the model’s sensitivity to changes in borrowing costs.
Scenario analysis helps you understand the risk in your model and prepares you for different business conditions.
Continuous Improvement
As your business or project evolves, your model should too:
- Update Assumptions: Replace old data with the latest numbers.
- Review Structure: Look for parts of the model that could be simplified or improved.
- Learn from Mistakes: Each error found is an opportunity to make your model more robust.
Remember, a model is a living document. It must adapt as circumstances change.
References to Standards
Throughout this article, we have referred to key ideas from two leading sources:
- FAST Standard (Flexible, Appropriate, Structured, Transparent):
Published in July 2019, the FAST Standard lays out clear design rules for financial models. Its focus on simplicity, clear structure, and transparency helps ensure that models are easy to review and modify. For example, the FAST Standard emphasizes keeping formulas short and ensuring that calculations follow a logical, step-by-step order. This means avoiding overly long formulas and breaking complex calculations into smaller, manageable parts citeturn0file0. - ICAEW Financial Modelling Code:
The ICAEW Financial Modelling Code provides practical guidance on building financial models. It focuses on clarity, consistency, and error reduction. The code advises that models be easy to navigate and include clear documentation. By using a consistent layout and proper error checks, the ICAEW code aims to reduce the risk of mistakes and make models more user-friendly citeturn0file1.
Both sets of guidelines agree that a good financial model should be robust, transparent, and easy to understand. They remind us that building a model is not just about plugging in numbers—it is about creating a tool that can help make better decisions.
CONCLUSION
Building a robust financial model is a blend of art and science. It requires clear planning, logical design, and a focus on simplicity and clarity. By following best practices—such as separating inputs, calculations, and outputs, using consistent formatting, and including error checks—you can create a model that not only forecasts financial performance but also builds confidence among its users.
Avoiding common pitfalls like overcomplicating formulas or mixing data types can save time and reduce errors. Regular testing and updating further ensure that your model remains accurate and reliable, even as conditions change.
Remember, financial models are tools meant to help people make decisions. The better the model, the more effective it is in guiding those decisions. By adopting principles from trusted sources like the FAST Standard and the ICAEW Financial Modelling Code, you build a foundation that is both flexible and transparent—qualities that every robust financial model must have.
Whether you are creating an investment analysis, budgeting tool, or valuation model, the key is to keep it simple and clear. Let your model tell a story that anyone can follow, from the first input to the final result. This approach not only helps in day-to-day decision-making but also prepares your business to adapt to future challenges.
By focusing on clarity, consistency, and simplicity, you lay the groundwork for financial models that stand the test of time. Whether you are a seasoned financial professional or a beginner just starting out, these guidelines will help you build models that are as robust as they are understandable. Happy modeling!
AG Capital provides fractional CFO services and Financial Planning and Analysis (FP&A) services to small and mid-size companies in the US, UK, EU and globally, including budgeting, profitability analysis, cost analysis, investment projections, and a cash flow planning. The company thrives in offering high-level financial expertise and leadership to businesses on a part-time or project basis.