In modeling, as in life, time is of the essence. Financial analysts who create models must carefully and exhaustively research their company’s financial statements to determine how to capture that data in a model. Each variable must be tested, assumptions made and implications checked.
One wrong assumption or overlooked implication can lead to disastrous results when the model is deployed – and not just an error message like “File not found” or “Something went terribly wrong.” Financial modeling is a complex process that involves many different tasks: gathering data, creating assumptions, testing outputs, reporting results, etc.
It generally takes between 3-5 hours to complete most models for one company. However, if you don’t have time for all those tasks, we have some tips that should save you time!
Define the scope before you start modeling
One of the biggest challenges in modeling is knowing when to stop. You have to have a clear idea of what information you need, what assumptions to make and what you should and shouldn’t include in your model. If you don’t define the scope of your model at the beginning, you’ll likely end up with a model that’s either too simple or too complex and that fails to meet the specific needs of your stakeholders.
If you’re modeling for an event that’s months away, you don’t have to pull real-time data. Instead, use projected financials, adjusted for the company’s seasonal patterns, to avoid having to update your model with new data as it becomes available.
Only pull data once
No matter how many times you have to find it, you only want to pull data once. Look for ways to streamline this step by pulling data from one central source (rather than from multiple sources) and putting it into one central location (rather than having it scattered around the model). This will save time as you won’t have to find the same data multiple times throughout your model.
Once you have all the data, you only want to look at it once as well – so you don’t have to keep going back and forth between your data and the model’s cells. This will save you time since you won’t have to “tear the model apart” to view the data again and again.
Summarize key assumptions in a comment box
A good financial model is based on sound assumptions. But some assumptions are more important than others. For example, your model may rely on the cash flow from a deal closing. If the assumption is incorrect, your model’s entire output is likely to be incorrect.
However, that doesn’t mean that every assumption you made is as important. Instead of creating footnotes for each assumption, you can summarize all your assumptions in a single comment box at the top of the model. This will help you keep track of all the assumptions you made, and will enable you to easily spot the most important ones.
Use calculated fields instead of formulas
Some Excel users like to use a formula for every cell in the model. This leads to a very complex model. Instead, create calculated fields, which are just like formulas, but don’t appear in the cells. These calculated fields are hidden from view but can be used in many places throughout the model.
They allow you to create a simpler model with fewer formulas. Formulas are also more visible than calculated fields, so you can make sure that your model’s cells are free of clutter. While formulas let you control the calculations, calculated fields let you control what appears in your model.
Automate your reporting with Macros
You may need to produce multiple versions of the same report, or you may need to run a complex scenario analysis. If so, you can automate these tasks with Excel macros. Use macros to automate repetitive tasks such as running a scenario analysis, summarizing data, or plotting data on a chart.
Macros are also a great way to test your model to make sure it’s working correctly. Make sure to save your model as an Excel Macro-Enabled Workbook (XLSM). This will allow you to run macros in the model.
If you regularly produce models for the same company, or for companies that use similar financial statements, you can speed up the process by creating a template model. You can also create a model for the recurring tasks in your model.
This can save a lot of time in the long run by allowing you to create models faster with less effort. You can use Excel’s built-in templates as a starting point for your model. Or you can use Excel’s built-in tools for modeling, such as the “Estimate” functions for calculating cash flows, and the “What-If” function for running scenario analyses.
Don’t Forget about Conditional Formatting
Conditional formatting can be used to highlight cells that meet certain criteria. You can use this feature to highlight important values, mark cells that are empty, and mark cells that have errors in them.
This can be helpful if you have a long model, where it’s easy to miss a mistake. You can also use conditional formatting to highlight cells that exceed certain thresholds. For example, you can highlight cells that show high profitability, high sales volume, or high net cash flows. This can be helpful for communicating your model’s results to others.
Check assumptions with quick estimates
Before you create your model, you should come up with quick estimates for all the assumptions in your model. These estimates should be based on your best guesses. You can then plug these quick estimates into your model to make sure they produce similar outputs to your thorough calculations, which you’ve spent time completing. This can be done on paper or in Excel.
This will help you identify the most important assumptions and spot potential problems, such as assumptions that lead to unrealistic outputs. This can also help you identify assumptions that are too complex to be properly captured in your model. If a calculation is too convoluted, it may be better to leave it out of your model and just report it in a footnote.
Plan your analysis upfront
Even though some modelers like to jump in and start creating their models, others prefer to plan their modeling process first. If you’re one of the planners, put some thought into what you want to achieve with your model.
Ask yourself what information you need, what assumptions you want to make, and what elements you want to include in your model. This will save you time in the long run as you won’t have to go back and forth between your model and your research.
This is particularly important if you’re creating a complex model. If you have a complex model, you may need to break it into smaller sections. You can then plan the model by creating a separate worksheet for each section of the model. This will help you organize your model, spot potential issues, and identify which elements are missing.
Divide your model in small chunks
If you’re creating a long model, it’s easy to get lost in the numbers. To stay focused, create separate worksheets for each section of the model and break long formulas into smaller chunks.
This will help you stay on track and avoid becoming overwhelmed. This is particularly important if you’re creating a complex model with many variables. If you’re modeling for a large company with lots of data, you may need to spend several hours creating your model. However, if you don’t have that kind of time, we have some tips that should save you time!
Define what “done” looks like and write it down
If you have a deadline, make sure not to fall behind. If you get behind, it’s easy to lose motivation and give up on your model.
To avoid this, make a schedule for yourself and keep track of your progress. You can also make use of the auditing feature in Excel to track your progress. This can be helpful for people who aren’t used to creating schedules. At the end of every modeling session, write down what you’ve accomplished.