Building a Financial Model by the Rulebook
building a financial model in Excel, you’re unlikely to encounter the limitations and boundaries imposed by other, less flexible software, so there are certain rules you should follow to avoid these issues. In this chapter, I fill you in on the key rules you should follow when building a financial model.
Document Your Assumptions
The term “garbage in, garbage out” is never truer than in relation to financial modeling. You can have the most beautifully laid-out financial model with perfect formatting, a great design, and fabulous-looking charts and scenario tables, but if the inputs that go into the financial model haven’t been verified, the model is use- less and you can’t use the outputs. Important decisions are made based on the outputs of financial models, so listing the assumptions that have gone into the model is critical.
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 assump- tions 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.
Still not convinced that documenting assumptions is important? How’s this for persuasion: When you move on to another role or you’re away on vacation, and something goes wrong with your model, who do you think they’re going to blame? You guessed it! Think of documenting assumptions as covering your ass. Your model needs to be able to speak for you when you aren’t around to explain or defend your work. The documentation of assumptions should explain your thought process and why the model is built the way it is. That way, if there are any ques- tions as to the accuracy of the model or the input assumptions, they can be easily explained by the model itself.
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 diff 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, as shown in Figure 4-1.
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 com- ment, right-click the cell and select Delete Comment.
If you want to try the examples out for yourself, you can download File 0401.xlsx at www.dummies.com/go/financialmodelinginexcelfd and select the tab labeled 4-1.
FIGURE 4-1:
Red triangle comments.
- 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, as shown in Figure 4-2.
FIGURE 4-2:
Data validation input messages.
This sort of in-cell comment is especially useful for creating little instruc- tions 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 specifi 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 specifi 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, my personal preference is the data validation input message, 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-specifi More important, general assumptions and comments should use plain text or another method of documentation.
» Hyperlinks: There are two diff types of hyperlinks that are useful for documenting assumptions and source data in fi modeling:
-
Cell and fi hyperlinks: You can create hyperlinks to sources or other reference fi and other sections of a model. Cell and fi hyperlinks can
aid in navigation of a long and complex model. They’re especially helpful for new users to fi 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 fi 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 fi 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 spread- sheet, 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 diff
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 fi
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 fi modeling, the simple solution is often best. As you can see in Figure 4-3, 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.
FIGURE 4-3:
Hard-coded text.
» Linked dynamic text: Documenting assumptions liberally within your model is very important, but it’s very diffi to keep the documentation up-to-date when your model is dynamic and inputs are continually changing. For
example, in the project costings model shown in Figure 4-4, 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 diff page, it would be useful to include this comment.
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 (&), as shown in Figure 4-4. If this formula is used, it will automatically change if the value in C2 changes:
FIGURE 4-4:
Linked dynamic text.
Download File 0401.xlsx at www.dummies.com/go/financialmodeling inexcelfd
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.”
