Atliq Technologies: Monthly Sales Report
Business Problem and Key Questions
Atliq Technologies are a wholesale electronics store that has multiple customers nationwide in India. But the company's sales have been declining and the national sales manager wants a clearer picture on sales performance to find out why, and a better way to track the companies performance going forward. As a data analyst on the sales team, I've been tasked with creating an automatically updating report that shows monthly sales.
Atliq's sales team have 3 standard metrics they want in each report:
Total Sales Quantity
Total Revenue
Total Profit Margin
In addition to those standard metrics, he is interested in the following key questions:
What is the revenue trend of the company ?
What Customers brought us the most revenue?
Which markets/cities contributed the most to the companies total revenue?
Which markets/cities contributed the most to the total profit?
Data Preparation
To prepare my data, I first wanted to query the data within MySQL to confirm that there were no anomalies and to check the total sales for each year.
I Downloaded the SQL text file I needed:
India_Sales_Data
I then Imported the tables into a MySQL Database.
To find out if there were any anomalies within the data, I ran the query:
SELECT DISTINCT currency FROM transactions;
2 transactions were in USD
As the requirements were only for sales in India, I would remove these remove these rows later in Power BI
Then to find the total sales for each year, I used a "SUM" function to get the total sales amount and a "YEAR" function to retrieve the order dates by the year
SELECT SUM(sales_amount) from transactions where YEAR(order_date) = 2019;
Total sales were as follows:
2017 = 92,882,653 INR
2018 = 413,687,163 INR
2019 = 336,019,102 INR
2020 = 142,224,545 INR
This confirms that sales in India are dropping. Now that I understood more about the data, I connected the SQL database to Power BI, so as the company adds more transactions it will automatically be reflected on the dashboard.
Approach
For the 3 standard metrics, I used the following approach:
Revenue = SUM('sales transactions'[sales_amount])
Sales Qty = SUM('sales transactions'[sales_qty])
Total Profit Margin = sum('sales transactions'[profit_margin])
Here is the approach I used to show these metrics by month:
Created a "Year" and "Month" column by extracting these from the existing date column in Power Query
Used these Dates in a Slicer to filter all data on the page by the chosen year and month
I created 3 pages in the report to look at the data from different perspectives
Performance Insights
Profit analysis
Key Insights
Here's my approach to answer what market/cities contributed the most to the total revenue:
I created a bar chart with a tooltip to show the Revenue Contribution by city, and highlights city's where the companies losing money in red. It can also drill up to filter by regions and down to filter by customers.
Here's my approach to answer what market/cities contributed the most to the total Profit:
I created 2 bar charts, one to show the profit margin on sales in each city, and one to show how much the city contributed to the company's total profit.
Here's the approach I used to see what customers brought the most revenue:
I created a table with columns that show the customer generating the most revenue, and their revenue and profit contribution to the company's total.
*All visuals on the dashboard can be filtered by clicking on a customer of interest*
I created a bar chart showing the top 5 customers by revenue.
Here's my approach to show the revenue trend of the company:
Created a bar chart to display the revenue trend and includes a trend line
Created a bar colum chart showing the current years revenue next to the previous years revenue, including a trend line
Given this is a first pass at what he wanted, I would use this dashboard as a launching pad to see if there were any other specific metrics he would like included or to be the focus of the dashboard.
Results & Key Takeaways
Revenue Trend
The revenue of the company is dropping each year, and changes should be made to ensure it's long term success.
Top Customers
2. The customer who brough the most revenue was 'Electricalsara Stores', they were followed by 'Electricallytical', and 'Excel stores'.
Market Performance
3. The Delhi market contributed the most to the total revenue for the company at 52.8%
4. Delhi also contributed the most to the total profit for the company at 48.5%