By Randolph P. Johnston
Executive Vice President, K2 Enterprises

One of the most serious challenges faced by financial professionals is that of providing accurate and reliable budgets and forecasts. Budgets in particular are key operational tools that provide authorization for use of company resources. A budget is also used to measure of a manager’s effectiveness in administering those resources. Forecasts are often critical to ensuring that good strategic decisions are made.

Considering the importance of budgets and forecasts, one would logically conclude that financial professionals are aware of best practices and implement these in building and managing budgets and forecasts. Unfortunately, this is often not the case. Spreadsheet-based budgets and forecasts are often poorly structured and therefore highly susceptible to errors. Further, they may be protected by little or no security.
 
In this article we will discuss a few best practices for developing and managing budgets and forecasts. Those practices include:

1. Structuring spreadsheet budgets and forecasts, to reduce risks and errors and to make them easier to use
   
2. Using Excel Services® to gather budget input from department managers and to securely distribute budget and forecast information to these individuals
   
3. Using dedicated budgeting and forecasting tools (Microsoft Forecaster®, for example) to overcome some of the inherent weaknesses of spreadsheet-based budgets

All current research on budgeting and forecasting suggests that Excel is OBSOLETE as a budgeting and forecasting tool. Notwithstanding the fact that many companies still use Excel in budgeting and forecasting, industry best practice is to ABANDON Excel in these processes, so the preferred recommendation is to use a budgeting and forecasting tool.

Structure Spreadsheet Budgets and Forecasts in a Way That Reduces Risks and Errors
Electronic spreadsheets, with their free-form design, are highly susceptible to human error. Common errors include typing a number over a formula or failing to update an assumption that is embedded in a formula. The spreadsheet auditing department at Coopers & Lybrand in London found that 91% of its audited financial spreadsheets containing more than 150 rows had error rates of 5% or greater (David Freeman, “How to Make Spreadsheets Error-Proof,” from Journal of Accountancy, vol. 181 (5), pp. 75-77, May 1996).

A few simple spreadsheet design standards can significantly reduce the risk that these common errors will occur:

1. Assumptions should appear only one time in a workbook. For example, if one of your budget or forecast assumptions is an interest rate, such as 6%, that assumption should be entered only one time in the entire file. When this interest rate is used in calculations, a reference should be made to the cell containing the assumption. When spreadsheets are constructed in this manner, you only have to change the assumption one time to update the entire workbook.
   
2. Each assumption should be entered in a cell by itself, and each cell should be clearly labeled.
   
3. Assumptions should be listed in a separate sheet (or a separate area of each sheet) that contains only assumptions.
4. You can significantly reduce errors and make your spreadsheets much easier to update by constructing them in this manner. An added benefit is that you now have a complete listing of the assumptions on which your model is based. This list will become an important feature of your budget and forecast reports – one that is generally unavailable when you embed your assumptions in your formulas.

Use Excel Services® to Securely Share and Accumulate Budget Data
Accumulating input data from department heads and providing preliminary budgets back to these department heads can be among the most challenging and time-consuming aspects of the budgeting process. With Excel Services, the accumulation and sharing of budget data can be automated and controlled in a way that was unimaginable just a few years ago.

Excel Services is a new server technology based on Excel 2007® and Microsoft Office SharePoint Server 2007®. With Excel Services, you and your budget users can view and modify live, interactive workbooks from anywhere in the organization with nothing more than a Web browser. You can also interact with budget workbooks to explore and pivot on data and to analyze PivotTable reports and charts. Excel Services supports workbooks that are connected to external data sources, so live data from your accounting information system can be incorporated into your budget and forecast models, with drill-down capabilities into your live financial data.

The following are some of the features of Excel Services that make it particularly attractive for the creation and management of budgets and forecasts.

1. There is only one copy of the true budget workbook, created and changed by trusted authors and kept in a central, secure location. (In some organizations where Excel spreadsheets are used for budgeting, the preliminary budgets are distributed for review. This can result in dozens or even hundreds of different draft copies of the budget stored on multiple computers throughout the organization.)
2. Your budget document is easy for both the administrator and budget users to access (via Web browser or Excel 2007®) and update.
3. The budget administrator can carefully control what other users are able to see and change. This allows the budget administrator to restrict department heads to viewing only their own budgets. It also enables department heads to input proposed budget numbers during the budget creation process – if the budget administrator chooses to allow this.
4.  All changes made to the budget can be tracked.
5. You can create snapshots of your budget at any point in time.
6. The correct and final version of the budget is easier to find, share, and use from within Excel and other applications.

For more information on Excel Services, see http://msdn2.microsoft.com/en-us/library/ms546696.aspx

Replace Excel Budgets and Forecasts with Dedicated Budgeting Software
Creating and managing budgets with electronic spreadsheets is infinitely better than creating them with pencils, columnar pads, and adding machines. Spreadsheets not only save time, but also provide analytical abilities that could not even be dreamed of 25 years ago. However, there are serious limitations to even the most sophisticated spreadsheet budgets and forecasts. For example, it is very difficult to design a spreadsheet budget that allows the user to drill down to the underlying data on which the budget is based. Furthermore, the integration of spreadsheet-based budgets and the accounting information system is generally a manual process that takes a lot of time and is highly susceptible to errors.

Dedicated budgeting and forecasting packages like Microsoft Forecaster® provide the best of both worlds. These products:

1. Have a familiar, user-friendly interface that looks and works a lot like Excel, and reduces implementation time and training costs.
2. Can be directly linked to the accounting information systems, thus eliminating the need for manual data input.
3. Contain sophisticated tools for the building of more accurate budgets based on historical data.
   
4. Are Web browser-based and do not require additional software to be loaded on the user’s workstation, thus allowing easy access from anywhere in the organization.
5. Have built-in workflow to automate the management of deadlines.
6. Have flexible reporting tools that make customized reports relatively easy to provide to budget users. Remember: in Excel, your data is your report. A change in the report often involves inserting and deleting rows and columns and moving data between cells. Not only is this is time-consuming, but it significantly increases the risk of serious errors in the budget calculations.

For more information on Microsoft Forecaster®, see http://www.microsoft.com/forecaster/product/default.mspx

Conclusion
For many organizations, the creation of budgets involves preparing and maintaining large, complex Excel workbooks. These budget workbooks are time-consuming to prepare and update, difficult to control, highly susceptible to errors, and almost impossible for someone other than the creator to use. A few simple spreadsheet design standards can reduce some of these risks and improve efficiency. Microsoft has recently released a new technology (i.e., Excel Services®) that can further improve the spreadsheet budgeting processes. For those who spend a lot of time on the budgeting process and rely heavily on their budgets and forecasts, dedicated budgeting software like Microsoft Forecaster® should be seriously considered.