By Graham Machray
Spreadsheets remain the bedrock of many businesses of all sizes. They provide a powerful, flexible and ubiquitous toolset that helps business managers and owners simplify and manage complex business processes, projects and reports. They are essential to implementing the cost, revenue and project management controls any business requires.
Spreadsheet Risk: A Single Point of Failure
The power, flexibility and value of spreadsheets means that businesses become very reliant on them. This means that spreadsheets can become a single point of failure, if they are not managed effectively.
Spreadsheet risk can be defined as the impact on a business from problems caused by undetected errors, flaws and omissions in the key spreadsheets that a business relies on. These might cover invoicing, cost management or management decision-making for example.
In simple terms, spreadsheet risk is more likely if:
- There are multiple versions of a vital spreadsheet in use throughout the business.
- Spreadsheets are linked together, taking data from one or more spreadsheets to calculate results in third one.
- Spreadsheets make extensive use of complex formulas or functionality to calculate results.
- Spreadsheets take data from other business systems, such as order management, production management or accounting for example.
The business impact comes from small, unnoticed changes having material impact on critical business processes.
For example, an error in changing an interest rate, perhaps by using stale source data that a business utilizes, can have a significant impact on the cost and profitability of a project, and potentially the viability of the business. Alternatively, price changes might not be properly captured in quotation spreadsheets, meaning that a company loses revenue, or issues uncompetitive proposals.
The power and flexibility of spreadsheets means they are central to the success of all businesses. To maximize their value, businesses need to know to how mitigate the risks involved in using them.
10 Ways to Avoid Spreadsheet Risk
Here are 10 practical steps to avoid getting hurt by spreadsheet risk.
- Prioritize Your Spreadsheets: Understand which are the most critical spreadsheets to your business. What would be the impact if there was a problem with any of them?
- Get Training: Make sure the members of staff who create them are suitably trained in creating, using and managing their spreadsheets.
- Use the Right Tools: Make sure that spreadsheets are the best tool for the job. Spreadsheets are ideal for managing and manipulating large volumes of structured data. Word, PowerPoint or project management applications might be better for some situations. Using the wrong tool for the wrong job can create problems for the business.
- Get Feedback: Understand how a spreadsheet is going to be used by its users, and test it with them to get feedback.
- Focus on Results: Understand the results you need from a spreadsheet, and then work out how the data, formulas and the output will work together.
- Create a System: Be consistent in the way you use formulas, so spotting errors becomes easier.
- Keep It Simple: Keep formulas simple and short; avoid hard-coding results into a spreadsheet.
- Back Up Your Data: Protect your spreadsheets by backing up your most critical data on a regular basis.
- Track Changes: Manage version control closely; retire spreadsheets once they are out of date.
- Protect Your Files: Lock those cells that you do not want changed.
Spreadsheets are a uniquely powerful and flexible business tool that drive the growth of any business. Managed effectively, using these guidelines, they let you focus on the development of your business, rather than solving preventable problems.