DATE: Mon 17 Oct 2016
BY: Stephen Aldridge
When you first see an Excel workbook, does it set your head spinning, trying to understand what you are looking at? Here's how we tackle a new workbook the first time we see it.
We see a lot of models here at Numeritas, so it’s essential that we can get the measure of them quickly. We have our own Excel add-in "nXt" (numeritas eXcel toolkit) to make that job easier and we are now giving away a version FREE OF CHARGE so you can do the same. This blog gives you the high-level view of how you can use nXt to understand a workbook quickly.
Get your free copy of nXt here:
When viewing an Excel workbook, you normally see the values returned by the formulae, any constants (ie just straight numbers) and labels or text, which hopefully describe what is going on. The difficulty is that it is not obvious what a cell contains. We use what is known as a spreadsheet map to help understand the structure of a workbook. This is what one looks like:
This section of a map gives us rich information about the corresponding area of the workbook. It is generally considered good practice to copy a formula across a row in a typical timeline based financial model. We can see in our example model that this is the case. A pink cell is a unique formula and the green cells are copies of the cell to the left or the cell above. The full key is shown below:
You may already know that in copied formulae, the cell references all have consistent R1C1 formula (see our earlier blog for more on R1C1). The map is a visual representation of this that helps to spot a problem, such as in the example below – this is the same spreadsheet as the map above, with a hard coded number added to the formula in cell AB87 and AA89 overwritten with a number. In the map, both stick out like a sore thumb.
Open the installer and once you have read and happy to accept the terms and conditions, click the install button.
Once downloaded, the software will automatically install itself and add a NUMERITAS tab to your Excel ribbon, that looks like this:
To run a map click on the Run Reports button
This will bring up the select tests dialog box:
This has several options that you can play with, but as long as the Formula map is checked, you will create a map. The next screen give you options to select which sheets – usually you will want to ‘Select All’ (the default setting) and click run.
When finished, there will be one or more new workbooks (depending on which options you selected), including the map workbook if you checked that box.
Save the map workbook as a .xlsm file, then you can double click on the map and jump to the corresponding cell in the workbook.
Take a look also at the workbook summary and unique formula listing if you have run those, which also give you useful information.
Keep an eye out for more blogs and videos describing other features of nXt and please let us know what you think.
It you like nXt, you probably know someone else who will like it too. Feel free to pass on the installer so they can download it too.
Stephen is a Chartered Management Accountant and has over ten years of financial modelling experience both at KPMG and Deloitte. His early career included engineering, sales and corporate management roles. In 2004, Stephen joined Numeritas as a co-owner and a Managing Director.
We love to hear what you think. please note that comments are moderated so there might be a slight delay. Your email address will not be published.