Line managers and finance staffs are frustrated by the inadequacies of spreadsheet based planning systems for delivering

useful budgets and rolling forecasts. A major problem is the disconnect between the operational elements of a
business and financial plans. In particular, managers have difficulty forecasting headcount and expenses because spreadsheet templates do not contain
models that allow them to relate their spending to marketing forecasts or other
operating activities. Finance staff who roll up the numbers from the managers
have very little backup for evaluating the reasonableness of submissions or for
answering questions from the executive staff.
Whats missing is driver-based planning, a best practice methodology where
financial plans incorporate assumptions about business activities which are
modeled to drive financial data such as revenue projections, headcount, spending and capital requirements. With driver-based planning, managers are empowered to do better budgeting and, in particular, improve the accuracy and
decision making usefulness of rolling forecasts.
Alight Planning is a multi-user software package that incorporates architectures, modeling tools and analytics that make driver-based planning accessible
to companies that want to improve their budgeting, forecasting and decision
making processes.

Driver-Based Planning for

Budgets & Forecasting
By Rand Heer

Helen Marston, Call Center Operations

Manager for Whitehorse Corp., a $300 million manufacturer of electronic equipment,
is reworking the Excel template for her departments expenses.
Each quarter Whitehorse updates its business forecasts. Key managers such as Helen
revise spending plans for the balance of the
year based on the latest marketing forecasts.
The process is time consuming and involves
a lot of guesswork.
`

2007 by Alight LLC. All rights reserved.

In the past two weeks Helen has been

given three different forecasts for unit
sales, new products and new customer
installations, each version supposedly

Driver-Based Planning for Budgets and Forecasting

more accurate than the last. Revising her
numbers takes three hours each time.
`

To update the numbers, Helen pours

through operating statistics for her department which are in a separate database. Metrics include number of operators, number of customers calling, calls
per day, etc. Helen has no specific procedure for tying out this operating history to the marketing forecasts.

Helen enters headcount projections into

an Excel template which updates salaries and benefits. She does so with a lot
of guessing; shes glad when the exercise is over; she hasnt learned anything.
But her job is done for this round.

Peter Forrester, Financial Planning Manager

for Whitehorse, is studying the Excel templates department managers have submitted.

Staffing plans for sales reps are inconsistent. In four regions, headcount doesnt
change for any of the three marketing
forecastsi.e. nobody paid attention,
perhaps for good reason. For other regions, salaries and benefits vary, but
other headcount related expenses that in
theory should be impacted by new
hires, such as training and recruitment
fees, are unchanged. Peter rolls the
numbers into the forecast knowing that
expenses are probably understated.

Here we go again, Peter says as he enters

the board room to present the forecast update to the executive
staff. The numbers add
up on the PowerPoint
slides, but Peter has an
incomplete
understanding of changes in
overhead spending, a
stated focus of the current reforecast. Many
times during the review he is asked the two
dreaded questions: Where did that number
come from? and Why did that number
change? Sometimes Peter has an answer.
Other times he says: I dont know.

The headcount increases Helen forecasts

for the Call Center seem low in relation
to the latest marketing forecast which
has sales up 20% in the next quarter.
Helen provides no backup or justification. Peter has no special insights either.
He rolls Helens numbers into the forecast.

The two most dreaded questions:

The pattern is reversed for
the Service Center. Harry
Where did that number come from?
Laswell, the manager,
and Why did that number change?
forecasts
a
dramatic
increase in headcount,
much higher than the 20% sales increase
Whats Missing? Driver-Based Plans
would seem to justify. He attaches a
Whitehorse recognizes that annual budgets
passionate note arguing that service has
should be supplemented with forecast upbeen understaffed for months and now
dates. However, the forecasts are not very
is the time to remedy the situation beaccurate, and there is little analysis of unfore everyone quits. After a heated
derlying assumptions, partly because the
phone call with Harry, Peter rolls the
forecast cycle is squeezed into a tight two
Service numbers into the forecast.
week time frame after the quarter end close.

Page 2

Driver-Based Planning for Budgets and Forecasting

