Blessing Ewuola's Blog

Analysis Of Orders Data Using Microsoft Excel

Analysis Of Orders Data Using Microsoft Excel

Blessing Ewuola's photo
Blessing Ewuola
·

3 min read

Table of contents

No heading

No headings in the article.

INTRODUCTION

An online store owner has sales representatives in 4 regions- North, Central, Caribbean, and South. He sells items that fall under three categories - Office supplies, Technology, and Furniture. This analysis is prepared to help him decide which region to build his first physical store and what items to sell.

ANALYSIS TOOL

I performed data cleaning, visualization, and dashboard creation using Microsoft Excel. I used Excel because the data has only 49 records.

DATA CLEANING

  • I cleaned the data by checking for duplicates and missing values, of which I found none.

  • I formatted the profit, shipping, and sales columns into the correct data types.

  • I made sure that the data was free of errors.

orders table.png

DATA ANALYSIS

  • I used the sum function to calculate the total sales made.

salessss.png

  • I used the SUMIF function to calculate the total profit and loss incurred.

profittt.png

losss.png

  • Chart 1 shows that phones have the highest total sale of $2124.5 while labels have the least total sale of $13.08.

chart1.png

  • Chart 2 shows that the most sales were made in the Northern region while the least sales were made in the central region.

chart2.png

  • Chart 3 shows the profit made in each region. The highest profit of $816.64 was made in the Northern region, but in the central region, a loss of $199.38 was incurred.

chart3.png

  • Chart 4 shows the top 5 subcategories from which profit was made. The most profit was made from the sale of phones, supplies, copiers, machines, and tables, in this order.

chart4.png

  • Chart 5 shows the total amount of sales made in the 3 product categories. Most sales were made in the technology category and the least sales were made in the office supplies category.

chart5.png

  • Chart 6 shows the total quantity of each item ordered. Customers made the most order quantity of 31 for supplies and the least order quantity of 2 for paper.

chart6.png

DASHBOARD

  • I moved the already prepared charts to the new worksheet.

  • I added 3 slicers to the dashboard, and made a connection among them to make filtering easier.

A combination of all the relevant visuals for this study is shown below:

final dashboard.png

INSIGHTS

  • There is high demand for copiers which are the most ordered in the Technology category.

  • The highest sales and profit was made in the North.

  • Selling only 5 units of phones produced the most profit.

  • A one-time loss was made in the North and South. Discounts were given for these sales.

RECOMMENDATIONS

  • More attention should be put in the North and the sales of items in the Technology category.

  • Promotion of the phones through advertisement or promos should be considered.

  • The store owner should focus more on the 5 most ordered items.

  • In the Central region, most items were sold at discounted prices. This made the store run at a loss. The percentage discount given should be reduced or scrapped entirely since the store cannot afford giving discounts.

datadata analysisexcelMicrosoftDatabases