By S. James
Microsoft Excel, the leading spreadsheet program, is
a powerful tool for modeling numeric systems, particularly financial ones.
Nearly all companies and non-profit institutions use Excel to model
their financial planning. In the
planning process, two activities stand out as exemplary applications for
spreadsheet modeling, namely, budgeting and forecasting.
Most organizations have one or more Excel power users who can model the
most complex of financial systems. These
spreadsheet models are built and refined to represent the organization’s
financial position. As good as
the models are, when used as the working budgeting system, they often suffer
They aren’t easily
updated to represent a financial picture from people in the know.
They are difficult to
Startup costs and
effort can be high.
What is it about workbooks, as Excel
spreadsheets are called, that restricts easy collaborative updating and
support? The answer resides in
the architecture and design of spreadsheet softwares.
They were designed as desktop productivity applications. Networking and collaboration as we know it today didn’t
exist when spreadsheet software was conceived years ago. The momentum and acceptance of Excel on the desktop has
mainly reinforced the extension of desktop features, not networking.
Another big factor is also a strength of this class of software.
It runs everywhere on the desktop and can be applied almost universally
to all corporate tasks. Thus the
software must be general in nature.
Workbooks are made up of worksheets.
If you craft the worksheets to represent discrete elements of the
financial model, and distribute the worksheets to the appropriate
organizations for updating, problem one is solved.
Not exactly, because Excel has no ready mechanism to automatically
distribute the worksheets external to the workbook; conversely, no handy
method of re-incorporating modified worksheets back into the master workbook
Specifically, the current method of
re-integrating a copied worksheet to the master workbook involves using a
method called “paste links”. This
is a manual point and click operation involving many person-hours of hard
work, accompanied by human error, which also leaves the master workbook in a
nearly unreadable state. Recorded
macros add automation, but must be re-recorded if anything changes in the
master workbooks. Some
organizations write their own macros but as a practical matter, programming
shouldn’t be a focus of the finance department.
The opposite of the decomposed approach to updating
the master workbooks is the “shared workbook” approach.
Many organizations use this approach and it does have merit.
The master workbook is shared on the network and probably password
protected. Budget group users
open the workbook and update data on appropriate worksheets. The downsides to
this approach are the following:
How do you provide
privacy and protect sensitive data?
How do you prevent
accidental or deliberate tampering to the master file?
How do you train the
budget group to use the master files?
How do the remote
budget group users participate?
Organizations overcome these problems using worksheet
protection, hidden sheets and ranges plus other methods, but at a significant
cost. Also, because these types
of deployments are difficult to manage, there may be increasing resistance to
Applying spreadsheet technology in today’s networked environments
remains a challenge, but it is still the best tool around for financial
planning. One idea for improved
collaboration are planned Internet solutions.
Web solutions that host spreadsheets and offer remote users the easy
browser interface to read and update master workbooks have a definite appeal.
This sounds good and will likely happen over time, especially for
organizations that can develop, host and maintain Web server technologies.
Most companies will continue to use Excel and make it work on the
network with less arduous solutions than Web servers. One new solution is a system called PerpetualWorkbook.
Created as companion software to Excel, it is the result of several
generations of development. The premise is simple; distribute copies of the worksheets to
budget group users that can best update the data. Then re-incorporate the changes – all done automatically.
The worksheet copies or user files get hosted on the local area network
or are distributed via the supplied email system to remote users.
The program detects changes to the user files and alerts the financial
administrator to initiate the master workbook update processing. The update engine reads, and compares data at the cell level,
writing changes to the master workbook directly, eliminating the “paste
The system provides an Add-in to Excel that interfaces to the shared
database module. This allows the
spreadsheet analyst to rapidly create or re-work workbooks using the
automation tools, while keeping the budget system database updated at all
times. The Add-in tools include
rapid creation of new worksheets, automatic macro application, summary
worksheet rollups and automated update processing.
Many of the system features can be run from Excel as well from as the
system interface. The main
difference being the Add-in works on the Active Workbook, whereas the system
batch processes at the budget level from the Windows interface.
Improving the Change Cycle
Accommodating changes to the budget workbooks is a strong point of the
product. The program
automatically senses changes to the worksheet complement and range or formula
changes. This allows the
financial analysts to make sweeping changes to the workbooks without incurring
the traditional and huge effort required to implement the changes to the rest
of the budget system. The program
takes care of re-distributing the user files and automatically adjusts to the
workbook changes for the updating processing.
Since the change cycle for the master workbooks is now much easier to
implement, it is feasible to keep the budget system in place on a continuous
basis. In this fashion, the activity becomes a kind of perpetual
budget! Processing features such
as detecting missing or corrupted user files help keep the system running
smoothly and reduce the manual workload often accompanying the use of Excel
spreadsheets for budgeting. The
on-line alerting and logging features of the product keeps the admin users of
the system in touch with the system status.
Finance Team Collaboration
The newest system feature includes being able to host the local database
on the network and create user accounts for other financial administrators.
In this fashion, the finance team can work together on the budget over
the network. The program
administers the database, an Access module, eliminating the need to learn a
database technology. The data is
secured by database passwords, user passwords and the use of a dynamic
encryption scheme for continually refreshing sensitive data in the system.
Any installation can create a network database and assign budget
permissions for the other finance administrators on the system.
This combination of a local and network database mode gives the admin
users flexibility in doing their work. They
can work off-line developing new budgets or creating reporting scenarios and
then migrate the budget data back to the network database with a mouse click.
Multi-department budgets can be updated by the program and then
rolled-up into division or higher totals using the network data mode.
The solution just described represents adapting Excel to the network
without sacrificing the investment companies already have in their existing
spreadsheet systems or having to invest in a new technology.
Impact to the budget users is minimal; they receive workbook templates,
update them and save the file. The
system does the heavy lifting saving the finance team for their real job,
analysis and reporting. More
information on PerpetualWorkbook can be obtained from Spicer-Baer Associates,
Inc. (209) 365-9646.
About PerpetualBudget and the author
PerpetualWorkbook is the creation of S. James Spicer (Jim@spicer-baer.com).
Jim comes from an executive engineering management background, managing
large software developments. Budgeting
has long been an activity of his trying to meet tough project deadlines within
the budget. After leaving McAfee
Associates, Inc in 1997, he has been working steadily to produce a low-cost
system of software to improve Excel collaboration.
The first two products PerpetualWorkbook, a Windows program and
PerpetualWorkbookXL, an Excel Add-in were released in 2000 and are available for
download, evaluation and purchase here.
Microsoft Excel and Windows are trademarks of the Microsoft Corporation.
PerpetualBudget and PerpetualBudgetXL are trademarks of Spicer-Baer