Unlike budgeting, quarterly updates dont
have the luxury of a three month cycle time.
At the operations level, responsible managers like Helen and Harry do their best, but
the process is essentially manual, despite
the fact that the spreadsheet-based system
captures information from the line managers and rolls it up to corporate. The spreadsheet templates do not contain models that
allow managers to relate their spending
plans to the market forecasts. Nor do Helen
and Harry have information systems that
integrate operational data into the forecast
templates.

cision usefulness of rolling forecasts. Finance staff like Peter also have the information to understand the numbers, negotiate
with line managers, and explain assumptions to higher ups like the executive staff.

Driver Model Fundamentals

Driver-based planning is about modeling
concepts. Its based on the idea (or structure) that line items within a plan have an
inherent units/rate/amount architecture
that is the basis for linking up activity
driver and financial relationships.
Thinking about driver-based planning using units, rate and amount
makes it work. Here are the
practice
fundamentals

Driver-based planning is a best

methodology where financial plans are
structured using models of underlying
business activities

Start
by
identifying
important drivers in a
business. Drivers are
typically
operating
activities that you can measure
numbers of things such as units of product, customers, installations, deliveries,
transactions, throughput and the like.
The key word is units: if an activity can
be thought of as units of something, then
it may be part of an activity driver
model.

Whats missing at Whitehorse is driverbased planning, a best practice methodology where financial plansnamely budgets
and rolling forecastsare structured using
models of underlying business activities
that drive financial data.
Driver-based planning incorporates a series
of step-by-step sub-models within an overall financial planning systeme.g. a call
center sub-model for Helenthat integrate
input assumptions about activity levels that
drive revenues, variable headcount and expenses, and capital that roll up to financial
statements for a company. Such company
forecasts are the foundation for many executive level decisions, allocation of resources and communications with investors.
Using driver-based planning, companies
like Whitehorse can empower line managers like Helen to do better budgeting and, in
particular, to improve the accuracy and de-

Multiple operating activitiesi.e. the

unitsmay have driver relationships
between each other that are connected
through a conversion rate. For example,
50% percent of customers who buy a
computer also buy a printer. The formula is: units of computers * 50% = # of
printers. 50% is the conversion rate.

A unit/rate/amount structure may be

applied to a series of line items that are
linked. Below is a simplified example of
how an activity model might be constructed for Helens call center, starting
Page 3

Driver-Based Planning for Budgets and Forecasting

from marketings forecast of new customers:
9 # of New Customers * Calls Per
Customer = Total Calls
9 Total Calls * Length of Calls In
Hours = Total Call Hours
9 Total Call Hours / Operator Utilization % = Operator Hours
9 Operator Hours / Hours Per Month
= # of Operators
9 # of Operators * Salary Rate = Operator Salaries
9 Operator Salaries * Payroll Tax Rate
= Operator Payroll Taxes
9 # of Operators * Benefits Rate = Operator Benefits
9 # of Operators * Cost Per Workstation = Operator Workstation Assets
Once an activity driver
model is structured, several
important planning and
analysis activities are possible.

Third, with driver-based planning, true

causal analysis of variances is now possible.
When actual financial results differ from
plan, its a straightforward exercise to identify the operational drivers which caused
the variance. In many cases, the analyst can
segregate financial variances by volume (i.e.
units) and rate (i.e. price/cost per unit) impacts. Where the underlying unit and rate
data is available, volume/rate causal analysis by line item is a powerful analytic tool
for the finance staff.

With driver-based planning, everyone can

see changes immediately and talk about
the assumptionsthe playing field is leveled and discussions are more objective.

First, rolling forecasts with

tight turnaround cycles are
now feasible and efficient. Line managers
like Helen can quickly respond to changes
in marketing forecasts. Updates to revenue
plans ripple through the activity model
automatically adjusting variable headcount
and related expenses. Managers spend no
time calculating offline what headcounts
should be. Everyone in the plan cycle can
see changes immediately and talk about the
underlying driver assumptions and impacts. The playing field is leveled and discussions are more objective.

Page 4

Second, when using a driver model, its

easy to identify and manage the most important, financially sensitive drivers in the
business. The old saying now has meaning:
Dont manage the dollars, manage the underlying units and rates that cause the dollars to be spent. Managers can focus cost
reduction and performance improvement
energies on the most important activities
and processes, knowing a payback is there.
Thats what the activity driver model tells
you.

Spreadsheets and BPM

