SAP’s IBP Add-In for Excel offers businesses incredible capabilities in planning, synchronizing, and implementing unique supply chain solutions. Far too often, though, businesses fall into the trap of implementing preconfigured planning views without asking “What else can I do with this?” I don’t need to tell you what a powerful tool Excel is. Almost every intern across America is well versed in VLookups, SumIfs, and pivot tables. If you aren’t taking advantage of Excel to customize IBP analytics, you’re only scratching the surface of its capabilities.
CONSIDER THESE FOUR TIPS FOR BUILDING CUSTOM EXCEL DASHBOARDS TO TAKE DATA VISUALIZATION IN IBP TO NEXT LEVEL
1) Use VBA… Sparingly
The Goal: Add new functionality, but avoid future errors
VBA for Excel allows users to write macros that may come in handy when creating dashboards, but it should be used sparingly – especially if the base IBP environment is still in configuration. Any time that a macro references a specific cell range, key figure, or planning level name, there is a risk that it will throw an error when any change is made to the planning view. For example, if a macro is designed to locate the first instance of a specific Product ID, it may search a specified column for that value. If at a later point, the planning view is edited to include another planning level, it could change the column that stores Product ID, which may in turn cause the macro to break.
Because of these possibilities, we suggest that VBA be reserved for tasks that do not vary with changes to the planning view. For example, the only macro included in our resources dashboard is a refresh button that universally refreshes our pivot tables to match the data in the planning view table, regardless of what values or what range the data takes on.
2) Keep your planning views and pivot tables straight
The Goal: Link multiple dashboard charts to the same set of filters
Our dashboards sit on top of standard IBP planning views. That means that like any other view, we load in planning levels, key figures, and filters into a main data table. All IBP dashboards must have at least one tab that reads in data as a planning view. We have decided to load the data directly to the main dashboard tab below our primary charts, so our users only need to interact with a single tab.
The real magic of our dashboards happens behind the scenes, though. Every chart on the dashboard tab is linked directly to a pivot table in a hidden tab. These pivot tables allow for user friendly on-the-fly filtering using the “slicers” on the left side of the dashboard, but syncing multiple charts to the same slicers requires careful attention to detail. Due to technical constraints of Excel, the hidden tabs for each pivot table cannot simply reference the main planning view data table. Instead, one must create another hidden tab with an intermediary pivot table, which in turn serves as the primary data model for our charts. The intermediary tab does not apply any additional filters, but simply allows for all three subsequent charts to be controlled by a common set of filters. Thus, in total, our resources dashboard has four hidden pivot table tabs in order to properly display our three summary charts.
3) Understand your planning levels
The goal: Only apply filters that make sense for each key figure
Many key figures that are directly related to each other exist at different planning levels. For example, Capacity Supply of a production resource has a base planning level of week-location-resource while Production Receipts has a base planning level of week-product-location. The two are directly related, because the capacity supply of a resource used in the production of a finished good constrains the production of that good, so it is useful to be able to view both side-by-side in the same dashboard. In order to load both key figures in the same planning view, however, we must clearly include both Product ID and Resource ID in the planning levels. We will note that any row with a value for Capacity Supply will have Product ID equal to “(None)” and any row with a value for Production Receipts will have Resource ID equal to “(None).”
Because of this formatting, it is important that any two key figures that exist at different planning levels are kept on separate charts and that the slicer filters used to alter our dashboard charts are only assigned to filter charts with key figures that are meaningful for that planning level. Namely, the Resource ID filter cannot be linked to the chart with Production Receipts, and the Product ID filter cannot be linked to the chart with Capacity Supply.
4) Dress it up in team colors
The Goal: Strut your stuff
A little extra care in design goes a long way towards improving how users interact with your dashboard and how they associate with your organization’s brand. Use your organization’s color templates and make sure your logo is prominent. A lot of work goes into making dashboards work effectively, take pride in that effort!
Beyond branding, make sure that your dashboard can function on monitors of different sizes. It’s good practice to design your dashboard for a smaller monitor, because image quality in Excel scales much better with zooming in than out. Make sure that edges of your charts and filters align and use consistent fonts and sizes throughout.
Follow these tips and you’ll be well on your way to advancing IBP’s role in your organization’s decision-making processes. The more work that goes into making dashboards customizable and user friendly, the easier it is to leverage them for effective planning. Don’t get stuck using the same preconfigured templates and planning views – use Excel in all its glory to continue improving how IBP works for you.