Financial Modeling Excel – Best Practices

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 analyse 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 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. For more information on how to build a financial model, visit the article here.

 

1.Versioning: To track the development and progress of the model its recommended to save the Excel model under a new version number in a systematic manner. Also sometimes, Excel models do crash, therefore 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, from top to bottom to be intuitively understandable by the users. Try to organize your modeling sections in a modular way. Also, apply clear headers 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:

  • Assumptions
  • 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 all the time back and forth, left, right center, it will create more confusion, and you actually might lose him. 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. Try to minimize the number of rows so the model will become easier understandable by users. This also means in case of a three statement model, add the statements in one worksheet and do not make 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: 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 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 just 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 does assumptions come from. Therefore, the financial model becomes much easier readable and understandable.

 

9. State the currency and units – 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 terms and abbreviation 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: Buld 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

We hope this gives you some additional ideas 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.

 

Financial Modeling in Excel – best resources available on eFinancialModels.

Leave a Reply