Constructing a complex spreadsheet such as a project finance model is a time-consuming process. Doing so in a manner that avoids errors and produces an organised workbook, just as a deadline looms, can be particularly challenging.

It is tempting to take shortcuts, but a hurriedly constructed spreadsheet carries risks. Instead, consider deploying the Operis Analysis Kit (OAK) to more efficiently manage the process of building a large, complex workbook.

Optimise spreadsheet construction with OAK

As your complex spreadsheet takes shape OAK can help maintain a bird’s eye view, assess spreadsheet risk, and speed up bringing about major changes if needed. OAK is also an unbeatable tool where there is a need for monitoring workbook versions and identifying errors that crept in over time.

Spreadsheet planning and mapping

Complex spreadsheets can be hard to grasp, and it is useful to maintain a summary of the workbook particularly where multiple team members are involved in developing it. Using OAK Summarize you can automatically generate a detailed report covering all the worksheets in a workbook.

Summarize provides a range of statistics covering each sheet including how many cells are in use in a sheet, the number of cells containing formulas and number of constants in a spreadsheet. The Summarize report provides a meaningful overview that lets you more easily track the development of a complex workbook.

Monitoring spreadsheet quality

With OAK you can efficiently keep track of spreadsheet quality and risk factors. As a workbook is built you can use OAK to quickly identify formulas that are too complex and to flag hardcoded constants, giving you an opportunity to review these early on.

Though it is possible to complete some of these tasks without OAK, OAK offers a quicker, simpler way of doing so which means that you can monitor spreadsheet quality and risk throughout the construction process, flagging potential problems as soon as they appear rather than waiting for a single, bulk manual review on completion.

Detecting spreadsheet glitches

In theory, glitches should be quickly eradicated as the team building a workbook discovers erroneous results, but sometimes errors are not spotted straight away and finding the source of erroneous calculations can be time consuming. OAK’s Search function can, for example, highlight primary error cells that are propagating error results across your spreadsheet.

OAK Search also lets you instantly identify references pointing to blank cells. This gives you the opportunity to determine whether cell contents were accidentally deleted or whether a cell reference is incorrect. OAK Search identifies unreferenced cells too, so you can check that only cells defined as outputs are left unreferenced.

Managing names in a workbook

Spreadsheet authors commonly use names in a workbook to make formulas more readable. Yet managing names across a large workbook can be challenging. OAK includes several tools that make managing names easier.

First, OAK can automatically build a database of names so that you can better manage existing names, identifying names that are overlapped or hidden. Changed your mind about names? Need to deliver a finished workbook without names? OAK Deapply automatically replaces all names with the actual cell co-ordinates that they reference.

Tracking workbook changes

OAK includes a comprehensive spreadsheet comparison tool, Compare, that lets users swiftly identify real changes in spreadsheets. OAK Compare makes it easier to find out where a spreadsheet has changed between versions, essential when tracking changes where multiple team members are involved.

A comparison tool such as OAK Compare can also greatly speed up the process of identifying a spreadsheet bug, allowing a team to track back across past versions, rapidly identifying changes. Finally, in the absence of a version history, OAK Compare offers a faster means to documenting spreadsheet versions as a workbook evolves over time.

OAK seamlessly integrates with Excel

Built by the spreadsheet experts at Operis, OAK’s functions are conveniently accessible from the Excel ribbon interface. Need to see how a team member changed a model? Simply select OAK Compare for a near-instant, detailed comparison of workbooks.

Likewise, as a team manager you can rapidly flag looming problems by finding complex formulas or cell errors at the click of a ribbon button. In essence, OAK seamlessly functions as part of Excel, available whenever you need it.

And you can always integrate any OAK tool with your VBA macros by using the OAK API.

Getting started with OAK

OAK is available as a 30-day free trial which includes full OAK functionality. You can download the OAK trial here. We suggest that you test some of OAK’s core functions including Summarise, Compare and Search as doing so will quickly reveal how OAK speeds up many of the key steps involved in constructing a workbook.