Question

The purpose of this assignment is to practice working with marketing research data, searching for patterns in the numbers that might lead you to a new understanding about consumers, their behaviors or their preferences. Download the McSandwich Excel spreadsheet that lists the responses given by 50 customers of the fast food restaurant. The customers were asked questions about the food (quality & variety), service (friendly, fast & competent), pricing, the overall experience (recommend to a friend, general satisfaction), and some personal information (gender, frequency of dining there, and how close to the restaurant they lived). Sort the responses by gender (0 = male, 1 = female) and use the “Average” function to determine if males and females had different opinions about the restaurant’s food, service, pricing and overall experience. Sort the responses again, this time by usage (0 = infrequent diner, 1 = frequent diner). Once again, use the “Average” function to determine if frequent diners and infrequent diners had different opinions about the restaurant’s food, service, pricing and overall experience. Sort the responses one last time, this time by home location (1 = less than a mile from the restaurant, 2 = 1-5 miles from the restaurant, 3 = more than 5 miles from the restaurant). Again, use the “Average” function to determine if nearby and more distant consumers had different opinions about the restaurant’s food, service, pricing and overall experience. In a 2 page document, summarize your conclusions from the three different ways you examined the data. Please include the specific numeric data that led you to those conclusions. Then, describe at least 3 recommended marketing actions for McSandwich, based on those conclusions.

Customer ID number	Friendly Employees (1 = not at all friendly, 10 = very friendly)	Competitive Prices                    (1 = overpriced vs. competitors,         5 = a bargain vs. competitors)	Competent Employees      (1 = not at all competent, 10 = very competent)	Quality of Food              (1 = poor quality, 10 = high quality)	Variety of Food                 (1 = not enough variety on menu, 5 = too much variety on menu)	Speed of Service        (1 = very slow, 10 = very fast)	Gender of Customer        (0 = M, 1 = F)	How likely to Recommend Restaurant to a Friend               (1 to 10, with 10 most likely)	Overall Satisfaction     (1 to 10, with 10 most satisfied)	Frequency of visits                     (0 = infrequent diner, 1 = frequent diner)	Distance from Home to Restaurant                (1 = less than a mile from the restaurant, 2 = 1-5 miles from the restaurant, 3 = more than 5 miles from the restaurant).
1	4	1	5	5	2	5	0	3	4	0	1
2	3	5	6	6	3	8	1	5	5	1	2
3	6	1	10	8	5	5	0	6	7	1	3
4	5	2	10	7	5	8	0	5	6	1	3
5	6	2	9	5	3	8	0	6	5	1	2
6	2	2	9	5	3	6	0	3	4	0	1
7	3	1	8	4	1	7	1	4	4	0	1
8	5	1	10	7	3	7	0	5	6	1	3
9	3	4	6	5	3	6	0	4	4	0	1
10	5	1	10	6	4	7	0	5	6	1	3
11	3	1	9	4	2	6	0	4	5	1	1
12	3	4	9	7	3	8	0	6	5	1	2
13	5	1	9	5	3	4	0	5	5	1	2
14	2	2	7	5	3	7	1	4	4	0	1
15	4	3	10	4	2	5	0	5	5	1	2
16	3	3	6	5	3	8	1	4	3	0	1
17	5	2	9	6	2	5	0	6	6	1	3
18	2	1	8	3	1	6	1	4	4	0	1
19	4	4	6	6	3	9	1	4	5	1	2
20	1	2	7	5	3	9	1	3	4	0	1
21	2	3	6	5	3	8	1	4	4	0	1
22	5	2	9	5	1	6	0	5	6	1	3
23	2	3	7	4	2	9	1	4	3	0	1
24	3	2	10	5	3	4	0	6	5	1	2
25	6	1	9	5	2	7	0	7	5	1	2
26	4	1	8	6	3	5	0	4	5	1	2
27	5	4	6	6	3	9	1	5	6	1	2
28	4	1	9	3	2	6	0	4	4	0	1
29	5	4	7	7	4	10	1	6	6	1	3
30	4	2	8	5	3	4	0	5	6	1	2
31	3	2	10	5	3	4	0	5	5	1	2
32	4	1	9	6	3	7	0	5	5	1	2
33	1	2	7	5	3	10	1	4	3	0	1
34	4	3	9	6	4	7	0	6	6	1	3
35	5	2	9	4	2	5	0	6	4	0	1
36	4	5	6	6	3	8	1	5	5	1	2
37	4	2	10	5	2	5	0	5	5	1	2
38	3	2	7	7	3	8	1	4	4	0	1
39	4	1	8	6	3	5	0	4	5	1	2
40	1	2	7	5	3	10	1	4	3	0	1
41	6	2	9	4	2	5	0	6	5	1	1
42	2	3	6	6	4	8	1	4	4	0	1
43	3	3	9	6	3	7	1	5	6	1	2
44	2	3	8	6	3	7	1	5	6	1	2
45	3	1	7	6	3	8	1	3	4	0	1
46	3	3	8	7	4	8	0	5	4	0	1
47	3	4	5	8	4	6	0	4	5	1	2
48	4	1	7	5	2	5	0	3	4	0	1
49	5	1	9	5	3	7	0	6	5	1	2
50	3	2	7	7	3	8	1	4	4	0	1

