Financial model Detective, episode 1: In search for hidden things...
I recently posted a financial model checklist that I use when I am reviewing someone else’s financial models. it is certainly not an exhaustive list and it should be the start of a conversation about what you should be looking for when reviewing a financial model.You can download it from here.
Since this is a big topic to cover, I decided to break it down and go deeper in some of the important topics.
The first set of issues in the checklist are the “Mechanical checks” meaning things like color coding, format, integrity and basically an overall check on compatibility of the financial model with the best practice financial modelling standards.
Transparency is one of the main building blocks of a standards financial model. So one of the first checks that you need to perform is to check for hidden things in the spreadsheet.
1. Use the Document Inspector
I use this as the first step to detect hidden sheets. Once you run the document inspector, you will see a summary of what it finds, and one of the items is the invisible objects, hidden rows and columns, hidden worksheet. The downside of this is that it only lets you remove and not to unhide the hidden information. But the good thing about it is that it also detects the very hidden worksheets.
To open the Document Inspector, click File > Info > Check for Issues > Inspect Document
2. Unhide hidden worksheets
Once you have detected hidden sheets from the document inspector, you can unhide sheets by either
going to Home>Format > Hide & Unhide or
Right clicking on any tabs and click on Unhide.
3. Unhide a very hidden worksheet
Very hidden sheets are sheets that do not appear in tabs at the bottom of your workbook, nor do they show up in the Unhide dialog box. To unhide these sheets, follow these steps:
Press Alt + F11 to open the Visual Basic Editor.
In the VBAProject window, go through the list of worksheets and identify and click on the worksheet that is very hidden.
In the Properties window (Press F4 in VBA to view properties window), set the Visible property to -1 – xlSheetVisible.
4. Unhide Columns and Rows
To unhide everything in a worksheet.
Select the entire worksheet and select Unhide for each by either right mouse click anywhere in the headings or by pressing Shift + F10 and selecting Unhide.
5. Unhide objects
Hidden object should also be depicted from document inspector (step 1).
To manage objects in your worksheet, you can use the Excel tool called Selection Pane and accessible :
From Home >Editing>Find & Select >Selection Pane
Or by using the shortcut keyboard Alt + F10
6. Hidden names
The Document Inspector can also find hidden names in your workbook but can not delete them. To view and eventually delete hidden names, you need to run a macro.
Sub ShowAllNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next n
End Sub
7. Hidden Macros
While reviewing someone else’s financial model, don’t miss the macros. Even if they are no macro buttons in the spreadsheet, there might be some macros working behind the scenes.
Check the list of available macros by :
Pressing Alt+F8 to open the macro dialogue box and you should be able to edit/view the codes.
Some macros might not appear in the macro dialogue box. To check for hidden macros press Alt +F11 to open VBA and check for these:
Private subroutine : The code simply starts with Private Sub name()
Functions: another way to hide a macro from the macro dialogue box is to declare it as a function
Also check for Private Subs and functions under worksheets in VBA. Some codes are written directly inside the sheet rather than as a separate module. In the right window pan of the VBA, you will find the list of worksheets available in the workbook. Double click on each worksheet name to detect any codes.
While inspecting the financial model for hidden information, make sure to keep track of the list of hidden things you find in the spreadsheet so that you can report to your organization and eventually include them in your Q&A sheet on the financial model. You can download my suggested Q&A sheet from here.
However, Many of these checks can be automated by using macros or tools that can check for these things but you can also use the excel document inspector tool and couple of clicks to spot hidden information in any spreadsheet.
Stay tuned for the next episode of Financial Model Detective…
