Golden Crust Pizza Sales Analysis

Contents

Dataset Introduction

The dataset contains a year's worth of sales from a fictional pizza outlet “Golden Crust Pizza” based in the US, including the date and time of each order and the pizzas served, with additional details on the type, size, quantity, price, and ingredients. I will be exploring this restaurant dataset to extract some useful information from the data at hand, such as total sales, seasonality, peak order hours, etc.

Objective

To explore the dataset, visualize it, extract insights & provide recommendations that could be useful for the business stakeholders in assessing the restaurant’s business and discovering the areas where it can improve to maximize profits.

Data

The dataset used has been downloaded from this source. It contains multiple tables in the form of separate CSV files. Each of these files contains a common identifier that could later be used in the process to join tables and retrieve relevant information.

Methodologies

I used Microsoft Excel to apply various techniques for analyzing and visualizing data. These techniques included but not limited to:

• Pivot tables

• Functions

• The Power Query Editor

• Merging tables

• Charts for analysis

Data Importing

The dataset was imported using the Import from Text Wizard. There are 4 CSV files located in the local directory. Each file contains data such as information regarding pizza sales, order details, and other details from the pizza menu like pizza types and main ingredients.

Data Wrangling

Some tables have a relationship based on common columns. To get a better view of the complete data set, I used the Power Query Editor feature to:

• Check columns to ensure the values are in the right format

• Join tables

• Add new columns

• Extract weekdays

• Extract months

I created pivot tables to summarize the data and extract insights..

Data Analysis

This phase involves analysis of the data & answering some questions to gain some insights from the pizza sales data for Golden Crust Pizza.

1. How many customers do we have each day? Are there any peak hours?

Technique used: Pivot Table, Average Formula, Bar Chart, Time Series Analysis

Findings :

• The maximum number of orders in total were placed on Thursdays where as Fridays was the day when the least number of orders were placed.

• Average orders per day, per week & per month were as follows:

• The number of orders peaked between
  • 11:00 AM and 01:00 PM
  • 05:00 PM and 06:00 PM

2. How many pizzas are typically in a order? Do we have any bestsellers?

Technique used: Average, Pivot Table

Findings : On average, approximately 5 pizzas were ordered per order.

Going by total sales the top 3 pizzas sold were:

• The California Chicken Pizza

• The Barbecue Chicken Pizza

• The Thai Chicken Pizza

3. How many pizzas were ordered per day?

Technique used: I used VLOOKUP to get values from the pivot table on total orders and added a new column to the orders table which represents the total number of pizza per order.

Findings:

• After plotting a chart of the number of pizzas ordered on different weekdays, it was found that the maximum number of pizzas were ordered on Fridays.

4. How much money did we make this year? Can we identify any seasonality in the sales?

Technique used: Power Query Editor, Pivot Table, Time Series Plot by Month

Findings:

• Total sales generated this year was $8,01,944.70

• The maximum sales were generated in August.

• The month of October witnessed the lowest sales.

• The sales peaked again In November after they soared in October.

5. Are there any pizzas we should take off the menu, or any promotions we could leverage?

Technique used: Treemap to visualize & compare total sales. Line chart to observe the pattern of top 4 pizzas that generated lowest sales.

Findings:

• Average sales amount to $ 25,060.77

• The Brie Carre Pizza generated the lowest total sale($ 11,352.00) in this year.

• The Thai Chicken Pizza generated the highest total sales($ 42,332.25) in this year.

• The Brie Carre Pizza has generated the lowest sale. The seasonal trend of Green Garden Pizza indicate that the demand for this pizza is soaring. We can promote these pizzas during peak hours of sales for a period a month or two. If this strategy does not work we can replace these pizzas with something new & exciting.

6. Which category of pizza had highest sales?

Technique used: Bar Chart, Filter

Findings:

• Classic Pizza was the category that had the highest sales(Approx. $ 2,15,732.60)

• Out of all the Classic Pizza Types, The Classic Deluxe Pizza generated the highest sale.

7. On which day there were highest number of orders? Which was the busiest day?

The maximum number of orders were placed on Friday & Saturday. Fridays was the busiest day of the restaurant.

8. Which pizzas were sold the most by size

Technique used: Pivot Table, Donut Chart

Findings:

• The L pizza type generated the maximum sales.

• The total Sales percentage difference between Small & medium size pizzas is lower compared to Large & medium size pizzas.

Data Visualization & Communication

The dashboard was created in Excel to deliver key insights visually. It shows the main highlights on the top & relevant pivot charts.

Recommendations

• We can offer discounts on food items based on minimum order bill during February, September, October & December to increase sales during these months.

• Thai Chicken Pizza generated the maximum revenue and hence should be made available throughout operational hours.

• We can increase manpower on Fridays & Saturdays to cater to the high number of orders.

• The resources can be allocated in such a way so that there is no chance of missing any potential sales during peak hours.

• The Brie Carre Pizza & the Green Garden Pizza can be promoted during peak selling hours. We can also leverage their favourability by offering discounts for a limited period.

• The maximum sale was generated by pizzas of Larger size so we need to promote them more. We can also give some small discounts on smaller size pizzas or include them in combo offers to maximize the profit.