We’ve seen couple of techniques on how to detect hidden things in a spreadsheet earlier in the previous post, so now would be a good time to focus solely on how you can detect misplaced inputs in a spreadsheet.
Mission 1: Detect hard-coded inputs within calculation sheets
Background: For transparency and ease of access to model assumptions, the modeller should dedicate separate worksheet or a separate place on a single model worksheet for assumptions. Also, the modeller should categories assumptions by type.
Detecting techniques: Use “Go To” function to identify hard-coded values in a spreadsheet. The F5 key gives you quick access to the “Go To” function.
Step 1: Save your file under another name
Step 2: Go to the first calculation sheet in the financial model and Press F5>click Special> select constants>check only the numbers box.
Step 3: Click OK and all constants (hard-coded inputs) will be selected.
Step 4: Once all constant all selected, open the Format Cells dialog by pressing Ctrl + 1 or go to Home tab > Font> Fill Color… and change the background color to red.
Step 4: you will have to repeat the above steps for each worksheet individually.
Mission 2: Detect hard coded figures within formulas
Background: Best practice is to avoid hard-coding figures within formulas and to have all inputs documented in a separate section of the financial model.
Detecting techniques:
- “ 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.
2. “INQUIRE”: This is an add in developed by Microsoft itself. You can use the spreadsheet inquire to analyse your spreadsheet and get a report showing detailed information about the workbook as well as details of all formula within the workbook that contain a hard coded value.
To enable INQUIRE in Excel
- Go to File > Options > Add-Ins.
- Manage box, select COM add-Ins and click Go.
- Tick the box next to INQUIRE and click OK.
Start the workbook analysis
- Click Inquire > Workbook Analysis.
- Under items, scroll down to Formula and then select “with numeric constants”.
- In the results pane you will now see details of all formula within the workbook that constant a numeric constant or a hard coded value within a formula.
Note that if a sheet within a workbook has more than 100 million cells, you will get an error message and you will not see results directly in the results pane. You can however export the data to a report by clicking the Excel Export button.
Last step is to list your findings in the Q&A sheet.
