Business Applications on Spreadsheets

Business applications on spreadsheets refer to the utilization of spreadsheet software for a variety of data analysis and management functions in a business context. Commonly used programs such as Microsoft Excel allow users to create graphs, charts, and reports, and perform statistical analyses, making them versatile tools for decision-making and financial management. Despite their popularity, spreadsheets are often criticized for their potential inaccuracies, which can pose significant risks, particularly in compliance with regulations like the Sarbanes-Oxley Act that mandates stringent controls over financial data. This has led to a growing industry for spreadsheet templates that offer ready-to-use solutions for various analytical tasks, from budgeting to inventory management.

Furthermore, the European Spreadsheet Risks Interest Group (EuSpRIG) and the Spreadsheet Standards Review Board (SSRB) work to address the inherent risks associated with spreadsheets by promoting best practices and standards for spreadsheet use. These efforts aim to enhance data accuracy and transparency, ultimately supporting better decision-making processes. As businesses increasingly seek more reliable data management systems, many are moving toward integrated solutions like data warehouses, although spreadsheets continue to be a mainstay due to their user-friendliness and adaptability. Overall, the evolving landscape of spreadsheet applications demonstrates their enduring role in business operations, alongside the necessity for ongoing improvements in quality and compliance.

Published in: 2021

By: Erbschloe, Michael

Subject Terms

Business Applications on Spreadsheets

This article examines how spreadsheet applications software is used as a business data processing tool. Problems with the accuracy of spreadsheets are examined along with the implications for compliance with various regulatory requirements such as Sarbanes-Oxley. The growth of the spreadsheet template industry is explained and the types of templates available for purchase are reviewed. The efforts of the European Spreadsheet Risks Interest Group (EuSpRIG) and the Spreadsheet Standards Review Board (SSRB) to improve the quality of spreadsheets are also reviewed.

Keywords Business Valuation Spreadsheet Templates; European Spreadsheet Risks Interest Group; Sarbanes-Oxley; Spreadsheet Errors; Spreadsheet Modeling Techniques; Spreadsheet Software; Spreadsheet Standards Review Board; Spreadsheet Templates; Subcontractor Information System

Business Information Systems > Business Applications on Spreadsheets

Overview

Spreadsheet software packages are widely used applications for analyzing and displaying data. Spreadsheets can help users develop graphs, charts, reports of financial data, or statistical analyses. Typical spreadsheet software has a wide variety of features including file creation and retrieval, report generation and printing, graphics, sorting, mathematical and statistical computing. The most widely used spreadsheet software packages are Microsoft Excel, Lotus 1-2-3, and Quattro Pro. Spreadsheet software packages can be used on almost any type of computer including personal computers, workstations, and mainframes and can run on virtually all the major operating systems (Chou & Gensler, 1993).

The functionality available in spreadsheets continues to be enhanced with some products now having capacities of one million rows by 16,000 columns. In addition, 3-D, soft shadowing and transparency effects help to spiff up charts and graphs. Conditional formatting features allow users to highlight cells with colors and icons to spot trends in data (Baig, 2007).

A critical part of an MBA's education is learning how to build business models that are adaptable to real-life situations. It's particularly useful for students to learn spreadsheet modeling techniques, which allow them to make mathematical models of relatively unstructured business problems and organize them via a spreadsheet. Such models help them think through a problem and even determine what data will be most useful to solve problems (Powell & Shumsky, 2007).

Spreadsheets can aid in analytical tasks that range from the simple to the very complex. Analysis of tabular data is a rather straightforward and simple use of spreadsheets. Decision support processes, on the other hand, involve the analysis of business intelligence using problem-specific methodologies. The purpose of an SDSS is to solve an unstructured problem in a business environment. Therefore, knowledge of procedures for problem solving is critical in the process of building an SDSS. Knowledge of the decision-making process is also critical in performing decision selection (Chou & Gensler, 1993).

Problems with Spreadsheets

Spreadsheets may be a liability in the Sarbanes-Oxley era because they could hold important corporate financial data. The Sarbanes-Oxley Act of 2002, also known as the Public Company Accounting Reform and Investor Protection Act of 2002 and commonly called SOX, is a United States Federal Law. The law requires, in part, that corporations certify the internal control of financial information and data and retain data relating to the financial management of the company. In simple terms, this law requires that the spreadsheets used in financial management or decision making need to be audited for adequacy of controls and accuracy of information. The law also requires that copies of the relative spreadsheets be retained.

