In my previous blog post I checked what the key influencer is for having a lot of stock using PowerPivot and Predixion Insight for Excel. Now i want to see what the number of units on stock will do in the future. I want to see this by country for the next 6 months.
We start in Excel, i have again loaded the same tables as before into PowerPivot containing the Factinventory and a table containing country’s and stores. This time we are going to use PowerPivot indirect since the data in PowerPivot is not really suited for the Forecast, besides we want to use an aggregated value for this and not all individual 8 million records we used before.
We start by creating a flattened pivottable, a great new invention in Excel 2010:
Here we can select the actual values as they are in our datasource, by year and month set out against country:
Important here is that i have one field that contains all months for each year we have data, ordered chronologically. I already had this field loaded in my date table, but you could easily create one using DAX =Year & Month
This is all it takes to start the data mining. So again I open the PredixionInsight Now Ribbon and click Forecast:
This time no direct PowerPivot connection for us:
We can just select the range my flattened pivottable is in and press OK.
In the next screen we can select which columns we want to forecast, in our case we created a column with units on stock for each country, we can select each of them. Next we need to select the time stamp we want to use, this is the Calendarmonth column, of the type Month. We want to predict 6 months ahead:
Now all we have to do is press Run, it will upload my table to the Predixion servers and in almost no time it has processed the values and i am ready to use my results. Note my previous job is also still in there ready to be used in this Excel sheet as well:
Press on results and the results are created inside Excel:
As you can see we need to pay extra attention to Germany, the forecast is that they are building up huge units on stock in the near future. Again another powerful use of your data, and very easy to do !
Update: I got a message from Jamie MacLennan CTO from Predixion who gave a hint to improve my forecast: if you cut out that part of the data that is very different from the rest of the graph (i.e. prior to 200802) the prediction may be better.
Of course i tried this and the results do look more realistic:
