The Power of Spreadsheets

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.

WHAT’S NEW?
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.

Exhibit 1

SEIZING OPPORTUNITIES
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.

Basics

• 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).
• Text processing functions: LEFT (extracts a set number of characters from the left side of the text in a cell), RIGHT (extracts a set number of characters from the right side of the text in a cell), FIND (provides case-sensitive search for words or numbers), MID (extracts characters from a string set) and & (joins several text items into one text item).
• Menu options: Copy, Cut, Paste, Paste Special, AutoFill (if you type January, AutoFill will automatically fill in the remaining 11 months), Find, Insert, Delete and Replace .
• Print formatting: headers, layout and sizing.
• Charts: pie, bar, line and column.
• Formulas: use of references to cells on other workbooks.
• Integration: copying spreadsheet data into the word processor.

Exhibit 2

Advanced
When used to produce reports, spreadsheets can apply features such as filters and outlines to save time, produce better results and allow “drill down” analysis of summary data to obtain the underlying source data. Filtering is the process of selecting data that match criteria. Both Excel and 1-2-3 can filter data by hiding the rows of information that are not selected. They also can copy the selected rows to a new worksheet. Excel provides an easy-to-use AutoFilter feature that adds a filter drop-down list to each column on the worksheet.

A vital function for filtered reports is SUBTOTAL. While the familiar SUM function totals all cells in the cell range, SUBTOTAL totals only the visible cells. The SUBTOTAL function also is easier to use than SUM when nesting totals.

Outlining features can be used to provide summaries of sorted data. In an outline, detail rows can be hidden so only the total lines are displayed. Outlining is new to 1-2-3 but has been available in several releases of Excel. Excel also provides an option that automatically adds SUBTOTAL formulas and an outline in one step. Once data are outlined, reports can be produced at any level of detail.

Outlining also provides a way to present summary data and then drill down to the detail behind a total. For example, a spreadsheet outline that shows totals by division and contains detail by location makes it is easy for the user to display the location detail for a selected division. Drill down is a key component in developing executive information systems.

Another new reporting feature is publishing to the Internet. Microsoft and Lotus have made it easy to publish spreadsheet data to an Internet or intranet server so information can be made available quickly to any user with an Internet connection.

LOOKING AHEAD
Forecasting features include regression analysis, exponential smoothing, moving averages and all the statistics needed to measure how well the forecast line fits the trend of past results.

While regression analysis is a topic covered in every business statistics class, most spreadsheet users have found it intimidating. However, the new applications make such analysis easy by building it into the charting package. Once the data are charted, a regression trend line can be added with a couple of mouse clicks. In addition to the charted trend line, the spreadsheet also displays the equation for the line, a measure of the line’s fit and projections into future periods.

When choosing to use a linear, exponential, quadratic or polynomial regression line, the user simply selects a picture of the type of trend line that most closely resembles the data on the chart. As shown in exhibit 2, page 67, Excel offers an exponential smoothing and moving-average trend line in addition to the regression choices.

Sales forecasting is one activity where CPAs can add value with trend line analysis. Future sales are not always a function of past results, but a trend line based on past results can bring another perspective to an organization’s sales plan. Seasonal trends can be estimated by using the AVERAGE function to create a 12-month moving average, adjusting the actual data for seasonal fluctuations before using the trend line charting options.

For the statistically inclined, Excel provides a full set of statistical data analysis tools including descriptive statistics, analysis of variance and histograms.

Exhibit 3

WHAT IF
Both 1-2-3’s Views and Excel’s Scenarios provide the option of storing several different sets of values for cells on a worksheet. For example, a cost analysis worksheet can contain several sets of overhead allocation options. Choosing an option places a stored set of values into the worksheet automatically and displays the resulting cost analysis. As shown in exhibit 3, page 69, the latest release of 1-2-3 provides a display of available Versions on the worksheet and step-by-step instructions designed to make Versions easy to use.

Another advanced feature is the data table , which is an advanced what-if feature that allows evaluation of results based on a table of what-if values. For example, a data table can show net profit values that result from multiple levels of sales and fixed overhead.

