Financial Models are especially designed excel spreadsheets which have order and consistency throughout the workbook, unlike random set of calculations.
So, excel doesn't provide utilites specifically for working on financial modellers.
As a finance manager, your team of financial analysts needs this tool every now and then to navigate the formulas, perform review of the calculations, Compare files, delete unwanted named ranges & styles, etc. Without these, it is like going blind for a fight.
These utilites have been combined in a single model ribbon using the experience, pitfalls from various other decade old tools. And, have been designed to keep up with the analyst's speed.
EMF Pro features helps you understand, navigate, compare workbooks, removing the inherent risk of working with Excel.
1. Trace Formula
This addin can trace any precedents of a formula in an interactive way. Using the addin it is possible to trace the source of any formula to its base input.
a. Traces any formula with the ability to drill down to precedent’s precedent by clicking on the cell reference in formula bar
using Alt + C key
b. List of all precedents
are listed and can be traversed through up & down arrow keys
c. Double clicking on a precedent
will change the source to the precedent cell clicked or Enter Key
d. Previous source can be traced back by clicking on Back
button or Backspace Key
Button takes to the current source, whereas Close
will take to the first source cell traced
f. It can trace INDEX, INDIRECT and OFFSET function to their source.
g. Besides it can also trace SUMIF, SUMIFS, 3D SUM
, VLOOKUP, HLOOKUP, LOOKUP and XLOOKUP.
h. You can use this tool without the use of mouse, all operations can be performed through keyboard keys.
2. Trace Dependents
This tool traces dependents of any cell within the sheet or offsheet. This has similar interface with that of Trace Formula
a. It lists all dependents in one place
, so that these can be traced easily
b. Collates all cells
to distinct ranges, thereby reducing the number of cells to inspect
c. Shades all distinct ranges with different colors
, making the dependents easy to identify
d. You can use this tool without the use of mouse, all operations can be performed through keyboard keys
3. Map Workbook
This creates a map of the workbook such that unique formulas are easily identifiable. Map is considered gold standard in finding out formula inconsistencies in the workbook.
a. Map can be run either in the whole of workbook
or specific sheets
which can be selected in the listbox
b. The unique formula
identified as shaded in “Cyan” for F, C and X
c. A Map Report
is generated that mentions a summary all unique formulas by sheet
d. In addition to this, a list of unique formulas
in the model, is created separately
e. The Map is generated for each sheet, and any inconsistency
is easily identifiable within the consistent columns with the change in cell color.
f. Any unnecessary
values / formula outside the working columns or within hidden columns / rows can be recognised by this file
4. Compare Workbook
Using Compare utility, any two version of the workbook can be compared against each other to identify changes in cell formula or inputs.
a. Using the dialog box, choose New workbook and Old Workbook. All sheets will be listed and in case of sheet name change, the individual sheet can be selected from the dropdown
b. Compare report
is generated for unique formula changes along with other changes separately
c. All changes are reported in Notes_ tabs
d. A Map sheet is also prepared highlighting changed cells in RED
5. Advanced Name Manager
This name manager utility has been designed to identify hidden named ranges and delete them.
a. All hidden named ranges can be selected all at once using Select hidden button
b. These hidden named ranges can be made visible to excel’s name manager by clicking on Unhide button and vice-versa using Hide button
c. The selected named ranges can be deleted if unnecessary by clicking on Delete button
6. Style Remover
Using this utility unnecessary / unused styles in a workbook can be deleted very easily. If a workbook has too many styles, the workbook becomes different to operate and hangs quite often.
a. This also provides a list of unused styles by clicking on Get Used Status
b. The occurrences of the listed styles are reported under Used count heading, and any unused style can be selected by Select Unused button and subsequently deleted using Delete button
7. Other Utilities
a. Link Workbooks :
Comparable versions of a model can be linked. This helps in navigating between one file to another just by double clicking on a cell. This is particularly useful when performing comparison between two workbooks
b. Copy References :
While reviewing a model or makes notes, cell references are needed to pasted in summary sheets. This addin allows you to copy cell references and paste them in your reports. Very useful addin for model reviewers.