13 Pitfalls of Excel Budgeting and how Microsoft Forecaster Overcomes Them

13 Pitfalls of Excel Budgeting and how Microsoft Forecaster Overcomes Them OVERVIEW Studies show that a surprising 70% of all mid-sized organizations ...
Author: Lynn Richards
23 downloads 2 Views 752KB Size
13 Pitfalls of Excel Budgeting and how Microsoft Forecaster Overcomes Them OVERVIEW Studies show that a surprising 70% of all mid-sized organizations use Excel to manage their budgeting process. This paper will examine thirteen pitfalls faced by organizations using Excel for budgeting. We will also demonstrate how organizations can avoid these pitfalls by adopting Forecaster for Microsoft Dynamics ERP, a streamlined application built to overcome the problem of inefficient budgeting. Forecaster, a product that many Microsoft Dynamics ERP customers may already own, may be adopted in as little as two to three week timeframe. "He who fails to plan is planning to fail" - Winston Churchill

INTRODUCTION Budgeting, planning and forecasting mean different things to individual organizations; terminologies vary, as do time horizons. These differences aside, all organizations understand the importance of peering into the future. In all cases the selection and documentation of financial goals is the cornerstone of corporate performance management. For the purpose of this paper, the term “budgeting” will refer to all constructs of financial performance models incorporating varying plans, strategies, assumptions, and events. We will assume that, at some level, budgets must enable the comparison and measurement of the actual financial operation of the business. “Checking the results of a decision against its expectations shows executives what their strengths are, where they need to improve, and where they lack knowledge or information.” - Peter Drucker

Performance management must go to the heart of organizational structure and the relationship between authority and responsibility. To facilitate evaluation of actual results, most organizations budget down to the responsibility level of their GL chart of accounts structure. Typically found on this level are departments, locations, cost centers, divisions or a combination of segments. These business units (BU) are normally assigned to, or “owned” by, a specific manager or group of managers. To achieve ownership of the respective BUs, organizations sometimes prefer a bottom-up budgeting approach which starts at the BU management level. By participating in the process, by submitting “their” budget, managers achieve a sense of proprietorship over their unit. A typical budget process treats each BU as a micro-budget by recording specific data for each. If your organization has 100 departments, your budget process involves 100 micro-budgets. (In the case of a top-down budget approach, the structure is the same, group and types of budget contributors differ.)

EXCEL PITFALLS AND FORECASTER SOLUTIONS The following section is a walk-through of a typical budget cycle using Excel. Forecaster solutions are discussed.

Common pitfalls and

Excel Pitfall #1 – Adding New Accounts Excel Pitfall #2 – Calculation Integrity Excel Pitfall #3 – Non-Centralized Assumptions Excel Pitfall #4 – Excel Budget Structure Not Documented Excel Pitfall #5 – Difficulty Using Baseline Data Excel Pitfall #6 – Difficulty Populating Known Data Excel Pitfall #7 – Security Excel Pitfall #8 – Issues with Budget Level Granularity Excel Pitfall #9 – Endless Linking Excel Pitfall #10 – Lack of Analytical Capability Excel Pitfall #11 – Version Control Excel Pitfall #12 – Mass or Global Updates Excel Pitfall #13 – Reporting on the Final Product

ADDING NEW ACCOUNTS The typical Excel budget process begins with the creation of a “master template” that encompasses the budgeting needs of all of the BUs in the organization. On these templates the financial components of the GL account structure (the main, natural or object segment) are typically row headings while the unit of time is represented across columns. While the master template can be comprised of multiple sheet tabs, each with unique assumptions or drivers, it itself must be uniform and repeatable. Excel Pitfall #1 – Adding New Accounts - If a new account is added to the GL after the creation and duplication of the master template, Excel budgeting requires the manual insertion of the new row (100X in the case of a 100 department organization). Forecaster Solution – Forecaster uses a building block approach to designing input screens and reports. In Forecaster, a Line Set is used to logically group, format and control the display of rows. You can think of a Line Set as a template that drives the horizontal presentation of your

Page 2

budget workspace. Since the Line Set is independent of the BUs, new accounts are automatically duplicated.

