Rolling Forecast in SAP BPC
Applies to: SAP BPC 5.1, BPC 7.0 MS, and BPC 7.0 NW.
Summary In the past, planning was very often a static process carried out once a year that intended to form the basis for the next year’s execution and budgets. Dynamic environments and fast-changing business situations force companies to think of strategies to react to changing conditions. Rolling forecasting is an ongoing process that compares the plan with the actual and derives an expected further evolution of the plan figures. If the deviation between plan and forecast extends beyond a certain range, the strategy has to be reevaluated in order to take appropriate measures or adjust the strategy. Rolling forecasting, thus, should be designed in a dynamical manner. Author:
Nilanjan Chatterjee
Company: Satyam Computer Services Limited Created on: 29 December 2009
Author Bio Nilanjan Chatterjee is an Associate Consultant with Satyam Computer Services Limited. Nilanjan has more than 2 years of experience on Business Process and Simulation (BPS) and Business Planning and Consolidation (BPC).
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 1
Rolling Forecast in SAP BPC
Table of Contents Dynamic Design for Rolling Forecast ................................................................................................................. 3 System Design ................................................................................................................................................ 3 BPC Admin Tasks ........................................................................................................................................................ 3 Designing the Input Schedule ...................................................................................................................................... 3
Disclaimer and Liability Notice .......................................................................................................................... 10
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 2
Rolling Forecast in SAP BPC
Dynamic Design for Rolling Forecast In the past, planning was very often a static process carried out once a year that intended to form the basis for the next year’s execution and budgets. Dynamic environments and fast-changing business situations force companies to think of strategies to react to changing conditions. Rolling forecasting is an ongoing process that compares the plan with the actual and derives an expected further evolution of the plan figures. If the deviation between plan and forecast extends beyond a certain range, the strategy has to be reevaluated in order to take appropriate measures or adjust the strategy. Rolling forecasting, thus, should be designed in a dynamical manner. The Rolling Forecast will be done based on the Products, for the Accounts – “Raw Material”, and “Packaging”. It should also include the member, “Direct Cost”, which is the Parent of Raw Material and Packaging. The Input Schedule, to be designed, will have Products in the ROW and Time, Account, and Category in the COLUMNS. System Design The simulation of Rolling Forecast can be achieved with the help of an input schedule in which the data for the actual posting periods should be locked for any further changes and the data for the forecast months should be enabled for any kind of manipulation. BPC Admin Tasks In the BPC Admin Console, create a dimension called Product and maintain few members for the same. In the Category dimension, create new members in the form of FCST_JAN, FCST_FEB, and so on. These categories will be used for doing the Rolling Forecast on a monthly basis. Forecasting for the entire year, done on each month, will be saved in the corresponding categories. Designing the Input Schedule Create a blank Input Schedule. On the first cell, A1, enter “=EVDRE($B$6,A17:B24,A27:B33)”. In this statement, the cell B6 contains the application name, A17 to B24 forms the RANGE and A27 to B33 forms the PARAMETER. Specify the Application name in the cell B6. The Structure of the Input Schedule should look something like below.
Fig - a
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 3
Rolling Forecast in SAP BPC
The parameters of the EVDRE function can be modified based on the cells occupied by the RANGE and the PARAMETER sections. For the above example, the EVDRE will look like
Fig - b
Keep the Product in the Row. Maintain Time, Account and Category in the Column. Note that the expansion has been done only on the ROWS and not on the COLUMNS.
Fig - c
For designing the Columns, look at the below screen
Fig - d
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 4
Rolling Forecast in SAP BPC
In the above screen, we have Time, Account, and Category in the Columns. To populate the members for the Time dimension, a function has been used (highlighted in the above fig). The next two columns (M6 and N6) also have the same formula to get the same member as in L6. For getting the members in O6, P6, and Q6, the formula used is
Fig - e
Similarly, the other columns are also populated. In this example, the Column goes till AU (3 columns for each month, starting from Column L). In the Row 7 for these Columns, “RawMaterial”, “Packaging”, and “DirectCost” has been hard – coded. In the Row 8 for these columns, the function which has been used is
Fig – f
Drag the above formula to the other columns in the Row 8. Considering $B$6 contains the Application Name and $B$14 contains the Current View, the above function will populate the appropriate Category.
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 5
Rolling Forecast in SAP BPC
Note: The current view should not be at the YEAR level (2009.TOTAL). It should be at the MONTH level (2009.JUN, 2009.AUG, etc). This is required, since; the planning will be done on a monthly basis. Based on the month, the Categories should be populated. So, if the CV is 2009.JUN, Category should be ACTUAL for 2009.JAN to 2009.MAY and the Category should be FCST_JUN for rest of the months. Similarly, if the CV is 2009.JUL, Category should be ACTUAL for 2009.JAN to 2009.JUN and the Category should be FCST_JUL for rest of the months.
Next step is to form the COLUMN Descriptions using the Rows 6, 7, and 8. The descriptions will be displayed in the Rows 17, 18, and 19, using the function EVDES(), as shown below
Fig – g
The same function can be used to populate the description in the other cell too. This step is not mandatory; however, it gives better view to the Users by displaying the Descriptions instead of IDs. Let’s define the different parameters in the RANGE section of the EVDRE report.
Fig - h
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 6
Rolling Forecast in SAP BPC
The “PageKeyRange” points to B7 to B14, where all the dimension members are available. These members are getting populated directly from the Current View. However, these can also be hard – coded to suit the requirements. The “ColKeyRange” points to L6 to AU8, where we have defined the Time, Account and the Category dimension members (refer to fig c, d, and e). The “RowKeyRange” points to I20 to I37, where the Row should be expanded. Next step is to fetch the records from the database based on the appropriate Dimension Members.
Fig - i
To fetch the appropriate records, use EVGTS function in the cell L20 as follows =EVGTS($B$6,1,$I20,L$6,L$7,L$8) Where, B6 – Application Name I20 – Product Member L6 – Time Member L7 – Account Member L8 – Category Member
Once all these designing activities are done, the Input Schedule can be expanded to populate data in all the cells.
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 7
Rolling Forecast in SAP BPC
The next activity, which can be done, is to give a better view to the Schedule by coloring the different cells. For this, the “FormatRange” can be used and should be designed as follows
Fig - j
The coloring can be used according to individual’s taste. The formatting has been used for 3 regions
Default – this formatting will be used for all the cells by default
Product.CALC = “Y” – this formatting has been used for the parent or calculated members in the Product dimension
SalesAccount.CALC = “Y” – this formatting has been used for the parent or calculated members in the SalesAccount dimension.
The cells corresponding to the Actual Category can be grayed out using Conditional Formatting.
Fig - k
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 8
Rolling Forecast in SAP BPC
The same Conditional Formatting can be used for all the data cells. It will Gray out the cell if the Category is Actual. The Input Schedule, designed so far, will give you the ability to do Planning / Forecasting of the future months based on the Actuals of the previous periods. This can be improved further by having Variances in the Columns, which will display the difference between the Forecast of the current month of current year and the Actual of the current month of previous year.
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 9
Rolling Forecast in SAP BPC
Disclaimer and Liability Notice This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade. SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk. SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.
SAP COMMUNITY NETWORK © 2009 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com 10
