4 Tips to Building Better Financial Models

Financial models in excel can range from a simple one page spreadsheet, to more complex multi-sheet workbooks. No matter the complexity, if you aren’t well versed in excel, the thought of building a model from scratch can feel quite intimidating. Building models takes patience and practice, but there are a few things you can do to help make the process a little easier for you. As a business owner, it will be useful to be able to navigate development of financial models to help you analyze your finances, run ad hoc analysis and plan for the future.

Here are few tips to keep in mind as you work on your financial models:

1. Organization and planning is key
2. Separate your inputs and outputs
3. Focus on formatting
4. Link formulas back to input cells whenever possible

Tip #1: Organization and planning is key

Before you get started with any model take a few minutes to map out what your goals are for your model. This will help you organize your model into the most relevant sections for what you need. Ask yourself, what are going to be the key inputs for the analysis? What are the various outputs for the model and data do I want to be able to gather from those outputs? What are the different scenarios I will need to be able to run? What time periods are relevant for my analysis? All these questions will help you determine how to best setup your model to accomplish these goals.

Tip #2: Separate your inputs and outputs

Once you know your key inputs, outputs, scenarios and time periods, organize your model in a way that you will be able to easily find data. I find that it is helpful to keep all of my inputs and model assumptions in one place in my model – or within on section of a specific sheet if I am building a multi-sheet model. When it come times to run scenarios it is helpful to be able to focus on one part of the model to make changes that will then flow through to the various formulas and outputs.

Similarly, I have also found it useful to keep your output in a different section of the model. Your output is ultimately the results of the analysis you were running with your model and what potentially will be shared with others, so you want to make sure this is as clean and digestible as possible for yourself and your potential audience. You don’t need all your inputs and formulas here – just the results. This will allow you to easily make changes in your input/assumptions section of your model and see the impact of those adjustments immediately within the output. Your output can either be directly below your assumptions section on the same sheet or on another sheet altogether depending on the complexity of your model.

Tip #3: Focus on formatting

Formatting can really help you create models that are easy to update and analyze. In addition to separating your inputs and output, make sure you can easily identify where you should make changes in your model to update your output. Highlight input cells in a different color – I will usually bold outline and yellow highlight my input cells. This helps me make sure I don’t accidentally change a formula cell and I also know that these are cells that when changed, will flow through my model and change my output. Be sure to clearly label the various sections in your model as well to help you navigate throughout the workbook.

Tip #4: Link formulas back to input cells whenever possible

Great models when built correctly will be very easy for you to update. Now that you have all your input cells clearly identified in your model, do your best not to hard code numbers into cells that are not marked as your input cells. You want to link your formulas to input cells for easy scenario planning and model updates. As your models become more complex it is helpful to have all formulas and output linked to an input cell. You don’t want to forget that you’ve hardcoded a number that can skew your results once you start running different scenarios with your model. And you don’t want to have to have to change formula cells in order for your model to update.

A simple example: You have revenue stream that is based on price per unit and number of units sold. If within your model you want to be able to see changes in total sales based on different price points and sales volume. Have 1 input cell for units and 1 input cell for price per unit. Link these two cells to total sales in your output section of your model. Now you can easily change price or volume and it will flow through to your model output.

In conclusion

Just remember that modeling tends to be more of an art than a science and with practice you’ll become more comfortable with building your own models. If you need help with financial modeling tips and creation, email us at support@theprofitctr.com and we’d be happy to help!