This blog is offered to you by the Excel Community, where you can find additional blogs, extensive articles and webinar recordings on various Excel related topics. In addition to live training events, Excel Community members have access to a full range of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other members of the community.
Hello everyone and welcome back to the Excel tip of the week! This week we have a Basic User level message in which we look back on the history of our historical publication, the Twenty Principles for Good Spreadsheet Practice.
The history of the 20 principles
This document was originally drafted seven years ago, in March 2013, at the very first meeting of the Excel Community Advisory Committee, the volunteer group that helps steer ICAEW's work into the spreadsheet arena. At the time I was a volunteer on that committee – long before I became a member of ICAEW – and I remember the first conversations that led to the idea. The group – consisting of financial modellers, Excel trainers and other spreadsheet experts – had a first-hand view of the level of spreadsheet risk in the business world. Most users are usually self-taught in Excel and most work individually rather than in teams. Peer review of spreadsheets is rare. The work of the European Spreadsheet Risk Interest Group, EuSpRIG, has shown that spreadsheet errors are common and that people 's ability to make their own mistakes is low.
Accountants are closely associated with spreadsheets and with Excel, and the profession is often characterized as a spreadsheet-based. But there is no formal requirement for accountants to be competent Excel users, and it was (and is not) part of the ACA qualification – so it is quite possible to qualify as an accountant without knowing how to can use spreadsheet safely and efficiently. So the committee wanted to be wary in this area and make a statement: how everyone – not just accountants – should tackle the issues of spreadsheet risk and efficiency at the highest possible level.
The committee individually created their own list of suggestions, and serious work by my predecessor Paul Booth led to the preparation of a consolidated list of interfaces from those lists: the 20 principles.
An overview of the 20 principles
The 20P are divided into three groups, depending on the domain to which they apply.
The first group consists of principles that apply at organizational level to the use of spreadsheets in business processes:
- Determine the role of spreadsheets in your company and plan your spreadsheet standards and processes accordingly.
- Use a standard for your organization and stick to it.
- Ensure that everyone involved in creating or using spreadsheets has an appropriate level of knowledge and competence.
- Work together, share ownership, peer review.
In this section, we as a user are asked to consider spreadsheets as a serious business risk and to take into account the role they must play in our business practices. Spreadsheet risk is real and natural, so steps must be taken to address that risk. The above research shows that errors occur and that self-evaluation is not effective, so implementing assessment is essential. Spreadsheet skills are not automatically acquired and the hiring and / or training of staff suitable for their role must be taken into account (this question would ultimately inspire our second publication, the Spreadsheet Competency Framework).
Guidance when creating workbooks
The second and largest group of principles are specific risk management recommendations within the spreadsheet and best practices. First, some principles about whether or not to use a spreadsheet and how to appeal to the public:
- Before you begin, make sure that a spreadsheet is the right tool for the task.
- Identify the audience. If a spreadsheet is meant to be understood and used by others, the design should facilitate this.
- Take a sheet & # 39; About & # 39; or & # 39; Welcome & # 39; to document the spreadsheet.
Then some general design practices:
- Design for a long service life.
- Focus on the required outputs.
- Separate and clearly identify inputs, operations and outputs.
Some about consistency and simplicity:
- Be consistent in structure.
- Be consistent in the use of formulas.
- Keep formulas short and simple.
And finally some about avoiding errors and complexity:
- Never take anything that can change in a formula or that needs to be changed.
- Perform a calculation once and then refer to that calculation.
- Avoid using advanced functions where simpler functions can achieve the same result.
This entire block builds a layered understanding of what we should and should not do in the cells of the spreadsheet as we create it. Many of the principles are based on the desire to simplify the assessment and use of the spreadsheet by the end user and to reduce the chance of an error occurring and going unnoticed. Simplicity and intuitive design are also a recurring theme.
Guidance of workbook
The third and final group of principles is about filling in, testing and putting the spreadsheet into use:
- Provide a backup and version management system that must be applied consistently within an organization.
- Test the workbook rigorously.
- Install controls, controls, and alerts from the start and during spreadsheet design.
- Protect parts of the workbook that users do not need to change.
We ask ourselves to think about how we can know that our spreadsheet is error-free and how we can use protection and controls to ensure that it stays that way. In particular, testing (and peer review) is the most important step to improve the quality of the spreadsheet output.
The Twenty Principles for Good Spreadsheet Practice remain as valid and vital today as they were when they were first published – and they not only inspired the aforementioned Spreadsheet Competency Framework, but also our Financial Modeling Code. I encourage you to read it and distribute it to your colleagues and contacts – and hopefully we can all help to reduce the risk of spreadsheet errors!
Previous post – IFS, SWITCH
Next time – Visit number formats again!
. (tagsToTranslate) tip of the week (t) basic user (t) 20 principles (t) excel (t) minimize spreadsheet errors