This project explores a bike sales dataset, creating pivot tables and dashboard in Excel.
You can view the dataset, pivot tables and dashboard on this link.
Data Manipulation
The bike_buyers dataset consists of several columns including people's Age, Gender, Marital Status, Income, Education, Commute Distance, Region etc.
The following data manipulations were performed:
- in the Marital Status column, the values 'M' and 'S' were replaced with the values 'Married' and 'Single' respectively,
- in the Gender column, the values 'M' and 'F' were replaced with the values 'Male' and 'Female' respectively,
- the data type for the Income column was set as currency,
- in the Commute Distance column, the value '10+ Miles' was replaced with the value 'More than 10 Miles',
- a new column, Age Brackets, was created based on the Age column with the following criteria-
- Age > 54 --> 'Old'
- 31 <= Age <=54 --> 'Middle Age'
- Age < 31 --> 'Adolescent'
- Else --> 'Invalid'
Pivot Table and Visualizations
Four pivot tables and corresponding charts were created to analyze the data:
- average income of customers who purchased bikes versus those who did not, categorized by gender,
- count of purchased bikes based on customer commute distance,
- count of purchased bikes categorized by customer age brackets,
- count of purchased bikes based on customer age.
Insights
A dashboard for bike sales was created with the charts. Filters on marital status, region, and education were also added. A few insights are mentioned below, though additional findings can be uncovered by changing the filters, as it is an interactive dashboard.
- Females and males who buy bikes earn slightly more than those who don't (4% and 7% more, respectively). This indicates income plays a varying role in bike purchasing decisions across these groups.
- Single females show a small 2% income difference, while single males see the largest gap, earning 18% more than non-buyers. Single males with higher incomes may be more likely to buy bikes, reflecting different spending priorities or financial flexibility compared to single females.
For detailed visualizations and further insights, please chcek out the dashboard on this link.