Hello - we are in the process of evaluating new Budgeting software to perform an integrated P&L, Balance Sheet and Cashflow at store and product level. We also want to use this for the 5 year plan. My question is, what Budgeting software do mid-size Retailers (40-200 stores) use to perform their forecasts? Do many use
Budgeting and Forecasting in Retail
Answers
The most important criteria I have seen for Retailers (and Consumer Goods vendors as well) when selecting Budgeting, Planning, Forecasting software relates to 2 areas:
1) flexibility around the time dimension
2) scalability of custom dimensions
Regarding the time dimension, I'm not sure if this applies to your company or not, but many if not most retailers operate using a 13-Period calendar each with 4-weeks (except for 53 week years) as opposed to the more traditional 12-Month calendar. Not all software is flexible enough to handle the 13-Period calendar, so that's an important consideration.
Scalability of custom dimensions for Stores and Products is important as well. If you plan at store level and you sell thousands of products, and want to plan Revenue and Gross Margin at that level of detail (i.e., the individual product level), that will tend to push you toward high-end products that can handle that size of model.
If by contrast, you plan at a more aggregated product level (e.g., 20 Product Groups instead of 12,000 SKU's/UPC's), then there are a wider array of solutions that you could consider.
Because of the needs around the time dimension and the scalability of the Product dimension in particular, I have seen bigger retailers tend to select one of the "Big 3" solutions: Oracle/Hyperion Planning/Essbase, SAP BPC, IBM/Cognos TM1.
However, for mid-market firms, the time to value or total cost of ownership associated with the "Big 3" solutions can be prohibitive.
So I have seen mid-market firms, tend to gravitate to cloud solutions due to faster time to value and/or lower total cost of ownership.
Adaptive Planning has more customers than all the other cloud planning solutions combined, so it is a leader in that sense. However, today, Adaptive Planning only supports a 12-month calendar, so if you need a weekly and/or 13-period calendar today, then it won't be a viable solution.
Anaplan is another cloud-based solution that is newer than Adaptive Planning. Anaplan tends to sell to customers who are on average larger than Adaptive Planning (greater mix of Fortune 500 vs. mid-market), and is more expensive than Adaptive. However, it can handle a weekly and 13-Period calendar; and today it can handle larger dimensions than Adaptive Planning. For example, Adaptive would struggle with a product dimension that had 10,000 UPC's, but Anaplan can handle this scale.
Please feel free to contact me at [email protected] if you would like to learn more including I could show you a demo.
Good luck!
1
Anonymous, check out this free report here at Proformative:
"Budgeting Without Spreadsheets"
https://www.proformative.com/whitepapers/budgeting-without-spreadsheets
Best of luck... Sarah
I worked as a TM1 consultant for about ten years, and I've seen it used effectively in many retail companies. One outstanding feature of TM1 is that it provides worksheet functions that return data from the database to Excel at the cell level, and write data from Excel at the cell level back to the database. (This is often called "write-back.")
To give you an idea about the benefits of write-back, I once created a process that combined two methods to forecast revenue. First, as the high-tech company had always done, they had their field sales force forecast sales by product by month within their region. Those forecasts were sent to the TM1 database.
Second, I created a single time-series forecast in Excel that returned historical data for any given region by product by month over the previous years. Then I used classic time-series forecasting techniques to forecast the trends into the new year. (Yes, the forecast handled seasonality, like a retail company would need.) This forecast included a series of TM1 worksheet functions that wrote my forecast with all its detail to the TM1 database.
To update the forecast for each region, I would simply choose a new region name in a cell in Excel, recalculate to update the forecast and to write it to the server, pick another region, recalculate, and so on. If there had been many regions, I could have written a very short macro to do that "work" for me.
At this point, I had two competing forecasts in TM1. One was generated by people who knew their regions well and the other was generated by statistics. I created a worksheet that compared the two. When I found major differences, I went back to the sales force and asked why they were predicting a major change from previous trends. Sometimes, they gave me some great answers.
The revised forecast turned out to be quite accurate.
These days, I also would look seriously at two products that compete with TM1, by PARIS Technologies...
...PowerOLAP works about like TM1, but is more reasonably priced and has more worksheet functions. (One of my customers works for a major chain of home improvement stores. At my recommendation, he bought PowerOLAP for his small department. Today, according to one of the company's senior managers, they run their entire company with PowerOLAP.)
...Olation. Both TM1 and PowerOLAP use proprietary file formats for their data...which IT departments hate. So PARIS's new product, Olation, can read from and write to relational file formats. Not only does this allow IT to use standard methods to maintain their files, it allows Excel users to use worksheet formulas to return data from their ERP to Excel in real time. It also allows them to write budget and forecast data from Excel to relational files, files that most BI software can then report directly.
Using a spreadsheet (e.g., MS-Excel) to prepare a corporate budget is generally a bad idea for many reasons:
Spreadsheets are error prone, no matter how careful you are in your design and implementation process. They are very hard to scale and maintain and can be extremely complex to manage when there are many reporting entities and other business dimensions that need to be consolidated and then later on analyzed against actual
Using the ERP or accounting software built-in budget function is very limiting, as typically you will be entering one budget number per G/L account (you may have multiple versions of the budget available in the software), without the ability to drive the model using your assumptions and other business specific drivers and logic.
As Bill Aiken pointed out in his response, you have several choices if you don't have to have 13 period (or worse, weekly) reporting, as such solutions are going to be cost prohibitive and very complex to install and maintain.
Of all the small to mid size business applications, I have had great success with Budget Maestro from Centage corporation (www.centage.com). It was designed specifically for the SMB market (including the retail industry), will scale to any reasonable size and will automatically generate an Income Statement, Balance Sheet and Statement of Cash Flows without any user programming, formulas, etc., using the forecast input from its various modules (revenue, expense, personnel, fixed assets and other). You don’t do any programming at all because the software has all the business logic and tools built-in to model any budget line in any budget category.
My experience has been that setup can be done in a few days, depending on the size of the organization and complexity of the model, and maintenance is minimal and is usually done in-house with no outside consulting.
Transferring your G/L account balances at period end into the software is very easy and there is a direct link to some of the more popular G/Ls, which makes the process even easier.
The most important lesson I learned from participating in the budget and forecast process is that unless there is a practical solution in place, matched to the requirements and the way the organization works, you usually don’t fully benefit from this process.
There is very little value in going through the budget process without proper periodic and timely analysis of actual results vs. budget. I don’t know any other way to be able to confidently drive the decision making process.
1
I agree that the way most people prepare budgets and forecasts in Excel is error-prone. It also takes them many hours of work.
I should know. I've used spreadsheets for 35 years, and have prepared many sales and cash flow forecasts in VisiCalc, Lotus 1-2-3, and Excel.
But even though I was young, and was using primitive tools in those early years, my forecasts were pretty darned accurate because I avoided simplifying assumptions like a 10% sales growth or using DSO to forecast AR. And it was also because I tailored my forecasts to the unique characteristics of each business.
But these days, Excel-friendly OLAP tools like TM1, PowerOLAP, and Olation use Excel as the user interface and analytical engine, but store and consolidate both the historical and projected data on the server. This allows Subject-Matter Experts (SMEs) in the Finance Department to be in control of the budget and forecasting assumptions, not the IT department.
Excel-friendly OLAP products like the three I mentioned allow any Excel user with the appropriate license and credentials to report and analyze historical and projected data month-by-month, region-by-region, account-by-account, and so on. Therefore, those tools make it much easier for SMEs to discover company-specific problems and opportunities in the data.
I've seen these solutions work very successfully in just about any industry you could name, including home improvement, cellular services, interstate trucking, manufacturing, public utilities, and fat rendering.
I have no particular ax to grind here. I don't sell OLAP and I'm no longer an OLAP consultant. But I'm a huge fan of Excel-friendly OLAP products...those that offer read/write worksheet functions that talk to OLAP databases.
And finally, with regard to Excel errors, using simple techniques can virtually eliminate errors in Excel reports and analyses. But that's a topic for another day.
Filed Under:
FP&A