Tips for Making Great Financial Models in Excel

There is a lot to be said about Excel in terms of financial modeling: the tool is affordable and industry-agnostic. You can use it to create incredibly complex models for almost any purpose. It is practically a financial equivalent of a Swiss knife.

Spreadsheets are shared across companies and distributed printed out as appendixes to PowerPoints or even in .doc files. Advanced financial modelers working in Excel often forget that the mere mortals who work with them are not as proficient at creating and consuming spreadsheets as they are. So naturally, modelers should make them as simple to use and to read as possible. There are many best practice advice and principles on how exactly to do that. The approach I will talk about in this post comes from Liam Bastick, a man who (literally) wrote the book on financial modeling in Excel. According to Mr. Bastick, a good financial model has 4 key attributes:

• Consistency

• Robustness

• Flexibility

• Transparency

Following these 4 rules will result in fewer mistakes and errors and overall less frustration.

Consistency

“Consistent formatting and use of styles cannot be over-emphasized. Humans take in much information on a non-verbal basis.”

A very simple principle, indeed. Whenever you construct your models this way, you give yourself and your users comfort about the model integrity. In turn, it makes it easier for anyone to add/remove model elements - business units, categories, number of periods, scenarios, etc.

When designing a financial model, it is safe to presume that users are used to seeing certain design patterns and that they know how to interact with them, even when they see a model for the first time.

Every user knows where to enter data when a workbook is designed like this

Besides styling and design, you should also be consistent when handling these model elements:

• Formulae should be copied uniformly across ranges, to make it easy to add/remove periods or categories as necessary

• Position titles and hyperlinks consistently to aid navigation and provide details about the content and purpose of the particular worksheet.

• Use the same periods across different sheets - if you use months, quarters, or years for your forecasts, use them consistently. If you need to use more periods in your spreadsheets, model everything at the lowest granularity level (months), and use SUMIF to aggregate months into quarters and months.

Working consistently should reduce referencing errors, increase model integrity and enhance workbook structure.

Robustness

“Models should be materially free from error, mathematically accurate, and readily auditable.”

According to Bastick, model errors are like cockroaches: “Once you saw one of these errors in a model, you would believe the model was infested and never trust it after that.”

If you are a financial modeler with error messages such as #DIV/0!, #VALUE!, #REF you should construct error checks before handing your work away. Or live in shame for distributing broken spreadsheets. For example, when calculating profit margin (profit divided by sales) you will get #DIV/0! if any sales are missing in the sheet. There are two things you can do in this situation:

• Use an IF statement to check that sales are not zero (proactive test)

• Construct an error check to flag if sales are zero (reactive test)

This is how fighting model errors in Excel often feels like

There are many checks you can write, and they all fall into one of the 3 categories:

Error checks - does the model contain flawed logic and/or errors?

1. Sensitivity checks - are any of the outputs derived from the inputs that shouldn’t be part of the base case (using best instead of the base case)?

2. Alert checks - anything that does not fall into any of the first two categories (revenues are negative, debt covenants are breached, etc).

To increase the robustness of your model, and give your users confidence that the model is working, you should create a dedicated worksheet with a check summary.

Your dedicated check summary doesn’t need to be this intricate, but you should try and make your users’ life by building something similar.

Flexibility

“The aim is to have a model provide sufficient flexibility without going overboard.”

Spreadsheet modeling allows you to change an assumption and investigate how that affects different outputs. As a modeler, you need to be mindful about what inputs you will use as variables, and how.

To create really flexible models, you will want to take some time and consider what method of data entry to choose, and how to properly validate the data. Here is an example: if you have a cell labeled Volumes, and you don’t use data validation, users can enter any random figures, even negative ones, which makes no sense here - the volumes will never be negative. This is simply common sense.

You might even consider choosing other entry methods: option buttons, checkboxes, drop-down boxes, etc. Whatever method you decide to use, it is smartest to limit inputs to positive numbers, whenever possible. Why? If I say that last year our costs were $10000 and that they had increased 10% this year, it is immediately clear what I’m talking about. But if I say the costs were - $10000 and that they increased by -10%, things get messier.

Transparency

“If you can follow it on a piece of paper (i.e. no Formula bar), it’s transparent”

No matter how complex the model is, you shouldn’t forget that it needs to be clear, concise, and fit the intended purpose. I already mentioned how important it is to keep inputs away from calculations, and both of these two away from outputs. But what can be even more important for worksheet ease-of-use is to:

• Keep different areas of model separate - separate revenues from costs and CAPEX

• Keep base case data away from sensitivity data - most of the mistakes happen when users change the wrong inputs.

• Keep formulas as simple as possible - simple is always better.

• Include a simple flowchart that explains how your model works for unsuspecting users

If the flow-chart with boxes linked to sheets like this one is an overkill for your use case, you should at least make a Table of Contents to make it easier to navigate through the model

Conclusion

Financial modeling in Excel is all about design and scoping. Saving precious time by reusing and repurposing old templates can cost companies a lot of money due to slow and costly decision-making.

Using CRaFT principles as guidance during modeling takes a bit longer, initially, but it pays in the long run.

If you think that your financial models are overgrowing spreadsheets, and you need a better financial modeling tool, Farseer might be for you:

- Financial modeling in Farseer is centralized, fast, consistent - you won’t need to worry about errors and mistakes.
- Natural language formulas are intuitive, error-proof and cannot be deleted by accident.
- Sharing and exporting your work is granulated - you can share only parts of your model relevant to your user
- Built-in hierarchy makes modeling transparent by default
- Reorganize models by simply dragging and dropping entire spreadsheets to a specific location in the model 


LEARN MORE ABOUT FARSEER HERE