DATE: Fri 22 Jun 2012
BY: Stephen Aldridge
The way you build your spreadsheet model can dramatically affect the time and cost of having it reviewed – read on to find out how:
If a spreadsheet model is being used as the basis for a financial decision, modelling aware companies will subject the model to a thorough review to identify and correct logic errors. It is essential to do this before relying upon the figures it produces – the risk of error in spreadsheets is the subject of much academic research and is well known. If a company doesn’t think this is important, any banks involved certainly will.
Obviously, a professional firm will charge a fee to do perform a review, but there is a lot you can do to reduce the cost.
A couple of models we reviewed recently, one small, one quite large, contained 90,000 and 500,000 formulae respectively. Clearly it would take months or even years to look at each of these, which is partly why there is a widely accepted principle amongst professional modellers, that formulae should be the same across a row (see our whitepaper on modelling SCILS). If all formulae on a row are identical, we only need to check one formula per row – in a five year monthly model, that divides the total formulae by 60, getting down to a few thousand formulae. As a rule of thumb, formulae typically take one to two minutes to review (depending on complexity).
If you are constructing a model, the best way to minimise the cost of a review is to build it with as much consistency as possible. This means keeping the formulae the same across the row (as discussed above) but it is often also possible to construct a formula that works for several rows as well. You may also be able to make several blocks of repeated calculation or even entire sheets identical, to deal with departments, divisions or business units. Model reviewers will have software that can compare blocks and sheets, so only one of these identical sheets needs to be tested.
Beware though; two identical blocks of calculation or sheets for two business units will need different inputs. If the inputs are on another sheet, the formula referring to the inputs will be different if you use a normal cell reference – if one business unit’s inputs start on row 10 and the other on row 20, the formulae on the calculation sheet will be =input!H10 and =input!H20 respectively and will register as being different when analysed with spreadsheet review software.
It is possible to construct identical formulae to pick up the correct input, so the calculation blocks or sheets will be identical and the reviewer need only look at the exceptions on each sheet, thereby reducing time and cost. A few key functions make this possible – they include INDEX, MATCH and OFFSET. We will be posting videos of how to use these functions on our website, but if you really want to know how to reduce the cost of your reviews, you could attend one of our training courses that go into these things in detail.
If you would like to know more about keeping your costs down with modelling, please call us – all the details are on our contact page.
Stephen is a Chartered Management Accountant and has over ten years of financial modelling experience both at KPMG and Deloitte. His early career included engineering, sales and corporate management roles. In 2004, Stephen joined Numeritas as a co-owner and a Managing Director.
We love to hear what you think. please note that comments are moderated so there might be a slight delay. Your email address will not be published.