Various studies report that 47% to 64% of companies use stand-alone spreadsheets for planning and budgeting. Although spreadsheets were originally invented as a personal productivity tool, they may not be well suited to collaboration, data quality or regulatory compliance. Spreadsheets often contain substantial errors. As corporations seek consolidated planning and financial reporting, spreadsheets pose challenges not dreamed of when they were first used on personal computers.

Many company spreadsheets have errors, and surveys have shown that up to 91% of the spreadsheets used by large organizations had errors. The mistakes range from mechanical errors (such as pointing to the wrong cell when entering a formula) to logic errors (such as entering the wrong formula) when setting up the spreadsheet. Washington-based Fannie Mae, for example, made a $1.2 billion accounting error because of what it called honest mistakes made in a spreadsheet used in the implementation of a new accounting standard (Horowitz & Betts, 2004). In 2012, a formula error resulted in traders at J. P. MorganChase undervalueing risk by 50% and contributed to a loss of $6.2 billion (Herbert, Lowth & Buckner, 2013).

To some extent, the criticism, or demonization of spreadsheets, may come from software producers pushing their own, more expensive financial software such as business performance applications packages. Although spreadsheets have significant shortcomings, they provide the benefits of usability, analysis, and presentation graphics. Most observers say that spreadsheets will be around for the foreseeable future.

Many IT departments are embracing new business intelligence tools that aim to forge a balance between strident user demands for spreadsheets and corporate requirements that financial data be consistent and accurate. This includes spreadsheet development environments for building enterprise-class systems with customized user interfaces and strong management controls. To maintain an audit trail, automated write-back systems are deployed that update central data stores so transactional systems reflect user changes. In instances where data should not be changed, system lock down methods are being implemented (Havenstein, 2005). In addition, many companies are gradually moving away from using spreadsheets and are adopting data warehouse systems that combine several database sources together and have sophisticated business analysis tools to support users (Horowitz & Betts, 2004).

Regardless of how the control of spreadsheets and spreadsheet data is handled, the Sarbanes-Oxley legislation requires that the IT Department conduct risk assessments to determine the type of controls necessary and to implement an appropriate level of control for spreadsheets. This includes establishing policies and procedures that assure necessary actions are taken to address risks. Information systems auditing processes, in turn, must test to determine if controls for spreadsheets are adequate and properly implemented.

Spreadsheet Templates

Although many people benefit from using spreadsheets, they do not have sufficient skills or enough time to actually create a reliable spreadsheet. A spreadsheet template industry has emerged that provides off-the-shelf ready-to-use spreadsheets for a wide variety of management or analytical activities. Most sellers of spreadsheet templates tout reliability and accuracy of their products. The templates vary in price and typically cost between $100 and $200. Applications that templates can support include:

Bond yield calculation;
Customer invoicing;
Event planning;
AInventory management;
Investment and business valuation;
Multiple regression analysis and forecasting;
Personal finance;
Portfolio optimization, monitoring, and valuation;
Project planning and management;
Real option valuation.

Business valuation templates can be designed to accept information from publicly-available 10-K reports in order to calculate the total and per-share value of a business. Valuation models can include a basic income statement and balance sheet, a cost of equity calculator, and a detailed ratio analysis. This type of template often allows the user to vary their analysis based on forecast of items such as the expected return on the overall investment or expected new investments. Changes made to one part of the model can automatically flow through to the other parts modifying the projected per-share valuation of the company.

Spreadsheet templates are also available for inventory management that allow small business owners to track inventory and view automatic alerts when reordering is necessary. This often involves the ability to pre-define reorder levels and customize reorder levels. When the number of units of an item currently in stock, falls below a certain number or pre-defined percentage, alerts can be generated.

There are also a wide variety of spreadsheet templates available in the realm of freeware. These templates were created by individuals, small companies, or organizations and are made available to users on a no charge basis. Some of the free or low-cost software available online suffers from design deficiencies that make it difficult to install or uninstall, and may cause problems with operating systems. Other software downloads may come packed with adware that bombards you with marketing pitches (Mainelli, 2002). Freeware does take caveat emptor to a whole new level, though. You're not a buyer, so whatever problems you encounter are often yours, not the developer's. If free software comes with tech support, it's usually through online forums, and many freeware apps are plagued with bugs (Steinhart, 2005).

