Estimating errors: seven spreadsheet limitations

Spreadsheets were first developed in the 1960s but their practical use on computers only became feasible in the 1980s. In 1985, Microsoft Excel launched and quickly became the dominant spreadsheet application due to its simple UI, useful features and OS compatibility — a position it enjoys to this day.

While electronic spreadsheets initially provided an easy-to-use alternative to paper-based methods, increased functionality and popularity over the following decades led to adoption for a wider range of uses. However, spreadsheet applications are not the optimal solution for many job requirements — particularly for cost estimating on major projects, where multiple internal and external parties have responsibility for separate project elements. The lack of support for collaborative input risks one or more parties being oblivious to anomalies, meaning a simple data error could lead to budget discrepancies that pass undetected.

The following functional limitations illustrate how using spreadsheets for estimating can introduce new issues and threaten the integrity of project costing data.

1. Lack of automation and real-time updates

Cost estimates for large projects involve many interdependent components, so a change to one can significantly impact others. Spreadsheets lack the functionality to ensure any such change ripples through to update data in all other affected areas, leaving projects open to timing and cost errors. Limited reporting capability makes it difficult to reconcile the current project status if data or structure changes are made outside initial parameters.

2. Lack of tracking functionality

A cost estimate is a living financial information resource. Data and formulae will likely be updated to reflect changes in a project and those adjustments must be tracked to ensure overall integrity and accuracy. Spreadsheets rarely incorporate these functions and changes are often invisible or not easily tracked, leading to potential for confusion.

3. Collaboration not supported

Major project cost estimating involves input from various people with close knowledge of specific project aspects. This means multiple parties need to generate, update and collaborate. Spreadsheets don’t easily permit multiple users to simultaneously work in the same sheet. While some cloud-supported alternatives do support this functionality, the accuracy of reflected changes can suffer due to a lack of real-time tracking.

4. Limited auditing and security capabilities

When working with sensitive data, like project estimates, it’s crucial to ensure only authorised parties can make changes. In some cases, there may be a need for hierarchical access and approvals restrictions, a feature not available in most spreadsheet alternatives. While many provide password protection, data is still vulnerable to manipulation by anyone with knowledge of the password, regardless of position or seniority.

5. Sharing is a challenge

The difficulty in providing granularity of read and write access to spreadsheets creates problems when estimates need to be shared with external parties, such as vendors and contractors. The only way to protect the integrity of the estimate is to create a copy of the workbook, which can quickly become outdated as it doesn’t reflect subsequent changes to the main worksheet.

6. Lack of documentation and quality assurance

Using a spreadsheet-based workbook to estimate costs is complex. Without rigorous documentation and quality assurance processes in place, the integrity of the workbook can easily be lost. Users are dependent on the original creator, which can lead to other problems if they try to adapt it to changing requirements without fully understanding its structure and functions.

7. Lack of status monitoring capability

Current status details should be available to all stakeholders in any project — major or otherwise. Spreadsheet solutions don’t offer a central repository for the real-time information needed to enable status monitoring and updates.

When compared with alternative solutions, spreadsheets lack much of the functionality required to effectively estimate costs on a major project, which can easily lead to expensive errors. Incorrect or outdated information, limited collaboration capability and a lack of auditing functionality all contribute risk. To avoid these issues, seek alternative solutions that can offer features designed to deliver a more accurate outcome: real-time updates, effective collaboration, secure workflows, efficient data handling, comprehensive reporting and centralised project monitoring.