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!
Figure 1: VisiCalc running on an Apple II (source wikipedia)
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:
- Speed and responsiveness – planners often have to come up with answers quickly.
- Flexibility – they frequently have to respond to novel situations and questions that require them to combine information from many sources – ad hoc.
- Detail – there is no point showing a planner a high level graph and expecting them to be satisfied with that. They want to be able to drill into the detail.
- What-if – a planner’s job is usually about deciding between options – sometimes the least bad choice. To do this you need to be able to see the effects of different decisions and compare them.
- Ease of use – they are often overworked and really don’t have the spare time to learn a complex new user interface – so it helps if it is one they know already.
- Integrated – the planner needs to have his finger on the pulse of what is happening in the business and this information needs to be at his fingertips. So the tools need to draw data from the various execution systems on actual performance as well as forecasts and plans defined in other systems such as SAP Advanced Planning and Optimisation and SAP Business Planning and Consolidation.
- Offline use – less of an issue nowadays compared to the days of unreliable modem connections but still important in some geographies or while commuting.
- Sharing results – unfortunately their bosses and peers don’t always take their word for things and they need to be able to share the evidence in a format that others can easily consume.
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:
- Display any of the key figures in the planning area (excepting helpers which are used to hold as intermediate values). I have seen views with nearly a hundred key figures. Users can easily add or remove key figures themselves.
- Add extra rows or columns within the view containing Excel formulae – the Add-In automatically propagates the formulae to all similar cells. See Figure 7 for an example where it is used to calculate the difference between two key figures. It can be used to calculate row or column totals as well.
Figure 7: Example of a Local Member calculation
- Have multiple views in the same workbook – as long as they connect to the same planning area!
- Use conditional formatting to emphasise trends, anomalies with colour and icons and apply special formats to any characteristics or key figures – for example fonts, number formats, borders. The format therefore adjusts with the data returned. Figure 8 below has several examples of this in use including borders, number formatting to percentage, bold font and conditional formatting.
Figure 8: Conditional Formatting in a View
- Drive Excel charts based on the data returned – SAP provide an example template for this.
- Drive Pivot charts from the data – just don’t go mad with the number of cells!
- View and edit master data – change attribute values, add new key records, delete obsolete ones and mass create or delete planning combinations.
- Use macros to change the data in the view. There is an example in the view shown earlier (see Figure 9 for detail) which I coded to increase or decrease data in the currently selected cells. SAP do warn that not all macros will work with the IBP Add-In so it is a question of trying in a PoC first.
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:
- If you have made any changes to key figures in the view you can click on ‘Simulate’ and the system will calculate all dependent key-figures – from base planning level up if required. The neat thing is that the results aren't persisted in HANA unless you save them so if you don’t like or need them you simply refresh the data.
- The same applies to the Supply planning heuristics and optimiser. You can change some values such as capacity and simulate the results for the whole supply chain (within limits of course!) and immediately see the impact.
- If you have multiple views in one workbook the simulation ‘session’ results apply to all the views. So you can change some data in one view, simulate, and check the effects in another as well.
- There two types of more persistent what-if’s that allow you to compare before and after:
- One is called scenarios which are brilliant when you want to compare a limited number of changes between two plans – say a significant change to production capacity somewhere. You can run the planning for both and show both side by side in an excel view. An example of this is shown in Figure 9 where the revenue and profit for the baseline plan and a what-if scenario are being compared.
- The second type are Versions where you copy most of the key figures and can have version dependent master data. Useful for much larger scale what-ifs with many changes. Again the data from multiple versions can be displayed in the same view.
Figure 10: Comparing financials between what-if scenario and base plan
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
- SAP Managed Tags:
7 Comments
| User | Count |
|---|---|
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
