Using Cube Structures to Move Budgeting to the Next Level

By John Orlando

An organization’s goals and direction are solidified when the budgeting plan is developed for the year, but the moment the plan is finalized, it risks becoming stale or even obsolete. Inescapable change is where the fight begins. Whether it’s an economic or political event, or myriad issues closer to home, the usefulness of a budget can decline from the moment it’s formed – if we let it.
Obsolescence isn’t inevitable. Whether a budgeting cycle takes one month, three months or five, a company needs to make that cycle perpetual. To stay congruent with strategic changes, the budget must keep up. Corporate cultures embedded with the acceptance of change will remain strong. Keeping the budget and forecast at the forefront of managers’ minds is essential for it to be followed, particularly without it becoming a detriment to inhibiting innovation and potential profits.
When asked to make critical decisions that can change the trajectory of previously approved annual budgets, executives need revised financial statements that reveal the potential profitability and liquidity positions they’re being asked to evaluate. Since prudent decisions must often be made quickly, the expectation should be that the financial planning and analysis team is able to create pro forma financial statements with no more than the click of a button.
A survey conducted by Centage Corporation to determine financial planning trends for 2016 reported 80 percent of respondents continue to use Microsoft Excel as their budgeting and planning tool. Whether using standard spreadsheets or contemporary software, considering changes to the budget should, as Steven Covey so aptly put it in The 7 Habits of Highly Effective People, “begin with the end in mind.” 
A proposal to change an annual budget must include evaluations of the presented options, an impact analysis on scenarios and the ability to drill down into enough information to satisfy potential questions. The ideal way to get to those answers is to consider the attributes available in analytical business intelligence tools.
Fiduciary Responsibility Meets Reality
In too many instances, the target of budget planning sessions (or any project meeting) becomes the creation of a document rather than developing a workable plan. This happens more frequently at lower levels of the organization, but it can happen in the boardroom as well.
The trick is to create a plan that is respected and adhered to while acknowledging the reality that priorities, needs and opportunities will shift continually.  Budgets have many concurrent goals. While the end goal for most plans is to maximize the profit potential (or for non-profits, to remain solvent), many other objectives also have to be met.
Fiduciary responsibilities compel us to spend funds optimally and conservatively. Being prudent isn’t solely a tight fist around the organization’s wallet. It’s also making sure the strategic plan and budget align with the company’s mission statement, values and objectives. If environmental conservation is a prime value for an organization, adequate funding and support needs to be placed with that objective. If career development is a hallmark of the corporate environment, programs (and likely funds) need to shift in that direction.
Changing Budgeting Perspective: Annual to Perpetual
Budgeting, forecasting and strategic planning have traditionally been annual events, but executives must stay on top of the market, promote ongoing evaluation, comparison, and, when necessary, make revisions to their strategic plans. Annual budgeting can continue to be a worthwhile exercise as it draws a line in the sand and forces people to make decisions they’d otherwise delay as long as possible.
Ideally, every department provides input into the annual budgeting process. The involvement secures the detail of operational needs for the area and also establishes ownership and secures buy-in from the participants. Often the global budgeting plan and strategic direction are communicated to the team, providing the guiding dictate that needs to be followed to keep all parties in alignment. Until things change.
Carefully defined assumptions are considered, formed and documented when comprehensive corporate budgets are prepared. Since it’s impossible to predict the future, the final assumptions listed in most documents are as nebulous as ‘other duties as assigned’ in a job posting. They’re the catch-alls that bring in the risk of change. Assumptions typically look like this:
  • No major changes in the economic picture
  • No significant new competition in the market
  • No significant market-related events
  • No major shifts in technology
  • No significant regulatory changes
  • No major supply issues that affect our products or services
  • The budget (or project plan) may change as new information and issues emerge.

And in most instances, at least one of these assumptions breaks. It might be after the original budget is finalized. It might be after approval but before the budget year begins. Or perhaps more likely, it happens when you’re within the budgeted year.
Shifting From “What Now?” to What If
Static budgets no longer serve organizations. Budgets are similar to any other project where the scope, objectives, assumptions, resources and risks need to be defined. When assumptions aren’t met, our budget plans need to become dynamic and mid-year analysis and scenario planning must include adopting those same factors. As options and conditions present themselves, creating scenarios (or what-if evaluations) would be moot if we don’t continue to scrutinize timing, risks and alternatives.
If an unplanned event occurs, the focus will likely be on revising revenue expectations or updating the strategic plan to get back on course. The updated strategic plan could involve the expansion or cancellation of a product line, severe expense contraction, or the re-deployment of resources from another endeavor.

