Time Series Forecasting using Power BI
Time Series Capabilities for Microsoft Power BI
With our latest release 1.7, we are introducing time-series forecasting capabilities in ValQ, Modern Visual Planning for Microsoft Power BI. At present, Linear Trend, Moving Average, and Weighted Moving Average is supported in addition to the Auto Forecast option. Also, in the roadmap for the future versions are ARIMA, Holt-winters, and many more.
Let us take the example of a consumer goods manufacturer selling hair care products in 4 different regions. Assume they have the preceding 3 years actual performance data. Let us see how to prepare a plan for 2020.
To start using time series forecasting, go to the ‘Plan’ tab. Click on ‘Auto Forecast’.
You will see the following screen. Assume only the last 2 years data is relevant for the region ‘East’ – choose the target node and target period. Under Forecast Basis, make the selections as shown in the image. ‘Target Node’ is the node for which forecasting is performed. ‘Target Period’ is the period for which forecasting is performed and ‘Source Period’ is the range of periods for which historical data is used.
Then click on ‘Advanced Options’. Choose ‘Linear Trend’ as the forecasting method and ‘M2M (+1)’ as the distribution method. Based on the forecasting method, the annual baseline is first calculated. Then, based on the distribution method, this value is distributed across time periods by the historical trends. Click ‘Preview’.
You will be able to see a preview of the forecast as shown below.
Once you click apply, you will be able to see the changes in the planning grid.
What it does – Captures historical trends and projects future trends. Cyclical or seasonality factors are not considered.
Where to use – New product forecasts (particularly intermediate and long term)
Let us take a quick look at the other methods.
Moving Average and Weighted Moving Average
What it does – Averages the results of recent history and projects for the short term. Irregularities are smoothened out.
Where to use – Inventory control for low volume items
The following image shows forecasting performed using the moving average method. The last three years actuals have been used and the distribution method is set as M2M (+2).
What it does – Provides optimized, balanced, and conservative forecasts based on the historical data.
You can analyze the ‘Net Growth’ for the three forecasts and select the most suitable option.
Why ValQ for time series forecasting?
With ValQ, it is possible to
1. Examine historical trends – Analyze your business model at any level of granularity – geography, product category, or accounts.
2. Incorporate and instantly visualize changes – Simulate on key drivers and analyze the impact on key performance indicators.
3. Perform what-if analysis – Create and compare scenarios, perform variance analysis over different periods.
4. Bottom-up and top-down forecasting – Easily create bottom-up and top-down forecasts and improve the accuracy of your predictions.
Get Started with ValQ today!
Try ValQ, Modern Visual Planning for Microsoft Power BI to instantly visualize the power of business optimization.