Financial Modeling in Excel: Document Your Assumptions

Documentation of assumptions is certainly not the most exciting part of financial modeling, so you may be tempted to leave it to the end. Don’t fall into this trap! When you’re done building your model, you won’t remember what those assumptions were. Document as you go. Whenever you make a structural change or even a minor change to one of the inputs, document it, even it if it seems unimportant at the time.

List assumptions on a separate page, and label them clearly, so that they can be easily identified and referenced at a glance. For a small model, you may decide to mix source data and assumptions together. In a large model, you may separate them with as much detail as is possible or practical. For a detailed model, you may list out every single detail on a Detailed Assumptions page and then summarize the important ones on a Key Assumptions page. Think about the level of detail in your model, and let that guide the detail of your documentation of assumptions.

A model is only as good as the accuracy of the assumptions. You need to mitigate your liability by documenting your assumptions thoroughly and adding caveats where necessary.

Here are some commonly used methods for including documentation in a model:

• In-cell comments: There are two different methods of creating in-cell comments:
  • Red triangle comments: The most common method of creating an in-cell comment is to simply insert a comment within the cell, which appears as a red triangle in the corner of the cell.
    

    Red triangle comments.

    To create a red triangle comment, right-click the cell and select Insert Comment. To make a change to an existing comment, right-click the cell with the comment and select Edit Comment. Similarly, to delete a comment, right-click the cell and select Delete Comment.

    If you want to try the examples out for yourself, you can download File 0401.xlsx and select the tab labeled 4-1.

  • Data validation input messages: The less common method of creating an in-cell comment is to use data validations. These types of comments are more discreet, because they don’t have the red triangle in the corner and you don’t see the comment until you actually click on the cell.
    

    Data validation input messages.

    This sort of in-cell comment is especially useful for creating little instructions and warnings regarding input data to users, because they won’t see the comment until they actually click the cell to enter the data.

  • To create a data validation input message, click the cell and then, in the Data Tools section of the Data tab of the Ribbon, click the Data Validation button and the Data Validation dialog box appears. Select the Input Message tab, and type the message you want to appear. This message will appear only when the cell is selected.

Documentation of assumptions using in-cell comments is most appropriate for specific information relating to only one cell or range, because they can be viewed only in a single cell. In-cell comments are useful for communicating details to another modeler about specific calculation details, because the comments are shown only on-screen and won’t show when printed out. More wide-ranging, generic assumptions should be documented using other methods.

Of the two types of in-cell comments, the data validation input message is a good choice because they’re neater and cleaner looking. Remember, though, that if the cell isn’t selected, the comment won’t be viewed, so make sure that the comment is only cell-specific. More important, general assumptions and comments should use plain text or another method of documentation.

• Hyperlinks: There are two different types of hyperlinks that are useful for documenting assumptions and source data in financial modeling:
  • Cell and file hyperlinks: You can create hyperlinks to sources or other reference files and other sections of a model. Cell and file hyperlinks can aid in navigation of a long and complex model. They’re especially helpful for new users to find their way around the model.

    To insert this kind of hyperlink, on the Insert tab, select the Hyperlink icon from the Links section. On the left side, select Place in This Document. Then select the sheet and cell reference or named range of the hyperlink source. You can change the Text to Display at the top to display something like Go to Calculations instead of Calculations!B147, for example. Similarly, you can insert a hyperlink to another file for source referencing.

  • URL hyperlinks: Hyperlinks can also be used to refer to relevant websites directly from your spreadsheet. For example, if the interest rate you assume in your financial model came from a central bank’s website, you can simply copy the URL into a cell in your model. Note that if you’re using Get & Transform (formerly called Power Query) you can link data straight from external data sources, including websites, directly into your spreadsheet, which can be quickly updated without having to manually open the website.

    If you’re not using Get & Transform, you can manually insert a URL hyperlink by copying and pasting the URL from the browser directly into the cell. If you don’t want to show a messy URL, you can show different text. On the Insert tab, select the Hyperlink icon from the Links section, and then select Existing File or web Page. Paste the URL into the Address field at the bottom, and change the Text to Display at the top.

• Hard-coded text: This method of assumptions documentation is not very sophisticated, but as with many things in financial modeling, the simple solution is often best. The assumption has been simply typed into the cell below the calculation table. There is no danger here that a modeler or user might overlook this assumption, whether it’s being viewed on the computer or in a printout.
  

  Hard-coded text.

• Linked dynamic text: Documenting assumptions liberally within your model is very important, but it’s very difficult to keep the documentation up-to-date when your model is dynamic and inputs are continually changing. For example, in the project costings model, you want to make sure that the person using the model understands that only 15 hours has been entered into the model, so you want to put the commentary below “Calculations based on 15 hours of billable time spent.” This seems fairly obvious in this example, but if the model were more complex, and the inputs were on a different page, it would be useful to include this comment.
  

  Linked dynamic text.

If you were to simply type the text “Calculations based on 15 hours of billable time spent” manually into cell A10, this would be correct … until someone changes the number of billable hours spent on the project, and then the text would quickly become out-of-date. There is a very high risk in this case that you could distribute the model with incorrect assumption documentation.

Instead of typing in the hard-coded value of 15, you can convert the text to a dynamic formula using an ampersand (&). If this formula is used, it will automatically change if the value in C2 changes:

="Calculations based on "&C2&" hours of billable time spent."

Download File 0401.xlsx and select the tab labeled 4-4 to try this out for yourself. Change the value 15 in cell C2 to another value, such as 20, and you’ll see that text changes from “Calculations based on 15 hours of billable time spent” to “Calculations based on 20 hours of billable time spent.”