12 Oct Budget Expectations and Spreadsheets
It’s Annual Budget time this week in Ireland, and much drama and pageantry is played out. In many ways a budget from government is an exercise in managing expectations. The same is true perhaps in a practical sense with spreadsheets.
“How is that result arrived at?” “Where did that come from?” “I thought red meant credit entries”.
Do any of those sound familiar? If so, you’ve encountered the problem of violated expectations in spreadsheets. Most users know how their own spreadsheets work, unless since they last used it enough time has lapsed for them to forget something. But when using workbooks for communication with others, particularly those who need to use the spreadsheet, there is no room for uncertainty. Assumptions need to be clearly shown on an input or parameters sheet; and never buried as magic numbers in formulas.
The source of data needs to be known, and its date of update, to allow for reconciliation with those other sources.
Where there is any opportunity for someone to misunderstand a calculation method, for example when there are several ways to get the same result, make it clear which method is being used. Otherwise, someone could put in a “correction” intended for a different method.
If you are using specialist financial functions, be sure that their algorithm is explained to any user not intimately familiar with them.
Format for communication, not decoration. Good user design and formatting conveys meaning and enhances readability. Where several people are involved, these conventions need to be agreed so that everyone is singing off the same hymn sheet. Every non-trivial spreadsheet should have an instructions tab that details its standard operating procedure and what checks should be applied to it to add confidence to its results.
If you ever receive a spreadsheet with these principles applied, say thank you to the originator for making your job easier and saving all of you from potential confusion and embarrassment!
Patrick O’Beirne @ExcelAnalytics
Spreadsheet Safe Programme
126.96.36.199 Set out any conventions used, such as calculation methods or functions, the meaning of formatting and styles.