The commonality of these ideas is that there are choices to be evaluated and decisions to be made.
Cubism Goes Beyond Picasso
The ideal way to ensure the critical considerations are made during scenario planning is to use the structure of online analytical processes (OLAP) to identify the catalysts (key performance indicators (KPIs)) and their surrounding dependencies and variables to assess one proposed option against another – or as is more often the case, several options against each other.
OLAP, as defined by Microsoft, is a computer processing methodology that lets the user extract data from different points of view. This type of technology can be used in a rudimentary form in Microsoft Excel pivot tables, but it is often more developed and flexible in dedicated budgeting, forecasting and analytical software specifically designed for OLAP data.
Data housed in a multidimensional database allows each attribute to be assessed individually or in combination. The OLAP data structure is often referred to as being a cube despite its ability to view beyond three dimensions.
When creating scenarios for comparison, cube properties ultimately describe what needs to be evaluated.
Those properties are the key to identifying and securing the right data and metrics that will best serve decision-making.
Fact Tables: Processes or events
The data, situations or processes to be evaluated are stored in fact tables. A sales process would be in a fact table that includes the details describing any given sale. This may include the product sold (recorded via an ID number or name), the quantity of the units sold, and perhaps a customer identification number.
In scenario creation, the fact tables are the ‘what’: Is a new acquisition being evaluated? A product line being eliminated? Have swings in currency rates prompted a reduction in profits? Or is it all three?
Dimension Tables: The Who or When
Attributes — characteristics that describe someone or something — are organized into dimension tables. Customer and vendor tables can include information such as their name, each element of their address, and details such as income rate, education level, and preferred contact method. Dimension tables can draw on fact tables to pull the appropriate U.S. state that corresponds with a zip code, or it can have independent data that is not repeated elsewhere.
Time is almost always included as in a dimension table that allows us to evaluate data by month, quarter, year, etc. Other attributes that may need to be evaluated, such as a specific SKU number or the address of a new location being considered, would also be included in dimension tables.  There is also a dimension that creates the need for evaluation, such as a specific SKU number or the address of a new location being considered. The implementation or start date, as well as potential end date, of the change within the budget are notable dimensions that affect everything from depreciation schedules to overhead allocations. What-if planning must include timing criteria along with the new lease or SKU being considered.
Many attributes can be organized in a hierarchical manner. For example, the state, county and city can be identified when a zip code is entered.  However, exceptions do exist. The developer site StackExchange identifies 13 current zip codes that have carrier routes that straddle state lines. When precision is required, including exceptions within fact tables isrequired necessary.
Dimensions, such as with the zip code example, can occur independently, used in multiple different cubes, or occur multiple times within a single cube (such as in both a mailing address and a physical address). The relationships can be notated as 1:1, 1:many, and many:many. Where the attributes intersect between tables is defined by their schema.
In scenario planning, consider the trickle-down effect one change can have on another. If a budget needs to be updated due to a consolidation of marketing territories and potential layoffs, a good understanding must be made to identify which drivers assign territories to individuals, and any departments and individuals that would be affected by the realignment. The what-if plan can go into great detail and isolate the cost centers, individual salaries and anticipated revenue projections affected. In this case, your balance sheet, P&L statement and cash flow will need to reflect any modifications.
Every cube has at least one measure. Measures are the numeric columns in fact tables that can be aggregated along each dimension or among multiple dimensions. These figures can consist of quantities, prices or sales amounts by funnel or any number of things. Any combination of elements (measures or dimensions) can be translated into information that may be helpful to decision-makers. Measures are the pieces that associate dimensions with one another.
What-if planning, and budgets in general, all come down to numbers. Your measures of sales, profits, revenue, and costs all can affect the bottom line. CFOs and others frequently manage by exceptions. Ratios, product volumes and expenses that fall out of line with expectation are often the measures that we isolate and investigate further.
Being able to quantify the impact of an economic event, such as a currency devaluation, presents the obvious need for tallying and recalculating virtually the entire financial statement package. The impact of subtler changes can also be revealed in non-standard ways. When adjusting a metric for teacher ratios for a grade-level, a seemingly small change from 18:1 to 20:1 can trickle down to affecting free meal calculations and after-school enrollment programs as well as the more obvious number of classrooms per grade level.
Key Performance Indicators (KPI)
KPIs move the needle. These strategically aligned metrics are used to gauge the degree of business success. There are two types of KPIs, lagging and leading indicators. Outcome KPIs, the lagging indicators, measure past activity. They describe the output of effort and are often calculated easily, for example by taking the sales result and dividing it by a time period. Forward-looking KPIs, the leading indicators, are drivers that measure activity with a significant impact on the outcome KPIs. Leading indicators are where we’re able to affect the outcome.
If a scenario doesn’t adequately consider the critical leading KPIs a business values as important, then tweaking a call center’s call volume without also adjusting moves other factors, such as the number of FTEs per shift, can deliver misleading results.
In an operations example, if calls are routed to a west coast call center after the east coast center closes at 6:00 instead of at the current 8:00 time period, you may see a false ‘savings’ by adjusting the number of calls per day upward in the west facility. With sufficient volume, you could tax your customer service agents to inadvertently default on their service level agreements. The number of calls per agent could get to an unreasonable level if you don’t also adjust the number of agents on the floor at that time.
A Case for Modernizing
There are many ‘quick and dirty’ ways to perform what-if analysis, including going with gut reactions. However, that intuitive feeling doesn’t always hold water when the stakes are high. Reviewing OLAP and cube structures when completing what-if planning is a valuable exercise that will benefit financial planning and analysis capabilities and should be a key responsibility of today’s financial professionals. 
Spreadsheets will continue to be a resource for financial teams. However, when considering the degree of detail that should be included in comprehensive scenario planning, there is a solid case toward retiring the spreadsheets from budgeting and re-forecasting tasks. Sticking with known software can have its merits, but a better way to use those merits is to integrate capabilities that are beneficial into criteria, while identifying and eliminating the detrimental aspects. Version control and ongoing maintenance of complex budgeting spreadsheets are risks that don’t need to be taken.
Today’s budgeting and forecasting solutions offer what-if features, consolidation ability, multi-currency environments and complete financial statement preparation capabilities including balance sheets, P&Ls, and cash flow statements to streamline mid-year scenario reviews and annual budget preparation.
Take the opportunity to evaluate potential planning solutions that can provide a leg up on the competition, pay significant dividends in time and resource savings, and assist management by providing the full scope of financial statement information needed to fuel critical decision-making.
John Orlando is CFO, Centage Corporation.