Answer 1

Procedure in EXCEL to examine data:  

Here detailed explanation for way 1 is given (Procedure for way 2 & way 3 is similar).  

• In Excel, select the data in the column of gender-->
• Home-->Select "Sort & Filter Option"--> Smallest to largest-->
• A dialog box appears. Select "Expand the selection" . Click on Sort.
• The whole data is sorted according to gender. There are 30 males and 20 females.
• Using average function, averages are calculated for each "Question (i.e. column)" separately for males.
• Similarly averages calculated for females.

Way 1: Sort the responses by gender (0 = male, 1 = female) and use the “Average” function to determine if males and females had different opinions about the restaurant’s food, service, pricing and overall experience.

Way 2: Sort the responses again, this time by usage (0 = infrequent diner, 1 = frequent diner). Once again, use the “Average” function to determine if frequent diners and infrequent diners had different opinions about the restaurant’s food, service, pricing and overall experience.

Way 3: Sort the responses one last time, this time by home location (1 = less than a mile from the restaurant, 2 = 1-5 miles from the restaurant, 3 = more than 5 miles from the restaurant). Again, use the “Average” function to determine if nearby and more distant consumers had different opinions about the restaurant’s food, service, pricing and overall experience.

The three different ways examined the data. The specific numeric data is as tabulated below:

			Customer ID number	Friendly Employees(1 = not at all friendly, 10 = very friendly)	Competitive Prices                    (1 = overpriced vs. competitors,         5 = a bargain vs. competitors)	Competent Employees     (1 = not at all competent, 10 = very competent)	Quality of Food              (1 = poor quality, 10 = high quality)	Variety of Food                 (1 = not enough variety on menu, 5 = too much variety on menu)	Speed of Service        (1 = very slow, 10 = very fast)	Gender of Customer        (0 = M, 1 = F)	How likely to Recommend Restaurant to a Friend               (1 to 10, with 10 most likely)	Overall Satisfaction    (1 to 10, with 10 most satisfied)	Frequency of visits                     (0 = infrequent diner, 1 = frequent diner)	Distance from Home to Restaurant                (1 = less than a mile from the restaurant, 2 = 1-5 miles from the restaurant, 3 = more than 5 miles from the restaurant).
30 (males)	M 0	Average		4.233333	1.866666667	8.7	5.46666667	2.866666667	5.866667		4.966666667	5.066667	0.76666667	1.933333333
20 (females)	F 1	Average		2.7	2.75	6.9	5.5	2.85	8.2		4.25	4.35	0.35	1.4
20 (infrequent diner)	INF 0	Average		2.65	2.05	7.15	5.05	2.65	7.35	0.65	3.9	3.8		1
30 (frequent diner)	F 1	Average		4.266667	2.333333333	8.533333333	5.76666667	3	6.433333	0.233333	5.2	5.433333		2.2
22 (less than a mile)	1	Average		2.818182	2	7.318181818	4.95454545	2.590909091	7.181818	0.590909	4	3.909091	0.09090909
20 (1-5 miles from restaurant)	2	Average		3.95	2.55	8.2	5.65	2.9	6.35	0.3	5.1	5.2	1
8 (more than 5 miles)	3	Average		5	2	9.25	6.5	3.5	6.875	0.125	5.5	6.125	1

