I'm working on a fascinating project at the moment implementing SAP BPC for a large company in the services sector. We're in the early stages of the project which includes working through their P&L to see how each section is currently budgeted and forecasted (in spreadsheets), and where it makes sense to bring those planning models into SAP BusinessObjects Planning and Consolidation (SAP BPC, to you and me).
The number of spreadsheets out there in the business that contribute, ultimately, to the financial budget is large and there's a great buzz amongst the project team who are keen to get as much of that into the new tool. Sadly, our budget and timescales are more restricted than our remit, so discussions recently have been coming down to how to prioritise the models that do get included in BPC, and those that can simply remain as offline planning spreadsheets whose results can simply be copied and pasted into a direct entry input schedule against a particular GL code. In doing so, I've come up with a list of factors that help determining how worthwhile it is to migrate a spreadsheet planning solution into BPC.
The pain of spreadsheet planning solutions is the administrative effort involved in gathering information from a large number of stakeholders planning in spreadsheets and consolidating it into a central spreadsheet model for reporting and analysis. This problem is eliminated by SAP BPC which automatically and painlessly consolidates data from a large number of users. Any spreadsheet model, however simple, is worthy of inclusion in BPC if it is completed by a large pool of users.
This leaves those one-off "homegrown" spreadsheets often developed out in the business, independent of central FP&A, whose rolled-up financial results get copied and pasted into the standard budget spreadsheet templates. It is here that hard decisions must be made.
Whilst the goal should always be to make financial plans as accurate as possible, it is important to learn to pick your battles. There's no point in spending considerable effort solutionising a complex spreadsheet that accurately predicts spend on lighbulbs, for example, if lightbulb expenditure represents a fraction of a percent of your overall OPEX and any variation in this spend makes no material impact to your EBIT. If, however, you are a factory and have a cost centre responsible for machinery maintenance, it may well make sense to model this significant cost in SAP BPC.
Impact from global assumptions and upstream plans
The case to model the machinery maintenance budget in SAP BPC is made all the more stronger if that model is impacted by figures budgeted elsewhere in the process. For example, your maintenance model may depend largely on the throughput of that machinery, ultimately related to your sales forecasts. By including this in BPC, the possibility to plan different scenarios is greatly increased; any changes to your sales forecasts will ripple through to your machinery maintenance costs automatically to give a fully balanced financial picture. This is a powerful capability.
Similarly, if your model has effects on other parts of the financial plan downstream, then the same arguments hold.
Complex, actualisable drivers
Explaining actual-budget variance can be unpleasant as you're only asked to do it when the variance is negative and you're being called to account. If you have complex drivers or a large dataset of drivers, then explaining that variance becomes all the more harder. Providing detailed variance analysis of effects such as currency, price mix, volume mix etc. is hard in a spreadsheet model and involves manual administration of the model to include actual data. Not so in a multi-dimensional database such as SAP BPC when those actuals can be automatically imported via BW and the variance reported on using a standard report.
Finally, of course this is not just an exercise in repeating what is currently done in spreadsheets and solutionising them. Another significant part of the exercise is to challenge what is currently being done and see whether the features and strengths of SAP BPC can enable more sophisticated/ accurate/ less time consuming means of budgeting.