Create Dynamic Formulas Using Links
In financial modeling, you need to understand the difference between linked and hard-coded numbers. A linked number will automatically change when the source data changes. In Figure 4-5, cell C3 contains the formula =B3*$C$2. This means
that if, say, the number of hours spent on the project in cell C3 changes from 15 to 20, the cost of the team leader will change from $1,200 to $1,600. If you want to try this out for yourself, you can download File 0401.xlsx at www.dummies.com/go/
financialmodelinginexcelfd
and select the tab labeled 4-5.
Formulas versus hard-coded numbers.
In comparison, cells C2 and cells B3 to B6 contain hard-coded numbers. These are simply typed directly into the cell and won’t change unless a user manually changes them. These cells can also be called input cells because if a user or modeler changes them, it will change the model output calculations.
Format cells containing hard-coded numbers differently so that it’s obvious to the user which cells can be changed and which cannot. By formatting input cells dif- ferently, it’s a signal to your user or another modeler that this is a hard-coded input variable that is designed to change if necessary. Excel has helpfully provided an Input style on the Home tab, as shown in Figure 4-6. There is no hard-and- fast rule that says that you must use this particular format, but it’s important that you do use a consistent format throughout your model so that the user can see at a glance which cells should be changed, and which should not.
This process of linking calculation cells to input cells is an important concept in financial modeling. Always link as much as possible so that when the model inputs change, the outputs also change. The only hard-coding should be input cells. To find out more about linking between cells, sheets, and external files, see Chapter 6.
FIGURE 4-6:
Input style on the
Home tab.
By linking, you can trace source data back through the links, making your model auditable, traceable, and easy to validate. If you got the input from somewhere, you should document where it came from wherever possible, because that will help with auditing and validation, and give your model credibility.
Only Enter Data Once
When entering data, you should document where it came from if possible, and then link to it with formulas. This may sound obvious but be careful never to enter the same value twice — enter it once as a source and always reference that one cell.
During a long and complex model build, it’s very easy to forget that you’ve entered certain assumptions or inputs, and then enter them again in a different part of the model. For example, in my intermediate online financial modeling course, we build a business case from start to finish, during which we use inflation multiple times within the model, both for indexing salaries, as well as increasing the rev- enue charged to the customers. Because we’ve entered the inflation in a single cell on the assumptions page, and then referred to it multiple times throughout the model, any fluctuation in the interest rate can be quickly and easily applied throughout the model.
Never type a value within a formula. A calculation such as =453*24 should not appear in a financial model. Similarly, a statement such as =IF(H$6<=$E7,0,157000) should have a link, not 157000 typed in. The only exceptions to this rule are those things that are standard or commonly accepted values that will not change, such as 24 hours in a day, 7 days in a week, or 12 months in a year. In fact, some hard- core modelers even say that you should put the value 12 in a separate cell, and then link to that cell as an assumption that there are 12 months in the year, but I think that’s taking it a bit far!
Try to link directly to the source data where possible. If you link to a link, this cre- ates spaghetti links within your model, which can cause problems later on. To find out more about spaghetti links, turn to Chapter 14.
