Not Just For Accountants Anymore Taking a second look at DP uses for spreadsheets
From "VS Workshop",  Access to Wang, January 1992
[ Prior Article ]     [ Return to the Catalog of articles ]     [ Next Article ]

It is often true that a really good idea remains useful when applied to other disciplines. The remarkable spreadsheet is a prime example: among financial and consulting users, work would be unthinkable without spreadsheets and related PC analysis tools. Spreadsheets have altered the perspective of modern managers by presenting data in a row-and-column format and allowing direct control over the format and summarization of that data.

Though the same data presentation can assist in systems design and construction in many important ways, few DP types make use of these capabilities. As a recent disciple of spreadsheets myself, I certainly did not made early use of these powerful tools. Here are six reasons to use spreadsheets in your practice.

Reason One: Easier File Design and Documentation

Like everyone faced with the design of a new file, I have struggled through continued versions of the file description until arriving on the copy that contains all of the data elements in a logical order. After that exercise I get to sit with a calculator and determine the positions of each element for the related CONTROL file. If I don't dodge fast enough, I might even get the opportunity to trudge through a technical description of the file's elements and acceptable range. Well, no more!

Now all of my file descriptions begin as simple spreadsheet files that describe the elements of the file and show their position and internal length. Each row contains a single field element. A typical column arrangement might include the COBOL name, starting position, internal length, external length, element type, condensed name (for CONTROL files), acceptable value range, and comments. The starting positions for each field row are computed from the prior row's starting position and internal length, so rows can be changed and the effects noted. Through judicious use of row and column page breaks and fixed titles, each printed page contains the field name and logically grouped elements, providing good technical documentation.

Reason Two: Project Planning and Control

Every development project has some element of project control involved. While some important elements of project management - such as resource loading and dependent tasks - cannot be handled without more sophisticated methods, spreadsheets offer a means for easy time and task tracking and reporting.

The row-and-column arrangement of spreadsheets allow easy preparation of work breakdown structures, expected resource requirements, and related deadlines. The actual times can also be tracked, with graphic presentations of progress possible with some spreadsheet products. Spreadsheets are easily interchanged among project participants for updates and comments.

Reason Three: Time Reporting

If my guess is correct, about half of you in the DP field are required to report on the amount of time you spend on work activities. If you're like me, this is a grueling task that is undertaken only when the deadline (and your boss) looms. Well, spreadsheets don't make the process pleasant, but they might make it more accurate. My time tracking system employs the time-stamping feature of a spreadsheet product to register the start and end times and a description for any process. The columns include: date, time in, time out, description, and elapsed time. The elapsed time is computed using a fairly complicated formula and expressed in digital hours (e.g. six-minute increments). At the end of the month I sort by task and produce a summary by major category, including percentages of all working hours. Naturally, more detail is available if required. It still isn't fun but it works.

Reason Four: Tables

If you're using Wang Word Processing (or equivalent) for tables, you know what real pain can be. In the absence of a word processor with real table handling, I have found it helpful to use a spreadsheet for simple tables. The math elements are also helpful in performing the simple arithmetic that usually escapes careful editing prior to presentation to senior management. Again, the row-and-column arrangement of data makes it very easy to enter tabular information and keep it in view.

Reason Five: Performance Analysis

Gathering VS performance statistics is not easy, but providing meaningful performance reporting is often even more difficult. Those of us who use SAM (the System Activity Monitor) or program logging tools such as SMF (the System Management Facility, a VSAID) to gather numbers must now refer to the limited reporting systems that come with these subsystems.

My answer to this situation is to export summary or detailed data to my PC and use a spreadsheet product to perform the reporting. The advantages include easier integration with my PC-based word processing systems, built-in graphics, and (most of the time) faster response. (You vendors out there understand the advantages for you: greater sales of large PC hard disks!) The export process can be relatively simple, such as the method I described in this column, using the REPORT utility (see "Number Crunching", Access to Wang, May 1989; p. 13), or specialized program and procedure solutions can be used. Naturally, you will need utilities to transfer files between the VS and your PC to attempt this.

I have used such a process to report on the relative performance at the workstation, based on the workstation response method described in "Time Trials" in the June 1989 issue (see p. 14). The response times were gathered in screen dumps, condensed by a procedure into single lines in a file, and sent to the PC. The times were summarized in a spreadsheet and presented as a bar chart, clearly showing the performance of the system across time.

Reason Six: Data Conversion

Spreadsheets offer a good means of performing small data conversion tasks where manual adjustment of the information is the most practical method of conversion. Spreadsheet tools offer easy means for sorting columns, summarizing fields, and moving row information laterally. Some spreadsheet products can accept a consecutive file and parse it into columns, which can then be moved up, down, left, or right. Fields can also be "filled down", offering a quick means of pre-filling information over a large area quickly. Moves and copies can be performed over block areas.

I recently used such a process to split a name data field into first name, last name, and title fields using this method. After parsing the individual elements into columns and sorting to check proper column placement; item in the wrong columns were moved with block copies. When the elements were properly created a salutation field (e.g. Mr., Ms., Dr., etc.) was added and the file returned to the VS.

I think I've made my point: spreadsheets aren't just for accountants any more. Open your sights (and your toolbox) to this powerful and underutilized system design tool.