MRA PROJECT- CAFÉ DATA

Prepared by: Avinash Kumar Ray

 PROBLEM OBJECTIVE
The data set provided is the data set of a Café Chain for one of its restaurants. Do a thorough analysis of the
"

data and come up with the following analysis:

• Exploratory Analysis
• Menu Analysis
• Recommendation
Exploratory Analysis
 Exploratory Analysis of data & an executive summary of your top findings, supported by graphs.
 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 you suggest the best combo meals?
CATEGORY WISE NUMBER OF
RECORDS
12 months rolling trend for quantity

• From Jan 2011 onwards

decline was observed from all
the items available at the café.

• Although decline has been

observed across all the items,
however the larger % from
Liquor & Wines, followed by
beverages, food, Tobacco and
rest of the other items.
 Revenue Contribution by Individual Items

For rolling 12 months, 97% contribution is from Tobacco, followed by Food,

beverages & Liquor.
Tobacco growth is majorly due to increase in prices, as there as been -7.8% drop in
quantity.
The second most important component to revenue growth is food, which has also
observed -6.9% drop in sale, therefore as observed in slide, it would be important
to come up with new combo offers like introduction of Breakfast meals, Happy
hours, Set Menu Combo especially for solo trippers.
Largely footfall increase post 20:00 hours. To increase the numbers of customers
between 10 AM to 18:00 hrs.
• Set Morning breakfast menu: 6:00 AM to 10:30 AM.
• Set Menu lunches between: 12:00 to 1500 hrs.
• Happy Hours: 1500 hrs to 1830 hrs.

Recommend Happy
Hour/Combo Offers

Busy Hour
Highest foot fall is observed during 12:00 Midnight , between 19:00 ~20:00 PM and
comparatively higher on Saturday and Sunday.

Footfall is comparatively higher on sat, sun but its just 2% higher , weekend are not
having an impressive impact on the restaurant business.
Weekday/Weekend
Sale

Around 48% of the

sales happened
on Friday ,
Saturday &
Sunday.
December Month Has highest Revenue 10.6% in the Financial Year
REVENUE CONTRIBUTION CATEGORY-WISE

Tobacco, Food and Beverages are top

contributors
 Revenue Share Vs. Quantity Share

Food is sold the most. Tobacco holds the top revenue share
 Most Frequented Items # Tobacco Category
9000
8000
7000
6000
5000
4000
3000
2000
1000
0
 Sales of products increases from June and peak up in Dec month.
 From Jan till June sales is low for all the products.
 The months of July and August there is a sharp increase in sales for Food and Beverage
 Liquor and Tobacco shows contrasting trends for other months except December when the sale peaks up for both alike.
TOP 20 ITEMS IN TERMS OF QUANTITY SOLD
 Review Menu Items -
Food
Food Quantity Food Quantity
BBQ CHICKEN WINGS 67 CAFE ZABAGLIONE 9
MAC N CHEESE 60 POLENTA & CORN CAKES 8
HONEY ROASTED PEANUT BUTTER CK 48 3COURSE NON-VEG MEAL 7
BUNUELOS DE CHIRIZO 45 MUSHROOM & CORN 7
SAIGON NOODLES 40 RED VELVET CHEESE CAKE 7
SAIGON NOODLES 40 SCRAMBLED EGGS + BEVERAGE 7
3COURSE VEG MEAL 6
37 BEANS NACHO CHILLI W MEAT 6
CRUMBED TOFU CRUSTINI CALAMARI FRITO 6
POLLO PELOTA CON TAMATE SALSA 36 6
TOAST CIABATA
WARM PEPPER & CHICKPEA SALAD 34 CHEESE FINGERS 5
AFRICAN OMELETTE BREAKFAST 26 CHICKEN HAM 5
CEREAL KILLER 23 MOTHERS DAY SPL 5
FRIED MOZZARELLA 23 NONVEG PASTA PESTO 5
HONEY & MUSTARD GLAZED CHICKEN 21 CARROT CAKE 4
MEXICAN CHILLY CREPES 19 VEGETABLE PASTA 4
CURRIED WASHINGTON APPLE SOUP 17 2 MUFFINS + BEVERAGE 3
SALI PER EDU/PAPETA PER EDU 16 WAFFLES + BEVERAGE 3
CHICKEN DRUMMETS 13 NIRVANA HOOKAH DOUBLE 2
SPINACH TEMPURA 12 SCHNEIDER 2+1 2
B-BEANS G-TOMATO & WEDGES 11 STRAWBERRY MERINGUE 2
CHOCOLATE REVOLUTION 11 SUNNY SIDEUP + BEVERAGE 2
ALIO AGLIO 10 CAPONATA 1

Food: Need to remove the above listed items, since these are no frequently
sold. The above data is for rolling 12 months.

Items which good have observed good response in terms of quantity sold
from last 3 months are Chicken salami, Chicken Sausage, Hazelnut Fondue,
Honey Mustard Chicken, Moroccan Soy Kebabs, Masala Omelette, Marinated
Chicken Aioli, Non-Veg/Veg Arabiatta Pasta, Chicken Burger, Lebanese Wrap,
 Review Menu Items - Beverages

Beverages Quantity Beverages

GUATEMALA ANTIGUA (AULAIT) 21 3 RED BULL
GUATEMALA ANTIGUA (REG) 21 APPLE PEAR N LIME
BERRY BULL 19 FRUIT FROM THE TROPICS

ICE BULL 17 BAHUT BERRY

17 GREEN TEA
JAVA ESTATES (AULAIT)
15 APPLE CARROT N GINGER JUICE
NEW ORLEANS BLUE (REG)
11 GREAT LAKES FRAPPE
VARLHONA HOT CHOCOLATE
10 CAPPUCCINO GRANDE
BOTTLED WATER (1LITRE)
10 PEACHES N STRAWBERRIES
HOUSE BLEND DE CAFFE (AU LAIT)
CINNAMON APPLE TEA
NEW ORLEANS BLUE (AULAIT) 10
SUGARLESS LEMON ICE TEA
WHAT A MELON 4
WHITE TEA
MIXED FLAVOUR SINGLE 1
PEPPERMINT TEA
MOCAFE HOT CHOCOLATE(SF) 1
GHANA HOT CHOCOLATE
PEACH BULL 1
CRANBERY SMOOTHIES
2 AXE TWIST 1
GINSENG PEACH TEA
DECAFFINATE COFFEE FRAPPE 1
CAFE MIT SCHLAG
MIXED FLAVOUR SINGLE 1
CELERY N ORANGE
MOCAFE HOT CHOCOLATE(SF) 1 TOMATO CARROT N CELERY
PEACH BULL 1 5 RED BULL

Beverages:
Beverages: • The above mentioned items can be
promoted and are newly added to
• The mentioned items can be removed the menu (started from Jan 2011
considering their total contribution is onwards) and have observed a good
less than 1% to total no of days (365 response since its introduction in
days). menu. On an average 1 drink a day.
 Review Menu Items -
Tobacco
Tobacco Quantity
LATE HARVEST SULA CHENIN (BTL) 14
BLUE LAGOON SHEESHA 12
GOLD FLAKE KING BLUE 11
GOLD FLAKE KINGS-BIG 11
BENSON & HEDGES SPL 9
SPICE SHEESHA 6
CLASSIC MENTHOL 4
CLASSIC ULTRA MILD 4
BENSON & HEGDES GOLD BLUE 3
GOLD FLAKE LIGHTS-BIG 3
MINT FLAVOUR DOUBLE 3
APPLE FLAVOUR DOUBLE 2
CLASSIC MENTHOL RUSH 2
GREAT LAKES HOOKAH SINGLE 2
ICE SPICE SHEESHA 2
AL SIKANDARI HOOKAH DOUBLE 1
CLASSIC REGULAR 1
GOLD FLAKE ULTRA LIGHTS(20) 1
INDIA KINGS OCEAN BLUE 1

Food: Need to remove the above listed items, since these are no frequently
sold (less than 2 quantities in a month). The above data is for rolling 12
months.
 Review Menu Items – Tobacco, Liquor &
Wines
Tobacco Quantity Liquor + Wine Quantity
B1G1 ZINZI RED (BTL) 3
LATE HARVEST SULA CHENIN (BTL) 14
B1G1 ZINZI WHITE (BTL) 3
BLUE LAGOON SHEESHA 12
MAISON PIERRE SAUV MARSAN 3
GOLD FLAKE KING BLUE 11 SCHNEIDER BUCKET - 6 3
GOLD FLAKE KINGS-BIG 11 1+1 BTL4 SEASON WHITE 2
1+1 VLN CAB SAUV CLASIQ (BTL) 2
BENSON & HEDGES SPL 9
1+1 WINE BOTTLE 2
SPICE SHEESHA 6
4 SEASONS CLAS SAUV(BTL) 2
CLASSIC MENTHOL 4 2
B1G1 4SEASON CLAS SYRAH(GLS)
CLASSIC ULTRA MILD 4 MANDALA VALLEY RED ZINFANDEL(G 2
BENSON & HEGDES GOLD BLUE 3 SULA SATORI MERLOT (BTL) 2
3 WHISKEY (SM) 2
GOLD FLAKE LIGHTS-BIG
1+1 VLN CAB SAUV (BTL) 1
MINT FLAVOUR DOUBLE 3
1+1 VLN SAUV BLANC (BTL) 1
APPLE FLAVOUR DOUBLE 2 1
2 OCEAN PINOTAGE (BTL)
CLASSIC MENTHOL RUSH 2 4 SEASONS CLAS SYRAH(BTL) 1
GREAT LAKES HOOKAH SINGLE 2 B1G1 4SEASON CLAS SAUV(BTL) 1
2 B1G1 4SEASON CLAS SAUV(GLS) 1
ICE SPICE SHEESHA
GOSSIPS CHARD AUS (BTL) 1
AL SIKANDARI HOOKAH DOUBLE 1
MANDALA VALLEY CHENIN BLANC(GL 1
CLASSIC REGULAR 1
MATEUS ROSE PORTUGAL(BTL) 1
GOLD FLAKE ULTRA LIGHTS(20) 1 SULA CHENIN BLANC (BTL) 1
INDIA KINGS OCEAN BLUE 1 ZINZI WHITE (BTL) 1

Food: Need to remove the above listed items, since these are no frequently sold
(less than 2 quantities in a month). The above data is for rolling 12 months.
 FINDINGS Recommendations
• Clearly price increase has resulted in decline of sales of certain item.
• Top selling Category of the product for the Café is Tobacco, Food, Management may review the change in the price plan
Beverage, Liquor.
• Sales of products increases from June and peak up in Dec • Few of the items which contribute less in quantity and Revenue may
month. be discontinued. This will free space and time to mange other product

• Sales of most products starts increasing on Friday and reaches a • Resource allocation should be restructured to cater the peak in
peak on Saturday . demand at different times of day and different days of week .
• Sales show steady progress from 1100 hours onwards. Resource requirement can be computed from the finding shared.
Restaurant makes max sales in the evenings between 1900-
2300 hours. Peak time is 1900-2000 hours. • During the month of December and January , Tobacco has a brighter
consumption pattern than most other categories .Promotion of Tobacco
• Tobacco and Wine are sold throughout the day but increases up in can be planned according to the Monthly recommendation
night.
• Merchandise, Miscellaneous and Liquor and tobacco are the • Inclusion of combo meals can be planned during the time when the
categories of product sold mostly during the night. sales are lean.