Not all firms budget, but nearly all that do use Excel. This is true for firms of all sizes, across all industries, and around the world.
Among those firms that do budget, even staff who have relatively few numbers to contribute—in contrast to Finance professionals who monitor/manage the budgeting process—often enter numbers into a spreadsheet and send it to those responsible for compilation in the master budgeting application. And, fully aggregated results provided to C-Suite staff often are sent in spreadsheets—or, in dashboards whose data is derived from individual workbooks. Most pertinently, a vast amount of work is typically done in Excel by Finance to make the budgeting numbers right.
Given those facts, this white paper will investigate how firms budget with Excel—the complexities and complications they encounter—and how firms of all sizes can do their budgeting most simply, quickly, and productively while continuing to use Excel. The benefits of this type of solution—an application that can “take the burden off Excel (and Excel users)”—is, logically, something all firms should strive for. A fast, effective, mistake-free budgeting application will result in significant gains in productivity, boosted staff morale, and a far more agile overall planning (budgeting, as well as forecasting) strategy. The result would be a vastly more effective budget planning solution, helping firms navigate the future in a way that promises meaningful gains to the bottom line.
We will also explain how “making the complicated simple” is the trademark of PowerExcel and how the product can help firms with their budget planning applications. Additionally, there is an addendum that details “Visionary Intelligence” precepts, which are the basis for software solutions developed by PARIS Technologies specifically to overcome business and technology issues that are common to so many planning systems. [Note that sections specifically referencing PowerExcel have been indented and italicized.]
Because this white paper is concerned with the centrality of Excel to most firms’ budgeting process, we will not consider:
- Firms that do not budget—almost certainly all small firms.
- Firms that, in their budgeting process, Excel can accommodate to their full satisfaction—meaning, firms that experience no issues with using Excel. These, too, are mostly small firms that have very few contributors.
We will not exclude discussion of firms that disclaim the use of Excel in their budgeting process—those using, for example, a web-based or ERP-centric budget planning application—but which still use spreadsheets to either collect data from contributors and/or, very likely, also use Excel for calculations (consolidations, adjustments, etc.) before typing in or otherwise entering figures in another/”main” [e.g., web-based or ERP-centric, etc.] budgeting application.
What we will consider are the following subjects, which constitute the outline of this paper:
- A Backgrounder: on the importance and complexities of budgeting; and participant (contributors, Finance, C-level, IT) involvement in the budgeting process.
- Complications in the budgeting process resulting from disparate data and the lack of a centralized system; Finance staff requirements and eventual ownership of budgeting through use of Excel.
- The iterative budgeting process; versioning and the need for initiative/strategic planning and forecasting.
- How Excel can best be used in the budgeting process.
- An addendum concerning Visionary Intelligence.
Budgeting – Backgrounder
Budgeting is one of the primary ways, if not the primary way, for an organization to measure performance—it is that important. Budget decisions, based on past performance and future expectations and strategies, have enormous consequences for how firms choose to spend money in order to grow and become more profitable. The exercise of creating a budget is a critical undertaking for important stakeholders like owners and managers. In a public company, budgeting is a requirement for public reporting purposes, detailing expected future costs, earnings, and actions (and, implicitly, non-actions).
For all its importance, the yearly budgeting exercise is lengthy—typically beginning in September and intended for review in December, but often extending weeks into the next year. It is a process that can involve many staff members, and it can most certainly become tedious. Part of the tediousness is its length: excepting small firms, companies naturally go through an iterative decision-making process, making choices about priorities for the budget year while the budget is being worked on.
Just as it is of critical importance to a firm’s current understanding of itself—and its next-year plans—budgeting is important to all stakeholders, from staff that need to fill in only a nominal set of numbers, to the top management that must make decisions about prioritizing company resources against targeted earnings.
Non-financial staff (e.g., operations managers, sales staff) may regard budgeting as extraneous to their core work responsibilities. This is regrettable: anything that mitigates against the recognition that budgeting is of paramount importance—like a frustrating budgeting application—undermines the purpose of the planning exercise, not to mention staff morale.
For financial staff who manage the budgeting process, there is indeed a heavy layer of extra work to be done at a very busy time in the fiscal and calendar year. As we will examine in more detail, the burden of making corrections, adjustments and all manner of calculations related to the budgeting model—and the iterative, evolving nature of the budget in the overall—can consume time measured in weeks and months for these Finance professionals.
Leaders are concerned with results, the top-most figures. They are looking at the future, to understand prospective costs to the business and what decisions might be made about spending, based on anticipated/targeted revenue. They are not concerned with the particularities of what makes up the budget figures as much as they are about driving increased profitability.
And where is IT in the budgeting process? As “keepers of the transaction-records domain,” IT staff will deliver reports based on queries made to the systems they manage, reports that contain a “snapshot in time” of current data “Actuals” that exist in, for example, the ERP application or applications. Finance staff use these reports for the work they will undertake through the entire, ongoing budgeting process.
The volume of work, the magnitude of effort, and the degree of complexity in the budgeting exercise tend to increase with the size of the firm and the number of budget contributors, as well as with the complexity of the business itself.
The Budgeting Application – its Complexities and Eventual Ownership by Finance
The budgeting process, as noted, is of primary importance; it takes time and can be tedious; and it includes a lot of moving parts—human and technological. However, it is not inherently problematic. For all the complexities of even the most elaborate budgeting exercise, there’s nothing that says it must be unachievable by humankind. Companies know to prioritize a budgeting system that will guide them to a better, more profitable future, one which allows for differing visions, competing priorities, and other intangibles as the budget evolves to its finalized form.
A superior budgeting system should be achievable, but rarely is. Instead—and this essentially applies universally (to firms of all sizes, industries, countries)—complications arise because of the inherent difficulty (or impossibility) of obtaining and organizing data from different systems, as well as in agreeing on what the data constitutes. To compensate, staff (almost exclusively in Finance) use their skills with technologies and tools to bring the budget model into being. Excel, of course, is the most prominent tool that Finance uses. Often they succeed, in a manner of speaking, given the time and effort involved. As is well known, however, the use of spreadsheets will reach a point past which it can and inevitably will compound problems in the budgeting process.
In the real world, these complications exacerbate the inherent complexities of budgeting and make the budgeting process much worse than it should be—again, most often these complications concern the data in question. These are complications that can significantly undermine the budgeting process, if not be fatal to its objective of measuring performance and helping a firm drive towards greater profitability.
From the C-suite to the “lowest” budget contributor who enters the fewest numbers, every budget participant “relies on the data” to make decisions. Decisions about what budget numbers to enter (from the “bottom up”) may be based entirely on data from current year results; decisions made by the topmost leaders about plan strategies will be based in part on aggregated Actuals and budget data that works its way up through Finance and the CFO.
Here let’s begin at “the top” and first examine in what form the CEO/business owner (the term “CEO” will be used throughout) will, throughout the process, typically be provided with budget figures.
We know that the CEO is concerned with a business model that reflects his/her vision of where the company should be in the future; and what decisions should made based on different sets of strategies to achieve that vision. However, as likely as not, he or she will often be provided with budget data in the form of a disconnected spreadsheet. The figures in it might be printed in a board book and/or shown in a PowerPoint along with a dashboard (Power BI is the best example) that is created from a single-copy spreadsheet or spreadsheets.
The sad fact is that the spreadsheet or workbook in question, existing only as a single file, is not connected to any kind of model application. We can say here, and essentially put this topic aside for the moment, that in the optimal-case scenario the CEO (and other top leaders) would make decisions based on information (called it Business Intelligence related to the budget plan) that is immediate, dynamic, and true to a comprehensive plan model. Likewise, along with the C-level staff, virtually all other stakeholders—end-users, financial staff, departmental leadership—would be connected as well.
[Making the Complicated Simple: PowerExcel, at its core, is a shared, collaborative model with a familiar front-end—whether that front end is Excel, Power BI, other dashboards, PowerPoint, Word, a Web page, and so on. One of PowerExcel’s greatest benefits is to bring into the budgeting process all users, simply. It removes the complication of users working with disconnected data sets, which in the overall is one the greatest and worst problems of budgeting the way it is currently done. At the same time, PowerExcel ensures the most rigorous security access to data in the model (budgeting or otherwise), fully customizable by user, role, time period, department—essentially by any “dimension of the business,” and down to the single data point level.]
It is worthwhile next to ask, Where does the budget data come from?
Stepping back, we know that the basis of the budget data must include “the Actuals.” But is it conceivable for IT to write queries and provide a wholly valid “snapshot in time” of, say, actual current (present year) accounts (e.g., Revenue)? Sadly, it is not conceivable…but it is likely the start of things. From an accounting standpoint, the problem is that Revenue can be booked differently and brought together differently from the way results are obtained by IT via queries to the ERP system(s). This problem will potentially redound across many different product lines and many different lines of business. And so a dance (putting it euphemistically) occurs in trying to figure out how the figures that IT has provided should tie back to the way the accounting staff needs to book and organize it.
As the budgeting exercise occurs before the current year is over, Finance will need also to obtain data from, quite likely, another system (e.g., the CRM) or systems, so that they can calculate forecasted opportunities and product sales (e.g., auto-generated renewals) through the current year’s end, taking into account current/expected trends. Here again Finance may approach IT, and a whole series of discussions about how revenue is brought together and should be booked will take place with data that has yet to be, well, Actualized.
Centers of Excellence have been established and Project Managers hired to make right the differences that exist between Finance and IT…initiatives that, while potentially helpful, add another layer of complexity. (And additional work: e.g., the IT staff being directed to address a requirements document that will initiate additional queries intended to match more closely to what Finance is looking for.]
The irony here is that although the Actuals (the “basis” of the budget data) exists with IT, and IT is responsible for all systems and software (Excel included), the budgeting data model by this point is in ongoing, continuous development by Finance. And the tool that Finance uses, often to the exclusion of all other tools, is Excel. The budgeting model itself takes shape around Excel, with data coming—sometimes grudgingly, often “incorrectly”—from other systems.
An ideal solution to resolve the complication forced upon Finance and IT would be a centralized data system—which is why the concept of the data warehouse was introduced. Regrettably, data warehouses have simply become more refined storage areas for data that still must be “worked over” by Finance. Data warehouse systems are, like transaction-record databases, built on table-based technologies: they do not have the agility to accommodate the work that Finance must do to reconcile data from other table-based applications (ERPs, CRMs, etc.)
[Making the Complicated Simple: The PowerExcel modeling environment not only allows dynamic connectivity to all user front ends, but also acts, at the back end, as a nexus between and among all types of database systems—SQL, SAP, Access, data stored in spreadsheets, etc. In this respect PowerExcel extends and improves upon the concept and practice of a data warehouse: it features a centralized multidimensional database that has none of the limitations of a table-based transactional system, but which instead is designed for the fasted possible execution of calculations from differently-sourced data, as well as from data entered by users. In short: model calculation occurs upon pressing F9, not by the execution of a repeated query/queries.]
Once Finance essentially becomes the prime architect of the budgeting system, they can build in the calculations they need using their Excel skills: adjustments, allocations, hierarchies, presumptive drivers, allowances for inflation and currency fluctuations, KPIs, etc. Just as there’s no limit to Excel, there’s no limit to what an Excel spreadsheet user can do! That said, in the real world—as so many firms learn from experience—there is only so much that Excel can handle as a modeling environment for a large, complex organization.
And at this point—once Actuals are accounted for, including projected costs (which go through their own adjustments for allocations and anticipated events through the end of the current year)—the crippling complications in the budgeting process really begin to be felt. Now is when the CFO’s team spends untold hours to arrive at figures that are valid to their own and the firm’s (from an accounting standpoint) needs. The almost-certainly Excel-based system, now owned by Finance is: disconnected from other data sources (no dynamism with transaction records, owned by IT); disconnected from other users (other than Finance); fathomable only by Finance, and; subject to the limitations of a personal productivity tool (Excel) that was never built to be an enterprise-wide modeling (budget or otherwise) application, much less an efficient calculation database.
[Making the Complicated Simple: PowerExcel not only connects to all systems, but also gives Finance ownership and allows them to use the skills they have—or at least be confident (if an expert team is helping with creation of the application) that they both understand and can make logical (a.k.a., accounting-compliant) changes to a business model. In sum, Finance can apply concepts and skills from Excel to customize the model at will for accounting-based financial-formula calculations—calculations performed in real time on data as it is recorded in underlying databases and, for planning purposes, on data entered by budget contributors.]
Also at this point, the entry and updating of budget entry figures from departmental leaders/staff may yet remain to be done!—their contributions of course are necessary from the bottom-up level.
The CEO’s vision in the end will be determinative; that said, other department leaders will have their own ideas about what might be done to increase their department’s and the firm’s profitability, not to mention how to cut costs, etc., in furtherance of the vision articulated for the firm.
The Finance team has by this point worked as midwives (did labor ever take so long?) in making adjustments to Actuals and in creating/managing the de facto budget model application. Now they must work as psychologists, consulting with end-users and translating what they think can be done departmentally, in order to validate these business suppositions into the model. The Finance team also works as taskmasters, to obtain on-time budget contribution figures for each go-around (there are deadlines, after all, for different stages of the budgeting process). All too often, at the end of the line, those who must submit budget numbers may be told by Finance, “I’ll send you a spreadsheet, just type in your final numbers.”
[Making the Complicated Simple: With dynamic connectivity to all users and data centralized with in a single-version-of-truth budgeting model, PowerExcel frees Finance to work at the job they were hired to do: provide the quickest and most informed financial analytics and advice about the running of the business.]
Budget Iterations and the Need for Versioning and Forecasting
Whether before budget entries have been made by contributors—and certainly after a first-cut budget plan has been formulated—the CEO will begin to weigh the possibility of larger-scale initiatives (these may be contemplated by the CEO and other C-level staff, or they may be proposed by business entity/unit leaders): for example, purchasing another company; adding a new product line; undertaking a significant initiative meant to increase sales by 10%; a large capital expenditure, etc. These initiatives align with top-level scenario planning, but the consequences filter down to lower levels of granularity in the budget model and have their own associated accounting implications—e.g., a product line may need to be cut, which will impact revenue (and costs), or new staff may need to be hired to generate increased sales (also affecting costs, e.g., human resources). As a consequence of these decisions, some departments “win,” while others may see their proposed budgets cut back.
At the so-called bottom of the chain, and based on new strategies set by the CEO, those end users need to enter new figures. Those figures must be collected and re-calculated into the budget model. So the application ends up, on an ongoing basis, with data from IT (ERP system, for example); data from systems used by Finance and other departments (e.g., the CRM); calculations (for accruals, adjustments, allocations, etc., done by Finance people, in spreadsheets almost certainly); and data sent back by end users for inclusion. (“Please just fill in the spreadsheet again and send it back to me, there’s new information on your budget targets…)
As different initiatives, strategies and premises are considered, leaders up to the CEO will reasonably want to make decisions not only for different successive budget iterations, but also for different versions of the planning application: the budget model grows to include what are essentially different segments of the budget plan, which may include breakout plans for different entities within the business.
For Finance, the possibility or even occurrence of unforeseen events (e.g., a pandemic) must also be taken into account. And on the basis of updated Actuals and budget models, regular—or in the best-case scenario, “continuous”—forecasting must be done to make the most immediate course corrections to how the business is managed.
And so the process goes on through successive iterations: contributors must enter new figures that take into account changes they have been directed to make to their budgets. These new figures must undergo new calculations, down the line, and then up into aggregations. This happens repeatedly over time as the budget is worked on and passed up to the C level staff, ultimately the CEO. Finance takes on the full role as mediating genies, no longer with pencils in hand, but with plenty of spreadsheets a click away!
[Making the Complicated Simple: PowerExcel enables the healthy, collaborative back-and-forth, and the modeling and remodeling of a budget application: changes required of the topmost leaders can be understood, appreciated, and acted upon by budget participants; entries by budget contributors are dynamic to the model. All of this builds consensus in the budgeting exercise. Furthermore, PowerExcel provides for essentially limitless budget versioning over time and based on different initiatives and strategies. As well, the budget model will form the basis of ongoing/continuous forecast planning, which is also critical to a business’s future success.
Excel: Sharpening Its Use as a Tool for Budgeting
Excel is everywhere in the budgeting process. Whether used by budget contributors as a front end, printed in a board book and used as the basis for a dashboard, it is almost certainly used by the Finance staff for the calculations necessary to create and validate numbers in a budgeting model. This includes for eventual entry into the “main” budgeting application—e.g., a web-based or ERP-centric system. Indeed, the entire budgeting model itself may, in part or in whole, be stored as an XLS file (!).
Any firm whose experience matches the budgeting process as described in the preceding pages will acknowledge that Excel plays a critical role in its budgeting process. And yet that firm would, through experience, almost certainly admit that, for all that Excel punches above its weight, it is sub-optimal or worse for complex budgeting needs.
There is no need to repeat what is already well known about the consequences of “over-reliance” on Excel or to retell stories of companies that have misrepresented their reporting numbers on the basis of spreadsheet errors. We will just restate that Excel is pervasive in budgeting, but its use has unfortunate consequences, measured in time or effort or mistakes—or, more likely, all three.
And yet: displacing Excel is not really feasible without a complete conversion onto another system to the complete exclusion of Excel. It would mean going “cold turkey”—and the likelihood of that happening among Finance professionals is next to nil.
What, therefore, would an ideal budgeting solution look like?
There is a quote attributed to Abraham Lincoln: “If I had 6 hours to cut down a tree, I’d spend the first 4 hours sharpening my ax.”
Making the Complicated Simple: Rather than using Excel alone—or over-using Excel as a tool—PowerExcel is an ax for budgeting. Whether the user is the most expert, a budget contributor, the Finance staff, the CFO, or the CFO, nothing could be more simple!
PARIS Technologies develops software solutions, including PowerExcel , based on these precepts of Visionary Intelligence :
- Multidimensional Database – Relational databases are limited to one dimension. A spreadsheet has two dimensions, which can be referred to as a two-dimensional cube composed of Rows and Columns. Even the smallest of enterprises cannot model their business in two dimensions. (A small enterprise could need five logical dimensions to understand its business: Accounts, Months, Year, Product, and Version). In fact, there is a need for multidimensional-modeling—cubes—that is robust enough to support the needs of the enterprise. A multidimensional database will consist of multiple cube structures with a powerful modeling language. This database will become the central nervous system for communicating the vision; controlling against the vision, and; delivering decision support.
- Data Independence – As an example, data would need to be independent of spreadsheets. Data would be stored in a multidimensional database. When the data is independent, it opens the possibility for data to be collaboratively shared, collected, and displayed in many spreadsheets simultaneously.
- Basic Math, Algebra, and Calculus – The multidimensional database would need to be able to perform mathematical functions normally found in a two-dimensional spreadsheet, but now across all the dimensions of a multidimensional cube. In addition, the multidimensional database would do the same mathematical functions across multiple cubes.
- Relational Transformational Connectivity – The multidimensional database would need to be able to connect directly to a one-dimensional relational database table and transform it live to a multidimensional database. This connection would have bidirectional capability.
- Spreadsheet Transformational Connectivity – The multidimensional database would need to be able to connect directly to a two-dimensional spreadsheet and transform it live to a multidimensional database. This connection would have bidirectional capability.
- Dashboard Transformational Connectivity – The multidimensional database would need to be able to connect directly to dashboard products and display it live from a multidimensional database.
- End-User Tool Transformational Connectivity – The multidimensional database would need to be able to connect directly to other end-user tools (such as Word, PowerPoint, etc.) and transform data live from a multidimensional database.
- Collaboration Transformational Connectivity – The multidimensional database would need to be collaborative, i.e., allowing many simultaneous connections for Relational; Spreadsheet; Dashboard and other End-User tools to connect directly to other end-user tools (such as Word, PowerPoint, etc.) and transform data live from the multidimensional database.
- Web Services – The multidimensional database would need to have secure internet capabilities that support global enterprises with live data to and from the multidimensional database.
- Compatibility – The multidimensional database would need to have compatibility when used with any tools. In that respect it would not interfere with the normal use of any tool. It would add the capability to capture and/or report on collaborative data from the multidimensional database.
- Security – The multidimensional database would need security for creating, updating and changing dimensions, cubes, formulas, and any data at the most granular level of detail.
SUMMARY OF BENEFITS
Executing on this vision will enable financial process improvement and will deliver unprecedented gains in ROI and productivity. It would provide management with a single, fully capable, integrated, Visionary Intelligence process that delivers the comfort and guidance to run the business.
