Excel modeling is the process of building a dynamic spreadsheet which predicts the future financial performance. The benefit of creating a financial model is that you analyze in a systematic manner how the key value drivers impact the future financial performance of that business. In this regard, a financial model justifies in a rational manner on which basis the future financial performance is predicted. A sophisticated finance model will, therefore, provide you with a good understanding of what impacts the value of a business.
The way Excel models are built vary from user to user. We would like to provide you with a short overview of the Best Practices in Financial Modeling in Excel, which we have seen over the years and found to be very valuable.
1.Versioning: To track the development and progress of the model, it is recommended to save the Excel model under a new version number in a systematic manner. Also, sometimes, Excel models do crash, therefore it is critical to at least every half day, a model should be saved under a new version to avoid a complete loss of the work, in case the model breaks.
2. Modeling Logic: Finance models should be built logically. This means calculations should be done from left to right, top to bottom, to be intuitively understandable by the users. Try to organize your modeling sections in a modular way and also, apply clear headers or titles and organize the sections in a clear understandable and logical structure. E.g., assumptions are the prerequisite to build the operating model; therefore they should come first.
Normally, a model structure looks like follows:
- Operating model
- Operating ratios
- Fixed Asset Schedule
- Debt Schedule
- Financial statements (Income Statement, Balance Sheet, and Cash Flow Statement)
- Financial ratios
- DCF or IRR analysis
If the user has to go through the excel model repeatedly to backtrack and refer different parts of the pages, it will create more confusion, and lose track of direction that your user needs to go. Therefore, logic and structure are key to allow for a smooth understanding!
3. Simplicity: Excel modelling should be as simple as possible (less is more) with an adequate number of worksheets and calculations. Applying a precise and consistent logic is key as well to this. Try to focus on the essential aspects and the key value drivers.
Sometimes, too much detail is counterproductive. Minimize the number of rows so the model will become easier to understand by users. This also means in case of a three statement model, add the statements in one worksheet only, without making the model unnecessarily complicated by using a separate worksheet for each statement.
4. Allow for easy model navigation: Setup a clear navigation structure by using adequate labels for all worksheets and sections of your model. The objective is that the user can orientate himself and find the relevant sections quickly without having to redo all the Financial Modeling Excel work.
5. Model consistency: If, e.g., you have several sheets which project the cash flows from year 1 to 10, make sure year 1 always starts in the same column number on all sheets. This way it will be easier to check the model and copy/paste formulas from one sheet to another if you need to.
6. Calculations should be done only once: Since the financial model is in Excel, if you need a calculation, try to calculate it only once and link all dependent cells to this calculation. In case your calculation is wrong, or you wish to change it afterward, you will have to change it only once, and the change will be applied concisely throughout your model.
7. Remove hardcoded figures in calculation cells: This is a common mistake we see in many beginners’ model. If you have a calculation cell, e.g., with the formula =0.04 * D2, move the 0.04 in a separate assumption cell which you can use to perform the desired calculation. This will add more transparency to the model and avoid surprises afterward.
8. Clarify assumptions – Financial models are easier to understand when you separate inputs from output cells in a visible manner. All inputs should be put in separate cells and marked with a different font color such as e.g., blue, while calculations should be in black (or any other color code you like). Another point here is to insert the source of your assumptions to make it very clear from where do assumptions come from. Therefore, the financial model becomes much readable and understandable.
9. State the currency and units – Consistency is important. For every figure in your model, it needs to be clear if that is a dollar (currency) amount, a volume unit, a price or something else. Therefore, you need to make it always very clear and state the unit in a clear, comprehensible manner. For volume units, a good way to make it clear what unit is referred to is to put the unit abbreviation at the beginning of the row in Excel.
10. Avoid macros and circular references: On this point, not everybody might agree with us, but here is our point: Macros do not work on all computers, and most users will not understand how the macro works, therefore it’s a black box. Thus, if you intend to use a macro, try to be very clear why the macro is needed and if there is no solution to model the same without a macro.
Concerning circular references, some financial modelers, e.g., use an iteration calculation to calcite interest in excess cash to model the cash flows correctly. However, while this might be more precise, the model will show a circular reference, and therefore it becomes challenging to assess if the model is correct and to run sensitivity analysis. Try to keep it simple.
11. Terms and Abbreviations: Financial models normally use a series of abbreviations, e.g., NPV (Net Present Value), DCF (Discounted Cash Flow), which might not be easily understandable by the reader. To make your Excel modeling better understandable, add a terms and abbreviations section somewhere for easy reference by the user.
12. Build automatic error checks inside your model: e.g. a balance sheet must show that total assets match total equity and liabilities. Therefore, you can build in there an automatic check that there is a zero difference in your balance sheet. In case that examination shows anything else than zero, it will tell you to recheck your calculations and will lead you to the source of error (by using the detective/audit function in Excel).
13. Risks and Sensitivities: The model should allow to figure out the risks inherent in the business by allowing to change key assumptions and see how the model reacts to any changes of such.
14. Executive Summary: Build a dashboard with charts in the form of an Executive Summary so users can quickly understand the essence of your model. Recommended is to summarize the key outputs, key financials, and assumptions on an executive summary page (max 1 – 2 pages). This should help to grasp the essentials of the model one page.
Conclusion: Building Finance Models
With this list, we hope that we’re able to help you save time and give you some additional ideas about how to work with the financial model templates and which best practices to consider when building them.
Building a quality finance model is worth it, as it will help you to better understand the value of a company or an investment. The better a spreadsheet is built, the better arguments you will identify to substantiate your evaluation of the business case.
This article might also give you a better idea what to watch out for when selecting your next financial model template. So, now that you know the best practices in financial modeling, you can start building your very own financial model. If you want more information on How to build a Financial Model, feel free to visit the article by clicking the link.
Financial Modeling in Excel – best resources available on eFinancialModels.