Scenario:

I recently acquired a franchise, Maven Roasters, a coffee shop chain with three locations in New York City.

Goal:

The goal of the project is to create a dynamic dashboard so that we can understand the trends, patterns and business opportunities.

How I went about the project?

I had a transactional data from January to July 2023. I explored the data. The data was clean and was free from any duplicates or any errors, and converted the data into a table. I created four columns which were required for the creation of the visualization. They were:

• For calculating the revenue:

Revenue =[@[transaction_qty]]*[@[unit_price]]

• For extracting the month the shop was open and displaying the 1st three letters of the month:

Month Name Month Name =TEXT([@[transaction_date]],"mmm")

• For extracting the day of the week the shop was open and displaying the 1st three letters of the day:

Day of Week =TEXT([@[transaction_date]],"ddd")

• For extracting the hour of the day the shop was open:

Hour =HOUR([@[transaction_time]])

Pivot tables were created for:

• Revenue per Month,
• Transactions per Day of the Week,
• Transactions per Hour,
• Transactions & Revenue per Product Category, and
• Transactions & Revenue per Product, which was sorted by descending to display the top 15 products only by Transactions.

Using the above pivot tables, line chart, bar chart and column chart were created.

A KPI card for the total revenue was provided at the top of the dashboard.

All the charts and KPI was connected to a location slicer for interactivity.

Findings:

At all 3 locations, from 7 AM to 10 AM is the busiest period. And 8 PM is relatively quiet.

Coffee was the most sold product category followed by Tea.

Brewed Chai Tea was the most sold product.

Interestingly both Regular Syrup and Sugar Free Syrup found a place in the Top 15 Products, at 14th & 15th respectively.

Astoria sold the most in the product category, Tea.

Hell's Kitchen had the most orders & revenue among the 3 locations.

Lower Manhattan had relatively less orders between 7 PM to 8 PM.

Link to the dashboard: Coffee Shop Sales - working_file.xlsx - Microsoft Excel Online (live.com)