PROJECT PROPOSAL:

Project Objective:

Uncover transaction patterns, boost product performance, and optimize sales and operations efficiency for a richer, data-driven coffee shop experience.

Project Overview:

This project enhances the analytical capabilities of the coffee shop by leveraging an Excel dashboard for sales analytics. By examining transaction data, the intention is to gain valuable insights into customer behavior, popularity of products, sales patterns, and operations efficiencies. The goal is to improve decision-making processes, optimize inventory management, and identify potential cross-selling opportunities.

Project Objectives:

1. Data Collection:

• Gather transaction data including transaction IDs, product details, and timestamps.

2. Data Cleaning and Preparation:

• Confirm the data is void of inconsistencies or missing values.

• Structure the data for efficient analysis using basic Excel features, functions, and formulas.

3. Basic Sales Analysis:

• Utilize PivotTables to analyze monthly, daily and hourly sales patterns.

• Identify high-performing days and time slots.

4. Product Performance Analysis:

• Determine top-selling products through PivotTables and charts.

• Explore relationships between products for potential cross-selling opportunities.

5. Operational Efficiency Optimization:

• Analyze peak hours and optimize staffing levels for efficient service.

• Evaluate location-based product sales differences to minimize waste and ensure product availability.

Expected Deliverables:

1. Excel Workbook with organized and cleaned transaction data.

2. PivotTables and PivotCharts illustrating sales patterns and product performance.

3. Summary report outlining key findings and recommendations for improving sales and operational efficiency.

Timeline:

• 8 hours over three-days.

• Prioritize tasks and utilize features to effectively meet project deadlines.

Key Stakeholders:

1. Project Manager: Jade Handy

2. Data Analyst: Jade Handy

3. Coffee Shop Management Team: Fictitious

Success Criteria:

1. Improve understanding of sales patterns.

2. Identification of top-selling products and potential cross-selling opportunities.

3. Enhance operational efficiency leading to improved customer service.

Risks and Mitigations:

• Data Quality Issues: Regular data quality checks and cleansing procedures will be implemented.

• Time Constraints: Prioritize tasks and allocate resources effectively to meet project deadlines.

Analysis Process:

1. Project selection and platform/tool selection.

2. Data readiness and initial structuring.

3. Iterative development of PivotTables and charts.

4. Analysis and insights gathering.

5. Final analysis, report generation, and portfolio presentation preparation.

This project provides actionable insights to optimize the coffee shop's sales strategy and enhance overall customer experience.

---

FINAL ANALYSIS REPORT:

Insights and Trends Analysis:

1. Transaction Timing:

• The majority of transactions occur between 8:00 a.m. and 10:00 a.m., indicating a peak in morning sales.

2. Peak Days of the Week:

• Monday, Thursday, and Friday are identified as peak days for transactions, suggesting increased customer activity on these days.

3. Weekend Comparison:

• Surprisingly, Saturday records the lowest transactions, even lower than Sunday. Further investigation may reveal factors contributing to this trend.

4. Busiest 6:00 Hour:

• Thursday stands out as the busiest during the 6:00-hour, suggesting a specific peak time on that day.

5. Monthly Increase in Products:

• All product categories and types experienced increased sales from January to June. Further analysis is needed to identify the factors driving this upward trend.

6. Sales Drop in Lower Manhattan (February):

• Lower Manhattan exhibited a more visually pronounced drop in sales, especially in February. Leveraging a 'Filter' in the PivotTable Field List provided a granular view, potentially indicating localized factors affecting sales.

7. Varied Monday and Weekend Activity:

• Monday activity and weekend activity vary significantly between stores. This suggests that customer behavior and preferences differ across locations.

Observations:

• The morning hours are crucial for sales, with a concentration between 8:00 a.m. and 10:00 a.m.

• Different stores may have distinct customer behaviors, emphasizing the importance of location-specific strategies.

• Monthly and weekly patterns indicate potential opportunities for targeted promotions or adjustments in staffing levels.

Recommendations:

• Further investigate the factors influencing Saturday's lower transaction numbers.

• Explore reasons behind the observed drop in sales in Lower Manhattan in February.

• Consider store-specific strategies for Mondays and weekends based on observed variations.

Observations/Recommendations — Cross-Selling:

• Bakery/Coffee: A near certainty, at just below 1, that someone who buys coffee cake also buys a Coffee.

• Merchandise/Coffee: Over 1 is encouraging. Who buys a branded t-shirt without buying the Coffee/Tea that provided the inspiration?

• Coffee beans/Coffee: Ditto

• Coffee and Hot Chocolate in the same sitting anyone? What?—next to no takers? Huh.

• Flavors/Coffee: Obviously no one is likely to order Flavors without Coffee to put them in—duh! (That would be creepy)

• Loose Tea/Coffee: This is a shocker! Loose Tea bought with Coffee? Huh. I wonder what this number looks like with a Tea purchase! Another project, another time.

• Packaged Chocolate/Coffee: No one is going to the coffee shop to buy just chocolate! Bonus: Two addictions satisfied in one sitting.

• Tea/Coffee: Highly unlikely someone would drink Coffee and Tea at the same time. Unless, of course, it was Coffee as a chaser to a Long Island ice tea—LOL.

• 50% of all purchases—kind of surprising it's only 50%! Consider this if starting a coffee shop. Call it Half Coffee Shop.

• The Mother of All Insights—As you can see in the featured image post, people prefer bakery items with coffee or tea. So, they're more likely to visualize coffee or tea with a bakery item, rather than a promotion with just coffee and tea together (even though coffee and tea are the bulk of all sales.)

Conclusion:

These insights provide a foundation for informed decision-making and strategy adjustments to enhance overall sales and customer experience.