Many successful companies now recognize the limitations that come with an Excel-based Annual Budget process. They realize that Excel does what it was intended to do extremely well – work flexibly with different forecasting methods – but with that comes the overhead of diagnosing and correcting errors that are often hard to catch. In Excel you must wrestle with tracking changes, version control, as well as keeping track of who performs the changes. Even with solid budgets created, accounting must contend with consolidating those budgets across the company.
As a principal consultant for ISI, I have worked with many companies to help automate the budgeting process. In that process, we have developed purpose-built software and data models as part of a methodology that encapsulates the following:
- Recognize the consistency between budgeting accounts – All companies I have worked with over the years have ultimately created their budgets at the General Ledger account level. In most cases, there is a subset of accounts where budgets are created. This set of accounts may vary in some departments or areas of the company. Usually a consistent set gets used between like areas of the company in which accounts are required.
- Provide an automated framework for the budgeting process – In addition, there often is consistency in methods of creating the forecast. These methods include things like a percent change off of last year, some combination of other accounts times a factor such as a percent of sales, or some other rule based method of forecasting.
- Automate budgeting concepts – Another component of a budget or forecast is spreading an annual number to each of the 12 months for an annual budget. In some companies and in some areas of the forecast, it is important to apply seasonality to how an annual number is spread across the months. This is particularly applicable in organizations that are affected by weather or some other cycle of the business.
The Approach
So how do you do all of this without Excel? I mentioned that we create purpose-built software and data models – which means going beyond just a few tables in a database and a couple of user interfaces. Purpose-built means keeping the system flexible for key aspects of the budgeting process while remaining easy to use. Questions we answer before we build include:
- How much flexibility do you want to give your users? Some companies like to impose consistency in how a particular account is forecasted, requiring each area of the company to do it the same way. Others like to give the field great flexibility in how an account budget is generated. This includes global control of formulas and algorithms, which I talk more about below.
- What flexibility do we need with sub accounts? By establishing a meta data table (account definition table) you can define how each account, or perhaps group of accounts, is forecasted. Revenue accounts can be forecasted one way or certain Sales accounts are forecasted another way. All of this can be controlled by attributes stored in an account meta data table where you establish the rules that govern how an account is forecasted.
- How will users interact? A user interface can be created to maintain this account meta data which is typically available only to a budget administrator.
The Benefits
By now you probably already recognize the potential that this approach can bring to your budgeting process. That change is always positive and dramatic, yet in my experience the benefits always arise in different areas for different companies. To illustrate this concept, I wanted to wrap up by discussing some of the bigger gains we typically see.
As I mentioned above, a big win with this approach lies in the ability to control formulas and algorithms in one central location in the system. Formulas might include a factor times last year’s budget or perhaps last year’s actuals. Something like taxes or interest expense can be much more complicated and defined by a series of other accounts that result in the account being forecasted. All of this can be defined in one place, or distributed among key users of the system, who establish the methods available and then create the “meta data” required to define each method. Once this is established, the methods can be made available to individual accounts or groups of accounts.
Once you have established a system to support the budget process, it is a fairly simple extension to provide workflow within the system. This functionality will provide the ability to track and control the budget process and also establish the ability to provide versioning. Workflow can mean emails or texts sent to appropriate owners and roles in the system, locking down data as appropriate or any combination of notification and security that makes sense.
Another big payoff comes from consolidating the various entities involved in the budget process. Each budget unit uses the same structure and rolling up the budgets is no different than rolling up actual data each month. The big gain here lies in the fact that, once formulas are established, you will never find errors introduced by bad formulas created by individual budgeters.
One final benefit: a logical connection between budgets and forecasts. I have used the terms “forecast” and “budget” somewhat interchangeably. Many companies create their annual budget and then do a reforecast each month throughout the year. The above forecasting method is very conducive to creating ongoing forecasts. We often include forecasting methods that do things like keep the annual budget fixed and reforecast the remainder of the year based on year to date results or perhaps apply the same rate of change to the remaining months as experienced so far. The beauty of this is it establishes one system and one forecasting process that can be used year round that has all of the controls you wish you had in Excel.
Sound interesting? We would be happy to meet with you off the clock to demonstrate the process, discuss how to tailor this approach your company, and help you take advantage of this kind of budgeting process.