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
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.
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 entire section is 3939 words.)