Introduction

In 2015, Marvens Pizza Place achieved significant growth in sales and customer engagement. To understand the factors driving this success, we analyzed a comprehensive dataset provided by Maven Analytics. The datasets were of high quality, requiring minimal data cleaning, enabling us to proceed directly to analysis using Excel, Power Pivot, and Pivot Tables. The datasets can be seen and downloaded.

Methodology

• The analysis was carried out using Microsoft Excel, we utilized various techniques to manipulate and visualize the data. These techniques included:
• Data Import and Merging: Combining multiple datasets into a single, unified source for comprehensive analysis.
• Data Relationships (Power Pivot): Establishing connections between different datasets (workbooks) to uncover underlying patterns and trends.
• Pivot Tables: Generating comprehensive tables to meticulously examine sales patterns and identify key insights.
• Data Visualization Charts: Employing charts to create compelling visual representations of trends and insights, enhancing the understanding of the data.

Customer Dynamics: Understanding Order Patterns

Our analysis of Marven’s Pizza Place customer data revealed a clear pattern in order volume throughout the year. With a total of 48,620 distinct orders placed by 21,350 customers, we observed distinct peaks and troughs in activity.

Peak Hours:

Lunchtime (12 PM): 6,543 orders

Early afternoon (1 PM): 6,203 orders

Early evening (6 PM): 5,359 orders

Late afternoon (5 PM): 5,143 orders

These peak hours suggest increased demand during lunchtime and dinnertime, necessitating appropriate staffing and resource allocation.

Off-Peak Hours:

Morning (9 AM & 10 AM): Notably lower activity

Late night (11 PM): Significantly lower activity

Understanding these patterns allows for optimization of staffing during off-peak hours, ensuring efficiency and cost-effectiveness.

This data-driven insight into customer dynamics is crucial for Marven’s Pizza Place to make informed decisions regarding staffing, resource allocation, and overall business operations.

Order Composition and Popular Items

Our analysis of order composition revealed that the average order consisted of two pizzas. The Thai Chicken Pizza, Barbecue Chicken Pizza, and California Chicken Pizza were the most popular items, providing valuable insights into customer preferences. These findings can inform inventory and supply chain management strategies.

Pizza Categories

The pizzas were grouped into four categories: Veggie, Chicken, Supreme, and Classic. Here is how they performed.

Interestingly, while Chicken pizzas had the least number of orders (10,815), Veggie pizzas generated the least revenue ($193,690). Chicken pizzas generated more revenue than Veggie pizzas, indicating higher profitability per unit.

Revenue Analysis

Marven’s Pizza Place generated $817,860.05 in revenue during the year, with an average revenue per customer of $38.31. Sales peaked in July and fell in October, suggesting potential seasonality in pizza demand. These insights can inform strategic planning, particularly in marketing and promotions.

Limitations

• The scope of our analysis is confined to data spanning just one year.
• The dataset exclusively contains information on selling prices, omitting any details regarding the cost of production

Recommendations

Menu Optimization

Our in-depth analysis of order patterns revealed that certain menu items, such as The Brie Carre Pizza, The Mediterranean Pizza, and The Spinach Supreme Pizza, were less popular and could be considered for potential phasing out.

· The Brie Carre Pizza: This is the third most expensive pizza, but had the least revenue (1.4%) and least quantity ordered (0.9%).

· The Green Garden Pizza: Had the second least quantity percentages of 1.93% and revenue of 1.87%.

· The Mediterranean Pizza: Had the third least quantity percentages of 1.90% and revenue of 1.88%.

Promotion Suggestions

· Conversely, promotions could be leveraged by Marvens to boost sales of other pizzas, particularly those in the Chicken category, which had fewer orders but generated more revenue than Veggie pizzas. This suggests that customers may be willing to pay more for Chicken pizzas, and promoting these could potentially drive sales and revenue.

· Focus on promoting best-sellers and high-margin items, particularly The Thai Chicken Pizza, The Barbecue Chicken Pizza, and The California Chicken Pizza, to maximize revenue.

· Implement seasonal promotions aligned with observed sales trends. Consider introducing combo deals or promotions during low-sales months like October to boost sales.

The table below details the Quantity sold and revenue generated for each pizza. It also details the respective percentages (as compared to revenue and quantity sold, respectively).

Detailing the Quantity sold and revenue generated for each pizza, and the respective percentage in the table below.

Conclusion

The 2015 sales analysis has provided valuable insights into the sales dynamics at Marvens Pizza Place. The data reveals a healthy diversity in pizza preferences among customers, with a notable skew towards Chicken and Supreme categories. The data displays a significant demand for pizzas like The Thai Chicken Pizza, The Barbecue Chicken Pizza, and The California Chicken Pizza, indicating their high revenue generation potential.

The analysis also identifies specific peak hours for customer orders, underscoring opportunities for targeted marketing and operational efficiency during these times. Given these insights, it is advisable to concentrate marketing efforts on high-revenue-generating pizzas and categories, especially within the Chicken category.

Additionally, phasing out less popular menu items and leveraging seasonal sales trends can be effective strategies. The proposed menu optimizations and targeted promotions are poised to streamline Marvens Pizza Place’s offerings and enhance overall profitability.

Appendices

Kindly find the Excel Dashboard here _WTF Assignment 1 _ Excel _ Abena & Nora.xlsx