Let us explore how you can quickly forecast multiple items with seasonality in Power BI.
Consider a scenario where your firm has accomplished the following in monthly sales in FY 2022 (totaling 1.16B in sales).
Your company is targeting to achieve 1.2B in sales for the upcoming year 2023. They want you to put together a quick forecast with the following considerations:
Once these two steps are complete, you will have a baseline plan on which you can make further adjustments.
In a scenario like this, here are three considerations:
Let us explore how we can accomplish this.
First, we shall take a look at the options available in Power BI for forecasting with seasonality.
One common method to forecast is to use the analytics feature in line charts.
However, this approach has the following shortcomings:
This is a more practical and flexible approach that we are likely to use in Excel. This is a two-step process:
For example, the seasonality weightages for each product category has to be calculated as follows.
You can then use these weights to distribute the total forecast for each product category across months.
A slightly different approach is to create the weights based relative to overall company sales (instead of overall product category sales). Using this approach, you can directly distribute 1.2B across all categories and time periods.
The weightage-based method has the following drawbacks in Power BI:
Inforiver allows you to overcome all of the setbacks listed above and helps you create seasonal forecasts in a matter of seconds.
This report shows 2022 Sales using Inforiver for Power BI. The data is scaled to millions. Let us now create 2023 Forecast with seasonality.
The first step is to insert a visual-level measure to hold 2023 Forecast values using the Data Input feature. The end state is shown in the image below. Note that you are only creating a single visual-level measure 2023 Fcst, which automatically replicates within each Quarter-Month combination. To ensure readability we have collapsed the Q3 and Q4 columns.
To create a forecast, select the blank cell ‘All – 2023 Fcst’. Double click and enter 1.2b (you can enter numbers in scaled format).
Press Enter. The value is not only written to the cell but also allocated uniformly across sub-categories (240M per product category for 2023) and time periods (20M each per product per month). Inforiver automatically highlights the updated values.
But we do not need this equal allocation. We need to allocate the forecasts proportionally based on 2022 sales. Click on the context menu appearing on the cell with the value 1,200.0 and choose ‘Distribute by weights of > 2022 Sales’.
Once this option is selected, the 2023 Forecast values are updated automatically across product categories & months by their relative contribution to 2022 Sales.
And before you realize, you have created forecasts with seasonality. And it takes almost no time, and it needs no data transformation.
You can quickly visualize seasonality by using the charting capabilities of Inforiver (this takes almost no time too). You will notice that the trend for 2023 Forecast mirrors the trend for 2022 Sales at all levels.
The best part of this approach is that you can operate at the speed of thought (well, almost). And it scales up even for 1000 products.
You can accomplish a lot more using Inforiver for forecasting use cases. Some of them are listed below. We will cover each of them under separate blogs.
Try or buy Inforiver directly from Microsoft AppSource today.