Pizza Place Sales

Pizza Place Sale Detail Analysis:-

• Background: Year 2015 worth of sales from a fictitious pizza place, including the date and time of each order and the pizzas, served, with additional details on the type, size, quantity, price, and ingredients.

• Solution: A year’s worth of sales data from customer  are given from fictitious pizza place. The problem can be solved in MS Excel workbook to carry out the recommended analysis. All analysis can be solved with the help of pivot table.

• Aim : find out the required analysis in easy way and less number of steps,  

• Excel Tolls used: COUNT, AVERAGE, MATCH, Pivot Table and Pivot chart, VLOOKUP

•  Data given: Data is given in 4 tables with columns

1) Pizza( Pizza_id, Pizza_name, size, price)

2)Pizza_type ( pizza_id, pizza_type, ingredient)

3) order:- (order_id, date, time, quantity)

4)order_detail:-(order_detail_id, Order_id, pizza_id_quantity

• Recommended analysis:

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

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

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

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

• Solutions:-

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

Analytics:-As an average 60 pizzas can sales each day and peck hours are 12  Pm to 1pm normally at lunch time and 5pm to 6pm at evening

Tables Used: Order

Step 1:-Pivot chart is inserted on all data set and set date in row and order_id  in values and count function is applied on order_id and = AVERAGE() function used to find the average pizzas sale each day. which show that the approximately 60 pizzas sold per day.

Step 2:- Pivot chart is inserted on all data set and set time in row and order_id  in values and count function is applied on order_id and   insert pivot chart which show that 12  Pm to 1pm normally at lunch time and 5pm to 6pm at evening

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

Analytics:Generally 2-3 pizzas are ordered in a order. The Big_Meat_Pizzza is ordered Maximum number of time that is 1811 times there for The big_meat_pizza in small size is the bestsellers.

Table used: Order_detail

Step 1:- pivot table inserted on full table data at the row labels order_id and  order_detail-id is drag and drop at the values, Summarized values by count, average calculated by using average function average come 2.70 i.e  2-3 typically

Step 2:- Pivot table inserted on full table data at the row labels pizzas_id and order_detail-id is drag and drop at values, Summarized value by count, sort the count of order_detai_id coloumn the max value show in first row i.e.1811  along with pizza_id big_meat_s find the name from pizzas_id table the name of pizza of such id is The big meat pizza small in size.

And pivot chart inserted for good visualization.

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

Analytics:-Pizza place sales make  $801944.70  in this year 2015 For looking graph of total month wise sale the July month showing the Maximum sale so we  can conclude that in  July month is the

Table used: Order, order_detail, pizza

Step 1:- pivot table inserted on full table data at the row labels  month of date and  order_id is drag and drop at the values, Summarized values by count,  sort data A-Z, The result showing that July month is having large sales.

Step 2:- Pivot table inserted on full table data at the row labels pizzas_id and order_detail-id is drag and drop at values, Summarized value by count, copy price list from pizza table to fetch price of each pizza and new price column is added name as price_per_unit.per unit price is fetch by vlookup formula, and total sales for each pizzas calculated by multiply the total sold unit with per unit price, then do the final sum of all pizzas sales. Get answer $801944.70

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

• Analytics:-Big_meat_m ( Big meat in medium size and large size) Five_cheese_s,Five_Chees_m( Five Chees in small and in medium size) and four_Cheese_S  pizzas never get sold in a year 2015. We can think to off them. But if  we keep Big_meat_m and big_meat l,Fivecheese_m in offer price and  Five_cheese_s and  and four_cheese_s  free on bbq-ckn_l and ital-cpcllo_l  for few days to increase sales

Table used: Order_detail, Pizza

Step 1 :- Pizzas name list copy from pizza table, write match formula to check the occurrence of pizza_id in order table. Some places show 0 result i.e that particular pizzas not get sold. Find name of that pizzas from pizza table