INTRODUCTION
In Europe bikes dataset, there exist 18 columns for the date, gender, revenue, profit, cost, products etc. I’m to extract the meaningful insight from the spreadsheet that will be used for analysis.
ANALYSIS TOOL
Data Cleaning, Data Analysis, data visualization, creation of dashboard, all using Microsoft excel.
DATA CLEANING
Opening the data, there were some errors and duplicates, and the data entry weren’t in order with the dates . I cleaned the data by checking for duplicates and missing values, I found 1000 duplicates.
Then, I formatted the date column into the correct data type which is from the oldest to the newest, and I also changed the numbers from general numbers and added the $ sign to depict their value.
After all of these, I was sure the data was free of errors.
DATA ANALYSIS
I used the sum function to calculate the total quantity of orders, unit cost, profit, cost and revenue made from sales. (NB: I freezed the panes to make the headings visible).
DATA VISUALIZATION
Tools; Pivot tables and pivot charts I started by inserting a pivot table using the cleaned data and I begin to extract what i could get from the data.
CHART 1 represents the analysis of the total sum of order, cost incurred, profit and revenue made from sales of bike across the 6 countries; Australia, Canada, France, Germany, United Kingdom and United states. United States made the highest revenue from sales which is $27,777,098, followed by Australia $21,196,395 while Canada made the lesser amount of revenue, $7,906,182.
CHART 2 represents the analysis of the total sum of orders of bikes made by each gender across the 6 countries, which is 639,958 orders from the female in Europe with United States having the largest to be 225,681 and 693,747 from the male across Europe, United state also had the largest here which is 248,199 orders.
CHART 3 shows the analysis of the total sum of revenue on sales of bikes made across the 6 countries; Australia, Canada, France, Germany, United Kingdom, United states and their states inclusive between 2011 to 2016. The least sales was made in 2011, $8,951,966.
CHART 4 represents the analysis and visualization of the categories of age group (Adult; 36-64, Seniors; 66+, Young adults; 25-34 and the Youths; <25) orders of bikes made across the 6 countries; Australia, Canada, France, Germany, United Kingdom, United states and their states inclusive.
CHART 5 shows the analysis and visualization of profit by product categories sold across the 6 countries; Australia, Canada, France, Germany, United Kingdom, United states. More profits were made on Road bike which is $10,012,631 and the least on caps,$77,921.
CHART 6 shows the analysis and visualization of top 8 product sub- categories ordered across the 6 countries; Australia, Canada, France, Germany, United Kingdom, United states.
CHART 7 shows the analysis and visualization of total sales by product categories (Accessroies, Clothings and Bikes) across the 6 countries; Australia, Canada, France, Germany, United Kingdom, United states. Total percentage on sales of bike is 72%, clothing 10% and accessories 18%.
DASHBOARD
I moved the already prepared charts to a new worksheet. Chart 1 - 7. I added 5 slicers to the dashboard and reported the connections to make it easy to filter.
This is the combination of all the relevant visuals for this data;
RECOMMENDATION
The data I extracted will be useful for proper analysis and I recommend that advertisement of goods, particularly bike categories should be more focused on the youths in United States and Australia and less cost should be incurred in production of goods in Canada and France.