fbpx

Portfolio Forecasting with Microsoft Power BI: How-To Guide

PPM and Power BI enables improved overview and better decision making, more efficient collaboration, reduced maintenance cost, and a customized solution for clear overview of financials, resources and portfolio health and allowing for portfolio forecasting. Portfolio Forecasting is ability to anticipate the impact of economic and market changes on your portfolio. If you guess the future economic conditions right, you can build loss forecasts. Portfolio Forecasting extends the concept of forecasting to directly impact revenues Power BI is filled with many exciting features including the newest analytics feature, clustering. Power View in Power BI gives users advanced statistical analysis tool. It empowers you to generate forecasts that incorporate trends and seasonal factors. It also has table and matrix features. Forecasting is available both in Power BI Desktop and the Power BI online. You can see your forecasting in reports and create new forecasts not just in Desktop version.

Portfolio forecasting, as you know, requires vast amounts of historical data. Power BI by itself does not have the ability to store so much data. So, when using it to “predict the future” you might need to equip yourself with some storage for data. You can go either set up a database or use FluentPro DataMart Cloud. In the second case the data can be constantly streamed to the Datamart, then to be “pulled” to Power BI to be used in forecasting.

Forecasting in Power View is based on a proven effective model for prediction called “exponential smoothing”. There is more than one method have been developed for the analysis of time series. They differ depending on whether the data is strongly seasonal or has no seasonality, whether the data contains “surprises” or irregular peaks and even how much “noise” you’ve got in your data. “Exponential smoothing” has a great track record both in academia and business. It has the advantage of suppressing noise and “surprise” variation that can distort the model. But it still allows you to keep up with the trends.

 

 

Four basic steps to get started with forecasting

 

Power BI can predict the future costs of our projects! To do that you have to switch over to the analytics pane of the visual and underneath the “Forecast” option, apply forecasting. Power BI can extrapolate the input data into what could be the possible project costs based on the parameters configured. There are several visual parameters within Power BI that require simple configuration. They are:

  • Forecast Length: This describes the number of points or the duration of time that Power BI predicts data for. This can be either of Year(s), Month(s), Day(s) etc.
  • Ignore Last: This allows the user to ignore a few points within the dataset. For example, if the dataset uses project costs for past 12 months and we know that the costs for the last 2 months are incorrect and shouldn’t be included to forecast, you can do that with Ignore Last.
  • Confidence Interval: This option allows us to set the confidence interval which determines the probability of a real value being close to the predicted value i.e. there is 95% chance of the real value being within the range of the predicted value.
  • Seasonality: Datasets exhibit seasonality when the time value in the dataset exhibits a pattern. Using this option, we can specify seasonality in the given dataset whether it is yearly, quarterly or monthly.

The version of the algorithm that takes seasonal data in account is called “ETS AAA” is also known as the Holt-Winters algorithm. The one for non-seasonal data is “ETS AAN”. Power View uses the appropriate model automatically, based on an analysis of the historical data. The seasonal algorithm uses an equation that accounts for additive error, additive trend, and additive seasonality. It is widely used, for example, in predicting and planning demand in businesses. In Power BI the algorithm is enhancement to be resistant to noise in the data. Specifically:

  • Use of validation window for optimal parameter selection
  • State vector correction at the end of training window when data is noisy

The non-seasonal algorithm (ETS AAN) uses a simpler equation. It does not consider seasonality at all.

 

 

How does the seasonality value affect forecasts?

 

Seasonality differs by the number of time frames in one full data cycle, before t repeats itself throughout the time series. For example, if you have sales data that changes over the course of a year but tends to look the same year over year, then the time series has a periodicity of one year. If your historical data is presented in units of months, and of course 12 months comprise a year, then to get the best results you would set a seasonality value of 12, meaning 12 units makes one complete data cycle. Seasonality estimation has a strong effect on time series forecasts and is an important first step when predicting just about everything cyclical, in your portfolio’s future. Popular time series forecasting algorithms are:

  • Exponential Time Smoothing
  • Auto-Regressive Integrated Moving Average

They both require seasonality as an input and are often very sensitive to different input values.

Power BI can forecast the future of your data using built-in predictive analytics. In this day and age of an ever-changing business insights landscape; simply generating insights from current/past project data to track project cost isn’t always enough – project managers and business users within your organization need to forecast future project costs so that they can take corrective action today and gain a competitive advantage tomorrow.