DATA ANALYSIS TOOLS
CPAs can make good use of three advanced data analysis tools: cross tabulations, goal seek features and optimizers .

Microsoft has expanded the cross-tabulation feature in Excel, called the PivotTable . Lotus 1-2-3 provides cross-tabulation capability in conjunction with Approach, the LotusSuite database program.

Using Excel’s PivotTable , shown in exhibit 4, page 70, an accountant with a list of sales by product, customer, salesperson territory, office, division and week could analyze from multiple views to spot trends. The PivotTable’s built-in features make it easy to change the sales analysis to product by month or even product by division for each month. A built-in drill down capability allows the PivotTable user to display the detail behind each total in the table. Changing the type of analysis is as easy as dragging field names to a different location on the worksheet.

Excel calls its goal geek feature Goal Seek ; for Lotus the name is Backsolver . Despite the name difference, the feature is very similar. Given a worksheet with values and formulas, the feature finds the values that produce a certain result. For example, a Goal Seek or Backsolver can find the sales increase necessary for a 2% increase in return on equity.

Optimizers are used to maximize or minimize a value based on a set of rules. For example, you can order the program to maximize profit given that sales cannot exceed $10 million and cost per unit increases at such-and-such rate per thousand units produced. The feature is called Solver in both Excel and 1-2-3.

Exhibit 4

DATABASES
Today’s spreadsheets can double both as spreadsheets and as simple database programs. This shared function saves time and provides quick access to an easy-to-use database. Built-in lookup and database formulas add function to spreadsheet databases. The newest version of Excel can store up to 65,000 “records” or actual rows of data. With this capacity, users can handle most small database applications entirely in Excel. Both Excel and 1-2-3 offer three methods to make it easy to get data into the workbook.

The first is a built-in data-entry form that displays each row of data in a pop-up dialog box. Buttons in the dialog box make adding, changing or deleting records easy. The import feature is a second method. In Excel, this feature is supported with step-by-step instructions that guide the user through all the decisions necessary to assign imported data to individual columns in a worksheet. Import in both 1-2-3 and Excel works with dBase files and comma-delimited files as well as text files.

If the data already exist in another database program, the new spreadsheets provide a third method of getting data into the workbook with database query , which makes it easy to trade data back and forth between the spreadsheet and a database-especially if the two are part of a suite of applications-such as Microsoft Office or LotusSuite. For example, 1-2-3 provides menu options that make its database partner, Approach, look as if it is built into the worksheet. Data query also is available to interface with SQL and most other database formats. Data query can be quite different from the import feature because a query can link tables of data together and apply selection criteria to bring just the right combination of data into the worksheet.

Table lookup functions such as VLOOKUP, INDEX, MATCH and the 1-2-3 XINDEX function come in handy when working with long lists of data. For example, in a database of monthly account balances, VLOOKUP can be used to find the account description based on an account number. This means that only the account number need be entered in the long list of monthly account balances. The VLOOKUP formula can use the account number to look up the description from a separate account master table. In addition to saving time on data entry, lookup functions allow the user to change the account description once, in the master table, and have the change applied to each detail line automatically.

An accounting example of this feature is a commission-calculation worksheet. Sales data are put in the spreadsheet database using data entry, import or a database query . Lookup formulas calculate commissions due based on percentages stored for each salesperson in a master worksheet. Once the commissions are calculated, the data can be exported to another accounting application or used to prepare reports in the spreadsheet.

DEVELOPING APPLICATIONS
In what many see as a revolutionary change, the inclusion of a built-in programming language allows spreadsheet software to become complete programming applications. Instead of calling in programmers to create an open order-tracking system, say, users can create a spreadsheet application that uses Visual Basic to automatically import order and shipping data. Using the features of the spreadsheet to match the order and shipment, the application can export shipped data to the accounting system’s accounts receivable module. And if the spreadsheet is part of a suite of applications, the application can print forms using the suite’s word processing program and store data in its database program.