Financial planning managers typically use
spreadsheets to model a few activity driver
relationships in budget and forecast
templates. The most common and easy to
implement are driving fringe benefits from
headcount and driving payroll taxes from
salaries. However, true driver-based planning, where revenues or other higher level
activity drivers impact headcount and department expenses, is rarely implemented
in spreadsheet-based budgeting systems.

Driver-Based Planning for Budgets and Forecasting

The reasons are two: a) formulas and data
relationships for pervasive activity models
are too complicated to maintain, and/or b)
driver-based links do not automatically
flow through to workbook templates distributed to line managers for department
budgets and forecasting.
Support for driver-based planning in software from business performance management (BPM) vendors is limited or difficult
to implement. In some BPM applications
modeling tools are weaki.e. they do not
support a minimum level of modeling and
linking comparable to spreadsheets. In
other BPM applications, modeling and linking are more sophisticated, but the interfaces are technical requiring scripting or
programming skills that finance staff do not
have or want to learn.

Each line item incorporates data fields for Units, Rate

and Amount. URA values may be input assumptions
as indicated by shaded cells, or they make be linked.
URA values are automatically displayed on reports.

Driver-Based Planning with Alight

By contrast to spreadsheets and other BPM
vendors, Alight Planning is sophisticated
multi-user software specifically designed
for driver-based planning.
Its architecture and modeling interfaces
directly support requirements for building
robust sub-models anywhere in a financial
plan structurefor revenues, headcount,
expenses and balance sheet itemswith
automatic rollups to financial statements.
Critical to effective driver-based planning,
Alight has a built-in unit/rate/amount architecture that facilitates building activitybased sub-models as described earlier.

Alights modeling interface is based on object-based

linking versus cell formulas. You can link the units
and rates of a line item to the URA of any other line
item, rollup sub-total, or a search based combination
of line items. Linking automatically operates across
all time periodsi.e. no fill right as required with
spreadsheets.

With Alight, operational or financial data at

the line item level can be computed using
any of six computation methods that facilitate driver-based modeling. Such methods,
using the unit/rate/amount architecture,
simplify cross time period, accumulation,
indexing and other operations that are difficult to implement and maintain with cellbased formulas in spreadsheets.
Page 5

Driver-Based Planning for Budgets and Forecasting

9 Plan Billed Hours / Utilization % =
Staff Hours
500 / 70% = 625
9 Staff Hours / Hours Per Month =
Staff Headcount
625 /170 = 3.7

Alights modeling interface includes easy-to-use tabs

for special purpose calculations. The cross time period tab pictured above lets the user link to the URA
of other items in prior or future time periods with
accumulation of values rolling forward.

Importing Actuals for Activity Analysis

Importing actuals into plan files is a
fundamental requirement for effective
driver-based
planning.
The
import,
however, cannot be restricted to financial
data from the general ledger. It must also
include operating data so that comparisons
for variance analysis can be made for
operating assumptions that are the activity
drivers of the sub-models. As well, importing and integrating actuals for operating
data is a foundation for analyzing activity
driver trends that help tremendously when
developing rolling forecasts.
The tricky part of integrating actuals into a
financial plan is that operating data is
usually not apples to apples in formats or
structure to plan data. Lets walk through a
common examplecomparing actual staff
utilization percent to planto illustrate the
issue:
`

Plan utilization percent is usually an

input assumption that might be used to
calculate staff headcount and salaries as
shown below. Note that Utilization % is
an input assumption.

Page 6

9 Staff Headcount * Salary Rate =

Staff Salaries
3.7 * $10,000 = $37,000
`

For actuals data, Utilization % is a

calculated value requiring a different
modeled structure:
9 Staff Headcount * Hours Per Month
= Staff Hours
3.0 * 170 = 510
9 Staff Hours / Actual Billed Hours =
Utilization %
510 / 500 = 102%

The above example represents one major

difficulty with implementing driver-based
planning. For comparisons and analysis, actual
and plan data must often be modeled differently.
But the two data sets, actual and plan, must
appear as the same line item for reporting. This
is difficult or often impossible to do in both
spreadsheets and other BPM applications.

Integrating Actuals with Alight

