Post crisis market conditions driving Infrastructure strategies
Excel Auditing Tools - Avoid errors in financial models
In this article we have reviewed the most commonly used add-ins in the project finance sector used to minimise the number of errors in cashflow forecast models.
Using an audit tool in Excel is a good way of minimising the number of errors in spreadsheets and financial models. In this article we have reviewed the most commonly used add-ins in the project finance sector, in order to give you an overview of the tools that are available.
We generally find that there is not one software that is superior, so you need to clearly assess the needs of your organization in order to find the right mix of tools. Navigator is neither affiliated with nor endorses any of the tools below, as this is an overview of the market only. We have done our best to include the most relevant tools for analysts in project finance, but should you feel that we have omitted something substantial, lease let us know.
Operis Analysis Kit (OAK)
Developer:
Operis
Website:
http://www.operis.com/oak.htm
Price:
UK£395 (+VAT) per license
Discounts:
Available for multiple licenses
Trial:
30 Days Free
Upgrades:
Free for Version 3 software license; UK£89 (+VAT) to upgrade to Version 3 from any earlier versions of OAK
Screenshots on website?
No
About OAK
OAK was developed to assist users in the analysis and development of spreadsheet models. It offers common functions such as comparing different versions and mapping out the structure of a spreadsheet, but also extends on existing excel functions for greater speed and efficiency, eg, editing multiple range names at a time. What separates OAK from the rest is its ability to rank formulae in order of complexity, thereby directing users to the potential problematic areas in a model.
Functions
- Graphically map the structure of a model
- Find formulae that break best practice rules
- Reconstruct formulae to list their precedents
- Identify all cells that are not used in calculation
- Rank the model formulae in order of complexity
- Locate formulae that are sources of error values
- Generate a database of all range names used in a model
- Compile a summary report on the composition of a model
- Compare different versions of a model or worksheet
- Search for spreadsheet features that cause error values or violate the principles of good modeling practice
- Modify existing names to correct misspellings
- Define local names
- Re-produce formulae based on best practice principles
- Apply multiple range names at a time
- Delete multiple names at a time
- Insert or delete rows and columns through arrays
- Replace range names with cell references within formulae of an entire worksheet
About Operis
Established in 1990, Operis specializes as an advisor in corporate and project finance within the health, transport, oil and gas, mining, education, power, waste, renewables and public infrastructure industries. It provides a range of specialist financial advisory and capital-raising services, specialist financial modeling, model audit, and tax and accounting services.
Spreadsheet Advantage
Developer:
Spreadsheet Advantage Pty Ltd
Website:
http://www.spreadsheetadvantage.com/
Price:
Australian Customers – AU$429 (+GST) per license
Non-Australian Customers – US$299 or AU$390 per license
Discounts: May be available for multiple licenses
Trial:
30 Days Free
Upgrades:
Please contact Spreadsheet Advantage Pty Ltd
Screenshots on website?
Yes
About Spreadsheet Advantage
Spreadsheet Advantage was developed by a small group of professionals from various backgrounds, ranging from computing to finance to mathematics. Like OAK, it provides the most commonly used functions for auditing models. However, it also allows the user to show dependent and precedent arrows of a group of cells with one keyboard shortcut and display imbedded IF functions over individual lines for better understanding of the formula.
Functions
- Compare spreadsheets or worksheets within a spreadsheet
- Align rows and columns of comparable spreadsheets
- Find the number of unique formulae in the worksheet and their references to other worksheets
- Generate a list of all actual and potential circular references in the spreadsheet
- Map worksheets to show which cells contain inputs and which contain formulae for detection of inconsistencies
- Set bookmarks in a spreadsheet for faster reference through the use of a specific shortcut key
- Compare formulae in two different cell ranges
- List all range names in a spreadsheet
- Show all dependents and/or precedents to a group of cells with one shortcut key
- Display IF functions over several lines for better interpretation
- Trace dependents and/or precedents for all cells select
- Locate source of error values
Spreadsheet Detective
Developer:
Southern Cross Software
Website:
http://www.spreadsheetdetective.com/
Price:
Trial:
Free
Upgrades:
Free in the first year after purchase; 30% of list price per annum thereafter.
Screenshots on website?
Yes
About Spreadsheet Detective
The most unique feature of Spreadsheet Detective is its use of visual representations within the actual worksheets. Amongst common functionalities it also has an inbuilt sensitivity analysis which will tabulate the output value’s sensitivity to input values.
Functions
- Indicate nature of formulae through shading
- Create a report of all unique formulae in a spreadsheet
- Show how formulae have been copied by using lines, dots and circles instead of shading
- Move between precedents and dependents using a Data Flow Dialogue.
- Produce a precedent/dependent report to describe how a cell is calculated
- Compare spreadsheets
- Produce a summary report to show worksheet data flows
- Rename range names and update all formulae using the original name
- Show how sensitive a selected output value is to all the input values
- Produce formula map which represents each cell as a single character
- Identify and separately list formulae that reference other workbooks
- Show how multiple workbooks are (or are not) related
- Flag cells that are (or are not) referenced by any unique formula
- Highlight formulae copied between different worksheets in three dimensional models
- Document local range name relationships
- Visualize array formulas
- Highlight certain dubious constructs and circular references
- Document charts
- Control and override AutoNames
- Provide a summary of all the worksheets in a book
Spreadsheet Professional
Developer:
Spreadsheet Innovations
Website:
http://www.spreadsheetinnovations.com/
Price:
UK£295 per copy
Discounts:
Available for multiple copies
Trial:
Free – will only work on spreadsheets with less than 50 rows
Upgrades:
Please contact Spreadsheet Innovations
Screenshots on website?
Yes
About Spreadsheet Professional
Apart from model audit purposes, Spreadsheet Professional also provides building, testing, documenting and usage tools. Instead of running several functions consecutively to find errors, it is able to test 25 common errors such as unused input value, blank cell references, duplicate range names, and complex calculations at the one time. It is able to produce a sensitivity report much like that by Spreadsheet Detective, though with the addition of a graphical interpretation. Overall, Spreadsheet Professional incorporates the more useful functions at the expense of the “cool” but less important capabilities found in other tools.
Functions
- Provide a visual representation of dependents and precedents of any formula to any depth
- Examine a spreadsheet for 25 common types of errors
- Map inputs and unique formulae in a spreadsheet
- Translate each unique calculation into words
- Produce and report sensitivity and breakeven analysis for any cell
- Compare spreadsheets
- Report a brief summary of the model, eg, when it was produced, the sheets within, etc
- List the range names and external references within the model
About Spreadsheet Innovations
Spreadsheet Innovations has been dedicated to the development of tools to assist in spreadsheet model development since 1994. It has over 10,000 clients globally, including most major financial organizations.
Fast Excel
Developer:
Decision Models
Website:
http://www.decisionmodels.com/fastexcel.htm
Price:
UK£44 or US$79 per license
Discounts:
Available for multiple copies
Trial:
None available
Upgrades:
Please contact Decision Models
Screenshots on website?
Available in the user manual which can be downloaded from the website
About Fast Excel
Fast Excel is mainly used to show the complexity of a model by finding performance bottlenecks, and to show the memory used by each worksheet. It does not, however, perform model audit functionalities such as model comparisons, trace interdependent cells, and spreadsheet mapping. The program is used solely to make the model run faster.
Functions
- Find performance bottlenecks
- Additional advanced Lookup and Match functions
- Look at the flow of calculations between worksheets
- Optimize the calculation sequence of the worksheet
- Show the memory used by contents of each worksheet
- Compare calculation speeds of difference formulae, worksheets, etc
- Quickly calculate a small subset of the workbook
- Eliminate wasted space
About Decision Models
Decision Models was founded by Charles Williams in 1996 to provide consultancy, solutions and tools to assist clients with their business decisions. The company incorporates the knowledge and experience of professionals from software development, modeling, forecasting, marketing, business planning, product management and new product introduction. Prior to founding Decision Models, Charles Williams worked for IBM in both Europe and the USA.
Want more Tutorials?
See All Resources Get the latest Project Finance Tutorials and Blog Posts...
Subscribe to our newsletter
You will go into the draw to WIN a FREE training course.
Instantly unsubscribe at any time. We value your privacy.
Project Finance Training around the globe.
We provide leading project finance professionals with in-house training and four public courses in Asia, Europe, US and the Middle East.
Upcoming Courses:
- VBA for cashflow models - Sydney
- Business case modelling using Excel - Sydney
- See all
Latest Posts
Latest Tutorial
Case Study
Testimonial
The new model is easy to navigate, analyse and present, Lynas has received positive feedback on the financial model from potential debt and equity participants. The professional presentation and functionality of the model has made our financing process smoother.Lynas Corporation