Use Excel to analyse a sales dataset
It is really nice that Excel can be used to analyse datasets because most businesses have Excel as a standard software package that is installed on most computers.
I have obtained a sales dataset that had five questions associated with that dataset and have endeavoured to use the tools in Excel to answer those questions.
A screenshot of the dataset can be seen below:-
The first question is what is the total revenue generated by the store.
This is a rather straightforward question because, according to the investopedia website, revenue is sales. The only thing that I needed to do was to use the SUM() function to total all of the values in the Sales column of the dataset:-
The second question asked which category contributes the most to sales.
In order to answer this question it is necessary to create a pivot table and then a pivot chart of the Product Category and Sales columns of the dataset.
The pivot table is created by clicking on to the PivotTable icon of the Insert ribbon:-
The Product Category is placed in the Rows window and the Sum of Sales is put in the Values window to create the pivot table:-
It is then necessary to create the PivotChart from the pivot table by clicking onto the PivotChart icon of the Insert ribbon:-
The resulting chart can be seen below, and it shows that Technology had the most sales in this dataset:-
The third question asked what the sales trend for the past year has been.
Again, a pivot table will need to be created using the Order Date by Month in the Rows window and the sum of Sales column in the values window.
After the pivot chart has been created, it is necessary to create a Pivot chart from the pivot table that had previously been created. I chose a line chart in this instance and included a trendline in the chart to visually show that the trend of sales is increasing:-
The fourth question asked which region has the highest sales and the lowest sales.
In order to answer this question, it was again necessary to create another pivot table. I placed the Region in the Rows window and the sum of Sales in the Values column.
Once the pivot chart was created, I created a chart from the pivot table using the PivotChart icon. I chose a column chart in this instance, which revealed that the East region had the highest sales and the South region had the lowest sales:-
The fifth and last question was what is the average profit margin of the store.
According to the investopedia website, profit margin is calculated as (profit / sales) * 100.
I therefore used the AVERAGE() function to calculate the average profit divided into the average sales and multiplied the result by 100:-
I have prepared a video to accompany this blog post and it can be found here:- https://youtu.be/XIVIAkvlW2k