In addition, because the product is programmable, users can define functions without resorting to complex programming. For example, a CPA who needs to calculate a markup based on a complex set of product characteristics can code a Visual Basic custom function named MARKUP that will return the proper markup percentage based on a product number. The function can obtain product information from a worksheet containing a product master list and the logic in the function can do the calculations. User-defined functions can be used in a worksheet just like the built-in functions (SUM, SUMIF, IF). The markup function could be used in an Excel formula like this: “=C2 * MARKUP(“12345”) where C2 is the cost and 12345 the product number.

MAKING LINKS
Both LotusSuite and Microsoft Office include state-of-the-art presentation packages that can prepare and present slide shows. Because the applications work so well together, it’s easy to move spreadsheet data into a presentation package, adding a presentation’s color, formatting and graphics to spreadsheet reports. In addition to preparing slides and overheads, the presentation packages do an excellent job with printed output-especially on color printers.

For example, a spreadsheet and presentation package can be linked for a combined presentation-using slides and spreadsheet data. The applications are so flexible that during the presentation assumptions can be changed on the spreadsheet and recharted. Microsoft’s presentation application, PowerPoint, provides an additional feature: Meeting notes can be recorded on a laptop during the presentation.

The newest versions of spreadsheets also provide enhanced mapping capabilities. Whether the task is mapping sales by sales territory or market potential by county, seeing the data charted on a map can be a useful decision-making tool. Spreadsheet and map can be linked so that when data are changed on the spreadsheet, colors or chart symbols change on the map. The set of maps that comes with the spreadsheet can be augmented with special-purpose and more detailed maps from third-party suppliers.

Accountants engage in the annual workgroup extravaganza called “budgeting.” All levels of management invest hundreds of hours in planning, forecasting, estimating and sending budget data from department to department. With the workgroup tools built into the new spreadsheets, accountants can produce more timely and accurate budgets while saving a significant amount of effort. For example, 1-2-3 provides TeamReview, a workgroup feature shown in exhibit 5, on page 72, that can route budget spreadsheets electronically to every participant. As changes are made and budgets reviewed, 1-2-3’s version manager keeps track of each change. Portions of the budget can be routed through various reviews; 1-2-3 will put the pieces back together for the final version automatically.

Excel provides an enhanced Shared Workbook , a workgroup feature that can route and keep track of revisions in a spreadsheet. Much like multiple revisions made by reviewers in a word processor, Shared Workbook’s spreadsheet revisions can be applied or rejected selectively.

ADD-ONS AND TEMPLATES

Instead of reinventing a spreadsheet for a common task, such as loan amortization, the new spreadsheets allow users to access ready-to-use templates with built-in formulas and formatting. Microsoft also markets a set of office solutions that work with Excel. Of particular interest to accountants is Microsoft’s Small Business Financial Manager, which can import data from many of the most popular accounting systems and analyze the data with Excel. The package can generate financial reports, analyze sales margin and generate interactive what-if reports to forecast the impact of changes in product mix, working capital, equipment, investment and other factors (see exhibit 6 , below).

Exhibit 5

LEARNING TOOLS
Both Microsoft and Lotus have gone to great lengths to make advanced features user-friendly by building intelligent assistants into the spreadsheets. In the latest version of Excel, an animated character provides helpful context-sensitive advice and answers questions as features are employed. Lotus has eliminated layers of confusing menus and dialogs by providing an InfoBox that presents the proper choices for the part of the spreadsheet in use at the time. For example, if a chart is active, the InfoBox presents charting options; if a cell is selected, the InfoBox presents formatting options.

Called Lotus Assistants in 1-2-3 and Wizards in Excel, these features are designed to lead users through a complex decision process-one choice at a time-on the theory that seeing the process is a much quicker way to learn than reading about the task.

With the advances in usability and the increase in power, today’s spreadsheets stand ready to help CPAs build valuable business models and computer systems. Whether the task is delivering innovative, value-added client services or creating business models and information systems, the new spreadsheets provide the tools accountants can use to improve the accounting process.

Exhibit 6

Free Spreadsheet Information
Readers interested in downloading free spreadsheets examples and tutorials may visit John Lacher’s Web site at http://www.lacher.com