The writer of this article is a qualified Management Accountant, who has built and worked with Excel spreadsheet forecasting models in a past accountancy life, and is now an SAP BPC functional consultant at Column5 Consulting. The experiences gained from both lines of work have been drawn upon to analyse the benefits of replacing spreadsheet models with SAP BPC.
Spreadsheets such as Microsoft Excel are tools that offer wide functionality and flexibility to build a forecasting model. This article considers various features and drawbacks of spreadsheet forecasting models and analyses how SAP BPC replaces and improves upon these functionalities and overcomes drawbacks.
- Excel spreadsheet models enable a model to be built that incorporates all of the necessary forecasting assumptions and calculations and extrapolates them forward over the forecasting period.
SAP BPC also enables all of the necessary assumptions and calculations to be specified within the system and then be performed. The driver-based forecasting capabilities of SAP BPC enable a forecasting structure to be built that relies on users entering forecast drivers and then running system calculations to generate forecasts, for example entering forecast sales volume and prices. The system then performs these calculations. A spreadsheet model may be very large and cumbersome if it needs to be used for a large number of product lines, each requiring volumes and prices to be input in order to generate revenues and associated costs but SAP BPC can be used to enter just these drivers and then calculations are performed behind the scenes. The implementation of SAP BPC is a good opportunity to re-evaluate the forecasting process, integrate processes and ensure good practice (e.g. maximising the use of drivers for forecasts focusing on the key and material drivers of revenue and costs).
- Spreadsheets are widely used tools that are familiar to existing users and new recruits which is why this accepted interface is valuable.
SAP BPC has an Excel Add-In based reporting tool that is used to build input schedules for data entry and reports. This combines that familiarity of Excel with robust SAP BPC functionality.
- Forecasting spreadsheets are effective when built but can be prone to errors such as incorrect formulae and take extensive time to re-build if the forecasting basis is changed. The effort can be especially great because every formula in every worksheet potentially needs to be written and copied across columns and worksheets to change the model. Formula errors can be hard to spot and are sometimes overlooked or even widely applied.
SAP BPC accounting and forecasting structures are specified once in the system with functionality such as master data hierarchies and system calculations. The structures are then applied uniformly across the whole system. The system has financial modelling capabilities to calculate the financial results (i.e. calculating P&L, BS and Cash Flow).
- Excel models often require that prior periods’ actuals are entered or copied into the model as a starting point and to enable full year forecasts to be reported. This can be a time consuming and sometimes error prone process.
SAP BPC is used to upload or copy the necessary actual data directly into the forecasting category with little manual effort.
- Departments and companies within a group may develop different forecasting models of varying quality and consistency of structure.
SAP BPC has the flexibility to accommodate the necessary differences in forecasting processes for different business lines. It is often used to develop a consistent and high quality forecasting process that is used across a business. This limits duplication of effort to develop forecasting methodologies and can result in greater confidence in the resulting forecast.
- The consolidation of forecasts in Excel requires a separate worksheet for each level or view of consolidation. This worksheet needs to contain formulae for each figure in other worksheets that it needs to add up. If the consolidation includes forecasts in separate files, the use of external Excel links is required. This adds additional risk of error because links may stop working if the layout of one of the source spreadsheets has changed. It is sometimes necessary to re-consolidate manually every time a source forecast changes. If the structure of source forecasts changes, it may be necessary to change the consolidation spreadsheets as well. These consolidation methodologies are often a significant contributor to the amount of time necessary to perform a forecast.
SAP BPC posts all data in the forecasting process into a single database. The master data structure includes hierarchies. Nodes within these hierarchies can be selected in reports in order to perform consolidations. When standard reports have been built for all required consolidations, they can be refreshed on demand to perform consolidations.
- The “data island” effect of spreadsheets creates a range of problems such as consolidation (as discussed above), redundancy of data due to copying, as well as potential errors in copying and reporting.
The SAP BPC Platform uses a shared database and master data. This makes reporting and data transfer simpler and maintains consistency across an organisation (while allowing flexibility in models for different teams/business lines and geographies).
- The resulting forecasts are usually subject to future analysis. Key examples of this are variance analysis against past forecasts during the forecasting cycle and future months’ actual results. The spreadsheet based forecast may need to be copied into reporting spreadsheets or other reporting systems to enable this analysis.
SAP BPC is also a highly flexible and capable reporting tool. Its database pools all forecasts into one data source and reports can be specified according to user requirements. It is commonplace for actual data to be uploaded monthly and SAP BPC to be used for reporting including against forecasts. This functionality enables reporting to be generated by refreshing standard reports each month when all the data is present. This saves significant manual effort and shortens the monthly reporting cycle.
- The organisation and monitoring of forecasting progress is difficult when different companies and departments hold spreadsheets remotely.
SAP BPC offers functionality to organise the forecasting process and monitor it at each stage. The system has workflow functionality, known as Business Process Flows. This can guide the creation of forecasts making sure that key users focus on the material items that really matter, perform each step in the right order and are guided through the rest of the process in a highly automated way. The person at the summit of the forecast can manage the process through visibility into the status of each user and where they are in the workflow, thus improving communication and speed of forecasting
In summary, spreadsheets are very flexible and can in theory accommodate complicated forecasting processes. In practice, complex forecasting processes, especially when there are large consolidations involved, face the practical limitations of spreadsheets. An efficient forecasting process should keep the effort with the mechanics of the forecast to a minimum so that the focus can be on the commercial judgment required to perform and review a forecast. The use of a spreadsheet can be inefficient in this area. The more complex the forecasting process is and the greater the volumes to consolidate, the more impractical spreadsheet models become and the longer the forecasting cycle is. These limitations can limit the frequency and quality of forecasts.
SAP BPC is designed to overcome these limitations of spreadsheets as described in this article and make the forecasting process simpler, more accurate, and less time consuming.
Interested in learning more about the planned innovations and future direction that SAP has up their sleeves? Join Column5 and SAP’s Pras Chatterjee for an informative discussion on the updated roadmap for BPC and EPM including BusinessObjects Cloud.
SAP BW-IP, SAP BPC NW 10.1 Standard and SAP BPC NW 10.1 Embedded Compared