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
Lots of data, not so many
Consistent data formatting
Ever changing line item
Multiple views of thematic data
Widely distributed and customized
Revised and distributed
Prior to the design and
implementation of Worksheets On-Demand we had as objectives the following
Manage document change
Accommodate multiple data sources
Increase throughput and
automation of documents
Improve data integrity
Reduce human error
Lower customer costs
Enhance the systematic
In this paper we address these
issues and show that Worksheets On-Demand meets most of them.
A system like this has the
following major components:
Repeating Data Blocks
Graphical User Interface
Several of these terms require
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.
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
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.
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
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
Microsoft Excel and Windows are trademarks of the Microsoft Corporation.
PerpetualBudget is a trademarks of Spicer-Baer