Coffee Sales Dashboard
Excel Project
Overview
The objective of this project was to create an interactive and visually appealing dashboard to track and analyze coffee sales data over time.
The dataset contains coffee sales data spanning from January 2019 to August 2022. The dashboard provides insights into total sales, sales by country, and top customers, allowing for more informed decision-making and strategic planning.
Main Objective
The main objective of this project was to design a comprehensive dashboard that helps visualize coffee sales data. The dashboard is intended to offer an easy-to-understand overview of sales trends, geographical distribution of sales, and key customer information.
Key Features
- Interactive Filters
The dashboard includes interactive filters for order date, roast type, size, and loyalty card status. These filters allow users to customize their view and drill down into specific segments of the data.
- Total Sales Over Time
A line chart displaying the total sales over time, segmented by different types of coffee (Arabica, Excelsa, Liberica, Robusta). This feature helps identify trends and seasonal patterns in sales.
- Sales by Country
A bar chart showing sales distribution by country. This visual helps in understanding which countries contribute the most to the overall sales.
- Top 5 Customers
A bar chart highlighting the top 5 customers based on sales. This feature provides insights into key customer relationships and their purchasing behavior.
Total Sales Over Time - Line Chart from Dashboard
Sales by Country - Bar Chart from Dashboard
Top 5 Customers - Bar Chart from Dashboard
Step 1. Data Collection and Initial Setup
The coffee sales data was sourced from the company's internal database, which tracks all transactions and sales activities. The raw data was exported into an Excel file for further analysis. Initially, the data was unstructured, containing inconsistencies. This required cleaning and formatting to prepare it for analysis and visualization.
Step 2. Data Cleaning - Preparation - Transformation - Formatting
To prepare the coffee sales data for analysis, several data cleaning and formatting steps were undertaken:
- Date Formatting
The date columns were converted to a standard date format using Excel's date formatting options. This ensured consistency and allowed for accurate time-based analysis.
- Size Formatting
The sizes of coffee products were standardized by adding "kg" to the values using custom formatting. This made the data more readable and consistent.
- Accounting Formatting
Sales figures were formatted using accounting formatting to ensure that all currency values were presented in a clear and professional manner. This included aligning decimal points and adding currency symbols.
- Checking Duplicate Values
The dataset was checked for duplicate entries using the REMOVE DUPLICATES feature. This helped to eliminate any redundant records and ensured the integrity of the data.
- Specific Excel Functions and Formulas Used
TRIM: Used to remove any leading or trailing spaces in text fields.
CLEAN: Applied to remove any non-printable characters from the data.
UPPER: Utilized to standardize text data to uppercase.
DATEVALUE: Used to convert date text to date values for consistent formatting.
REMOVE DUPLICATES: Employed to identify and remove duplicate records.
These steps ensured that the data was clean, consistent, and ready for further analysis and visualization in the dashboard.
- Dataset (raw data) before Transformation and Formatting
- Dataset after Transformation and Formatting
Step 3. Formula Application
In this project, several key Excel formulas were applied to manipulate and analyze the data effectively.
- XLOOKUP
Used to search for and return data from specific columns. It was particularly useful for fetching product details based on the product ID.
- INDEX
Employed to return the value of a cell in a specified array based on given row and column numbers. This helped in retrieving data from specific locations within the dataset.
- MATCH
Utilized in conjunction with the INDEX function to find the position of a value in a column or row. This combination facilitated dynamic data retrieval.
- IF
Applied to perform logical tests and return different values based on whether the condition was true or false. This was useful for creating conditional calculations and categorizing data.
These formulas were instrumental in transforming and analyzing the dataset, enabling the creation of an insightful and interactive dashboard.
Step 4. Creating Pivot Tables and Charts
PivotTables and PivotCharts were key components in summarizing and visualizing the coffee sales data. Here’s how they were used:
Pivot Tables
- Data Aggregation: PivotTables were used to aggregate sales data by various dimensions such as product type, country, and customer. This allowed for quick summarization and exploration of large datasets.
- Dynamic Filtering: With PivotTables, it was possible to apply filters dynamically, enabling users to drill down into specific aspects of the data. For instance, filtering sales by specific months or product sizes provided more granular insights.
- Calculations: Custom calculations, such as total sales and average sales per customer, were easily implemented using the calculated fields feature in PivotTables.
Pivot Charts
- Visual Representation: PivotCharts were created to visually represent the aggregated data from PivotTables. This included line charts to show sales trends over time, bar charts to compare sales by country, and bar charts to highlight top customers.
- Interactive Elements: PivotCharts provided interactive elements like slicers, which allowed users to filter the data visually. This interactivity made the dashboard more user-friendly and insightful.
- Trend Analysis: The line chart displaying total sales over time helped in identifying trends and seasonal patterns, while the bar charts offered a clear comparison of sales performance across different categories.
By leveraging PivotTables and PivotCharts, the data was transformed into meaningful insights, facilitating better decision-making and strategic planning.
Step 5. Dashboard Design
The design of the coffee sales dashboard was focused on creating a user-friendly and visually appealing interface that effectively communicates key insights. The following steps outline the design process:
Layout Decisions
- Organized Sections: The dashboard was organized into distinct sections to highlight different aspects of the sales data. These sections included:
- Sales Metrics: Displaying overall sales performance and trends.
- Regional Performance: Visualizing sales distribution by country.
- Customer Insights: Highlighting top customers based on sales volume.
- Consistent Design: A consistent color scheme and font style were used throughout the dashboard to ensure a cohesive look and feel.
Visualization Choices
- Line Chart for Sales Trends: A line chart was used to show total sales over time, segmented by different coffee types. This helped in identifying trends and seasonal patterns.
- Bar Charts for Comparison: Bar charts were employed to compare sales by country and to highlight the top 5 customers. These visuals provided clear and straightforward comparisons.
- Interactive Filters: Slicers were added for order date, roast type, size, and loyalty card status. These interactive elements allowed users to filter data dynamically and customize their view according to specific criteria.
- Data Labels and Tooltips: Data labels and tooltips were included to provide additional context and detail for each data point, enhancing the user experience.
User Interface Enhancements
- Conditional Formatting: Applied conditional formatting to highlight key metrics and important trends.
- Clear Titles and Labels: Ensured that all charts and sections had clear titles and labels for easy understanding.
- Spacing and Alignment: Paid attention to spacing and alignment to create a clean and uncluttered layout, making the dashboard easy to navigate and interpret.
By carefully considering layout and visualization choices, the dashboard was designed to be both functional and aesthetically pleasing, providing users with valuable insights at a glance.
Final Dashboard Layout
Conclusion
The coffee sales dashboard provided valuable insights into the company’s sales performance, enabling more informed decision-making. By visualizing key metrics such as total sales over time, sales by country, and top customers, the dashboard helped identify trends, seasonal patterns, and areas for growth. The interactive features allowed users to drill down into specific data points, making it a versatile tool for various stakeholders. The dashboard was well-received for its clarity, interactivity, and comprehensive design, ultimately contributing to better strategic planning and performance monitoring.
Key Takeaways
Skills Demonstrated:
Data cleaning and preparation
Application of advanced Excel formulas (XLOOKUP, INDEX, MATCH, IF)
PivotTable and PivotChart creation
Data visualization techniques
Dashboard design and user interface enhancements
Tools Used:
Microsoft Excel
Github repo for files: github.com/mustafaekinci7/Portfolio-Excel-Coffee-Sales-Dashboard