SAP Integrated Business Planning (IBP) is SAP’s next-generation planning application, which uses the computational power of SAP HANA to enable a truly integrated and collaborative planning process.

SAP have also recognised their past problems with somewhat un-intuitive user interfaces and have invested a great deal of thought into the underlying technology and implementation of the interfaces with which users interact with the system. Of course supply planners are not an easy group to please: they usually work in high pressure environments where time is limited and ease of use vital. To further complicate the issue, the user community for an IBP process is much wider than just Demand & Supply Planning and brings in the Finance and Sales departments among others. 

So to fix this problem, after years of trying and failing to get planners to use the SAP GUI based Planning Books in APO, SAP decided to ‘go with the flow’ and use browser and Microsoft Excel based technologies. The key technology for users who are defining the plan or examining it in detail rather than checking status via analytical charts is MS Excel as highlighted in Fig 1 below. The IBP Excel interface is enabled by installing a couple of SAP supplied Excel Add-Ins – one for the interface itself and another to drive charts within Excel.

Fig1: SAP IBP Overview

So what's the problem?

I have been using the IBP Excel User Interface for over 2 years now and have found it simple and intuitive to use. The standard functionality is sufficient for the majority of requirements although it has to be said that some of this functionality is not well documented and has to be learned from the experience of others or trial and error!

You are able to meet a lot of advanced requirements through either configuration of key figures in IBP itself and/or the use of what are called Local Members in the IBP Add-In. These are key figures you can add to the view itself and are not configured or known to the IBP backend: they are purely local to the IBP View itself. With these you can define a calculation as an Excel formula that refers to one or more IBP sourced key figures. An example is shown below:

Fig2: Standard Excel formulas within SAP IBP

However! We have found some use cases ourselves and clients have suggested others that point to requirements beyond those delivered as standard with the Add-In. Some examples are:

• IBP doesn’t report Day’s Supply unless and until you run a planning algorithm and then only at Product/Location level – not say aggregated across all or selected locations for example.
• I’d like to do some calculations on key figures in the view without having to add configuration in IBP itself.
• There are some cross period calculations that are either very involved or impossible to configure in IBP without resort to L-code, which only SAP can do, or attribute transformations, which have a performance impact.
• I’d like to create some dynamic macros like we had in APO planning books.
• I’m frequently changing a lot of cells with the same values – e.g. absolute or percentage uplift across many time periods or combinations. Can I have something to make this easier?
• Master Data maintenance in IBP is a little primitive – it has neither validation nor possible values – at least not in mass maintenance. Can I add this functionality?

The good news is that you can! And we have achieved all of the above leveraging Microsoft’s Visual Basic for Applications programming language – more commonly referred to as VBA. This comes as standard with Excel but access to it is initially hidden from view. You do need either some programming experience yourself or to use a partner who does.

Example VBA Developments

So here are some examples of what can be achieved using some of our own developments.

The MS Excel view below shows some standard IBP for supply related key figures such as Dependent Demand, Projected Inventory and Number of Sub-Periods (latter being in this example days in a month). You will see other key figures that do not exist as standard in IBP nor are custom configured. The description of these is highlighted in bold – for example Demand 3 month offset:

Fig3: Types of key figures calcuated via VBA highlighted in bold

This key figure is a local member defined in the view itself. To achieve this we have developed a custom function in VBA that uses the Dependent Demand Qty key figure values and offsets by the number of periods in a supplied parameter. In this example it displays the value from 3 months in the past. The really cool thing about Local Members in the IBP Add-In is that you only need to type it in one cell and the Add-In automatically propagates it to all other time buckets for that planning combination and all other combinations. Even better if the number of combinations changes the Local Members adjust accordingly. The screenshot below shows how this example function is defined in the cell of a Local Member with the function call =OH_OffsetBucket(...).

Fig4: Local member and function

IBP, particularly but not only IBP for demand and IBP for sales and operations, usually involve a significant amount of manual input modifying values or making adjustments. You may have 24 months or 26 or 52 weeks of columns displayed and you may want to change many or all of them by a similar amount. Sometimes the standard Excel drag to copy is sufficient but in others it is more difficult.

One way of making this easy for users is to create a macro that allows them to modify a selected range by a defined quantity. This is where we started but then quickly realised this was complex to deliver in each view – see section below on delivery and required several macros.

We realised that we could deliver the functionality much more flexibly using a custom form where the user can choose the type of transformation, the scale of it and the range(s) of cells to be changed. See the screenshot below where we a 10% increase is being applied to three selected cells.

Fig5: Olivehorse IBP Addin

As well as being defined as a function, called from a button on a ribbon or a control it is also possible for code to be triggered on certain events. These so called VBA Hooks started to be delivered by SAP from release 1702 and now cover both normal views and the master data views. As an example use case this can allow you to perform validation checks before saving data. For more info see SAP Note 2421657.

How to deliver VBA?

As mentioned earlier there is an issue with how to deliver this VBA customisation to users. SAP faced the same issue with the VBA used to drive the Excel Charts. For the charts VBA there are two options:

• The required VBA is embedded in the workbook itself. This means that you need to embed the VBA in a template workbook that is used to create all Excel views.
• The VBA is delivered in an Add-In (type .xlam)

We quickly realised that the latter is the far better approach. Although all users will need to install another Add-In it means that the code does not have to be embedded in any templates and even more importantly the code can be maintained in the event of a bug or extended with new functionality by simply releasing a new version of the Add-In. Otherwise you face the unenviable task of, as well as changing every template, asking every IBP user to maintain every IBP view they have saved to their favourites – not going to happen.

Custom functions don’t need anything else but User Forms and macros really need a custom ribbon – which we have created for the above example. Creating ribbons is a whole other story perhaps for another blog!

Steve Rampton

Director & IBP Practice Lead, Olivehorse Consulting
steve.rampton@olivehorse.com