The goal of this project is to create a dashboard of sales data for coffee store. The objective was crystal clear: transform raw data into actionable insights that drive smarter business decisions. The project involved a meticulous process, from collecting data to crafting visualizations that offer deep insights.
- Data Cleaning, Data Transformation, Data Analysis
- Filtering, Sorting and Excel Functions- Vlookup, IF
- Pivot Tables and Pivot Chart
- Data Visulisation and Dashboard creation
This stage began with creating a working sheet and transforming the datasets presented in different sheets into "Tables". After this we start getting to explore the data and check for any data quality issues.
- Checking NULL values, blanks and Errors.
- Removing duplicates, and irrelevant columns.
- Made sure data is consistent and clean with respect to data type, data format and values used.
- Using Vlookup function to find the customer name, email.country, coffee type, roast type,size unit price, loyalty card from the customers and products table.
- Created “Sales” column by calculation and “Coffee Type Name” and “Roast Type Name” columns by using IF function.
3 pivot tables were created to summarise the data and help identify trends in the dataset focusing on relationship between sales and other factors such as date, customers, country.
Finally, the dashboard was created by inserting and customizing the pivot charts of corresponding pivot table on a separate sheet named “Dashboard”. For user friendly and interactive experience 3 "Slicers" and 1 “Timeline” were added.
Below is a snippet of the final dashboard in Excel.
- 🇺🇸 The United States Takes the Crown in Coffee Sales 🌟
- The United States shines as the leading contributor to coffee sales revenue, surpassing all other nations.
- 💰 Liberica Leads the Way in Revenue Generation 💼
- Liberica coffee takes the top spot in revenue generation, showcasing its impact on the coffee industry.
- ☕ "Light" Roast Coffee Emerges as Revenue Leader 💡
- Among various roast types, "Light" roast coffee stands out as the leader in revenue generation.
- 🌍 Coffee Sales by Country:
- 🇮🇪 Ireland - Liberica coffee outperforms other types in revenue in Ireland.
- 🇺🇸 United States - Arabica coffee secures the top spot for revenue generation in the United States.
- 🇬🇧 United Kingdom - Robusta coffee stands out as the revenue leader in the United Kingdom.
- 📊 Sales Soar in 2021 with a 13.6% Surge 🔝 While 2019 and 2020 saw nearly identical sales figures, 2021 witnessed a significant boost with a 13.6% increase compared to the previous year, reflecting a thriving coffee market.
- Download the dataset from the provided GitHub repository link.
- Explore the interactive dashboard to gain insights into Coffee sales based on various demographics.
- Contribute or modify the project as needed for your specific requirements.