PerpetualBudget    

Budgeting From the Desktop     


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

Contact Us
 

 

       

 

              

                                       Budgeting From the Desktop

By S. James Spicer

Spicer-Baer Associates, Inc.

 

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 budgeting tools.

 

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 equation.

 

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.

 

Templating Methods

 

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, distribution, etc.

 

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.

 

Staged Deployment

 

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:

         Dedicate data templates to a collection task.

         Separate data areas and formula areas.

         Use the same formula over the calculating range.

         Reuse the data templates for every department.

         Avoid the use of direct links between worksheets.

         Use the windows interface (database) for all sheet naming, ID assignments and tracking.

         Use the 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 collating logically.

 

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 here.

 

Microsoft Excel and Windows are trademarks of the Microsoft Corporation.  PerpetualBudget and PerpetualWorkbook are 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.