Assignment Snapshot
• Background
• The data set provided constitutes the data of a Café Chain for one of its restaurants. We need to do a thorough analysis of the data
and come up with the following analysis:
• Exploratory Analysis
• Menu Analysis
• Price Analysis
• Exploratory Analysis
• What kind of trends do you notice in terms of consumer behavior over different times of the day and different days of the week? Can
you give concrete recommendations based on the same?
• Are there certain menu items that can be taken off the menu?
• Are there trends across months that you are able to notice?
• Menu Analysis
• Identify the most popular combos that can be suggested to the restaurant chain after a thorough analysis of the most commonly
occurring sets of menu items in the customer orders
• The restaurant doesn’t have any combo meals. Can we suggest the best combo meals?
• Pricing Analysis
• Identify pricing changes and menu items which saw a positive impact and / or negative impact of the price changes?
• Show a price analysis chart with four quadrant price analysis as below, along with quantification of the volume increase and decrease
in relation to the price changes
• Price increased – sales volume decreased in post period of 2-4 weeks ( take a pre period window of sales of 1-2 weeks)
• Price increased – Sales volume increased
• Price decreased – Sales volume increased
• Price decreased – Sales volume decreased
Exploratory
Analysis
Revenue Share || Month-wise
TOTAL
2011 Mar, 2,801,710 , 9% 2010 Apr, 2,442,077 , 7%
2010 May, 2,360,411 , 7%
2011 Feb, 2,839,305 , 9%
2010 Jun, 2,352,765 , 7%
2011 Jan, 3,110,742 , 9%
2010 Jul, 2,728,633 , 8%
2010 Dec, 3,473,691 , 11% December is
2010 Aug, 2,832,578 , 9%
the month with
highest sales
2010 Nov, 2,657,476 , 8%
2010 Sep, 2,497,643 , 8%
2010 Oct, 2,708,864 , 8%
Revenue Share Vs. Quantity Share
Share of Quantity Share of Revenue
BEVERAGE
TOBACC 17%
O BEVERAGE
23% 31%
TOBACCO
44%
FOOD
30%
FOOD Food is sold the
38% most. Tobacco
holds the top
revenue share
Revenue Contribution ||Category-
wise
3,500,000 17,148 18,000
15,070 16,000
14,705
3,000,000 14,216
1,423,520
13,514 13,334 14,000
13,097 12,875
12,710 12,793
12,201
11,856
1,412,834
2,500,000 12,000
1,230,823
1,308,124
1,234,956
1,198,491
1,235,362
1,211,199
10,000
1,146,639
1,016,455
2,000,000
1,031,435
1,057,238
8,000
1,500,000
6,000
960,410
930,919
4,000
926,697
1,000,000
830,657
907,435
797,599
721,928
799,857
789,523
774,513
773,830
739,359
2,000
500,000
631,029
545,530
513,753
-
477,217
472,730
468,938
461,704
430,848
391,558
391,027
368,872
357,035
- -2,000
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
2010 2010 2010 2010 2010 2010 2010 2010 2010 2011 2011 2011
BEVERAGE FOOD LIQUOR LIQUOR & TPBACCO MERCHANDISE MISC TOBACCO WINES Sum of Quantity
Tobacco, Food
and
Beverages are
top
Day of Week Sales Trend
6,000,000 28,476
25,151
5,500,000 24,240
24,500
21,539 21,625 21,539
5,000,000 20,949
19,500
4,500,000
4,000,000 14,500
5,642,03
Sum of Total
Sum of Quantity
4,987,90
9
4,851,13
3,500,000
9,500
4,379,45
4,361,24
4,361,11
8
4,222,99
7
3,000,000
7
5
7
2
4,500
2,500,000
2,000,000 -500
Sales increases
Mon Tue Wed Thu Fri Sat Sun on onwards with
Friday peak on
Saturday
Sales shows steady progress
Hour of the day Sales trend
from 1100 hours onwards.
Restaurant makes max sales
in the evenings between
1900-2300 hours. Peak time
18,000
is 1900-2000 hours
16,000
14,000
3,14
3,12
5
0
3,56
3,23
3,06
0
12,000
5
8
2,68
3,20
WINES
5
1
TOBACCO
3,03
3,00
5
10,000 MISC
5
MERCHANDISE
2,42
LIQUOR &
6
8,000 TPBACCO
5,94
6,10
3
LIQUOR
1
1,80
5,07
3
FOOD
5,26
4,75
5,63
5
5,32
7
BEVERAGE
1
6,000
4,19
1,59
8
4,23
1
1,45
0
6
3,90
4
1,38
7
6
3,44
4,000
7
2,56
9
2,02
64
2,14
4,95
2
1
4,72
4,63
4,25
4,09
0
2,000
3,85
2
7
3,63
3,61
3,59
0
3,22
3
6
7
1
5
2,55
6
2,20
1,88
1,74
1,319
1,239
3
3
2
2
1
8
-
1
0 1 2 3 4 5 6 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
Consumer Preference based on Time of Day
Peak time of selling Beverages are from
4PM to 8PM
Peak time for selling Food items is from
4PM to midnight
Peak time for selling Liquor is from
8AM
till midnight
Peak time for selling Tobacco is from
8PM to midnight
Tobacco and Wine are sold throughout
the day but increases up in night.
Merchandise, Miscellaneous and Liquor
and tobacco are the categories of product
sold mostly in night.
Consumer Preference based on Day of Week
Food along with
Tobacco
has the highest
preference on
Saturday
Items To Be
Discontinued
•There are 399 different Items which have sold less than 100 quantity in entire year across all
categories.
•These items can be taken off the menu after a discussion with business.
Trends Across Months
Sales of products increases from June
and peak up in Dec month.
From Jan till June sales usually is low for all
the products.
The months of July and August notice sharp
increase in sales for Food and Bevereage
Liquor and Tobacco shows contrasting trends
for other months except December when
the sale peaks up for both alike.
Menu Analysis
Suggestion For Best Combo Meals
Top 10 Items in terms of Quantity
Sold
Row Labels Sum of Quantity Average of Rate
NIRVANA HOOKAH SINGLE 8,686 258
CAPPUCCINO 7,144 62
MINT FLAVOUR SINGLE 6,019 232
GREAT LAKES SHAKE 5,914 115
SAMBUCA 4,765 365
POUTINE WITH FRIES 3,741 125
QUA MINERAL WATER(1000ML) 3,633 50
[Link] AVALANCHE 3,446 167
CARLSBERG 3,380 121
CALCUTTA MINT 3,339 372
Pricing Analysis
Items with Positive Price Change
The positive
price change
ranges from 4.5
to 100
Items With Negative Price Change
The negative
price change
ranges from 5 to
125
Price Change Vs. Sales volume Change
Price Change Vs. Sales volume Change
Recommendations
Sufficient resources ,inventory and staff should be available to
cater the peak in demand at different times of day and different
days of week . The findings from analysis of monthly trends can
be used as a basis to compute forecasted quantities for next fiscal
year .
Based on our analysis, below are the findings:-
noticed that during the month of May , Tobacco has a
• Top selling Category of the product for the Café is
brighter consumption pattern than most other
Tobacco, Food, Beverage, Liquor.
categories . Hence , the promotion of Tobacco can be
• Sales of most products starts increasing on Friday increased to tap this trend.
and reaches a peak on Saturday .
• Sales show steady progress from 1100 hours Inclusion of combo meals will help in boosting the
onwards. Restaurant makes max sales in the revenue during the lean period and overall since Food is
evenings between 1900-2300 hours. Peak time is a versatile category here .
1900-2000 hours.
• Merchandise, Miscellaneous and Liquor and
tobacco are the categories of product sold mostly in As discussed, There are couple of items which constitute for
night. a small portion of revenue for a category , hence these
• Tobacco and Wine are sold throughout the day but should be discontinued
increases up in night.
• Sales of products increases from June and peak up
in Dec month. Some items have taken a dip in sales after an increase in price.
Details of this increase/decrease in sales w.r.t price should be
monitored to strategize the pricing of popular items .