Simple Rolling Forecast
Rolling forecast is a technique that is widely used in planning. As opposed to a ‘fixed’ plan that has a fixed setup and fixed end data in a rolling forecast the planning horizon moves on with time. A very simple version of a rolling forecast would be to always plan the next 12 month. So in a plan cycle executed in January 2017 the planning screen would offer the possibility to plan for all month from January 2017 till December 2017. In a plan cycle in March 2017 the system would offer to plan data from March 2017 till February 2018. In BPC Embedded the input screens are essentially defined by BW queries. And obviously instead of using many different queries (one for each new forecast) you would use a variable to define the interval for which plan columns should be visible. The variable could be an exit variable that determines the correct interval from the current system date.
This very simple rolling forecast can be easily realized in BPC Embedded model when you are using for example the dimension 0FISCPER or 0CALMONTH for the periods. You can base the planning on a BW query that has the periods in the drill down in the columns. In the fixed filter you use an interval in the selection for the period. As we do not have any data yet and must make sure that all columns are shown we have to set the access type for the result values for the period to ‘Master data driven’ or ‘Characteristic Relationships’ (also called ‘combination proposal’). For the first plan you would use the interval 01.2017 – 12.2017 (dimension: 0CALMONTH) and for the second forecast you would select 03.2017 – 02.2018.
Unfortunately the situation is usually not as simple as the model above. Just assume that you are using two dimensions for time – year and say the two digit month (0CALMONTH2). In the first case we can restrict the year to 2017 and the period to 01-12. If you now drill down by month and year you would get the expected result. But in the second case you would have to select the years 2017 and 2018 and the periods 01-12. As a result you would see all periods in 2017 and 2018 in the drill down. There are several options to overcome this problem:
1. Use characteristic relationships
If you have an additional characteristic in the column (drill down or used in a structure element you use in the column) you can use characteristic relationships to make sure only the allowed combinations are displayed. You have to use year and period in the drill down and again use the selection 2017-2018 for the year and 01-12 for the period. Set the version to your forecast version in the filter. Now create a characteristic relationship that only allows the desired combinations depending on the current month. You can do this by using a characteristic relationship type exit or by using a characteristic relationships of type selection and a new forecast version for each month. Please keep in mind that the combination proposal will generate all allowed combinations and will display them as input enabled. In addition the system will display all existing data that is in the selection but anything ‘outside’ the characteristic relationships will be shown as display only. If you want to make sure that you only see the 12 month and now older data that is already planned you should use a new plan version for every month.
2. Use structure elements with variables
Instead of using a drill down by the year/period you can use a structure in the columns and create one structure element for each of the columns. For each of the structure elements you use a different variable for year and month. Thus in our case you end up with 12 variables for year and 12 variables for the period. Obviously it makes sense to use exit variables so the variable values can be adopted to the current month automatically. Please note that with this approach you can also work with different versions for the different monthly forecasts. You will need additional variables for the versions.
The second approach also can be used to tackle another type of rolling forecast - the rolling forecast with ‘fixed’ ends. In this case you only do planning for a fixed period, say the current year. In January all columns for the 12 month of the year are open for planning (version: ‘Plan’). In February the column for January should not show the plan data any more but the actual data that is already available for the past month. Thus we have one column showing the actual data (version: ’Actual’), and the remaining columns should show the plan data. In March two columns show actual data, and 10 columns show plan data. In order to make sure that the actual data is not changeable you can use data slices (provided the actual data has been copied to the plan provider) or you can build your scenario on top of a CompositeProvider/MultiProvider and make sure that the data is read from the correct provider (you will need another set of 12 variables for the InfoProvider in the columns).
More Sophisticated Examples
Now let us have a look at a more sophisticated version of a rolling forecast. Here we have a longer time horizon and the granularity of the planning changes depending on the month.
Let us assume we are in the first quarter of 2017. We want to plan the next 12 month (i.e. January 2017 – December 2017) by month, the year 2018 by 4 quarters, and then another 5 years by year.
In February 2017 and March we assume we also plan until the end of the year 2017 in periods, the year 2018 by quarters, and the next 5 years as above.
Now in April 2017 we enter a new quarter. So we plan April 2017 – March 2018 as individual months, then the remaining 3 quarters of 2018, and again 5 more years.
This is just one example how such a rolling forecast can be done. Two facts are important in our example:
Such a query cannot be realized by using a drill down, we need a structure in the columns and have to create a structure element with the correct selection for each column. You could build a new query for each month of the year. But obviously this is a quite tedious work…
We have two other approaches to handle this problem:
1. Use the BW display variable.
Create all columns that are could be visible in any of situations above. In our example this would be a column for each month January 2017 – December 2017, and January 2018 - March 2018, Quarter 1 – Quarter 4 2018, and the following years (obviously when doing the planning after April 2017 additional columns will have to be added to the model).
Now select the structure in the query designer and go to the tab ‘Display’. Choose ‘Switchable via Default Variable’ for the visibility of the structure members.
Now execute the query – say in an Analysis for Office workbook.
You will receive a variable popup where you can select which of the columns should be displayed.
Obviously in order to make the scenario more flexible you should use variables for the year. As the number of columns can get quite large it is highly advisable to use variable variants so the work of selecting the individual columns has to be done only once.
2. By using Axis sharing in SAP BO Analysis for Office 2.4
In Analysis 2.4 we plan to introduce the new feature for axis sharing. The idea is that you can take two (or more) queries that have the same rows but different columns and bring them together in one table in AO. The connection between the queries is not just a visual one. Any change on say the restriction in the columns will be reflected on all columns of the visible result.
Now you can go ahead and create one query that shows the periods. Use the techniques described above to make sure that we display only the desired columns depending on the current month (say January 2017 – December 2017 or April 2017 – March 2018). Now create a second query for the quarters (number of quarters depending on the current month), and a third query for the years.
Use axis sharing to link the three parts together in the Analysis for Office workbook.
This solution even works on the case that one of the queries does not return any columns. This might happen in some scenarios where you might not want to see quarters at all but only plan by periods and years (e.g. last quarter 2017).
Summary and Outlook
We have given you above a glimpse of how a rolling forecast can be realized in BPC 10.1 Embedded. In order to create your concrete scenarios you will probably have to combine some of the above techniques.
In addition to the options shown above we are currently working on further features that will make the setup of rolling forecast scenarios even easier.
Rolling forecast is a technique that is widely used in planning. As opposed to a ‘fixed’ plan that has a fixed setup and fixed end data in a rolling forecast the planning horizon moves on with time. A very simple version of a rolling forecast would be to always plan the next 12 month. So in a plan cycle executed in January 2017 the planning screen would offer the possibility to plan for all month from January 2017 till December 2017. In a plan cycle in March 2017 the system would offer to plan data from March 2017 till February 2018. In BPC Embedded the input screens are essentially defined by BW queries. And obviously instead of using many different queries (one for each new forecast) you would use a variable to define the interval for which plan columns should be visible. The variable could be an exit variable that determines the correct interval from the current system date.
This very simple rolling forecast can be easily realized in BPC Embedded model when you are using for example the dimension 0FISCPER or 0CALMONTH for the periods. You can base the planning on a BW query that has the periods in the drill down in the columns. In the fixed filter you use an interval in the selection for the period. As we do not have any data yet and must make sure that all columns are shown we have to set the access type for the result values for the period to ‘Master data driven’ or ‘Characteristic Relationships’ (also called ‘combination proposal’). For the first plan you would use the interval 01.2017 – 12.2017 (dimension: 0CALMONTH) and for the second forecast you would select 03.2017 – 02.2018.
Unfortunately the situation is usually not as simple as the model above. Just assume that you are using two dimensions for time – year and say the two digit month (0CALMONTH2). In the first case we can restrict the year to 2017 and the period to 01-12. If you now drill down by month and year you would get the expected result. But in the second case you would have to select the years 2017 and 2018 and the periods 01-12. As a result you would see all periods in 2017 and 2018 in the drill down. There are several options to overcome this problem:
1. Use characteristic relationships
If you have an additional characteristic in the column (drill down or used in a structure element you use in the column) you can use characteristic relationships to make sure only the allowed combinations are displayed. You have to use year and period in the drill down and again use the selection 2017-2018 for the year and 01-12 for the period. Set the version to your forecast version in the filter. Now create a characteristic relationship that only allows the desired combinations depending on the current month. You can do this by using a characteristic relationship type exit or by using a characteristic relationships of type selection and a new forecast version for each month. Please keep in mind that the combination proposal will generate all allowed combinations and will display them as input enabled. In addition the system will display all existing data that is in the selection but anything ‘outside’ the characteristic relationships will be shown as display only. If you want to make sure that you only see the 12 month and now older data that is already planned you should use a new plan version for every month.
2. Use structure elements with variables
Instead of using a drill down by the year/period you can use a structure in the columns and create one structure element for each of the columns. For each of the structure elements you use a different variable for year and month. Thus in our case you end up with 12 variables for year and 12 variables for the period. Obviously it makes sense to use exit variables so the variable values can be adopted to the current month automatically. Please note that with this approach you can also work with different versions for the different monthly forecasts. You will need additional variables for the versions.
The second approach also can be used to tackle another type of rolling forecast - the rolling forecast with ‘fixed’ ends. In this case you only do planning for a fixed period, say the current year. In January all columns for the 12 month of the year are open for planning (version: ‘Plan’). In February the column for January should not show the plan data any more but the actual data that is already available for the past month. Thus we have one column showing the actual data (version: ’Actual’), and the remaining columns should show the plan data. In March two columns show actual data, and 10 columns show plan data. In order to make sure that the actual data is not changeable you can use data slices (provided the actual data has been copied to the plan provider) or you can build your scenario on top of a CompositeProvider/MultiProvider and make sure that the data is read from the correct provider (you will need another set of 12 variables for the InfoProvider in the columns).
More Sophisticated Examples
Now let us have a look at a more sophisticated version of a rolling forecast. Here we have a longer time horizon and the granularity of the planning changes depending on the month.
Let us assume we are in the first quarter of 2017. We want to plan the next 12 month (i.e. January 2017 – December 2017) by month, the year 2018 by 4 quarters, and then another 5 years by year.
In February 2017 and March we assume we also plan until the end of the year 2017 in periods, the year 2018 by quarters, and the next 5 years as above.
Now in April 2017 we enter a new quarter. So we plan April 2017 – March 2018 as individual months, then the remaining 3 quarters of 2018, and again 5 more years.
This is just one example how such a rolling forecast can be done. Two facts are important in our example:
- There are columns with different granularity (month, quarter, and year) in our query
- The total number of columns is changing as well as the number of columns in each of the different categories (month, quarter, and year).
Such a query cannot be realized by using a drill down, we need a structure in the columns and have to create a structure element with the correct selection for each column. You could build a new query for each month of the year. But obviously this is a quite tedious work…
We have two other approaches to handle this problem:
1. Use the BW display variable.
Create all columns that are could be visible in any of situations above. In our example this would be a column for each month January 2017 – December 2017, and January 2018 - March 2018, Quarter 1 – Quarter 4 2018, and the following years (obviously when doing the planning after April 2017 additional columns will have to be added to the model).
Now select the structure in the query designer and go to the tab ‘Display’. Choose ‘Switchable via Default Variable’ for the visibility of the structure members.
Now execute the query – say in an Analysis for Office workbook.
You will receive a variable popup where you can select which of the columns should be displayed.
Obviously in order to make the scenario more flexible you should use variables for the year. As the number of columns can get quite large it is highly advisable to use variable variants so the work of selecting the individual columns has to be done only once.
2. By using Axis sharing in SAP BO Analysis for Office 2.4
In Analysis 2.4 we plan to introduce the new feature for axis sharing. The idea is that you can take two (or more) queries that have the same rows but different columns and bring them together in one table in AO. The connection between the queries is not just a visual one. Any change on say the restriction in the columns will be reflected on all columns of the visible result.
Now you can go ahead and create one query that shows the periods. Use the techniques described above to make sure that we display only the desired columns depending on the current month (say January 2017 – December 2017 or April 2017 – March 2018). Now create a second query for the quarters (number of quarters depending on the current month), and a third query for the years.
Use axis sharing to link the three parts together in the Analysis for Office workbook.
This solution even works on the case that one of the queries does not return any columns. This might happen in some scenarios where you might not want to see quarters at all but only plan by periods and years (e.g. last quarter 2017).
Summary and Outlook
We have given you above a glimpse of how a rolling forecast can be realized in BPC 10.1 Embedded. In order to create your concrete scenarios you will probably have to combine some of the above techniques.
In addition to the options shown above we are currently working on further features that will make the setup of rolling forecast scenarios even easier.
- SAP Managed Tags:
3 Comments
