View Project
Pizza Place Sales
A year's worth of sales from a fictitious pizza place
BACKGROUND :
“A year's worth of sales from a fictitious pizza place” in which various data are given like date and time of each order, type of pizzas served and some of additional details with it. The reason behind solving this problem is to know how many customers visit & order each day, what is the peak hours, which pizza is one of the bestseller and how much sales is been carried out each month of year 2015. The problem is quite tactical and a small project which can be solved by given data from source Maven Analytics.
SOLUTION :
A year's worth of sales data from customer insights are given from a fictitious pizza place. The problem can be solved by taking use of excel to carry out the recommended analysis. All the analysis are dependent on each other and can be solved with the help of pivot table in excel. So, that any person can calculate the sales of any pizza place on excel.
METHODOLOGY & PROJECT SCOPE :
Final data should be calculated and easy to understand is the main focus behind the work of this project
Firstly, you have to carry out the data dictionary from the source which includes 4 different table, different fields and its description.
According to this data dictionary, we can solve the recommended analysis part from the other data provided.
For execution of calculations part we need primary data, for this a data window is presented which collects the data for customer orders details, pizzas types, date, time, size and price of pizzas and then, calculations are carried out on excel.
From each data collected we can make required pivot table from it and use excel functions to solved the calculation part and then create a understanding pivot charts for the recommended analysis part of this project to provide solution.
Hence, a dashboard is prepared in which all the recommended analysis part is calculated and solved in excel.
GOALS & KPIs :
The success of my project is measured and carried out in following goals which includes:
Goal 1: Making a sales data understandable.
Goal 2: Can calculate the peak hours, bestsellers and seasonality of pizzas from the data.
Goal 3: It can calculate the revenue of any pizza place from its sales data.
EXCEL CONCEPTS USED :
Concept 1: COUNT, AVERAGE
Concept 2: SUMPRODUCT
Concept 3: NESTED SORTING, FILTERS
Concept 4: PIVOT TABLE, GROUPING
Concept 5: MAX, MIN
RECOMMENDED ANALYSIS :
After working on this dataset, we can conclude the following things:
How many customers do we have each day? Are there any peak hours?
Ans: It has around 60-65 customers each day. Yes, there are peak hours which is in between 12 PM - 1 pm & 5 pm - 7 pm.
How many pizzas are typically in order? Do we have any bestsellers?
Ans: By counting and averaging the Order_details_id (Unique identifier for each pizza placed within each order (pizzas of the same type and size are kept in the same row, and the quantity increases)) and Orders_id (Unique identifier for each order placed by a table), we come to know that, there are typically 3 pizzas in order and Big_Meat_S pizza is found to be one of the bestseller.
How much money did we make this year? Can we identify any seasonality in the sales?
Ans: In 2015, total revenue of 8,01,945 USD was generated. It has been found that a drastic changes occurs in month of October (12.32%), November (14.76%), December (15.12%). Therefore, "oct-nov-dec" are been identified as an seasonality in sales.
Are there any pizzas we should take off the menu, or any promotions we could leverage?
Ans: The pizzas which sold between the range of 1-500 pizzas in year 2015 should take off the menu, or for these pizzas promotions should be leveraged.
CONCLUSION :
Anyone can process their data and make an understandable sales data of any pizza company to know how many particular customers order each day, which type of pizza is the bestselling of that company, and how much revenue is been generated from its sales data. This data can be beneficial for customers, as they can make informed decisions about their orders based on the popularity of certain menu items and the company's overall sales performance. Therefore, it helps the customer to know the insights of that company if such kind of data is provided.
NOTE:
If viewers endeavor to open the Google Drive link towards the excel spreadsheet, some graphs and data will be scattered or disappear due to the features not being available on it. Simply download the file and you will be able to see the data and graphs from my project work.
Built with