Applications

Spreadsheets to Manage Construction Work

Spreadsheets can been used to develop computer-based modeling tools to help manage a wide variety of companies. One way spreadsheets can be used to help manage construction companies is by employing a simplified spreadsheet-based Subcontractor Information System (SIS). The SIS design involves the setup of several data lists, establishing relationships among them, and designing related reports.

In this model, resource data lists are stored in different worksheets for labor, equipment, crews, material, and subcontractors. These resource lists are referred to in the methods sheet, which defines alternative construction methods for various tasks. Once the general spreadsheets of the resources and construction methods are filled with data (ideally using the company's past records), they are ready for use in any project. The worksheets are as follows:

Labor. The labor list contains five fields: Code; description; total hourly rate; basic hourly rate; and availability constraints.
Equipment. The equipment basic rate includes rental cost and operating costs such as fuel, oil, and routine maintenance. An average of 10% is often added to the basic rate for overhead to calculate the rate per hour.
Crews. Crews are defined by assigning up to five labor and equipment resources, using their codes as reference.
Materials. The material basic unit cost includes delivery to the site, including the sales tax or allowance for wasted material. An average of 10% is often added to the basic unit cost for overhead to calculate the cost per unit.
Subcontractors. This sheet defines various subcontractors, providing their unit cost for the required tasks. An average of 10% is often added to the subcontractor's rate to calculate the cost per unit for subcontractor's work.

Various methods of construction are defined in a separate worksheet which defines the resources used in each construction method (crews and material, or subcontractor), the overtime strategy they use, daily production rate, and assumed seasonal productivity factors for winter, spring, and fall. Several business rules can be established to calculate productivity and costs:

Normal workday is eight hours.
The hourly rate of the first four overtime hours is 1.2 of normal rate.
The hourly rate of the overtime hours over the first four hours is 1.5 of normal rate.
An overtime hour has 90% productivity of a regular hour.
Different methods can have different overtime and/or resources.

Cost estimating is one of the most important functions to any contractor, as it is the basis for bid proposals, procurement plans, and job cost control. Construction estimating is a complex process due to the many interactions involved and the absence of standardization of conditions. Special care has to be given to cost estimation in order to be able to win a job and maintain a fair profit. One of the main functions of the SIS is to establish a realistic and automated estimating and cost control system utilizing the resource and method lists described previously.

Different levels of reports that summarize the data stored in the system become essential for supporting a project and for identifying important trends that can be used as the basis for corrective actions. Helpful reports can include a bid proposal, summary of methods of construction, and unit price deviations in contracts (Hegazy & Ersahin, 2001).

Issue

Developing Standards for Spreadsheet Design

Research has repeatedly shown that an alarming proportion of corporate spreadsheets are not tested to the extent necessary to support Directors' fiduciary, reporting and compliance obligations. Uncontrolled and untested spreadsheet models therefore pose significant business risks. These risks include lost revenue, poor decision making due to prevalent but undetected errors, and difficulties in demonstrating fiduciary and regulatory compliance. To counter these risks and address the pertinent management issues, the European Spreadsheet Risks Interest Group (EuSpRIG) holds annual conferences to discuss problems with spreadsheets and their solutions (EuSpRIG, 2007).

Studies from Coopers & Lybrand, KPMG, NYNEX, University of Hawaii and University of Michigan have revealed staggering error rates in spreadsheets. Although these studies assert that nine out of every ten spreadsheets contain errors, spreadsheets are still the choice of four out of ten engineers for doing calculations (Randles, 2006). Problems have been found in many well-known companies. At Rolls-Royce, for example, engineers faced problems when using spreadsheet programs not explicitly designed for engineering calculations and did not readily identify the errors. Several instances were documented in which a popular spreadsheet program returned wrong answers and problems worsened as size of a data set increased (Thilmany, 2005).

The use of spreadsheets is certainly increasing and many organizations develop their own spreadsheets or use a commercially supplied template. As the use of spreadsheets for important financial or forecasting activity increases, users need assurance of reliability and accuracy. The Spreadsheet Standards Review Board (SSRB) develops and maintains the Best Practice Spreadsheet Modeling Standards. The standards are publicly available at no charge.

The SSRB meets periodically to evaluate and consider proposals to add, delete or modify the Standards that are submitted to the SSRB by any party. The SSRB also invests significant resources into comprehensively analyzing every aspect of spreadsheet development, maintenance and usage in order to establish universally applicable standards and conventions for spreadsheet modeling activities. The primary objectives of the Standards are to:

Improve quality and transparency;
Ensure user-friendliness;
Decrease spreadsheet model development time and cost;
Minimize risk of errors;
Allow efficient sharing of model development methodologies;
Prevent model redundancy;
Align model developers and model users.

The Standards are rapidly gaining acceptance from banks, advisors, governments and large companies. By adopting the standards, organizations initiate a process of ongoing improvement in the efficiency and quality of financial analytics used for decision-making. The standards are comprehensive and cover every area of the spreadsheet development, maintenance and usage process. The standards are split into 16 spreadsheet modeling areas:

General concepts;
Workbook structure;
Sheet structure;
Formats and styles;
Assumption entry interfaces;
Sensitivity analysis;
Outputs and presentations;
Calculation formulae;
Naming principles;
Time series analysis;
Error checks;
Printing and viewing;
Multiple workbooks;
Security and protection;
Visual basic programming;
Miscellaneous.

Any party may participate in the further development of the Standards by submitting a proposal form to the SSRB. In order for a standard or convention proposal to qualify for inclusion in the standards, it must contain universally applicable methodologies or approaches to spreadsheet development, maintenance and use. The proposed standard must also reduce the likelihood of errors or mistakes in spreadsheets (SSRB, 2007).

Conclusion

Spreadsheet software packages have become widely used tools for analyzing and displaying data. Spreadsheets can help users develop graphs, charts, reports of financial data, or statistical analysis. Typical spreadsheet software has a wide variety of features, including file creation and retrieval, report generation and printing, graphics, sorting, mathematical and statistical computing. A critical part of an MBA's education is learning how to build business models that are adaptable to real-life situations.

Spreadsheets can aide in analytical tasks that range from the simple to the very complex. Analysis of tabular data is a rather straightforward and simple use of spreadsheets. Decision support processes, on the other hand, involve the analysis of business intelligence using problem-specific methodologies. However, various studies report that 47% to 64% of companies use stand-alone spreadsheets for planning and budgeting. As a result of these problems, spreadsheets may be a liability in the Sarbanes-Oxley era because they could hold important corporate financial data.

To eliminate the problems with spreadsheets, many IT departments are embracing new business intelligence tools that aim to forge a balance between strident user demands for spreadsheets and corporate requirements that financial data be consistent and accurate. This includes spreadsheet development environments for building enterprise-class systems with customized user interfaces and strong management controls. To maintain an audit trail, automated write-back systems are deployed that update central data stores so transactional systems reflect user changes. In instances where data should not be changed, system lock down methods is being implemented. Other companies are gradually moving away from using spreadsheets and are adopting data warehouse systems that combine several database sources together and have sophisticated business analysis tools to support users.

Spreadsheets are likely to remain popular and a spreadsheet template industry has emerged that provides off-the-shelf ready-to-use spreadsheets for a wide variety of management or analytical activities. Most sellers of spreadsheet templates tout reliability and accuracy of their products. The templates vary in price and typically cost between $100 and $200.

There are also efforts to establish standards for spreadsheets. The European Spreadsheet Risks Interest Group (EuSpRIG) holds annual conferences to discuss problems with spreadsheets and their solutions. The standards set by the Spreadsheet Standards Review Board (SSRB) are rapidly gaining acceptance from banks, advisors, governments and large companies. By adopting the standards, organizations initiate a process of ongoing improvement in the efficiency and quality of financial analytics used for decision-making.

Terms & Concepts

Business Valuation Spreadsheet Templates: A spreadsheet template designed to calculate various aspects of business value including benchmarks such as the total and per-share value of a business or the cost of equity.

European Spreadsheet Risks Interest Group: An interest group of academia and industry promoting research regarding the extent and nature of spreadsheet risks, methods of prevention and detection of errors and methods of limiting damage (EuSpRIG, 2007).

Sarbanes-Oxley: The Sarbanes-Oxley Act of 2002, also known as the Public Company Accounting Reform and Investor Protection Act of 2002 and commonly called SOX, is a United States Federal Law designed to improve integrity in corporate management.

Spreadsheet Errors: Factors that have the ability to alter/compromise the information being placed in and gleaned from spreadsheets. Mistakes range from mechanical errors (such as pointing to the wrong cell when entering a formula) to logic errors (such as entering the wrong formula) when setting up the spreadsheet.

Spreadsheet Modeling Techniques: In relation to business, spreadsheet modeling techniques allow them to make mathematical models of relatively unstructured business problems and organize them via a spreadsheet. Such models help them think through a problem and even determine what data will be most useful to solve problems.

Spreadsheet Template: A spreadsheet template is an off-the-shelf ready-to-use spreadsheet designed to support specific management or analytical activities.

Spreadsheet Software: Widely used applications for analyzing and displaying data.

Spreadsheet Standards Review Board: Body that develops and maintains the Best Spreadsheet Modeling Standards; the highest professional spreadsheet modeling standards that are publicly available in the world (SSRB, 2007).

Bibliography

Baig, E. (2006, November 30). Microsoft Office makes encouraging strides. USA Today, 6B.

Chou, D., & Gensler, P. (1993). Using spreadsheets to teach decision support systems in business schools. Journal of Education for Business, 69, 116-120. Retrieved September 17, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=9402017609&site=ehost-live

European Spreadsheet Risks Interest Group (EuSpRIG) Website. Retrieved September 24, 2007, from http://www.eusprig.org

Havenstein, H. (2005). Tools clamp down on spreadsheet abuse. Computerworld, 39, 72-72. Retrieved September 25, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=18596804&site=ehost-live

Hegazy, T., & Ersahin, T. (2001). Simplified spreadsheets solutions. I: Subcontractor information system. Journal of Construction Engineering & Management, 127, 461. Retrieved September 17, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=5905121&site=ehost-live

Herbert, I., Lowth, G., & Buckner, E. (2013). Spreadsheets under the spotlight. Management Services, 57, 36-38. Retrieved November 15, 2013, from EBSCO Online Database Business Source Complete. http://search.ebscohost.com/login.aspx?direct=true&db=bth&AN=90522103&site=ehost-live

Horowitz, A., & Betts, M. (2004). Spreadsheet overload? Computerworld, 38, 46-47. Retrieved September 27, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=13459188&site=ehost-live

Leon, L., Kalbers, L., Coster, N., & Abraham, D. (2012). A spreadsheet life cycle analysis and the impact of Sarbanes–Oxley. Decision Support Systems, 54, 452-460. Retrieved November 15, 2013, from EBSCO Online Database Business Source Complete. http://search.ebscohost.com/login.aspx?direct=true&db=bth&AN=83296002&site=ehost-live

Mainelli, T. (2002). Disasterware? PC World, 20, 22-24. Retrieved October 1, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=6646085&site=ehost-live

Powell, S., & Shumsky, R. (2007). Covering the spread. BizEd, 6, 46-50. Retrieved September 25, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=24530930&site=ehost-live

Randles, C. (2006). The hidden cost of mismanaging calculations. Manufacturing Engineer, 85, 28-29. Retrieved September 17, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=21066934&site=ehost-live

Steinhart, M. (2005). Free software: Good, for nothing. PC Magazine, 24, 76-77. Retrieved October 1, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=18391036&site=ehost-live

Spreadsheet data manipulation using examples. (2012). Communications of the ACM, 55, 97-105. Retrieved November 15, 2013, from EBSCO Online Database Business Source Complete. http://search.ebscohost.com/login.aspx?direct=true&db=bth&AN=78357200&site=ehost-live

The Spreadsheet Standards Review Board (SSRB) Website. Retrieved September 24, 2007, from http://www.ssrb.org

Thilmany, J. (2005). Don't trust that tool. Mechanical Engineering, 127, 16-167. Retrieved September 17, 2007, from EBSCO Online Database Academic Search Premier. http://search.ebscohost.com/login.aspx?direct=true&db=aph&AN=18790636&site=ehost-live

Business Applications on Spreadsheets

On this Page

Subject Terms

Business Applications on Spreadsheets

Business Information Systems > Business Applications on Spreadsheets

Overview

Problems with Spreadsheets

Spreadsheet Templates

Applications

Spreadsheets to Manage Construction Work

Issue

Developing Standards for Spreadsheet Design

Conclusion

Terms & Concepts

Bibliography

Suggested Reading