One of the things that you want to avoid in a financial model is to have hard-coded inputs within formulas. But why is that?
- Transparency: A user of your financial model should be aware of all the inputs used for making the projections. By hard-coding inputs within formulas is like you are sweeping dirt under the rug! not that data is dirt, on the contrary inputs are the most valuable part of a financial model but I hope you got my point. That is why one of the rules in financial modeling is transparency and hiding information in formulas breaks that rule.
- Flexibility: We live in an unstable world and things changes. So if you hard-code inputs in formulas, then you won’t be able to easily change them when needed or apply sensitivity. It’s doable but it’s inefficient. Instead you collect all inputs and present them in a defined section in your spreadsheet and this way you can easily change the input and see the impact on the model results.
Note: I see that now people go as far as flagging constants like 365 number of days in a year in formulas as a problem. I am not that much worried about the number of hours in day and in a year to be hard-coded cause these things are constants and will not change. But some people use some softwares and anything hard-coded is detected and flagged as issues. so to avoid that you can have a section in your inputs under “constants” and you can list all constants that you are using in formulas and use ranged names to refers to those constants.
Now let’s look at the techniques I use to detect hard-coded inputs in spreadsheets:
“ Bird view” technique: Make cells display the formulas they contain, instead of the formula result by going to Formulas>Show Formulas. You can quickly detect hard-coded figures if any within formulas. It is also a good exercise to strengthen your eye muscles.
Combo of Bird-view and acid test: If you have thousands columns the bird-view technique might be painful so what you can do is to check the formulas in the first entry in the row (the first column) by using the bird-view technique, then use the “Acid test” meaning copy all formulas across and see if anything changes in your spreadsheet. This way you can free 2 birds with one key. You are checking the formula consistency and hard-coding within formulas.
User Defined Function: I learned this from https: get-digital-help.com (Link) and he also got it from David Hager (link) and Chandoo has also done something similar (Link)
Step 1: Save your file under anther another name.
Step 2: If you have never dealt with the VBA editor under Excel, don’t get intimidated by it. It’s very easy. You just need to copy and paste a code that is already written by someone else. Copy and Paste the below code into your file
Step 3: Conditional formatting: Now you have the function but you can run it by just creating a button. Instead, we need to play with conditional formatting:
- Select the whole worksheet where you want to do the search
- Go to Home/Conditional Formatting/New rule
- Select the rule type “Use a formula to determine which cells to format”
- Type the name of the UDF: =CellUsesLiteralValue(A1)
- Under format select a flashy color like red for the cell background
- Press Ok
Step 4: Now go through the highlighted cells and see whether it is actually a hard-coded input or not because this function even picks up zero so if you have a great than zero “<0” or less than “<0”, it will flag it.
I am sure there are softwares or add-ins that can give a report with a list of hard-coded inputs included in formulas but I don’t use any paid tools. I only use the Generic Macro which is a free tool by Professor Edward Bodmer. You can download it from edbodmer.com.
