Coffee Dataset Analysis Dashboard using Mirosoft Excel-

• Dataset-
  https://github.com/tanjotveer-98/Advanced_Excel_Project/blob/777bb93a00551d3468a00b178cd74fb02481021b/coffeeDataset.xlsx
• Tools- MS Excel 2019.
  
  

The given dataset file contains three worksheets-

1. Customers- ID, name, email, phone number,address, city, country, postal code, loyalty Card.
2. Products- ID, coffee type, roast type, size, unit prize, price per 100g, profit.
3. Orders- ID, Date, Customer ID, Product ID, Quantity.

In orders worksheet some fields were yet to be filled, for instance, Cusromer name, email, country, coffee type, roast type, size, unit price, sales.

Data Cleaning-

• Check for duplicates.
• Check for empty values.
  

Filling up empty fields.-

1. For Customer name, Email and Country, Vlookup() can be used to import data from other sheets.
   For instance, =VLOOKUP(C95,customers!$A$2:$B$1001,2,FALSE)
2. For the remaining fields, INDEX() and MATCH() can be used to import all data at once dynamically.
   For instance, =INDEX(products!$A$1:$G$49,MATCH(orders!$D95, products!$A$1:$A$49,0), MATCH(orders!L$1,products!$A$1:$G$1,0))
3. Sales field can be calculated using (Quantity*Unit Price) formula.

Creating Pivot tables-

Before creating any pivot tables, the data on 'orders' sheet needs to be converted into Table, so that in case any new data is added it can automatically be refreshed into pivot table created on top of that data.

1. The first pivot table is to analyze trend of coffee sales over the years and months. The line chart is used to display the trend over the period. Timeline is inserted to adjust the period dfor which we want to see the trend on the line graph. Slicers are added to filter the data based on roast type of coffee, loyaly card of thecustomer and product size. Following images shows the pivot table and the charts created from the aggregated data.
   
2. The second pivot table is to analyze the sales of coffee products by country and visualize using bar chart. Following images shows the pivot table and the chart created from the aggregated data.
   
   
   
3. The third pivot table is to analyze the sales of coffee by customers and display top 5 customers using bar chart. Following images shows the pivot table and the chart created from the aggregated data.
   

Creating the final dashboard.

All the charts were copied to a new worksheets and all charts were connected using "Report Connections" feature to the timeline and the slicers. Following image shows the final result.