PerpetualBudget    

Worksheets On-Demand     


Home
Products
What's New
Support
News
About SBA
Web Resources
Downloads
Order

Contact Us
 

 

       

 

                                

                                 Worksheets On-Demand

By S. James Spicer

Spicer-Baer Associates, Inc.

Introduction

There are a number of controlled, repetitive worksheet tasks that lend themselves handily to full spreadsheet automation, including the dynamic creation of worksheets on-demand (WOD). One area of application is financial documents for budgeting, forecasting, analysis and reporting. The common threads of all these activities are General Ledger accounts, hierarchical organization structures and document formats. Read any financial report and you will find these commonalities.

 

The control points for the dimensions of these documents is usually de-centralized, i.e., the Accounting Department controls the Chart of Accounts, the organization chart is controlled by the CEO and the document formats are controlled by the CFO. This is a lot of potential for change, and change is the name of the game in financial documents. The spreadsheet analyst trying to keep up with this cycle can be overwhelmed at many points in the business cycle.

 

The actual tasks of adapting the spreadsheet models to change manually arenít rocket science but the analyst can fall behind through no fault of their own, which can lead to data errors. The prevailing criticism of spreadsheets used in financial systems is that they are error prone; spreadsheets are not error prone. But financial systems require consistent, managed interfaces that extend all the way to the spreadsheet.

 

Worksheets on-demand documents a proven method of integrating the data variables in any financial system with repeating Excel data blocks to produce dynamic documents that consistently produce and distribute reliable spreadsheets. This fulfills the objective of letting the program do the heavy lifting on deterministic, structure controlled tasks.

Applications

This technique lends itself to spreadsheets used in documents such as Profit and Loss Statements, Income Statements, Statements of Cash Flow, Balance Sheets, Indexes, Lists, Catalogs, Inventory Sheets etc. The commonality of these types of documents are the following:

  • Lots of data, not so many formulas

  • Repeating structures

  • Consistent data formatting

  • Ever changing line item definition

  • Multiple views of thematic data

  • Widely distributed and customized

  • Revised and distributed frequently

Benefits Objectives

Prior to the design and implementation of Worksheets On-Demand we had as objectives the following list:

  • Manage document change effectively

  • Accommodate multiple data sources

  • Increase throughput and automation of documents

  • Improve data integrity

  • Reduce human error

  • Lower customer costs

  • Enhance the systematic application of Excel

In this paper we address these issues and show that Worksheets On-Demand meets most of them.

System Components

A system like this has the following major components:

  • Repeating Data Blocks

  • Database

  • Graphical User Interface

  • Document Engine

Several of these terms require definition:

Repeating Data Block - a named range block of Excel cells organized to contain, control and present data. It is copied (stacked), expanded and populated with data from an external source.

Document Engine - a code routine that writes and populates data blocks in an order controlled by a "smart" topic outline.

Below is a block diagram of a typical WOD system:

In the PerpetualBudget environment, these functions are embedded in the program. Below we are exampling some typical Data Block examples.

This is the default data block that ships with the program. It is expanded by the document engine to produce worksheet segments like the one below:

Another data block for detailed data is shown below:

As can be seen there are no limitations to the style, column arrangement and row organization. This makes it possible to model Data Blocks for almost any financial document.

The general case for data block organization is shown below.

The actual column layout is a matter of style; the columns can be in any order. Our design preference is to include sufficient identifiers in each row, i.e., line item ID, line item name, org_ID and org_name such that the row data serves as a self-documenting record. This serves the host application (PerpetualBudget database) as well as any other consumer of the row data.

Database Requirements

The following tables are required to perform the Worksheets On-Demand engine functions:

  • Topic outline entries - each line in the outline instructs the document engine how to process that section.

  • Line item assignments - document sections contain assigned line items per organization.

  • Worksheet data - is stored in a separate table organized by the data block identifiers.

