In today’s fast moving world, there is an ever increasing demand for customisation and personalisation, whether it be initials stitched on to your running shoes or a bespoke paint colour for your car. Everyone likes to have a personal touch in their day to day activities, so why should SAP Integrated Business Planning (IBP) be any different?

If you haven’t seen the SAP IBP Excel Add-In, or want to know more about the various functionalities offered, please take a read of this blog from Steve Rampton “SAP IBP Excel Views – A Planners dream?”. I will split this blog as to how Planners can create their own personalised views within SAP IBP in two;

• Basic formatting of the Excel Add-In
• The advanced Olivehorse way!

Creating Basic Views

Let's first start with understanding the step by step process to create an SAP IBP Excel view from scratch. Remember, all of the steps below can easily be done by Planners themselves without any help from Support teams - the days of 5 figure Change Requests to amend a basic BEx query are gone!

Using the Excel Add-In, login to your SAP IBP instance and to create a new view select the option “New View > Without Template on Current Sheet...” as shown below;

On the next set of screens, we will be selecting the various data fields that we want to see on our View. For advanced users of SAP IBP, these are basic steps - please skip the first part and resume the blog on Business Cases part! Let’s first start with Time – say we want to create a view with monthly buckets for the year 2018;

Next, we would select the required planning level(s) and attributes using the Planning Level Tab as shown below, say we want to display at Product ID, Product Description and Customer Description;

Next go to Key Figures tab and select all the required key figures;

Selecting OK should then create a view as shown below;

This is a 'standard' SAP IBP Excel view without any formatting. Now, let's add a header to the view, the Olivehorse logo and some additional details like connection name, user, last refresh date and time etc.

Now, let's add header names to the data columns;

Now, to replicate the same header format for the time columns we would need to use Enterprise Performance Management (EPM) formatting. To do so, copy the SAP provided EPM worksheet to a different sheet in the Excel workbook;

And in the SAP IBP view, navigate to Sheet options, and select the “EPMFormattingSheet” as default worksheet for dynamic formatting;

Now, refresh the data and we should be able to see the view as shown below;

Now, let’s make the view more presentable by adding some colour to highlight those key figures which are not editable (grey background) and those key figures which are editable are with no background. I will also set the header to Olivehorse green! To do so, we can use the EPM formatting sheet property as shown below for the grey background;

And now the view is as follows;

All the above colours and formatting have been done using the “EPMFormattingSheet”.

Advanced Views

I will be using the simple view below as a basis for showcasing some of the more advanced formatting options within the SAP IBP Excel Add-In. Note the inclusion of both quantity and currency key figures in the same sheet.

Let's look at a few business examples of how Planners can further leverage IBP Excel Add-In functionality to personalise their views;

Example 1

As a planner, I would like to ensure that any data entered in to the key figure "Sales Forecast Adj – Percentage" is between -100% and +100 %.

Using EPM data validation, a rule can be written to check that the value entered is between a certain range, in this case -100% and +100%. To do so, navigate to “EPMFormattingsheet”, select the key figure which we need to put data validation on and enter the values as shown below;

We can also put a error message to display to Planners if they enter a value outside of the limits using the below settings;

Now, based on this data validation if I enter number outside the mentioned range, I will get an error message as shown below in the view;

Example 2

The initial view can look very confusing to a Planner with both Currency and Quantity values present. Can we do something to differentiate to Currency key figures like “AOP Rev”, “Actuals Rev”, “Sales Forecast Rev” and “Sales Forecast Price” so that they clearly stand out?

The answer is yes! Again using EPM data formatting, we can format all the revenue key figures to show its respective values with a currency symbol e.g. instead of displaying a value of “60,000” in “AOP Rev” we can make it display “£60,000”.

To do so, go to “EPMFormattingsheet”, choose the key figure which needs to be changed, 

Right click on values field and choose “Format Cells”, select the formatting as “Currency” and select symbol as applicable;

Now, we should see currency values with standing out with “£” symbol before the currency values;

Example 3

As a planner I want to see a “% change in my sales forecast” by comparing current Sales Forecast qty with Sales Forecast qty last plan. Is this possible?

Yes! Planners are free to create their own rows with custom formulas, whether it be simple mathematic formulas, lookups or complex mathematic functions. This is achieved by creating a local member in the Excel view.

Go the excel view, and insert a row, where you want to add this local member;

Enter a local member name, and enter a formula below the first data cell, in this case under ‘Jan-18’ and click enter. You can now see that a 'local' row has been added for our percentage comparison;

As you can see above, a local key figure is created, and I have also added icons to local member to display different icons based on the values in “% Change from last Plan”.

Example 4

As a planner, I want to create a pivot chart on my IBP data set. Can I create a Pivot chart?

Yes you can! This is achieved using the standard Excel functionality. Use the sheet options to repeat headers for each column/row;

Now, our view should be displayed as shown below;

Now, select the entire data sets and chose Insert Pivot;

Customise on how you want your pivot to be displayed and this will get you a Pivot table on your IBP data set.

Example 5

Can I use Sparklines within SAP IBP?

I take it you've already guessed the answer! Similar to the Pivot table above, Sparklines can give a nice visual on your SAP IBP view.

Or use the lines instead of bars as shown below to see the trend of actuals and statistical forecast quantities in graphical views.

Summary

The above examples only scratch the surface as to the kind of functionality Olivehorse bring to your SAP IBP implementation through our Accelerators. A few more solutions we have pre-built include;

1. Planners only wanting to see relevant combinations on the Excel view and hide all other combinations which are obsolete/not relevant.
2. Planners having a freeze horizon for say 3 months and data should only be editable starting from month 4 onwards.
3. Planners being confused with the standard 'technical' names of key figures and want to rename them to business 'speak'.
4. A toggle to switch between quantity and currency key figures.

We can also use VBA to enhance the user experience - more details can be found in Steve Rampton’s blog - “Leveraging VBA to enhance the user experience in SAP Integrated Business Planning

Lastly, If you are already using SAP IBP and have a Interesting business case for the IBP Excel Add-In, please use the comments section below or contact me on jacky.jain@olivehorse.com.

Jacky Jain

Senior SCM Consultant - Olivehorse Consulting