We have all seen spreadsheets which are overly complicated and reflect the inner workings of someone’s brain in finance and forecasting and when multiple people have access to the data and add their own calculations and formulae then it will not surprise us to know that spreadsheets can become error prone and in some cases misleading..
The industry standard is Excel, which is used extensively in public finance, energy and transport sectors where large projects require constant monitoring. Quite often the existing ERP system is ignored to provide this modelling and then the export routines into the spreadsheet become commonly used without really checking on the integrity of the underlying data.. Whilst there are extensive handbooks about ERP systems and firms that have invested heavily in training, the same cannot be said of the use of Excel where most learning has been done as we go along and find out about more of its functionality.
In essence a spreadsheet is another too for analysts and should carry a health warning because one tool may not fit all purposes because individuals make their own design and layout decisions based on their experiences and required outputs. Like most thigs in Accounting consistency is the key and simplicity should also be key criteria in spreadsheet design.
The issues that should be noted re as follows
Risk recognition within the formulae
Speed of information flow and unintended macros
Clarity on audit trail and integrity of underlying data
Version control and changes to underlying assumptions and individual cell attributes.
Techniques to avoid these issues are as follows
1 start with the output requirements and decide how the information can be presented as a basis for decision making
2Use consistent formulae
3 create clear and logical sections and easy navigation through links
4Clearly state assumptions and rules for user guidance
5 Bench test the model before publishing