Before You Hit Send:
Tips for better communications
with users of your financial models

I always tell my students that a financial model is like any other project document. I take them through a Word document with messed up formatting and ask them if they would dare to send such a document to someone they respected? If not, then why would they think it’s acceptable to send a horribly formatted financial model?

As I mentioned in a previous blog post, “Financial Modeling Commandments”, one of the qualities of a good financial modeler is to not be selfish and to think about the end user.

Here I want to list tools that you can use to format your financial models better, alongside a couple of good productivity tools that you can integrate into your models to make your life and your user’s life easier.

1.Using formatting: For formatting I use Professor Edward Bodmer’s Generic Macro file. You can download it directly from his website at edbodmer.com

With this tool you can insert a table of contents, a cover page, and color code for your spreadsheet with a click.

Here’s a video on how I use Ed’s valuable tool every single day and each time I open Excel. As a representative of all Excel users, I thank Professor Bodmer for saving us a hell of a lot of time by automating all of these otherwise painful tasks.

2. Add a tracking sheet: Most of the time different versions of a financial model are circulated between different interlocutors. For example, you might send version 01 of your model and when you are sending the next version of the same model, people want to know what has changed. So it is good practice to keep a tracking sheet in your model and to update it each time you circulate a revised version. For an example of a tracking template please check my tracking tool here.

3. Include data sources: The most important part of any analysis is the inputs. Without the right inputs the most sophisticated financial model will produce useless results. I therefore recommend that you clearly mention the source of each input that goes into your financial models. You can have a designated column in your inputs sheet for sources and in front each input mention the source. If you are at early stages of the project and most of your inputs are your own estimates then you just say “Internal estimate”, and you keep updating the references as you progress with the evaluation of your project. I recently started integrating a separate sheet in my model for resources.

4. Include a sensitivity analysis: There is always uncertainty around each input that goes into the model and you cannot say that you are 100% sure that your base case is going to be the reality of your project. So the ability of your model to do sensitivity testing/analysis is must. You can include it with a couple clicks using my suggested sensitivity tools here.

5. Add a guide sheet. This should be like a manual for the user to know how to work with the model. In this manual you should lay out:

• The color codes that you are using
• Worksheet structure
• List of worksheets and their purpose
• List of macros
• Abbreviations used in the model

For an example of a typical guide sheet, please check my suggested format here.

6. Include integrity checks: One way to help reduce errors is to include integrity checks in your models and to dedicate a sheet within your model that collects all the individual checks throughout the model into one sheet. This allows the user to check that the model, or section of the model, works correctly. For an example of a typical check sheet and a template, please download my check sheet here.

Let me know which of these hints are most useful and important to you. And if you think of something else to add, please do let me know.