
The main user interface for SAP Integrated Business Planning is Microsoft Excel using a special Excel Add-In provided with the product. In this article I want to explore if and why this interface will satisfy the needs of planners. Let’s start by having a look at where spreadsheets originated from, and then what planners need from the tools that support them.
The history of the spreadsheet
I've been around long enough to remember a world without electronic spreadsheets – a world that instead either involved pens, paper and calculators or punched cards, a mainframe and reams of printed output. In my first career as a Mechanical and Manufacturing Systems Engineer working for Dunlop, Chloride and Lucas, I had what in retrospect was the privilege of witnessing the birth of the personal computer and the early PC spreadsheet applications such as VisiCalc, SuperCalc and Lotus 1-2-3. It is also worth pointing out that at that time in the mid 1980’s, I had a DEC micro-computer on my desk where the computer was contained inside the VDU (a screen to youngsters) and the operating system, application and data were all held in RAM. Said computer was used to control manufacturing machines in real time. So some concepts are perhaps not so new!
Some people are under the misapprehension that spreadsheets arrived with the PC, whereas they actually originate from the world of accountancy. In printed media the word "spread" means a newspaper or magazine article that covers two facing pages, extends across the fold and therefore treats the two pages as a single large one. The compound word "spread-sheet" came to mean the layout used for accounting ledgers—having columns for expense categories across the top, invoices listed down the left margin, and the value of each payment in the cell where its row and column intersect —which were, traditionally, a "spread" across facing pages of a bound ledger ruled into rows and columns in that format. To learn more on the history of spreadsheets see https://en.wikipedia.org/wiki/Spreadsheet
Figure 2: Accounting Analysis Pad with Calculator!
What is important for planners?
During my earlier career mentioned above, I also had a long spell where I was responsible for the planning of production and the larger supply chain of a high tech battery. From my own experience and having seen other planners working there are a few key things that planners need from the tools they use:
Figure 3: The Integrated Business Planning Cycle (c) Olivehorse 2016
I should also clarify the term ‘planners’ when used in the context of IBP. Unlike APO which is a purely Supply Chain solution SAP IBP is not. One of the core aims of Integrated Business Planning – as the process defined by the Oliver Wight organisation – is that it is a single, consistent, plan for the whole organisation. For those familiar with the process you will know that the steps described in the figure above involve input from Marketing, Sales, Demand Planning, Supply and Logistics, Finance and Executives. All these roles in the organisation all need to use the tool that supports the process.
A Quick Look at the Excel Add-In
Some of you may not have seen the IBP Excel Add-In, so I have included some scree-shots to give you a flavour. The first shown in Figure 4 is of the Excel menu ribbon that appears once you have installed the Add-In, along with some labels to give you an idea of what each section does:
Figure 4: Menu ribbon for the SAP IBP Excel Add-In
What the user sees is dependent on their authorisations so you can limit their actions to suit their role.
Figure 5 shows an example of IBP via the Excel Add-In, and is intended to allow sales planners to update the sales forecast. In this view, although the base level of the data is customer and product, it is being displayed at a higher level of product colour as well as using Distribution Channel to group the customers. In this view there are both display only and editable key figures, for example Statistical Forecast Qty (IBP) and Sales Fcst Qty Adj – Value respectively. Some of the key figures such as Sales Forecast Qty – Last Month are stored persistently in HANA while several others including the Sales Forecast Qty and Sales Revenue are calculated on the fly in IBP from the base level of all the key figures required in its calculation if required. This is done every time the user saves or chooses <Simulate>.
Figure 5: Example IBP View
I deliberately hid part of the view for the previous figure in order to simplify it. This view also includes a chart which is simply an Excel Chart driven from the IBP data below via a hidden sheet. You can use the controls on the left to slice and dice the data and choose which key figures to display. As it’s an Excel object you can change the format as for any normal Excel Chart.
Figure 6: Chart incorporated in the View
So how does the IBP Excel Add-In match up to these requirements I presented earlier? Let’s consider them one at a time:
Speed & responsiveness
In IBP most of the work in extracting and calculating the data the user wants to see is of course performed in SAP HANA. If you don’t already understand why HANA is so fast for these kind of calculations, you must have been hiding under a rock for the last few years. I strongly recommend reading Hasso Plattner’s original papers and taking some of the openSAP MOOC courses on the subject.
Coupled with this our laptops/desktops are now very powerful multi-core machines in their own right – running a very mature product in Excel with a lot of investment over the decades to optimise performance.
Of course there are always things that can negatively impact performance and although spelt out in detail in Note 2153455 they can be summarised as follows:
Don’t be greedy: Be sensible about the number of cells (rows x columns) returned and/or updated. The more cells the more data transferred across the network and the more work for Excel. As a guide SAP indicate you should keep below around 2000 rows x 24 columns. If your aim is to extract the data for reporting somewhere else HANA Cloud Integration (HCI) is much more suited to that.
Control the Work Excel has to do: Conditional formatting and local calculations are all possible but Excel will need to run them every refresh. If not sure do some tests.
I took some timings for a productive system and for a view with 80 key figures x 24 months the data refreshed in 5 seconds – not bad if you remember this data is a result of aggregating and calculating values for the 10000’s of combinations at base level.
Flexibility
We all know that Excel is itself a flexible tool and IBP does impose some restrictions. However here are some cool things you can do with the Add-In:
Figure 7: Example of a Local Member calculation
Figure 8: Conditional Formatting in a View
Figure 9: Controls used to change data in a view
Detail
A view can show the data by one or more of the attributes(similar to characteristics for those familiar with APO/BW) in the planning level for the key figure. Data held at different levels can also be displayed in the same view. Users can easily change the attributes themselves on the fly. I was doing that with a customer this afternoon where we started at country level and ended right down in profit centres and product groups.
What is also sometimes done is, as you can have multiple views in one workbook, to have one view at an aggregated level and another at a more detailed level – but having fewer key figures.
What-if
To me it is the ability to do simulations on the fly in a practical time-scale that differentiate IBP from APO and other products. Here are key things to know regarding what-ifs in IBP:
Ease of Use
The great thing about using Excel as the basis is that it must be one of the most widely known software products in the world – especially amongst the finance and supply planning communities. It is fairly intuitive and users can be up and using Excel with the Add-In in hours – with virtually no training.
Integrated
The data for IBP, by definition, comes from multiple other systems whether it is ECC, APO, BPC or non-SAP systems. Interfacing via SAP HANA Cloud Integration (HCI) is straightforward compared to BW. The good thing about IBP is that as soon as HCI has loaded the data it can be seen in the Excel view when you open or refresh the view – there are no other jobs required to load to ODS or Info Cubes or to index or aggregate the data.
Of course it works the other way as well. Comma Separated Variable (.csv) format is quite common as portable file exchange format and you can save the views as .csv for loading into another tool.
Offline Use
With IBP you can open a View – it will refresh the data and you can then log off and continue to edit the values while sitting on a train for example. When you are next on-line, just login and save the data!
Sharing Results
It is very easy to share Excel workbooks by email; as my inbox will testify! However, the problem is that once shared they tend to be modified by the recipient who then shares it on and you end up with N! versions. The beauty of IBP is that you can share Excel workbooks containing views with other users. The key figure data is persisted in HANA of course but there can be data local to the workbook as well.
Conclusions
I have been using the IBP Excel Add-In almost every day for the last 6 months, both in productive environments at customers and for producing our own demos and proof of concepts. As I hope I have shown above it has many features that will please planners from all areas of a business and is a huge improvement over both the planning books in APO and BEx queries.
Steve Rampton, IBP Practice Lead
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
6 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |