Interactive Coffee Sales Dashboard

Interactive Coffee Sales Dashboard

Interactive Coffee Sales Dashboard

Excel Data Analysis Project

Excel Data Analysis Project

Introduction


Welcome to my coffee sales data analysis project! This interactive dashboard provides a comprehensive overview of coffee sales trends, allowing users to explore key metrics and insights with ease. Using advanced Excel techniques, I transformed raw data into a visually appealing and functional tool for business intelligence.


Project Overview


This project involved analyzing coffee sales data from three primary sources: order details, customer information, and product data. By leveraging Excel's powerful functions and features, I created a consolidated dashboard that offers real-time insights into sales performance, profitability, and customer preferences.

Data Processing and Integration


1. Data Consolidation: Utilized XLOOKUP and INDEX-MATCH functions to merge data from multiple sheets into a single, comprehensive order table.

2. Calculations: Implemented multiplication formulas for sales calculations and multiple IF functions for complex data transformations.

3. Data Cleaning: Performed date and number formatting, checked for and removed duplicates to ensure data integrity.

4. Table Conversion: Converted the range to a table format for improved data management and analysis.


Dashboard Creation


1. Pivot Tables and Charts: Developed pivot tables to summarize key metrics and created corresponding pivot charts for visual representation.

2. Interactive Elements:

- Timeline Slicer: Added a timeline for easy date range selection.

- Custom Slicers: Implemented filters for roast type, country of origin, package size, and loyalty card membership.

3. Customization: Designed custom chart styles, graph layouts, and slicer formats to align with a coffee-themed color palette.


Key Features


- Total Sales Over Time: Line chart displaying sales trends across different periods.

- Profit Analysis: Bar chart showcasing profit distribution and patterns.

- Interactive Filtering: Users can dynamically filter data by date range, product attributes, and customer loyalty status.

- Cohesive Design: A visually appealing layout with a coffee-inspired color scheme for enhanced user experience.


Technical Skills Demonstrated


- Advanced Excel Functions: XLOOKUP, INDEX-MATCH, IF statements

- Data Manipulation: Formatting, deduplication, table conversion

- Data Visualization: Pivot tables, pivot charts, custom formatting

- Interactive Dashboard Design: Slicers, timelines, dynamic updating


Acknowledgment

Special thanks to Mo Chen, whose work inspired and guided the development of this project.

Data Processing and Integration


1. Data Consolidation: Utilized XLOOKUP and INDEX-MATCH functions to merge data from multiple sheets into a single, comprehensive order table.

2. Calculations: Implemented multiplication formulas for sales calculations and multiple IF functions for complex data transformations.

3. Data Cleaning: Performed date and number formatting, checked for and removed duplicates to ensure data integrity.

4. Table Conversion: Converted the range to a table format for improved data management and analysis.


Dashboard Creation


1. Pivot Tables and Charts: Developed pivot tables to summarize key metrics and created corresponding pivot charts for visual representation.


2. Interactive Elements:

- Timeline Slicer: Added a timeline for easy date range selection.

- Custom Slicers: Implemented filters for roast type, country of origin, package size, and loyalty card membership.


3. Customization: Designed custom chart styles, graph layouts, and slicer formats to align with a coffee-themed color palette.


Key Features


- Total Sales Over Time: Line chart displaying sales trends across different periods.

- Profit Analysis: Bar chart showcasing profit distribution and patterns.

- Interactive Filtering: Users can dynamically filter data by date range, product attributes, and customer loyalty status.

- Cohesive Design: A visually appealing layout with a coffee-inspired color scheme for enhanced user experience.


Technical Skills Demonstrated


- Advanced Excel Functions: XLOOKUP, INDEX-MATCH, IF statements

- Data Manipulation: Formatting, deduplication, table conversion

- Data Visualization: Pivot tables, pivot charts, custom formatting

- Interactive Dashboard Design: Slicers, timelines, dynamic updating


Acknowledgment

Special thanks to Mo Chen, whose work inspired and guided the development of this project.