13 pitfalls of excel budgeting and how microsoft
Post on 13-Jan-2022
2 Views
Preview:
TRANSCRIPT
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.)
Page 2
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
Page 3
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 4
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 5
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 6
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 7
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 8
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 9
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 10
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 11
“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 12
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 13
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 14
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.
top related