BUDGET FORMULAS Budget administrators normally give their BU owners a head start in the budgeting process by providing formulas, assumptions, and drivers in each Excel template. This practice has the great intent of allowing the manager to focus his or her time on key input assumptions by limiting manual keystrokes and calculations. (Yellow highlighting can be used to guide managers to those cells that need their attention). Payroll is a good example of the use of cell-based modeling. As the BU owner inputs salary amounts, the formula calculates payroll taxes and displays in the correct cell. In Excel, there are several pitfalls to this practice. Excel Pitfall #2 – Calculation Integrity - The BU owner unintentionally overrides the Excel calculation with direct input. Forecaster Solution – Forecaster has built-in detailed modules for Human Resources, Capital and Revenue. Each of these modules includes unbreakable, system-generated, calculations to drive account balances. These modules will be discussed in more detail in the following sections. Excel Pitfall #3 – Non-Centralized Assumptions - The payroll tax rate was built into the formula, but changes require the formula to be updated. In Excel this is a manual process (again 100X in the case of a 100 department organization). Forecaster Solution – Forecaster’s Human Resource module provides the budget administrator a centralized benefits table that allows him or her to manage all HR-related driver assumptions such as tax rates and insurance costs. The benefits table is a sophisticated tool that, for example, builds in intelligence to allow for annual limits on FUTA taxes. The table also provides up to 10 user-defined fields to limit specific benefits by employee attributes.

Page 3

Figure 1: Human Resource Module’s Setup - Benefits

Excel Pitfall #4 – Excel Budget Structure Not Documented - When the individual who created the budget formulas embedded in the Excel templates is no longer available, others may not understand the nuts and bolts of the model. Unintentional dependence on individual employees can create risk. The cell-based syntax may not call out what the formula is actually doing. To audit, the administrator must trace back and look at the cell references. The logic can be difficult to decipher. Forecaster Solution – With Forecaster, when a calculation falls outside the range of the three detail modules, it is created in a centralized Calculation Set. The Calculation Set syntax uses easy to understand account references, incorporates a calculation verification option, and offers a note space to store descriptions of the purpose and functionality of each formula. The Calculation Set features more than a dozen powerful functions, including If-Then-Else. These functions make Calculation Set powerful, flexible, and easy to manage. As a building block, its elements are created and managed independently. Changes are automatically applied to each Business Unit as needed.

BASELINES In budgeting, many organizations use an approach, called zero-base, which starts the process by setting account values for each BU to zero. The goal is to induce a conscious consideration and justification of all revenues and expenses. In practice, most organizations, including those using zero-base, incorporate a comparison or baseline to test the reasonableness of assumptions. For example, a baseline may be the actual results of the previous cycle. Builders of Excel-based budgeting models find all kinds of creative ways to incorporate baselines, most of which are time consuming. Excel Pitfall #5 – Difficulty Using Baseline Data - Excel offers no simple or inherent method for leveraging baseline data. Forecaster Solution – Single Row Tab is a Forecaster feature that lays the baseline data set behind the budget data set. Single Row Tab allows BU owners to consult the baseline on an account-by-account basis. In addition, budget inputting can be driven from the baseline using value or percentage increases or decreases. Also, with Forecaster’s robust internal reporting package, budget vs. baseline variance reports easily pinpoint assumptions that need more attention. Analytical questions are quickly answered by drilling-down into report details.

Page 4

Figure 2: Forecaster Variance Report with Conditional Formatting

POPULATING DATA In Excel, another challenge is populating baseline templates with the specific data for each BU. This manual and error-prone process takes valuable time away from the budget administrator. Similarly, many budget administrators deviate from the zero-based approach by “seeding” micro-budgets up front with known expenses. In our example of 100 departments/Excel workbooks (or sheet tabs), each must be seeded manually. Excel Pitfall #6 – Difficulty Populating Known Data - In Excel, populating baseline templates and seeding budget templates is a time-consuming and error prone process. Forecaster Solution – Since Forecaster is a database application, it stores and utilizes data efficiently. Populating the baseline for each BU, or the entire organization, is done in one fast step with the aid of a wizard. In payroll, for example, the administrator simply “imports” a data dump from the GL or 3rd party payroll system. Organizations using Forecaster and its sister product, Microsoft FRX, integrate with Express Link to automate the import process, saving even more time.

Page 5

SECURITY Excel is not a secure format for sensitive financial data. Once budget templates have been “prepped” with formats and formulas, and populated with baselines and assumptions, files can be freely copied and distributed. Managers may accidentally distort the template structure. In many organizations, BU owners are authorized to work with only their own data, not that of other units. Some managers have access to functional budgeting elements that others don‘t. Using Excel, these limitations are difficult to manage securely. Excel Pitfall #7 – Security - Excel budget spreadsheets are vulnerable with respect to protecting sensitive information, such as salaries. Forecaster Solution – For security purposes, Forecaster requires that each budget contributor be set up individually by the administrator. Managers access Forecaster via Windows authentication or standard SQL user IDs and passwords. The client can be locally installed or web-based. Forecaster has two primary security layers: group and assignment. Each budget contributor is a member of a group, which provides the functional access to the application or what features they can see and use. Assignments limit their use to the specific BUs they work with.

