Today, I'm going to go talk about financial modeling best practices.
If someone asked me what are a few things you should do to become better at financial modeling, this is exactly what I would tell them.
If you're new to finance and FP&A, learning about financial modelling can be overwhelming. Let me tell you: Dealing with complicated and broken financial models can be very frustrating.
It results in chaos, adds so much stress, and fixing errors in the complex model is a real challenge. I don't want you spend hours and hours working on a broken financial model or finding and fixing errors which could've been avoided in the first place.
Whether you're just starting in finance and FP&A or if you're working on your next financial model soon, these 12 tips will be very handy.
So, let's dive in!
Tip 1: Avoid duplicate inputs
Hard coding an input more than once can lead to inefficiencies, increased chances of errors, and difficulties in maintaining and updating the model.
When you re-enter the same input in different places, it not only consumes more time but also makes the model less flexible.
You should aim for a dynamic approach where inputs are defined once and referenced as needed throughout the model.
Tip 2: Do not calculate more than once
Recalculating the same values multiple times not only consumes computational resources but also introduces the possibility of inconsistencies.
By referring back to the initial calculations, you make your model more robust and consistent. If you make an error. This makes troubleshooting issues and finding errors a lot easier.
Sabrinthia Donnelly
Tip 3: Avoid numbers in formulas
When you include numbers within formulas, especially hard-coded numbers, it makes the model rigid and less adaptable to modifications.
There can be an exception to this rule when a number is truly constant, meaning it will never change throughout the model's lifecycle.
Tip 4: Avoid long formulas.
Long formulas can be hard to understand, not only for you as a model creator but also for anyone who may need to refer, review or modify the model later on.
Try using Excel formulas that are clear, concise and logically structured. Break down complex calculations into smaller, manageable steps.
Tip 5: Grouping vs hiding rows and columns
It's advisable to group rows and columns instead of hiding them.
Hidden rows and columns usually result in a lack of visibility, potentially causing confusion or oversight.
Grouping rows and columns increases transparency and helps to clearly organize data in the model.
Tip 6: Center align vs merge cells
Whenever you feel like merging a cell in the model, consider using the Center Across Selection formatting option instead of merging cells.
Merge cells usually lead to complications and reduced flexibility. While if you use Center Across Selection, this will maintain individual cell functionality while visually centering content.
Patrick Dennehy
Tip 7: Footnotes & comments
Adding footnotes and comments is always a good practice to improve clarity and provide additional context inside the model.
Footnotes: Can be used to explain assumptions, methodologies, or any important points for users. They appear at the bottom of the model and show up when the model is printed.
Comments: They are built Within cells allowing for real-time collaboration and ensuring that more details are accessible to anyone reviewing the financial model for internal purpose.
Other tips ...
Tip 8: Structure your model as clearly and simply as possible
Tip 9: The Model should be usable in your absence. You can include an instruction tab which can help other users to navigate through the model.
Tip 10: Assumptions should be in one place. Including an “Assumptions” tab in the model where all the assumptions are documented is a very good practice.
Tip 11: Save your work often. Press “Ctrl + S” every few minutes if you are working on your local machine.
Tip 12: Take a step back and look at the bigger picture during the design stage to avoid rework later.
Drive strategic business and career growth with FP&A.
Take your career to the next level with FP&A Certified: Core. Learn to forecast, budget and turn complex data into insights that drive profitable business growth.
- Gain an all-in-one, in-depth understanding of financial planning and analysis.
- Get practical tools for real-world applications that you can put into action immediately.
- Build effective FP&A processes, providing a solid foundation for effective financial planning, insightful analysis, and data-driven decision-making.
