Can You Easily Perform Multiple Budget Iterations?

Learn to take advantage of your FP&A software’s capabilities to generate multiple budget iterations

I am surprised to hear that many people who use the world’s most popular business software application, MS-Excel, are not aware of the existence of a useful function called “Solver”, found in the Analysis group in the Data tab after the Solver Add-in has been enabled in Excel. This Add-in was developed by Frontline Systems, Inc. (https://www.solver.com/).

The basic Solver is offered to all Excel users, and there is a premium version, recently renamed “Analytic Solver® Optimization”, which includes additional data mining and analysis tools, available for licensing directly from Frontline Systems.

A good example that demonstrates how Solver is applied in a manufacturing environment is the common problem of attempting to maximize gross profit given the availability of material, resources (both machines and people), and having specific constraints. Using this information, Solver can tell us what products to produce, assuming we can sell them, and in what quantities in order to achieve our goal of maximizing our gross profit for the period. Solver is able to do that by performing many iterations of the basic calculation using different values and staying within the identified constraints, ultimately optimizing the model and delivering the data required to make decisions.

The benefit here is obtained through the use of a computer and software (the built-in Excel Solver or the premium version) that performs an untold number of calculations that would be impossible to do manually.

Iterations in Corporate Budgeting

Borrowing from the Solver example, I was thinking about what it would be like to be able to perform many iterations of the corporate budget in order to optimize it and align it to certain goals contemplated by management.

Another good analogy is the use of spreadsheets to recalculate the entire workbook or individual worksheets when values in one or more cells are changed and the results observed. This allows for quick, “what-if” analysis of several or many scenarios, relying on the computer to almost instantly give us new answers. Then each scenario’s results can be individually saved.

Optimizing Corporate Budgets

What if we could use the same principles when preparing a corporate budget in a purpose-designed planning, budgeting, and analysis solution? Shouldn’t we be allowed to use a base budget and make certain changes to inputs such as drivers or assumptions and have the software regenerate the entire budget under a separate version and present the results for analysis? What about having different business units submit multiple budgets of their business units (e.g. departments) and have the software automatically generate a unique version of the base budget?

Finally, wouldn’t it be nice to have each of these budget versions automatically generate a full set of financial statements and other reports, and have the financial statements (Income Statement, Balance Sheet, and Statement of Cash Flows) always synchronized to the underlying versions of the budget?

Encouraging Budget Optimization

Being able to automate the versioning of the budget and perform several (or even many) iterations of the budget automatically and effortlessly is something that can only encourage management to insist on having the budget optimized and aligned with the business goals.

Using a manual process with exclusive use of spreadsheets in the budget process, or using one of several purpose-designed software solutions where making changes to the budget is a time-consuming, error-prone, and complex endeavor, is guaranteed to discourage finance management from performing the desired budget iterations, settling on one or two versions instead of many more that could be useful in arriving at the optimal budget.

Addressing Common Budget Flaws

This is a common flaw in the budget preparation process and usually stems from the inability to quickly generate several or more versions based on different scenarios. This often results in the creation of a final budget that may not be realistic or even achievable and will cause large variances of actuals vs. budget numbers.

No matter how sophisticated your budget process and tools are, you will encounter variances; this is completely normal and expected. What is not acceptable is reliance on a deficient process or inadequate tools, especially at a time where planning and budgeting tools featuring Intelligent Planning technology are available even to smaller enterprises.

Intelligent Planning Technology

With Intelligent Planning, there is a set of tools specifically designed to improve automation (by use of built-in business logic and accounting rules) and collaboration, thus delivering forecasted financial statements that are an extension of actual accounting financial statements. Intelligent Planning provides the ability to quickly and effortlessly generate multiple versions of the budget, affording management the capability of analyzing and optimizing the budget to meet planning and business goals.

While the technology is relatively new, it has already found its way into many SMBs (Small and Medium-size Businesses) who realize the importance of optimizing the budget through multiple iterations and analyses while always keeping financial reports and statements in perfect synchronization with the various budget versions. I expect this to be the new standard in this software category and become an indispensable feature in all FP&A software solutions.

‍

Alan Hart - BioAlan Hart, MBA, is Principal Consultant at Pacific Shine Group in Portland, Oregon, with responsibility for client business development and hands-on client project implementations. Prior to starting Pacific Shine Group, he worked in various executive accounting and finance positions with technology and growth companies. Notable is his 18 years in the hi-tech manufacturing industry where he served as Controller, Vice President of Finance and CFO of several privately as well as publicly held companies in the Hi-Tech industry, such as Hybrid Arts, Inc. Hamilton Bay Associates and Syncronys Software. In his role in management consulting, Alan has worked in diverse industries and with a variety of clients, including fortune 1000 companies such as Boeing, Delta Airlines, Intel, Wyndham Worldwide and others, as well as many mid-market organizations such as Guitar Center, Ducommun AeroStructures, Cypress Semiconductor, TriQuint Semiconductor and others.

Combining his skills and experience in engineering with deep understanding of technical accounting, he is able to assist small and medium-size manufacturing companies establish GAAP compliant accounting and reporting systems.