Page 6

Figure 3: Customizable Group Security

FLEXIBILITY In search of precision, BU owners often need to expand upon an account to budget at a more granular level. Excel templates are limited in their expansion flexibility below the account level, due to the need for template consolidation with the other micro-budgets at the end of the process. Take for example an Excel template designed for 40 employees: all of the formulas that summarize salaries and related payroll expenses are based on that range. But what if a unit now needs to budget for 45 employees, due to aggressive growth plans? Although the budget may be compiled and compared at the account level, the devil is in the details that make up that account level balance. From the budget administrator’s point of view, the budget process must generate the most accurate assumptions in the least amount of time. The more comfortable and accommodating the budget

Page 7

interface is, the more likely these goals will be achieved. Forecaster has the familiar look and feel of Excel with the structure and flexibility needed for budgeting. Excel Pitfall #8 – Issues with Budget Level Granularity - Excel budget templates allow for only finite granularity and quantity of details. Forecaster Solution – Forecaster has built-in detail modules for Human Resources, Capital and Revenue. With these modules, users can budget details below the account level to arrive at precise figures. The HR and Capital modules allow users to budget unlimited employees and assets respectively. The Revenue module allows users to budget to two additional variables not found in their GL structure, such as markets, products, salespeople, customers, and territories. For those accounts not driven from the three detail modules, users may take advantage of the Line Item details feature. This feature allows users to double click on an account and budget to any of the unlimited line items that make up that account. Let’s take as an example advertising. The BU owner knows that several campaigns in the budget year overlap month to month. If the manager does not feel confident about actual monthly expenditures, he or she can use Line Item details to create a new input line for each campaign on the fly.

Figure 4: Line Item Details - Advertising

Page 8

TRACKING WORKFLOW In the approval phase of the budgeting process, a senior manager may have several micro-budgets to review. Organizations using Excel will find that it doesn’t provide the tools necessary to view a group of micro-budgets in context. How does the group look as a whole? If one business unit is projecting a decline, do others make up the difference? And as a budget works its way up the hierarchy to a senior VP, new information may come in back down at the BU level which requires changes. Conflicting budgets cross paths on email servers. Tracking the workflow can become confusing and inefficient. Excel Pitfall #8 – Budget Progress Tracking/Auditing - Excel does not offer workflow tracking or a sophisticated audit trail.

Forecaster Solution – Forecaster offers workflow tracking and audit trail features. The stages of the workflow are customizable and include an option to lock micro-budgets once submitted and automatic email notification when a micro-budget moves from one stage to the next. The budget administrator can view the status of each of micro-budget in real time, including the date and time of the last save. Since Forecaster stores budget data in a centralized database, users with access, such as senior managers, can review at anytime. If there are questions about numbers, the history file shows the origin of the last change. The history file is available for each account in a BU and shows the user, date, time and account balance (the perfect audit trail).

Page 9

Figure 5: Centralized Workflow Tracking

LINKING When administrators receive micro-budgets they may be required to audit each template. Have any of the formulas been broken? Have new lines or columns been added? Have all data points been entered? With Excel, this is a tedious, error-prone process. Organizations using Excel may discourage multi-user collaboration to avoid pitfalls. In other words, Excel’s lack of enforceable structure can impede the communication needed to create accurate budgets. The way an organization does business can be fundamentally altered. Before the organization’s overall budget can be approved, micro-budgets must be joined or

Page 10

“consolidated.” In Excel this is accomplished by linking workbooks (or sheet tabs) with the use of potentially fragile formulas. And once this process is complete, adding a new department or BU can require the modification of hundreds of cell-based formulas. In addition, budget views by division, geography or function, for example, require additional sets of linking formulas. Putting these formulas in can expose the process to error and delay. Excel Pitfall #9 – Endless Linking - Consolidating micro-budgets requires linking workbooks and/or sheet tabs. Time may not permit creation of hierarchical levels of consolidation. Adding a new department involves revising all linking formulas. Forecaster Solution – Forecaster’s internal reporting package allows for reports that pull from both baseline and budget data. Powerful features available in reports include segment Rollups. Rollups are the heart and soul of Forecaster as they provide for instant consolidations at multiple levels. Rollups are easy to build and manage, with features such as drag-and-drop and ranges. Rollups also allow for the ability to drill-down. Real time budget analysis couldn’t be quicker or easier. And thanks to Forecaster’s purpose-built architecture, adding a new Department or BU is as simple as a few clicks and keystrokes.

Figure 6: Instant Consolidation via Rollups – Examples Based on Function and Geography

ANALYSIS Page 11

The final budget is presented. But Excel refuses to yield strategic information. Questions go unanswered and doubts arise. Senior management may decide to make changes or re-start the process with all BU owners. In Excel, this daunting task includes template rebuilds, formula rewrites, tracking hoops, and linking drills on tight deadlines. In some cases, the same compilation of spreadsheets is used to make the requested modifications. Other organizations prefer “save as” copies of workbooks to preserve original versions. In either case, the inefficient process may begin again with the added burden of version control. Management’s changes may sound simple in the conference room, but down in the trenches they can require wrestling with numerous templates and more late nights at the office. Excel Pitfall #10 – Lack of Analytical Capability - Excel does not provide robust analysis tools for budgeting. A lack of information at the top can create unnecessary problems and delays across the organization.

Forecaster Solution – Storing budget data in a logical database structure enables powerful analysis. With the building block design approach, reports can be used to spot anomalies, and to create metrics and ratios to compare BUs on a side-by-side basis. Deep account analysis can be achieved quickly and easily. Forecaster reports are especially valuable during the budget approval process.

Excel Pitfall #11 – Version Control - The Excel budgeting process isn’t version friendly. Forecaster Solution – With Forecaster, creating additional versions is a simple process. Using a wizard, administrators create new sets of periods to house new versions of data. Think of periods as buckets that define the parameters of each version. Use another wizard to copy the original budget. This process is used for sensitivity analysis, forecasting, and what-if-scenarios.

Excel Pitfall #12 – Mass or Global Updates - Excel doesn’t provide effective tools for incorporating global changes to all BU’s assumptions. Forecaster Solution – Forecaster has two built-in tools to allow for the quick and accurate modification of budget data. These are known as Adjustments and Global Calculations. Forecaster’s Adjustments feature allows for rapid, top down, adjustment of accounts (or group of accounts), departments (or group of departments) and time/version periods (or group of periods). Adjustments may be increases or decreases in dollars or percentage terms. Forecaster’s Global Calculations is a quick and powerful tool used to manipulate entire

Page 12

time/version periods of data for all accounts and departments. In Global Calculations all mathematical operators are available. For one example, Global Calculations can be used to create a set of periods showing the average monthly run rate of the previous 6 months. For another, an existing set of periods may be viewed with a 5% reduction.

Figure 7: Top-Down Haircut via Adjustments Wizard

REPORTING THE BUDGET YEAR IN PROGRESS The budget year has started. The administrator must find an efficient process to generate monthly vs. actual reports for both BU owners and the executive team. The Excel user knows it is possible to load their budget data into the Dynamics ERP system, but, unsure how to do so, he or she may choose to create monthly variance reports manually. To accomplish this, he or she must find a way to twist the Excel budget to squeeze in extra columns showing actual results. The process is once again time-consuming and error-prone. And the analytical limitations of the resulting report are equivalent to those encountered during the budget collection process. While loading budgets into the ERP is possible, that process is painfully complex. Welcome to “Excel Hell.” The administrator knows there is a better way, but doesn’t have time to evaluate and implement a better solution. Excel Pitfall #13 – Reporting on the Final Product - Once the budget is final creating budget vs.

Page 13

actual variance reports is a laborious manual process.

Forecaster Solution – Forecaster has a tight integration with Microsoft FRx and Microsoft Management Reporter. Modifying existing reports to incorporate budget data from Forecaster is a straightforward process. Which means users adopting Forecaster can leverage their existing FRx or MR reporting package and avoid complications. All Forecaster reports are based on real time data.

CONCLUSION As a single user application, Excel is a powerful financial analysis tool with infinite flexibility. It is Excel’s lack of structure for budgeting that is the primary motive in the adoption of dedicated budget and forecast applications. This decision migrate is easy when the cost of lost staff time and errors quickly exceeds that of an investment in a dedicated application. Forecaster is a powerful and effective solution. ====================================================================================

About Forecaster Microsoft Forecaster is a budgeting application designed to help organizations save time and money, increase accountability, and improve decision making by gaining control of budgeting processes. Forecaster delivers a rapid return on investment as implementation is measured in days, not months. Forecaster is currently available to all Microsoft Dynamics customers.

Page 14