Financial Modeling Best Practices 2024

Financial modeling can quickly become detailed and complicated, so here are some best practices that every analyst should embrace. This ranges from meticulous preparation and systematic editing to clear labeling and dependency management. By following these guidelines, analysts will not only minimize the risk of errors but also create models that are transparent, maintainable, and capable of withstanding the test of time.

Best Practice 1: What To Do Before Editing a Financial Model

Before editing a financial model, there are several steps you should take to avoid errors:

1. Save a copy of the file: Give it a new file name so that if anything goes wrong, you have a backup to revert to. You can also start including version numbers in your file names (e.g., “Modeling Company A Version 1”, “Modeling Company A Version 2”, etc.) as you make edits and updates.

2. Turn off iterations: Go to File, Options in Excel (ALT F, T) navigate to the Formulas tab and untick the box that enables iterative calculations. This way, if you accidentally create any circular references, you’ll receive a warning and can stop to figure out what’s going wrong. You should also turn off any intentional circular references.

3. Plan your changes and predict the effects: For example, if you’re planning to spend money on capital expenditure, you would expect the Property, Plant, and Equipment (PP&E) to increase and cash to decrease on the balance sheet. These effects should then be reflected in the cash flow statement, with capex resulting in a larger cash outflow. In the year following the expenditure, you’d expect to see more depreciation on the income statement, which would reduce the PP&E on the balance sheet. This can also be seen with the depreciation in the cash flow statement.

4. Make one edit at a time: If you’re going to change something like capex, then ensure do all of those capex changes first. This including, the assumption, any calculations, and any changes to the financial statements or outputs. Only once you’re satisfied with those changes should you move on to the next edit, such as impairments or intangibles. This approach keeps the process safe and manageable.

For example, let’s assume a company has announced a new large 4-year growth strategy. If we are to model this, it will mean making changes to sales, capex and some other costs within the company. When updating the Capex assumptions (as shown below), it is important to complete all the capex assumptions in the forecasts before making other changes. Modelers must always check that these planned changes feed through to all the dependent cells as expected before moving onto the next edit.

The model would quickly become unmanageable if only part of the Capex assumptions were updated, and then the modeler moved on to work on another edit such as a Stock Repurchase Program. Firstly, the progress of the strategy update would be difficult for others using the model to follow, and it may also be misleading as both edits require the cash flow in the model to be working consistently and correctly. Leaving parts of the update incomplete can lead to errors being created in the model.

Remember, more line items and labels can make your model easier to audit as more detail can be included. However, be aware that if anyone inherits the model, they might not realize that there are multiple items within a single line item, such as two debt items within the debt line item. So, there’s a fine balance between using shortcuts for ease of building your model and ensuring ease of understanding for others.

Best Practice 2: How to Insert a New Item in a Financial Model

When inserting a new item into a model it’s always a good idea to predict the direction of those changes before inserting them into the model.

Here is an example where we want to insert a new debt tranche into a model:

1. Plan ahead: Predict the changes before you make them. If you want to insert a new debt tranche into a model, plan to do a new debt issuance or repayment line and a separate new interest rate line.
2. Update assumptions: Insert new lines in the assumption section according to your plan.
3. Update balance sheet: Consider having a new debt line in the balance sheets or it could be incorporated into the existing line items for debt.
4. Update debt schedule: Create a new debt section in the debt schedule. This will need new rows for beginning balance, issuance, repayments, ending balance, and the interest expense on all of that. If the debt schedule has a cash sweep, update that as well to calculate cash available for any other debt items that are more junior.
5. Update cash flow statement: Consider having a new debt line in the cash flow statement or incorporate it into an existing line item such as change in debt.
6. Update income statement: Consider having a new interest line in the income statements or incorporate it into the existing interest expense line.
7. Label clearly and consistently: Make sure to label these new items clearly and consistently. For instance, if adding new debt into the model assumptions, you might call it “Debt tranche 2 issuance”, “Debt tranche 2 repayment”, “Debt tranche 2 interest”. Then when it comes to the debt schedule, you want to have “Debt tranche 2 beginning balance”, “Debt tranche 2 issuance”, “Debt tranche 2 ending balance” so that anybody inheriting the model can clearly work out where these figures are coming from.

New items should get new rows where possible. Try not to add them onto another formula. If you’re looking to put new debt into a model, it would be very easy to think a nice shortcut is just to put it into the balance sheets debt line item. However, this will likely make the model more difficult for others to understand.

Best Practice 3: How to Remove Old Items from a Financial Model

To remove old items from a financial model, follow these steps:

1. Don’t delete rows randomly: Deleting rows randomly can cause dependent cells to return #REF errors. For example, if you want to delete row one from your model, but there’s a formula in cell A7 that refers to A1, deleting row one will cause the formula in A7 to return a #REF error.
2. Trace Dependent cells: If looking in cell A1, turn on trace dependents (shortcut: Alt + M + D). This will show you all the cells that depend on the cell highlighted (in this case A1). Edit the formulas in these dependent cells as needed before deleting the original cell.
3. Check for no links: Only delete a cell when Trace Dependents finds no links at all. If the Trace Dependents command finds no formulas that refer to the active cell, it is safe to delete that cell.
4. Trace Precedents: You may also want to trace precedents to find cells that may now be redundant. For example, if cell A1 was coming from A2, and you delete A1, A2 may now be redundant if it doesn’t link to anywhere else. It’s worth tracing precedents to find if there are any redundant cells that can be removed.

Tracing Dependents and Precedents (Alt M + D or Alt M + P) can be an efficient way to update a model and its calculations, particularly if a multi-tab model (as shown below). It can also be an extremely useful tool when understanding how inherited models work.

Remember, it’s important to plan changes and understand the dependencies in your model before making edits. This will help you avoid errors and maintain the integrity of your model. Often it is helpful to have a tick list of items that need amending so that you can work swiftly through the steps even if interrupted or called away from your desk.

Conclusion

Effective financial modeling requires a disciplined and methodical approach. These practices not only help prevent errors but also ensure that models are easy to understand and maintain for both the owner and others who may inherit or work with the model in the future. Adopting these best practices will enhance the reliability and longevity of financial modeling efforts, ultimately contributing to more informed decision-making.

Enroll on our financial modeling course to learn the best methods for checking, diagnosing and preventing errors when building financial models, and gain a practical understanding of building, editing, and checking financial models.

Additional Resources