Productivity software

Use and Misuse of Spreadsheets

If I had to name just one application that elevated the personal computer from a hobbyist device to an essential business tool, it would be the spreadsheet. This happened almost three decades ago, when VisiCalc became the first killer app for the Apple II computer.

Ideally suited for developing budgets, VisiCalc was the key that opened the door for personal computers in many finance departments. Apple's newfound presence in businesses convinced IBM it had to develop a crash plan for its own PC to stave off this new and cost-effective competitor.

VisiCalc was one of a mere handful of apps available at the IBM PC's launch in 1981. While it is no longer sold commercially, you can download this pioneering program for free from developer Dan Bricklin's Web site.

No Financial Panacea

But while spreadsheets are still extremely useful, they aren't the universal answer to a business's financial management problems. Furthermore, better data controls can help you can make more effective use of spreadsheets.

Today, many small businesses rely heavily on Microsoft Excel for most or all of their financial needs, from budgeting to tracking customer accounts receivable and calculating employee payroll. I have a soft spot in my heart for the venerable spreadsheet, but it's simply not the best tool to handle all the number-crunching that a business requires.

The widespread inappropriate use of spreadsheets in business reminds me of the tale of the poorly trained carpenter who was shown how to use a hammer to drive nails. Since he was familiar with the hammer, he used it for all his woodworking--even for breaking a board in two. Most people would agree that a saw is a more appropriate tool for that job. Similarly, while a spreadsheet is well-suited for some financial applications, it's a poor choice for others.

Flexibility Can Be a Drawback

When is a spreadsheet a poor choice? It's entirely too flexible for financial applications that need to follow specific rules. For example, I've seen too many payroll calculations performed on spreadsheets where it's too easy to make an error in income tax calculations and see no indication of the mistake. Furthermore, a spreadsheet makes it difficult to accumulate the net pay and deduction amounts necessary to prepare year-end tax forms and reports.

There's no excuse for relying on a spreadsheet alone when there are many easy-to-use and affordable payroll calculation applications. Read my "Outsourcing Your Payroll" column for a look at some good options.

Accounting Requires an Audit Trail

A spreadsheet is also a poor option for business accounting chores such as tracking the invoices you issue to customers, their payments, and outstanding accounts receivable balances. Among other shortcomings, a spreadsheet lacks an authoritative audit trail that shows who entered what data, and when. An audit trail is an important tool for identifying and correcting financial errors.

If, for example, a payment from Customer A is mistakenly entered into the account for Customer B, you can be sure that Customer A will eventually let you know about the mistake--but Customer B may not complain about the invoice you inadvertently wiped off their record. An audit trail would let you track the erroneous deletion of Customer B's invoice and reverse it.

Many professional accountants weren't fans of early versions of Intuit QuickBooks because the software made it too easy to change data without leaving a trace. Intuit subsequently addressed this issue by adding an audit trail capability and activating it as the default setting.

When to Use Spreadsheets

Spreadsheets remain king where flexibility and "what if" calculations are important--for example, in cash flow analysis, financial forecasting, and budgeting. However, there are ways to make spreadsheets even more useful, particularly if more than one person has a hand in their preparation.

Excel's collaborative capabilities can help track spreadsheet changes, for instance. You can guide others if you organize the spreadsheet so that data input is concentrated in one area. You can use Excel's protection tool to keep other users from making changes in important calculation formulas in the rest of the spreadsheet.

EasyVersionControl

Spreadsheets often progress through different versions. If you need something beyond Excel's built-in protections to prevent the use of obsolete versions of spreadsheets, consider using a third-party add-in or template. For example, EasyVersionControl is one of several useful Excel templates available from Business Spreadsheets.

With spreadsheets built from the EasyVersionControl template, you can easily distribute, share, and collect financial information. You can use a pop-up calendar to set the spreadsheet's version expiration date, which prevents users from viewing or using obsolete versions.

A right-click from any cell in the spreadsheet leads to a menu that displays the version number and expiration date. This makes EasyVersionControl well-suited for developing financial budgets, which typically require preparation of several versions before they're set in stone.

EasyVersionControl costs $50 for one administrator license and works with all versions of Microsoft's spreadsheet since Excel 97. A 30-day free trial is available.

Richard Morochove is an IT consultant and writer. Send him questions about using technology in your small to mid-sized business via e-mail. PC World may edit your query and cannot guarantee that all questions will be answered.

Subscribe to the Daily Downloads Newsletter

Comments