Continuous Planning with Rolling Forecasts: Finding the Holy Grail
If your company is like most, the July through September timeframe is when you put together next year’s annual budget. This annual budget can function as an effective means for spending control. However, as “The Planning Survey 14” from BARC describes, annual budgets are nearly outdated by the time the yearly planning cycle is over, rendering them ineffective as a performance management tool. This means most companies really need rolling forecasts to update their budget data throughout the year in monthly or quarterly intervals.
If you’ve just gone through a painstaking three-month annual budgeting process, the thought of a continuous planning process with rolling forecasts, perhaps on a monthly basis, is probably exhausting. The vast majority of companies ― 86 percent according to the BARC survey ― use Microsoft Excel for planning. Excel is the go-to tool for business professionals, but if it’s not directly connected to your ERP system or database, it is too cumbersome for monthly forecasts, not to mention risky in terms of data integrity.
In Aberdeen Group’s “Financial Planning, Budgeting, and Forecasting in the New Economy” study, 96 percent of best-in-class companies are characterized by the ability to track actual performance against budget, while 82% have the ability to reforecast as market conditions change. Most companies, however, don’t have this capability for continuous planning. Let’s compare the current state of budgeting, planning and forecasting for the majority of organizations with the tactics of best-in-class companies.
The Status Quo: Limited Functionality with Excel Alone
In the BARC survey, 97 percent of respondents viewed planning as important or very important, yet only 12 percent feel they have no problems with their planning process. Some of the top problems cited were lengthy process coordination (49%); strategy not considered for near-term planning/forecasting (42%); poor flexibility (29%), outdated plan once finalized (26%); and insufficient data quality (22%).
The problems listed are often made worse using Excel in the planning process.
If the spreadsheets don’t directly connect to the “live” ERP database, people may download static data to Excel at different times, creating multiple spreadsheet “versions of truth,” and then use them to create erroneous data for uploading. Then, there are inconsistencies from links among numerous spreadsheets, according to the survey.
The BARC survey indicates that conducting sub-budgeting activities exclusively with Excel takes 5 days for supplying actuals and inputting budget data; three days for transforming and consolidating it to produce the budget; and just three days for data analysis ― the most important part. It’s no wonder that companies with this planning and budgeting framework have difficulty contemplating rolling forecasts.
The Holy Grail: Continuous Planning and Forecasting
Many organizations are moving toward continuous planning and strategy cycles, according to the BARC survey. Eighty-one percent of best-in-class companies said they have a year-end forecast that replaces the prior period’s budget data with actuals and re-forecasts remaining months to the end of the fiscal year. Also, 47% of them use rolling forecasts that drop the previous period’s data and add another period to the end so they always forecast 12 periods ahead.
Utilizing these forecasts, managers can refine their forecasting techniques by learning from past periods. The forecasts also support more frequent evaluation of market conditions so managers can re-allocate resources to higher value areas and offer a way to merge planning with financial closes. The month-end close isn’t “what just happened,” but becomes a means for adjusting the next period.
For companies who can’t do this, the biggest impediments are time and money. How can we undertake a three-month process each month?? The key to continuous planning is incremental adjustments and not a complete rebuild. And, many times market conditions haven’t changed enough to even warrant a new forecast.
Money comes into play when organizations realize that a specialized ― and often expensive – planning tool is needed to accomplish continuous planning, because Excel alone is not flexible, accurate or timely enough to do so. But when the BARC survey asked why companies hadn’t adopted such a tool, 40% said they had a poor cost-benefit ratio. Other reasons were that the tools couldn’t fulfill requirements (32%), existing solutions were satisfactory (32%) and specialized tools were too complex (20%).
Attaining the Chalice
The BARC study highlights an important option ― specialized Excel planning add-ins ― for companies who shy away from expensive tools that don’t link directly to their ERP database or are extremely complex (like SAP’s BPC). These tools integrate their own functionality into an Excel interface so spreadsheets are the front end to the central database. With lightweight and non-disruptive Excel add-ins, companies get the functionality of a planning solution while continuing to plan in the already developed internal Excel model of choice. Most importantly, the inherent weakness of Excel by itself is eliminated.
An Excel planning add-in lets you validate and upload planning and forecasting data from Excel to ERP systems like SAP. It supports cost, revenue, activity and SKF planning. When used with an Excel reporting add-in, it can automatically access and refresh actual and plan data in the ERP system. Added to the mix, an Excel distribution add-in automates the distribution of templates and reports. This trio of add-ins installs quickly ― with no consultants or lengthy implementation ― and their price point is reasonable compared with specialized planning tools.
With Excel add-ins, users get the Holy Grail’s “special powers” to support continuous planning and forecasting. There is no painstaking, error-prone process of using Excel alone, yet users can plan more frequently using their favorite tool while being assured that data is from the single source of truth ― the ERP system. And, you avoid the expense and complexity of a specialized planning tool that might not truly fit your needs.
Author: Andrew Stutzman
Solutions Specialist, Excel4apps