More than 90% of spreadsheets have at least one error; in some circumstances, just one mistake might invalidate all the data you’ve entered into the spreadsheet.
A spreadsheet review is a great way to help reduce the chances that various errors will appear in your spreadsheet. In a large spreadsheet with tens of thousands of cells containing formulas, it is impossible for a reviewer to test and validate every cell manually.
OAK can save you time with your review process.
The bigger a spreadsheet gets, the more difficult it is to review. With a graphical map of worksheet structures, you can identify features and locate formula errors, saving you time with your spreadsheet review.
Focus your review on the current scenario and discard all inactive references, so it’s easy to understand how it derives its result.
Keep internal reviews of your spreadsheets organized and efficient. With the Review tool, you’ll no longer have to struggle with manually tracking feedback from your team members or costumers.
Map tool provides a top level perspective of how formulas have been duplicated across the worksheet, code inspectors frequently like to map the workbook.
Build names database will provide a listing of any names that are defined in the workbook. When performing a code inspection, many people feel compelled to look up each name’s definition. As an alternative, OAK can remove the names, leaving a model that is solely composed of coordinates that can be tested.
Verify that a spreadsheet’s outputs are based on correct calculations.
Have formulas laid out clearly and safely in a separate worksheet and verify the assumptions in one go.
The tool will prepare a list of all the formulas in the workbook and distribute it to the inspection team members.
In addition, Summarize will give a list of all the values used as the model’s inputs. These are candidates for comparison against any model data book or other explanation of the model’s assumptions that have been provided.
The initial review takes place before data input. It considers the role of the spreadsheet and the key expected outputs. When conducting initial assessments, you should consider the intended application of the spreadsheet and understand if a spreadsheet is the best tool for data entry.
It is essential to pay attention and focus on critical aspects that could influence the results in this phase because some components could appear unimportant the first time you look at them but become substantial in the end. You should then take a risk assessment and determine the validity of the results.
Following the initial review, you need to assess the spreadsheet itself. The structural review aims to provide an overview of the entire spreadsheet without getting into the details; get a sense of the overall soundness of the workbook and the risks it holds.
The activities you carry out in this phase are crucial because they show whether the spreadsheet is structurally sound and provides the correct foundations for modelling.
OAK Summarize is an excellent tool for reviewing and interpreting a model; the initial reports give you a general understanding of the reasoning behind the spreadsheet’s structure, layout, and formulas.
The data review is where you check the inputs within your spreadsheet against external documentation. It is essential during this phase that you don’t assume anything. It can also be a good point to test inputs to ensure there aren’t any substantial flaws that might indicate a more significant problem elsewhere.
You must review the spreadsheet outputs separately to determine whether they appear correct.
It is best to focus on how logically sound your data is. Does it look sensible? Do the results look plausible? If necessary, add checks that raise a red alert in response to a ratio deviating from the intended range. Some organisations have an optimism bias to check that the builder hasn’t been overly optimistic.
Charts can help make conclusions in analytical reviews more understandable. They can point to places, like some cells inside the spreadsheet, that might require further research by allowing you to spot new correlations or series of tendencies that either confirm or refute your expectations.
Analysing charts at the row level instead of the whole level is a great way to show additional information. For instance, examining income and expenses can reveal more complexity than just looking at profit.
When conducting a detailed spreadsheet review, you need to ensure the steps are proportionate to the context, scale and risk of the spreadsheet. The review can include thorough testing of the formulas within your spreadsheet and checking the design principles, such as external links.
To verify formulas, you can use our Formula Walker, which facilitates formula auditing and speeds up navigation.
After you have finished a review, you should make corrections, record your findings and send them to the appropriate parties involved.
Occasionally, you might have to carry out a new series of reviews on the spreadsheet. Spreadsheets used for budgets, management reporting, or strategy are likely to have a longer life cycle; as such, multiple changes may occur during their lifespan.
Therefore, following each review, we recommend you to:
