13 pitfalls of excel budgeting and how microsoft

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. Common pitfalls and

Forecaster solutions are discussed.

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

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.

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.

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.

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.

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

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

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

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

“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

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

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.

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.

13 pitfalls of excel budgeting and how microsoft

Documents