A bid model is to be prepared for an upcoming project, Fred is a analyst, assisting in the project and being in direct communication with the client. The bid is just a week away, though the model is prepared but Fred is working day & night on doing continuous changes in the model.

With that Fred isn't able to perform a quality checks having no luxury of time and the project manager is banking on the ability of the new consultant to deliver a perfect model. There are 5000+ unique formulas in the model, with interdependent logics. When one is changed who might never be sure how it affects other logics and still give correct results.

Though Fred's colleague did a full model review few days back, but it's not possible to do it again on the evolving versions before the deadline, due to time and price constraints.

Now it's upto Fred to perform a self-review of the model before any version is sent to the client.

The first most review that should be done on the model is formula consistency. All formulas under the timeline should be consistent all across the row, without this your scenarios will not work as you have expected them to.

A silver lining is that these type of errors are very easy to find out if you have the right tool for it. A Workbook formula Mapping tool is a gold standard in performing such a review. It helps you identify the following errors.

1. a) Inconsistent Formula
2. b) Hardcoded cells in calculation sheets
3. c) Missing formulas
4. d) Calculation beyond or ending before timeline
5. e) Sheet's last used cell, going unncessarily till column XFD or rows 50k and above.
6. f) Misplaced Inputs, which should ideally reside in input sheets and not calculation sheets.
7. g) External links

How to read the workbook map?

There are few mapping tools in the market. A sample from EMF Pro's mapping utility summary sheet

A map file is a replica of the model except all formulas are now represented by the following

All unique formulas are replaced by single letter "F" that gives a street view instead of full topography of the sheet. Whereas other consistent formulas are replaced as

• "<" - Formula copied from left or consistent formula to left
• "^" - Consistent formula to cell above
• "+ - Consistent formula both left and cell above

All callups are replaced by "C". Callups are cells that are just reference to other cells without any mathematical or logical operations

Any formula having external link (a link to a different workbook) will be represented by "X". External links are not recommended within the model and should be removed as soon as they are found.

Now the trick is to find out "F" or "C" or "X" within the consistent row or formulas at unusual places

• a) Formula Inconsistent, just like this one, a black sheep, colored in blue though 😉





• b) Hardcoded cells



• c) Missing formulas



• d) Calculation ending before the timeline



• e) Last used cell / End cell



• f) Misplaced inputs



• g) External links

Finally, you get to know the number of unique formulas in the model. A key metric that helps reviewers decide the length of any excel model and how much time and cost it will take to review.

If you wish to use our mapping tool, download our Excel Addin "EMF Pro" using this link.