We built a forecast model in Power BI and you can too!

This week we were faced with the task of building the initial high level forecasting model in Excel for a business, based on total market £ and market share %.

The foundation of any forecast model is understanding which variables will drive the performance over the coming period. In this case, we focused on total market growth and market share percentage—two powerful levers in any forecasting process. Here’s why these are so impactful:

1. Market Value as a Predictor of Opportunity: The total market value represents the overall size of the market you operate in. This is a fundamental input because it reflects the broader economic trends and demand conditions affecting the entire industry.

2. Market Share as a Measure of Competitive Position: Market share is an essential indicator of your competitive positioning within the market. Small changes in market share can have a significant impact on revenue, especially in a large market.

Step 1: Establishing Key Metrics

The foundation of any forecast model is understanding which variables will drive the projections. In this case, we focused on the following key areas:

• Total Market Growth

• Market Share Change

• Average Selling Price (ASP)

• Cost Price Change

Using these inputs, the forecast provided insights into volume, revenue, and profit trends over the coming year. Power BI’s user-friendly interface allowed us to input these assumptions easily and build a comprehensive model quickly.

Step 2: Building the Forecast Model

Power BI excels at integrating various data points into one cohesive model.

• We started by connecting different data sources related to market data, sales performance, and cost estimates.

• We used Power BI’s DAX (Data Analysis Expressions) language to create calculated measures for the forecast.

• One of the standout features of Power BI is its dynamic filtering and what-if parameters. This allowed us to quickly adjust our assumptions — for example, tweaking the market growth or ASP percentages — and instantly see how these changes would impact the forecast. This makes Power BI a highly interactive tool for scenario planning and sensitivity analysis.

Step 3: Visualising the Data

The final step was to present the forecast in a visually appealing and intuitive format. Power BI offers a range of customisable visualisation options that can be tailored to highlight the most important insights.

As we know from last week keeping colours, sizing, fonts, padding all equal and the same mean the graphs are easier to digest.

Why Power BI?

Power BI offers several advantages when building a forecast model, particularly over traditional methods like Excel:

1. Real-time updates: Once set up, the model can be connected to live data sources, ensuring that your forecast is always based on the most up-to-date information.

2. Interactive exploration: Power BI’s dynamic filters and what-if parameters allow users to explore different scenarios without needing to manually adjust data each time. This leads to faster insights and more flexible planning.

3. Scalability: As your forecasting needs become more sophisticated, Power BI can easily accommodate more complex models, whether it’s by incorporating more variables, additional data sources, or more advanced statistical techniques.

4. Collaboration: Reports can be easily shared across teams and stakeholders. Power BI’s cloud service allows for seamless sharing and collaboration, meaning everyone involved in the forecasting process can access the most recent data and insights in real time.

Taking It to the Next Level

The forecast model we built is just the beginning. Power BI’s adaptability means that it can grow with your business needs. As the next step, the model can be expanded to include more granular variables such as customer segmentation, product-level forecasts, or even integrating new product ranges.

For more advanced forecasting, Power BI can also be paired with machine learning tools like Azure Machine Learning to add predictive analytics. This can help identify patterns and trends in your historical data that might not be immediately visible through traditional methods.

Conclusion

Power BI is a powerful tool for building high-level forecasting models that are not only accurate but also flexible and scalable. By integrating various data sources, creating dynamic scenarios, and visualising the results effectively, we were able to provide a clear, actionable forecast for the coming 12 months. As your forecasting needs grow, Power BI’s capabilities ensure that the model can evolve alongside them, making it an invaluable tool for decision-making.

If you’re looking to build your own forecast model, Power BI provides the tools and flexibility to create a solution tailored to your specific needs.

Check it out:

Check out the live version here - you can move the sensitivity sliders and see how it alters the Revenue, Profit and other metrics.