PerpetualBudget utilizes ADO and the Jet 4 database engine to store data in Access type files. This is easily adapted to most of the popular commercial database programs. For ease of maintenance and portability, the database tables are not linked to each other and no program code is stored in the mdb file.

Graphical User Interface (GUI) Requirements

It's popular and convenient in the short run to take over the Excel and Access interfaces for this class of product, storing VBA code modules in Excel or Access. We made a decision two years ago to move away from this type of GUI for reasons of security, performance and the desire to leave the Excel interface unmodified. It's difficult enough to learn and use these GUIs without cluttering them up with additional functionality.

The GUI for Worksheets On-Demand (PerpetualBudget) is a standard Windows SDI application running as a Windows executable and also as an Excel child window. This approach allows the WOD GUI to initiate and manage data communications between the WOD engine and Excel and the supporting database, independent of spreadsheet operations.

Document Engine Operation

Imagine the WOD engine as a "stacking" robot, copying data block after data block on top of each other on a designated worksheet, expanding each one with line items and data from the database. It's that simple, powerful and fast! Best of all, it builds on-the-fly, so if the control or data elements change, those changes are reflected in the built worksheet each time it runs.

The diagram below shows the basic loop of writing a data block, structuring it with section labels and line items and finally writing data to the line item columns.

The flow is simple; the challenge comes from managing line control, concurrent year data sources and interpreting the topic outline lines to list or calculate as designated (we are not discussing the FormulaEngines in this paper). As someone once said, the devil is in the details, so it goes unsaid that there are a lot of things to keep track of, naming conventions to adhere to, workbook management rules and general application procedures. Ignoring all the "real world" limitations of any Windows application, Worksheets On-Demand is a straightforward and powerful technique to build dynamic spreadsheets of any complexity from data.

Conclusions

One conclusion is Worksheets On-Demand can revolutionize the use of spreadsheets in advanced data systems. No longer can critics of spreadsheets point to the traditional weaknesses and suggest other, newer and less developed programs offer more to the user. The formalizing of data processes and development of program engines to write the spreadsheets isn't trivial but once the costs of development are absorbed, the cost of running the system is far less than any of the manual methods traditionally used to create and support spreadsheets.

Another conclusion being reached by the author during the course of technical and business events over the past several months, is it may no longer be necessary to synchronize the worksheet objects with the database every time there is a change in data or budget structures. Let me explain more fully. Since the worksheets on-demand engine can create and distribute new or replaced worksheets at any time, then it should only be necessary to do so when a request is actually made, either by budget personnel or the system itself.c

This is a dramatic improvement in how file based systems are currently deployed and supported. Ordinarily if the document rules, data or system structures are changed, then all the system files must be replaced before it can be used reliably. There is a great deal of inertia behind how file based systems work, so the shift to a data standard (database) will take place over time after the document engines prove themselves totally reliable. During this period, worksheets on-demand will pay for itself by replacing the system file objects quickly, adaptively and accurately.

About PerpetualBudget and the author

PerpetualBudget is the creation of S. James Spicer (Jim@spicer-baer.com).  Jim comes from an executive engineering management background, managing large software developments.  He has been developing budgeting software for the past five years and the second generation product lines reflect a lot of good lessons learned. The product can be reviewed, downloaded and purchased here.

 

Microsoft Excel and Windows are trademarks of the Microsoft Corporation.  PerpetualBudget is a trademarks of Spicer-Baer Associates, Inc.

[Top]

 

 


PerpetualBudget and Spicer-Baer Associates are registered trademarks of Spicer-Baer Associates, Inc.  Excel, Access and Microsoft Windows are registered trademarks of the Microsoft Corporation.  QuickBooks Pro is a registered trademark of Intuit Inc.

Questions or problems regarding this web site should be directed to Webmaster@spicer-baer.com.
Copyright © 1999-2008 Spicer-Baer Associates, Inc.  All rights reserved.
Last modified: Wednesday February 17, 2010.