Data Analysis using Microsoft Excel: Sales Analytics.
Tags: discuss, analytics, beginners, career
Published: 2022-08-15 23:39:52

#### Hello! You're welcome. This is my first analytics project. This analysis and visualization was done using Microsoft Excel. 

```


```


**Bekky's** **Place** Online store is a supermarket that sells all varieties of items in different categories, from Fashion to Home and Office supplies. 

They have got a dataset of their shopping over 5 years, from 2015 to 2020. The CEO, Miss Ahmed Oluwaseunfunmi Eberechukwu would like to see how the supermarket is fairing by certain Key Performance Indicators. The manager, Mr Idara Ogunyele Sabo has been assigned the task to identify the key areas within the business that need clarification and get them sorted out as soon as possible. 

Mr. Idara decided to recruit Ayodele as a Data Analyst to analyze the sales dataset and come up with results and visualization. 

####Tasks
1. Total revenue by product category. 
2. Reasons why customers return some of our items.  
3. Revenue by YoY. 
4. Customers' gender by percentage. 
5. Which of our delivery channels have the biggest revenue by percentage? 
6. Percentage of revenue by gender across age groups. 
7. Top 5 product subcategories by revenue. 
8. Product category by Total revenue, percentage of total revenue, total orders and percentage of total orders. 

**Data structure**: The dataset was gotten from the Data Entry officer of the supermarket. The dataset contains records of sales with fields like Order date, Order ID, Delivery date, Customer age, Gender, Delivery type, Product category and etc. 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m1ztge06rc7gqi3tw3wg.JPG)

**Data preparation**: In order to be able to find the Year on Year, I created a new column for the years. To also find the average delivery days, a new column was created. 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n5byotycmhzv05zbahdg.JPG)

**Analysis & Insights**: The analysis of the dataset was done using pivot table. The spreadsheet file can be found at the end of this article. 

**<u>Charts</u>**
The Key Performance Indicators include: Total Revenue, Total Orders, Total Shipping Fee, Total Number of Customers and the Average Delivery Days of products.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1gcr2arx1aqakg3997ps.JPG)


° Total Revenue by Product Category

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bi0szbbgej28c7b69s2f.JPG)

° Reasons why items are returned

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fn495rq4ltm8t4cnlq2r.JPG)


° Year on Year revenue

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9fai4tafp0v8e0ct6kaa.JPG)

° Percentage of Customers by gender

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aehjsyx1kudzn5wtm8y5.JPG)

° Revenue by Delivery channels

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qkswjmt0tk1fq6oqipex.JPG)

° Percentage of revenue by gender across age groups

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/56j0p8kcpfctukolx9do.JPG)

° Revenue of top 5 products subcategories

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i5x65mj0l6f2sdwike0v.JPG)

° Product category by total revenue, percentage of total revenue, total orders and percentage of total orders.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hpmnhipo4ps6bkpiuqwn.JPG)


**<u>Dashboard</u>**
![Sales dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yb8dyetxp1l7qqve04ek.png)


The project file can be found [here](https://docs.google.com/spreadsheets/d/1Pgx9heGmGb2lx32kgWI-lPc_78lxhNrq/edit?usp=sharing&ouid=107210796143614581784&rtpof=true&sd=true)

Thank you so much for reading. 🙏😊