Budgeting and Profit Planning: Forecast Based On Current Year Actual Amounts - Part 7

One way to adjust the forecast is to project the current actual amounts into the future.  Linear regression and exponential smoothing with trend correction  are two examples of how this may be accomplished internally by the system.  The first graph in Figure 11-8 shows the straight line forecast that results from linear regression applied to the last three available current actual amounts in Figure 11-7.

Figure 11-7  Use of a Forecast to Calculate the Initial Budget.

Forecast Based on Average Error.  This approach uses a full year's worth of comparative amounts, such as the current budget or the prior year actual activity, and adjusts this by the average error this has over completed periods of the current year.  As an example of this approach, consider that half way through the year, the actual amounts average 8 over budget.  Using this approach to forecast the remaining six periods, the budgeting system can develop a forecast using the current budget amounts for periods 7 through 12 and adjusting them up by 8.

Of course, the prior year actual activity can be substituted for the current year budget amounts, if desired.
Remember that the issue being addressed is how to develop 12 periods worth of information for forming an initial budget.  Once the forecast is completed, the system will apply any linear adjustment to create a good initial or rough-cut budget for each account selected.  Figure 11-9 shows how this approach is used with the same data shown earlier.  Based on the differences between Figures 11-8 and 11-9, these two approaches can produce quite different initial budgets.

As the graph in Figure 11-9 suggests, this result will closely follow the trend and seasonality of the budget (or prior year actuals, if they are used).  A second distinction of this approach is that it uses different, older information as the basis for the forecast.  The budget manager should use this approach if either or both of these factors are desirable.

LOADING FROM OUTSIDE THE GENERAL LEDGER

Loading an initial budget from information available in the general ledger is a sensible approach.  However, many organizations use another technique that involves loading the initial budget from budgeting information maintained outside the general ledger.  Typically this involves loading information from a budget model build using a spreadsheet or using similar financial modeling software.  Consider some of the general characteristics of this approach.

General Characteristics.  A budget model represents a segment of the budget that is being built using a tool other than the budgeting system.  As the term “model” implies, this finalization is not the official budget because it does not yet reside in the general ledger master file, where it will be used to report organizational performance. 
The reasons for using this approach vary.  Most often

• The budget manager has become accustomed to using the spreadsheet or financial modeling tool and prefers this approach to using the budgeting system with which he may be less familiar.
• Strategically the organization prefers this approach because it may favor use of available personal computer resources.
• The spreadsheet or modeling system offers modeling capabilities and easy-to-use features that surpass the budgeting system’s capabilities for developing a complex budget, rife with interrelationships.
• Like the budgeting system, the spreadsheet or modeling program provides an easy ability to store and update budgets from year to year.

Given one or more of these situations, an organization can prefer this approach for developing an initial (or final) budget over an approach dependent strictly on the central budgeting system. 

Once developed, the system must provide the ability to load budget information from the budget model into the general ledger master file.  Figure 11-10 shows two typical arrangements in which this occurs:

1. Loading the initial budget from a budget model created using a personal computer.
2. Loading the initial budget from a budget model developed on the central computer.

The spreadsheet or financial modeling software used to develop the budget model are separate and distinct from the budgeting system.  If only a small number of accounts are being budgeted, the budget manager may prefer to manually load the information into the budgeting system from the budget model.  However, quite often the budget model contains information on hundreds of accounts.  In this case, the budget manager should automate this transfer.  To move budget information between these systems, the interface file and its contents must be recognized by both systems.  The interface file contains each account number and 12 budgeted account balances?one for each period.

Not only does the interface file assist with loading budget information into he budgeting system, it also can provide a vehicle for allowing the budget manager to extract budget and actual information from the general ledger master file back into the spreadsheet or financial modeling system.  Chapter 12 explains this extraction process in more detail.

NEXT MONTH'S TOPIC: BUDGET MODEL EXAMPLE

Material in this chapter has been adapted and reprinted with the permission of Warren, Gorham & Lamont, Inc., from Chapter 43, “Automated Budget Systems,” in Budgeting and Profit Planning Manual, 2nd edition, by James D. Willson.  Copyright 1983, 1989 by Warren, Gorham & Lamont, Inc. 210 South Street, Boston, MA 02111. All rights reserved.