May 1998

Situation...

A major health insurance provider needed to streamline the planning and budgeting processes. The existing system consisted of a series of worksheets, divided into sections for strategic planning and expense budgeting. Worksheets were manual – space was often provided only for an end result, forcing significant undocumented manual computations. Many numbers were manually entered in both sections. Budgets were consolidated manually for over 200 responsibility centers. Several days were required to summarize and report on the consolidated results.

Problem...

Taken together, these conditions resulted in user dissatisfaction and lack of confidence in the process. Improving the situation would require focusing on the following needs:

  • Providing extensive historical data for reference in preparing budgets
  • Validating reasonableness and consistency of data inputs
  • Enforcing the accuracy and integrity of computations
  • Providing the foundation for efficient budget versus actual cost analysis
  • Integrating planning, expense budgeting, and activity-based costing
  • Supplying user guidelines to improve overall budget integrity

Solution...

A custom-developed personal computer application using Microsoft Excel and Visual Basic for Applications. Automated worksheets are provided for each major budget category requiring significant estimation and calculation. These budgets are summarized and linked to the Line Item worksheet by responsibility center and expense classification. The functional line item budgets are then attributed to corporate-wide activities using selected cost drivers, providing the foundation for activity-based management.

Line Item Budget

Budget methods are automatically calculated for certain key accounts, and can be selected from several methods for the remaining accounts. Buttons and custom menus provide intuitive access to the application's features. Historical data, current budget, and percent change are conveniently presented in one screen.

Existing Employees

Detailed employee information allows planning FTE's, performance ratings, grade levels, and raises. A dialog box warns the user if a raise exceeds the corporate guideline for the situation. The budgeted employee cost, along with fringe benefits, are linked to the Line Item worksheet.

Results & Benefits...

For end users, the application:

  • Significantly reduced the time and effort necessary to prepare the budgets.
  • Improved accuracy and overall data integrity.
  • Reduced the number of back-end consolidations necessary to fine-tune the budgets for managers responsible for multiple responsibility centers.
  • Reduced turnaround time for consolidated reporting and analysis from several days to a few hours.

Altogether, these benefits led to a dramatic improvement in the process, with a high level of user satisfaction as indicated by quality surveys. For the Finance Department, the application:

  • Provided details on personnel, capital expenditure, and leasing not previously available, building a foundation for improved financial control and analysis.
  • Reduced the level of time and effort required for the process.
  • Yielded increased user confidence in the ability of the Finance Department to meet the information needs of the organization.

Conclusion...

This client used technology to streamline the budgeting and planning processes, increasing user satisfaction and obtaining better planning, budget, and activity information.