PerpetualBudget    

Improving Excel Collaboration     


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

Contact Us
 

 

       

 

                            Improving Excel Collaboration

By S. James Spicer

Spicer-Baer Associates, Inc.

 

 

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 from problems:

o        They aren’t easily updated to represent a financial picture from people in the know.

o        They are difficult to maintain.

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

The Decomposition Model

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 exists either.

 

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.

 

  Shared Workbooks

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:

o  How do you provide privacy and protect sensitive data?

o  How do you prevent accidental or deliberate tampering to the master file?

o  How do you train the budget group to use the master files?

o  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 their use.

 

  Improving Collaboration

  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 links”. 

 

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