Coffee-Sales-Dashboard - Data Analysis Project using EXCEL

Introduction:

As a Data Geek and an aspiring Data Analyst, I have analyzed the dataset related to an Coffee business and presented my findings.

Questions to Analyze:

To understand the Coffee Business better, I asked the following:

1. Country Wise Coffee Sales of the business?
2. Top 5 Customers of the Business?
3. Total Coffee Sales Over Time?

Excel Skills Used

The following Excel skills were utilized for analysis:

• 🧮 Excel Formulas & Functions
• 📊 Pivot Tables
• 📈 Pivot Charts
• 💪 Data Validation

Coffee Sales Dataset

The dataset used for this project contains data extracted from Kaggle.com. The dataset is available on Kaggle.com, providing a foundation for me to analyze the data using Excel. It includes detailed information on:

• 👨‍💼 Customers
• 💰 Products
• 📍 Orders
• 🛠️ Sales

My Final Dashboard File is in Coffee Sale Dashboard.xlsx

Data Cleaning

🔍 Skills: Excel Formulas & Functions

Extracting Data using XLOOKUP

To extract data from a secondary worksheet to the main worksheet:

To extract Customer Name based on Order:

=XLOOKUP(Orders!C2, Customers!$A$1:$A$1001, Customers!$B$1:$B$1001, , 0)

To extract Customer Email based on Order, with a conditional check:

=IF(XLOOKUP(C2, Customers!$A$1:$A$1001, Customers!$C$1:$C$1001, , 0) = 0, "", XLOOKUP(C2, Customers!$A$1:$A$1001, Customers!$C$1:$C$1001, , 0))

• 📊 Dynamic Array Formula: Utilizes XLOOKUP function with nested IF() statement to analyze an array.
• 🎯 Tailored Insights: Provides specific information for Customer Name, Customer Email and Country.
• 🔢 Formula Purpose: This formula populates the columns below, returning the values based on Customer Data and specified Country.

Extracting Data using INDEX & MATCH

• 📊 Extracted data from the Products sheet based on Order details:
• 🔢 Populated the fields of Coffee Type , Roast Type , Size & Unit Price

=INDEX(Products!$A$1:$G$49, MATCH(Orders!$D2, Products!$A$1:$A$49, 0), MATCH(Orders!I$1, Products!$A$1:$G$1, 0))

- =INDEX(Products!$A$1:$G$49,MATCH(Orders!$D2,Products!$A$1:$A$49,0),MATCH(Orders!J$1,Products!$A$1:$G$1,0))

🔢 I calculated the Sales by multiplying Unit Price and Quantity Sold:

=L2*E2

🔢 Used IF Function in order to change the abbrevations for the Coffee Types and Roast Type

=IF(I2="Rob","Robusta",IF(I2="Exc","Excelsa",IF(I2="Lib","Liberica",IF(I2="Ara","Arabica",""))))
=IF(J2="M","Medium",IF(J2="L","Lite",IF(J2="D","Dark","")))

1️⃣ Country Wise Coffee Sales of the Business?

🧮 Skills: PivotTables & PivotCharts

• 📈Pivot Table
• 🔢 I created a PivotTable using the data table which I created in Orders Worksheet.
• 📊 I moved Country to the rows area, sum of sales into the values area.

📊 Analysis

💡 Insights

• 💰 Pie Chart depicts that the United States account for 79% of total sales and rest 21% is made up by United Kingdom & Ireland.
• 🔍 United States is the top selling market for the Coffee Business.

🤔 So What

• Coffee Business should focus on maximizing the profit & sales via United States market as it poses an great opportunity for the business to get even more higher levels of sales & profit by more marketing and supply of coffee in this market.
• The business should also consider selling only specific coffee types in United Kingdom & Ireland which are in demand it will reduce overall cost for the business and could help boosting sales in these countries.

---

2️⃣ Top 5 Customers of the Business?

🧮 Skills: PivotTables
📈 Pivot Table

• 🔢 I created a PivotTable using the Data Model I created with Power Pivot.
• 📊 I moved the Customer Name to the rows area and the Sum of Sales into the values area.

📊 Analysis

💡 Insights

• 💼 The Bar Chart depicts the top 4 buyers are from United States and 1 from United Kingdom
• 🔎 Demand for Coffee is the highest in United States & United Kingdom.

🤔 So What

• These insights holds great importance for business and provides a strong base for decision making and understanding that the markets of United States is very fruitful in terms of growth along with sales & profit.

---

3️⃣ Total Coffee Sales Over Time?

🧮 Skills: PivotTables
📈 Pivot Table

• 🔢 I created a PivotTable using the Data Model I created with Power Pivot.
• 📊 I moved the (States) column to the rows area and the (Sum of Sales) column into the values area.

📊 Analysis

💡 Insights

• 💼 Analysis depicts Arabica & Liberica coffee types leads the sales numbers followed by Robusta & Excelsa.

• 💰 The Sales for Robusta coffee type remain the lowest among all the coffee types.

• 🔎 Months of January , Feburary and September are the hot selling months for the coffee types and specifically Arabica & Liberica.

  

🤔 So What

• The Company should focus more on increasing the supply of Arabica & Liberica and do more heavy marketing for these two coffee types and specifically in the months of January , Feburary & September.
• The demand for Robusta coffee type is on decline , less supply of this coffee type will reduce the cost for the business and other coffee types could bring in more sales & profits for the business.

---

Conclusion

In this Coffee Sales Dashboard analysis, I leveraged advanced Excel functions such as XLOOKUP, INDEX-MATCH, and IF functions to extract, manipulate, and clean data efficiently 🧮📊. Using these functions, I was able to link multiple datasets, enabling detailed insights into customer behavior, product sales, and market performance. PivotTables and PivotCharts were then employed to analyze country-wise sales, top customers, and sales trends over time 🌍💼.

The U.S. emerged as the dominant market, accounting for 79% of sales, while other regions like the United Kingdom showed potential with a more targeted approach 💡. Additionally, the analysis revealed that Arabica and Liberica coffee types drive the most sales, suggesting a focus on these high-demand products ☕📈. By combining these Excel tools and insights, the dashboard provides a data-driven strategy to enhance sales, reduce costs, and maximize profitability 💰.