the specific numeric data

			SERVICE				FOOD			PRICING		The overall experience
			Friendly Employees(1 = not at all friendly, 10 = very friendly)	Competent Employees     (1 = not at all competent, 10 = very competent)	Speed of Service        (1 = very slow, 10 = very fast)		Quality of Food              (1 = poor quality, 10 = high quality)	Variety of Food                 (1 = not enough variety on menu, 5 = too much variety on menu)		Competitive Prices                    (1 = overpriced vs. competitors,         5 = a bargain vs. competitors)		How likely to Recommend Restaurant to a Friend               (1 to 10, with 10 most likely)	Overall Satisfaction    (1 to 10, with 10 most satisfied)
30 (males)	M 0	Average	4.2333333	8.7	5.8666667	6.2667	5.466667	2.866666667	4.1667	1.8667		4.966666667	5.066667	5.0167
20 (females)	F 1	Average	2.7	6.9	8.2	5.9333	5.5	2.85	4.1750	2.7500		4.25	4.35	4.3000
20 (infrequent diner)	INF 0	Average	2.65	7.15	7.35	5.7167	5.05	2.65	3.8500	2.0500		3.9	3.8	3.8500
30 (frequent diner)	F 1	Average	4.2666667	8.53333333	6.4333333	6.4111	5.766667	3	4.3833	2.3333		5.2	5.433333	5.3167
22 (less than a mile)	1	Average	2.8181818	7.31818182	7.1818182	5.7727	4.954545	2.590909091	3.7727	2.0000		4	3.909091	3.9545
20 (1-5 miles from restaurant)	2	Average	3.95	8.2	6.35	6.1667	5.65	2.9	4.2750	2.5500		5.1	5.2	5.1500
8 (more than 5 miles)	3	Average	5	9.25	6.875	7.0417	6.5	3.5	5.0000	2.0000		5.5	6.125	5.8125
		To compare values we convert numbers into four decimal places

We simply average out the values, for example average of quality and variety of food for males=(5.466667+2.866666667)/2 =4.1667. Similarly all values can be found out.

FINAL TABLE			SERVICE	FOOD	PRICING	OVERALL
30 (males)	M 0	Average	6.2667	4.1667	1.8667	5.0167
20 (females)	F 1	Average	5.9333	4.1750	2.7500	4.3000
20 (infrequent diner)	INF 0	Average	5.7167	3.8500	2.0500	3.8500
30 (frequent diner)	F 1	Average	6.4111	4.3833	2.3333	5.3167
22 (less than a mile)	1	Average	5.7727	3.7727	2.0000	3.9545
20 (1-5 miles from restaurant)	2	Average	6.1667	4.2750	2.5500	5.1500
8 (more than 5 miles)	3	Average	7.0417	5.0000	2.0000	5.8125

Conclusions:

Way 1:

• Males are more satisfied by "SERVICE" and "OVERALL" than females because means are greater for males than females
• Females are more satisfied by "FOOD" and "PRICING" than males

Way 2:

• Frequent diner experiences better"SERVICE","FOOD","PRICING","OVERALL" than infrequent diner because all averages for frequent diner are greater than that for infrequent diner

Way 3:

• "PRICING" is same for people at distance less than a mile and more than 5 miles because their averages are same
• Rating by customers for "SERVICE","FOOD","OVERALL" increases as distance increases. Average (Mean) values increases as distance increases.

1. SERVICES: 5.7727 < 6.1667 < 7.0417
2. FOOD: 3.7727 < 4.2750 < 5.0000
3. OVERALL: 3.9545 < 5.1500 < 5.8125

Recommendations:

• Make environment more friendly for females.
• Instruct employees to not be biased for frequent diners
• Make sure to keep quality of food same for all irrespective of gender,frequency,distance
• Pricing should also be fixed.