Error-checking Excel Models using TopRank

As one of the products within Palisade’s DecisionTools Suite, TopRank perhaps does not get the attention that it deserves!  In many ways, TopRank is a very natural tool for general Excel modellers, perhaps especially so for those modellers who wish to perform sensitivity analysis, but who do not need the full risk analysis with simulation techniques that are available using @RISK.

TopRank is a great tool to audit models, and to check-for errors. For example:

  • By simply using TopRank to generate a list of all inputs that affect an output, one can quickly get an idea of the basic quality of a model, particularly in terms of the robustness of the model when performing a sensitivity analysis. A typical example of the type of issue that a simple scan of all inputs can give you is where an item that should be a single model input is repeated several times within the model (e.g. the same tax rate appearing as a separate variable in each year of a multi-year model); to conduct a (valid) sensitivity analysis on the tax rate, the model formulae relating to tax would need to be rebuilt first. There are many other similar examples that arise in practice.
  • When using the standard error-checking technique of calculating (what should be) the same value through two different paths in the model, or when comparing two quantities that should be equal (such as forecast assets and liabilities on a balance sheet), the difference between these two can be calculated (and it should of course always be zero). By setting this error check cell as a TopRank output, one can trace and vary all inputs that affect this (ideally zero) cell. Once TopRank has run, the input sources of any errors will be listed, will allows one to check formulae in the model which are dependent on that input (using standard Excel dependency tracing) until the formula containing the error is found.

These are just a couple of examples which try to show how TopRank can be useful as an error-checking tool in general Excel modelling, irrespective of whether further sensitivity analysis (or even risk analysis with @RISK) is required not. Get your copy now while stocks last!

Dr. Michael Rees
Director of Training and Consulting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s