We call it integrating actuals instead of importing because of the example above. The
issue is not simply importing. The planning
application must import operating data
from outside sources and allow easy manipulationi.e. modelingof actuals data
once its in the plan file. Alight Planning
was specifically designed for integrating
actuals as required in the staff utilization
example above.

Driver-Based Planning for Budgets and Forecasting

Heres the summary statement about import: Alight supports importing actuals
from any outside database, financial or
operational, to any level of detail. For example, you can import actual unit sales and
amounts by product from a sales database
or CRM system, and concurrently, you can
import total sales from the general ledger at
the account level. Alight reconciles any differences between the GL total and the sum
of imported line items with a plug value.
In addition to importing from any source to
any level, Alight also allows modeling actuals data with different algorithms and linking than the plan model for the same line
item.

9 How would call center headcount and

expenses (or any department spending)
be impacted by a 10% volume (activity)
increase?
9 What are the most important activity
drivers in the financial plan that impact
sales or profit? What are the least important?
9 How much of the miss in the sales
forecast for services was due to fewer
product sales versus lower service conversion rates?
9 Why are salaries higher? More people or
higher wages? Whats the ripple
through affect of higher headcount on
other expenses?
Just as Alight Plannings driver-based architectures let planners develop and maintain
more accurate financial plans, the software
also incorporates numerous automated
analytic tools for understanding activity
driver impacts and financial results. Such
tools, not available in spreadsheets or other
BPM applications, let planners quickly
identify the most important driver assumptions in a financial plan and perform other
sophisticated analyses.
The analytic tools include:

Each line item has three tabs for modeling: Structure, Plan and Actuals. On the Actuals tab, you can
use the same structure and linking as Plan, or you
can independently model actuals data with its own
linksas would be required in the staff utilization
example.

Driver Analytics in Alight

A major benefit of driver-based planning
using units, rates and amounts is that a
world of analysis questions can now be
asked and answered. Such questions as:

Sensitivity analysis: Unlike spreadsheets or other planning applications

where sensitivity analysis is a hunt and
peck operation, Alight automates the
process. In a special analysis pane, the
planner chooses a target such as net
sales or operating profit. Alight automatically produces a list of line items
with input assumptions that affect the
target and ranks them in order, top to
bottom, based on the dollar magnitude
of the impact on the target.
Page 7

Driver-Based Planning for Budgets and Forecasting

CAUSAL ANALYSIS: Alight Plannings causal interface lets users compute volume and rate variances for line
items that have underlying units and rates. The Units and Rate causal columns automatically calculate the volume
and rate dollar impact on the total variance. The Causal column automatically appears on all revenue, expense
and headcount reports.

Goal seek analysis: Alight automates

goal seek operations. The planner
chooses a target for a time period then
specifies a desired goal and input variable. For example: profits in May are
forecast to be $50,000; the planner enters
a goal of $75,000, then selects Unit Sales
as the input assumption to be tested. A
dialog appears showing the required
number of Unit Sales necessary to meet
the $75,000 profit goal.
Causal analysis: Alights built-in URA
architecture allows producing volume/rate computations that can be
applied to any activity driver or financial line item. For example, an operator
column called Causal can be added to a
revenue report that calculates the actual
versus plan financial impact of unit volume and price variances for each product. The same report format computes
volume/rate variances for headcount.
Key measures analysis: In a special Key
Measures pane, Alight lets plan administrators create custom dashboards for
activity driver models that allow users
to analyze in one view multiple line
items or plan subtotals, otherwise

Page 8

spread through out a plan, that constitute a subset driver model. For example,
all drivers and resulting metrics for the
call center model could be organized in
one key measures group for testing,
analysis and discussion. You use Key
Measures to tell the story.

Summary
Driver-based planning is a best practice
methodology where financial plans are
structured using models of underlying
business activities. Alight Planning software
incorporates a unit/rate/amount architecture, modeling tools, integration of actuals
and analytics that make this powerful
methodology accessible for any company
that wants to improve their budgeting, forecasting and decision making processes.
_______________________________________
Rand Heer is President of Alight LLC and the
creative force behind Alight Planning. He was a
contributing author to Business Intelligence:
Making Better Decisions Faster published by
Microsoft Press. He was also the founder of Pillar Corporation and designer of Hyperion Pillar,
the first enterprise software for budgets and forecasting. (800) 960-7717