Budgeting From the Desktop
By S. James
Two years ago (2001) I wrote a white paper ďImproving Excel
CollaborationĒ, which served as the concept document for a product we
produce, PerpetualWorkbook (PW). PWís function is to perform remote updating
on customer supplied spreadsheets. Several hundred corporate users have
deployed PerpetualWorkbook and we continue to support it and refine the
interface and techniques. At the end of that development, we started work on a
desktop budgeting system to be hosted in Excel on the desktop and utilize
Access (or higher) as the central database. This paper describes the design
goals and implementation details of the resulting product, PerpetualBudget.
The Problem Statement
The challenge of designing and implementing an effective budgeting
system is not about spreadsheets, databases, web interfaces or accounting
systems. Budgeting is about
projecting future company profits, negotiating the gap between top management
and the organization, getting commitments from operating managers to make the
plan, measuring the results and assisting in on-the-fly re-planning.
These requirements demand a high level of professional commitment by
key employees in the company. Profit
and cost center managers demand a high level of control and will show
initiative given the chance. This
starts with motivated thinking, pencil and paper, good communication tools and
flexible software tools.
Flexible software tools work for the professionals the way they want
them to work. They should be
lightweight, easy to deploy and maintain, and allow quick spin up of models
and reports. This can also
translate as tools that are universally accepted and understood and are
ubiquitous at the corporate, division and department levels.
Small, flat and visible profit centers need small, local and powerful
Stated like this, itís easy to see why financial analysts keep
reaching for spreadsheets to do budgeting.
They model easily, are widely deployed and supported and have high
acceptance by non-financial managers.
Why then is it difficult to have budgeting success with spreadsheets?
Itís not difficult; most companies achieve a level of success using
spreadsheets. The problems happen
down the road after the early success. Getting
real numbers from the operating managers, making timely changes, scaling
models up and down to reflect changing conditions, supporting segmented
operations and understanding the model after the analyst quits are just some
of the problems that assail budgeting systems.
One final requirement: The
spirit of budgeting is projecting the unknown, reaching for the limits, asking
the difficult. Itís not
accounting and itís not information management.
Itís the companyís professionals working together to assure the
future of the company. They
deserve the freedom to get there, their way.
One Size Fits All
When we started this project we sought advice from other budgeting
software and Excel power user professionals.
Our simple premise was: Can we make a budgeting product out of linkless
data templates, some control software and an Access database.
The answers ranged from yes, to no and one Excel power user thought we
could supply the basic data templates and then all data could be dumped to a
large database like SQL Server. Potential
customers could then do the rest customizing the product in the backend
server. That might work for
bigger IT shops but would leave a large part of the user market out of the
The answer we wanted to hear of course was ďYesĒ, but we listened to
the part about letting customers do what they wanted. It brought up the concept of an open architecture for
spreadsheet systems. In our early
prototyping, we experimented with taking over the Excel application, giving it
a specialized product interface and completely controlling the access to and
behavior of the spreadsheets. Not
only was this difficult to implement in software but it was inflexible to the
point of not achieving the system performance.
We decided to head in the opposite direction and implement an open
system architecture. We donít
take over the Excel interface, there is no code in workbooks and the user is
free to introduce any additional data or components they want into the budget
system. The program provides
basic capabilities to perform data collection and consolidation on wages,
expenses, capital, products, sales and orders.
A kind of ďone-size-fits-allĒ approach to business plan projection.
Using the simplicity of the Income Statementís profit formula (R Ė
COS Ė E = P), we believe most businesses operations can be normalized to
address the basics of profitability. The
speciality of each business is best addressed by the analysts in that company
using their tools of choice. These
data sources and consolidations are easily blended into the report structure
of the base product.
Earlier we mentioned the need for linkless data templates.
This means that worksheets designed for data entry of budgeting
information should not have links to other worksheets or workbooks but should
work stand-alone in any environment. Further,
the data and formulas in the worksheet should be completely separated so that
the data is portable in any environment.
This allows the system to utilize spreadsheets and databases
harmoniously without having to condition either environment during the
transfer and consolidation of data.
This was a difficult requirement to achieve but we did it.
Excel handles links so effortlessly you really donít know they are
there, until you attempt fully disconnected operation or start moving data in
and out of worksheets; then you not only know they are there but the workbooks
quickly become a cross-linked nightmare.
Data worksheets can be proliferated across the organizational structure
and exported, freely disconnected from the rest of the system without creating
links back to the source.
In addition to data templates, the system utilizes base templated sheets
to dynamically build consolidating structures based on changing organizational
requirements. This avoids the
problems associated with infinitely tracking the row and column status of each
consolidation. It also allows
remoted budget workbooks to consolidate local data without a live connection
to the budget workbooks or database.
Finally, we employ smart formulas in the consolidations, which adapt to
changing row and column configurations and program settings on-the-fly.
Role of the Database
The database role in our products is definitely increasing beyond simple
configuration and control to more traditional information management
functions. Two examples come to
mind; storing all consolidated profit center data for segmented reporting of
multiple profit center operations and worksheet data storage during template
changes and restoration of budget data.
Handling multiple profit centers in a purely Excel environment is really
difficult and even if you succeed in modeling the structure, the workbooks are
too complex and specialized to easily decompose and share with the operating
divisions and departments. We
choose to partition business structures at the profit center level.
Each profit center is housed in a stand-alone workbook which
consolidates all local operations including allocations and can be decomposed
and shared with the participating departments.
This structure also encourages a simple distribution of workbooks to
profit center managers and analysts.
The database then becomes a logical place to store all profit center
numbers. When you need to
consolidate at the division or company level, the product queries the database
and builds the structure or report you have requested. This can be further
formatted in the product or copied to Excel for further formatting, printing,
As the role of the database has increased, we allow the program to be
run stand-alone (outside of Excel) for team management, configuration,
analysis and remote access. In
future versions, we envision allowing remote data entry and access via web
browser. This will undoubtedly
involve hosting the database in a more sophisticated and secure environment
such as Microsoft SQL Server. This
will be provided as a product option.
One of the reasons most budgeting systems are home-grown is because it
is inevitable that you start small and grow the system over time because the
requirements only become known in a natural business climate.
We finally realized this after working several years on the system
complexities and talking with users about how ďgood it was going to beĒ
when they got their remote departments fully participating in the budgeting
process. The user reaction to
this was generally they needed to get a handle on where the company was
heading before trying to get the entire organization involved in the budget.
So we started working back toward the beginning of a budget activity and
have come up with a staged process that begins with the base configured
product utilizing Goal Setting Mode (GSM). Goal Setting Mode allows the financial analyst to enter
summary numbers about revenue, cost of sales, wages, depreciation and expenses
in each departmentís local consolidation worksheet.
These numbers act just like they were rolled up from detailed data,
supporting all the reporting consolidations in the product.
This is a quick method of getting a snapshot of projected company
operations perhaps responding to top management direction while at the same
time assigning target numbers to each department manager.
Program controls allow switching back and forth between GSM and
detailed data modes or a combination of the two.
GSM can be followed by a gradual top-down fleshing out of department and
division data, gradually involving departments in a remoted bottoms-up
updating of department detail. As
confidence and training builds in using the system, more and more of the
organization can be brought on-line interactively achieving the GSM target
projections. Over time the GSM
numbers can be replaced with actuals providing working variance management.
Keeping Things As Simple As Possible
Itís easy to over complicate the templates and reports in a budget,
making it more difficult to use and maintain. Itís also easy to introduce
errors into the system by manually editing important areas of worksheets.
There are some things we do to keep things simple:
data templates to a collection task.
data areas and formula areas.
same formula over the calculating range.
the data templates for every department.
the use of direct links between worksheets.
windows interface (database) for all sheet naming, ID assignments and
program to extend or alter the features of templates, insuring that data is
backed up and restored properly.
About Windows Interfaces and Spreadsheet Navigation
The Excel interface is visually good for working on a single worksheet
or two. But as soon as you get
more than a few worksheets and certainly if your budget is spread over a few
workbooks, the view of the budget is lost to a blur of worksheets that look
much the same. This is usually
overcome for the consumers of the budget reports by printing hard copies and
We have addressed this problem for the budget administrators by
implementing a budget object oriented Explorer interface that runs as a child
window in Excel and handles any size budget.
As you point and click on budget objects, the program activates the
appropriate workbook or worksheet anticipating you will want to have a
worksheet view as well as the database view of the object.
Using the windows interface, if the service request requires worksheet
assets that havenít yet been created, it builds them on-the-fly.
This on-demand concept extends to all areas of the budget, including
making budget workbooks for the remote managers of the system.
By setting the specification for remote workbooks in the program, you
only have to select a Group User and assign a department and the budget
workbook is made and distributed automatically.
Why Spreadsheets Are Still the King
If the same amount of systems design and planning went into every
spreadsheet budget as go into big backend systems, spreadsheets would always
be the system of choice. Now it
has and you can deploy it for a fraction of the cost!
Used properly and supported with contemporary tools and database
services, spreadsheets are still the best way to go when developing a company
budget. The business community
waited a long time to get powerful desktop computing tools like spreadsheets
and now coupled with newer system software assets, they will be used more
successfully than ever.
If you havenít started a company budget, using todayís tools you can
be up and running in a matter of hours. If
your company already has a spreadsheet budget and you want to take it to the
next level, donít throw it away and start over. Start from where you are and extend your system capabilities
using tools developed for your situation.
There are more topics to cover such as XML, web support and security. We
address many of these subjects on our web site and will take them up in detail
in the next paper. More
information on PerpetualBudget can be obtained from Spicer-Baer Associates,
Inc. (209) 365-9646 and the web site at http://www.spicer-baer.com.
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. 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 product is
available for download, evaluation and purchase
Microsoft Excel and Windows are trademarks of the Microsoft Corporation.
PerpetualBudget and PerpetualWorkbook are trademarks of Spicer-Baer