How to Build an Interactive Coffee Shop Sales Dashboard in Excel | Data Analyst Portfolio Project with Solutions

TL;DR

Build an interactive Excel dashboard by profiling the raw data, adding calculated columns, exploring patterns with pivot tables, and visualizing results with pivot charts. Chris Dutton uses nearly 150,000 Maven Roasters transactions from three New York City locations, covering January through June 2023. The walkthrough includes revenue, month, weekday, and hour calculations before analyzing sales trends, so read on for the exact project workflow and formulas.

Transcript

hey everyone Chris Dutton here from Maven analytics and in this video I'm going to walk you through a beginner level Excel project to build an interactive coffee shop sales dashboard from scratch we're going to load up the data do some basic QA and profiling apply some formulas and functions and finally use pivot tables and pivot charts to bring th... Read More

Key Insights

• Excel pivot tables are essential for exploring and analyzing data trends.
• Slicers in Excel allow users to filter data dynamically within dashboards.
• Data profiling and preparation are crucial first steps in dashboard creation.
• Line charts are effective for visualizing time series data.
• Column charts are useful for comparing data across categories like days or hours.
• Bar charts help in visualizing categorical data such as product types.
• Calculated fields in Excel can enhance data analysis by adding new dimensions.
• Interactive dashboards provide actionable insights for business decision-making.

Questions & Answers

Q: How do you build an interactive coffee shop sales dashboard in Excel?

Start by profiling and preparing the transaction data, then add calculated columns for revenue, month, weekday, and hour. Use pivot tables to explore revenue and transaction patterns, followed by pivot charts and an interactive dashboard that helps reveal business trends and opportunities.

Q: What data is used in this Excel dashboard portfolio project?

The project uses just under 150,000 coffee shop transactions from January through June 2023. The data covers three Maven Roasters locations—Astoria, Hell's Kitchen, and Lower Manhattan—and includes transaction, store, price, quantity, and product information.

Q: How do you calculate transaction revenue in Excel?

Add a revenue column and multiply the transaction quantity by the unit price. In the walkthrough, the formula uses the quantity in D2 and the unit price in H2, then fills the formula down and formats the results as currency with two decimal places.

Q: How do you extract the month and weekday from a date in Excel?

Use the MONTH function on the transaction date to return month numbers from 1 through 6 for this dataset. Use WEEKDAY with the option that assigns Monday as 1 and Sunday as 7; to display text labels, apply TEXT with three M characters for abbreviated months and DDD for abbreviated weekdays.

Q: How do you extract the transaction hour in Excel?

Use Excel's HOUR function and point it to the transaction timestamp. The resulting value indicates the hour of day, and the project data shows sales from 6:00 a.m. through hour 20, which is 8:00 p.m.

Q: What should you check when profiling the coffee shop sales data?

Review the transaction count, date range, store locations, prices, and available product categories. The walkthrough also checks for missing data, outliers, and miscategorized text; no obvious issues are found during the initial pass.

Q: What analyses should the Excel pivot tables include?

Create a pivot table showing total revenue by month, then examine when transactions occur by weekday and hour. The project also analyzes transaction counts by product category and compares transactions and revenue for the top 15 product types.

Q: Why keep calculated columns separate from the raw Excel data?

The walkthrough appends calculated fields to the right of the raw dataset instead of inserting them among the original columns. This creates a clear distinction between untouched source data and newly calculated or engineered fields such as revenue, month, weekday, and hour.

Summary & Key Takeaways

• Begin by loading and profiling your sales data to understand its structure and content. Use Excel formulas and pivot tables to prepare and explore the data, identifying key trends and patterns.

• Create pivot charts to visualize data trends, using line charts for time series and column charts for categorical comparisons. Assemble these charts into a coherent dashboard layout.

• Enhance your dashboard with slicers for dynamic filtering, allowing users to interact with the data. Polish the visual elements for a professional and intuitive presentation.