Sales forecasting is a crucial process for businesses to estimate future sales and revenues. Accurate forecasts enable companies to make data-driven decisions and plan inventory, hiring, marketing spend, and other key business functions.
In this post, I’ll walk through how to create a sales forecasting report in Power BI using a sample sales dataset I have downloaded from internet.
Data Preparation
I downloaded a sample sales dataset from internet containing transaction data like order dates, products, quantities and sales amounts for the years 2019–2021.
The data required some prep in Excel before importing into Power BI:
- Cleaned column formats and removed blank columns
- Replaced #N/A to 0 (As a special case scenario).
- Replaced placeholder values and formatted columns as dates and time.
- Checked the value sets in each column to check if any error in data representation.
- Created new column of delivery time by subtracting the date between order placed and order delivered.
The key fields for analysis are:
- Order Date (split by Year and Month)
- Customer Name
- Product Category
- Sales Amount
- Quantity Sold
- Profit Margin
- Average delivery time(days).
Visualising Sales Trends
In Power BI, I imported this optimised sales data and started creating visuals to analyse trends.
Sales Over Time — A line chart showing total sales by month provides a quick view of historical trends. We can see seasonality as well as overall growth.
Sales by Category — A stacked bar chart lets us break down sales by product categories. This shows which categories drive the most revenue.
Sales Metrics — Cards visualise key totals like revenue, profits, and total units sold. These are useful KPIs to track.
Ranking Tables — Tables showing top performing products, regions, etc. provide more detailed insights.
The key was organising the visuals into a clean, intuitive layout for easy analysis. I also formatted legends, axes, and labels for clarity.
With the cleaned dataset loaded into Power BI, I built a report page showing historical sales trends.
Key visualisations include:
- Total Sales by Year — Line chart to visualise growth over time. Sales have increased over 35% from 2019 to 2021.
- Sales by Product Category — Stacked bar chart to break down performance by product segment.
- Sales Metrics — Cards with totals and KPIs like revenue, profits and units sold. Useful for snapshots.
- Top Customers — Table showing customers with most sales. Good for profiling.
The visuals provide a robust overview of historical sales patterns.
Forecasting Model
To predict future sales, I built a time series forecasting model in Power BI.
The model uses past sales data to predict values for the next 15 days of sales . I created visuals comparing the forecasts to actual to test accuracy. The forecasts aligned closely with real sales.
Key Insights
With forecasting, we can better understand expected sales and make data-driven plans for inventory, marketing and operations.
Some top insights:
- The model predicts a 10% increase in sales for 2022 compared to 2021.
- Furniture and Office Supplies are expected to drive growth.
- Q4 holiday sales are expected to peak as usual in oct and dec months.
- Online sales have been growing faster than in-store sales over the 3 years, suggesting customers are shifting preferences.
- The Central region is the top performing region, while East has seen declining sales. This indicates where marketing dollars could be focused.
- Laptops have surpassed printers as the top selling technology product — good to know for product portfolio planning.
- Phones and chairs are selling more probably due to the effect of pandemic.
- Sales of folding chairs spiked in 2020 compared to previous years — likely due to pandemic shifting needs.
Creating accurate sales forecasts enables smarter planning and allocation of resources. This Power BI template can serve as a template for real sales analysis and forecasting to drive growth.
Thank you for reading my blog, Please clap if you find this insightful. Let me know your comments, if you have any suggestions.
