Electronics Shop Sales Dashboard With MS-Excel
Abstract
I have created this report with a sore purpose of practicing the use of the six phases of the data analysis process; Ask questions to understand the business task, Prepare the data, Process the data, Analyse the data, Share the data, and then Act, taught in the Google Data Analytics Certification Course.
Introduction
The purpose of this exploratory data analysis is to use historical data of the shop to draw valuable insights concerning the brands dealt with to help the management make informed decisions. The analysis will seek to answer questions such as:
i) Which is the highest-selling month?
ii) Which brand had the highest sales each year?
iii) Which is the most profitable brand?
iv) Which brand sold the most units?
v) Which sales person generated the highest profits
vi) Which region had the highest sales?
MS-excel was the most appropriate tool for data analysis because the dataset was small. Using the data, I created the following dashboard to answer the above business tasks.
i) Which is the highest-selling month?
I grouped the total sales data by month to determine which month the shop generated the highest sales in dollars. I filtered the data based on years to explore the Total sales separately for 2018 and 2019.
In 2018, according to the chart, the the shop recorded the highest sales in May with $ 146,156.
Similarly, in 2019, the shop recorded the highest monthly sales in May.
May is the highest-selling month generally and can be viewed better in the following bar chart.
Recomendation: The available data during this analysis was not sufficient to expore the reasons for high sales in May each year but I deloped a theory that people bought more electronics in May each year. Therefore, I recomend relevant data to be collected to explore the theory.
ii) Which brand had the highest sales each year?
The chart shows that in 2018, Televisions genrated the highest Sales while in 2019, Home theaters had the highest Sales.
iii) Which is the most profitable brand?
Profit is determined by subtructing the Total Cost of Production/Purchase of an item from the Total sales
Accordinng to the chart, Television was the most profitable brand.
iv) Which brand sold the most units?
According to the chart, Television was the most sold item in every region and by every manager except for Martha in the Eastern Region.
Recomendations: There seem to be many customers for televisions across all regions. Therefore, the management should focus on increasing its supply. The other items, especially video games and desktops, need a lot of advertisement to catch up.
V) Which sales man generated the highest profits
The chart shows the profitability of each salesperson sorted in descending order. Alexander had the highest sales while Diana had the lowest.
Recomendations: The management should investigate the best-performing sales associates’ strategies to help the least performing ones realize their potential. This would help increase the total profitability of the firm.
vi) Which region had the highest sales?
Central region had the highest total sales for the shop.
Recomendation: There is need for increased brand awareness for all items in the other two regions.
Vii) what is the average average profits for all items?
The average profit for all products sold during the two year period was $5,271.79.
Conclusion
The results of the above analysis can be used by different stakeholders in the firm such as Product manager, sales managers, Human recource managers and finance managers in the firm to make data driven decisions.
