The Power of Spreadsheets
Lacher, John, Journal of Accountancy
Before the first electronic spreadsheet, Visicalc, burst on the scene in the early 1980s, spreadsheet preparation was a slow, painful, manual job. Although Visicalc proved that the silicon chip was faster, more accurate and more versatile than paper and pencil, it wasn't until the IBM-compatible Lotus 1-2-3 reached the market a few years later that CPAs were able to toss away their columnar pads and whip up complex spreadsheets with a click of a button. The power of electronic spreadsheets continues to grow, revolutionizing the way accountants and others do business, but many CPAs fail to make full use of the software's sophisticated new features.
This article tells how CPAs can harness this power to develop business systems that would not have been possible just a few years ago. To illustrate what the tools can do, I used the latest Windows 95 versions of Lotus 1-2-3 and Microsoft Excel.
Until very recently, learning advanced features of the programs was difficult, mostly because the paths to them in the software generally were buried under long trails of menus and dialog boxes. But today's versions make these features easy to find and use.
Some examples of this trend are the auditing features of Excel, which display the relationships between all the formulas on a worksheet and help to identify spreadsheet formula errors. Excel's auditing features are only mouse clicks away. When users double click on a formula, relationships between cells are displayed with a colored diagram, as shown in exhibit 1, above.
Another outstanding feature of the new spreadsheets is speed. The Windows 95 versions of Lotus and Excel are designed to be much faster than previous versions.
The new spreadsheets also offer programmability, which allows the user to automate a spreadsheet task. In the past, spreadsheet "programs" were usually keystroke macros (processes that are simply the recorded keystrokes needed to activate a task), which, although powerful, were tricky to write, debug and document. While Excel and 1-2-3 continue to support the keystroke macro languages, they now provide a full-featured Basic programming language. Microsoft added the more powerful Visual Basic to Excel in 1994 and has improved it in the latest version to include everything needed to deliver fully automated applications. Lotus Script, the new Lotus 1-2-3 programming language, is very similar to Visual Basic. Some examples of what automation can do follow:
* A budget template can be automated with custom dialog boxes, buttons, list boxes and other controls so even someone unfamiliar with spreadsheets can add data to or get data from the file.
* Tasks such as order entry and executive information systems (providing an array of customized information) that once needed complex programming languages now can be implemented with spreadsheet software.
CPAs who want to use their spreadsheet programs as report writers can apply the new software's easy-to-use advanced features such as outlining, versions, forecasting, data analysis, importing data and charting to enhance budgeting, business modeling and analysis. And they can program complete applications to improve workflow and workgroup productivity. A controller, for example, can automate a business planning spreadsheet so an assistant with little knowledge of a spreadsheet program can input data and print reports.
The first step in learning to add value with spreadsheets is to look into all the key features of your product and then determine which can add productivity to your work. Since the features in Excel and 1-2-3 are similar, one general description usually fits both spreadsheets. Excel and 1-2-3 offer the following features for spreadsheet users.
* Cell formatting: fonts, colors and alignment.
* Commands: SUM (adds numbers in a column), SUMIF (adds the cells specified by a given criterion), COUNT (counts number of entries in a range of values), COUNTIF (counts the number of cells within a range that meets the given criterion), PV (calculates present value), FV (calculates future value), IRR (calculates internal rate of return for a series of cash flows), ROUND (rounds a number to a specified number of digits) and IF (returns one value if a logical test evaluates to TRUE and another value if it evaluates to FALSE). …