I had the pleasure of presenting the capabilities of PowerPivot at a network meeting for suppliers to FMCG retailers in Denmark. The purpose of the meeting was using POS data and integrates the data with other relevant data sources in order to gain business insight.
So I decided to illustrate how you could use your information about product introductions and illustrate how productions introduction cross time has performed in chains, stores, sales reps, category etc.
The primary KPI would be the numerical distribution and show how fast distribution in stores is created.
So the data we have is
Sales value by Store and by product
Stores master data
Product master data
This is integrated with a Linked table in Excel with our introductions
And by relating the tables in PowerPivot I add three calculated columns in the StoreSales table.
- New product introduction (lookup to introduction table from product)
- Introduction date (lookup to introduction table)
- NPI Age – this calculates the Sales date minus the Introduction date, which returns the age of the introduction (in this demo we have weekly sales data and to get the age in weeks I divide by 7)
In the table I then add a measure “First Week” – that is the Min of NPI Age (in weeks)
By using these KPI’s I can create a report that compares Introduction speed cross introductions dates (as shown in the first chart), but I can also create a report where I can follow the introduction speed in each store.
In this report we look at the top selling stores and when the products in the introductions first appeared.
Very fast and efficient analysis cross store dimensions is now possible and you can check difference in Introduction speed by chains, regions and sales reps.
And if you save the model on SharePoint with PowerPivot enabled you can create Power View reports to create great visualizations.
Or just let your users browse the same excel file in the web browser without even having access to Excel 2010 and PowerPivot on the client.