Unformatted Attachment Preview
Benchmark Assignment - Data Analysis Case Study
The Cicero Italian Restaurant was founded by Anthony Tanaglia in 1947 in Cicero, Illinois, a
suburb of Chicago. He built the business with his family from a small pizza and pasta restaurant
to 10 locations in the Chicago area. Michael Tanaglia, Anthony’s grandson, moved to Arizona to
escape the cold Chicago winters and opened a restaurant in the Chandler area. The Arizona
restaurant gained momentum thanks to the Chicago-style pizza and quality Italian dishes.
Anthony decided to expand operations in Arizona, adding a second location in Glendale. The
Glendale location was managed by Michael’s son Tony.
After a year of operations, Michael had some concerns with the Glendale location. Michael does
not want his family’s business to fail, and he wants his grandfather’s legacy to last. Michael also
understands how important an operational evaluation can be to identifying the strengths and
weaknesses of a business. Michael confides his concerns to you and asks if you will do him a
favor and use your quantitative analytic expertise to help him evaluate the Glendale location’s
operations in three key areas: customer satisfaction, customer forecasting, and staff scheduling.
As his friend, you agree – though his offer to treat you to the large pizza of your choice did not
hurt.
First Evaluation
The first evaluation required an understanding of the factors that contribute to customer
satisfaction and spending. Refer to the data Michael provided in the Excel spreadsheet
“Benchmark Assignment - Data Analysis Case Study Data.” Identify which variables are
significant to predicting overall satisfaction. Develop and interpret the prediction equation and
the coefficient of determination. Based upon the data in this evaluation, what areas should
Michael and Tony Tanaglia focus on to improve customer satisfaction?
Second Evaluation
The second evaluation requires a forecast of customers based upon demand. Michael reviewed
data for the previous 11 months in an attempt to better forecast restaurant customer volume.
MONTH
January
February
March
April
May
June
July
August
September
October
# OF CUSTOMERS
650
725
850
825
865
915
900
930
950
899
© 2017. Grand Canyon University. All Rights Reserved.
November
December
935
?
Which method should the business owner use to yield the lowest amount of error and what
would be the forecast for December? Refer to the Excel spreadsheet “Benchmark Assignment Data Analysis Case Study Template.”
Third Evaluation
The third evaluation concerns staff scheduling. Some of the customers have complained that
service is slow. The restaurant is open from 11:00 a.m. to midnight every day of the week. Tony
divided the workday into five shifts. The table below shows the minimum number of workers
needed during the five shifts of time into which the workday is divided.
Shift
1
2
3
4
5
Time
10:00 a.m. – 1:00 p.m.
1:00 p.m. – 4:00 p.m.
4:00 p.m. – 7:00 p.m.
7:00 p.m. – 10:00 p.m.
10:00 p.m. – 1:00 a.m.
# of Staff
Required
3
4
6
7
4
The owners must find the right number of staff to report at each start time to ensure that there is
sufficient coverage. The organization is trying to keep costs low and balance the number of staff
with the size of the restaurant, so the total number of workers is constrained to 15.
Based on these factors, recommend the staff for each shift to accommodate the minimum
requirements for customer service. Refer to the Excel spreadsheet “Benchmark Assignment Data Analysis Case Study Linear Programming Template.”
2
Dine In (1)/Take Out (2)
1
1
1
1
2
2
2
1
2
1
2
2
1
1
1
1
1
2
2
1
2
1
2
2
1
2
2
1
2
1
2
2
1
2
2
1
1
1
2
2
1
2
2
1
2
1
Satisfaction with Service Satisfaction with Food
4
2
3
5
3
2
3
4
3
2
1
2
5
4
4
3
4
3
3
4
4
2
3
3
3
4
3
4
3
4
2
2
4
3
3
3
3
4
3
3
4
3
2
3
4
2
Overall Satisfaction
4
3
3
5
4
4
4
3
3
3
3
2
4
5
5
4
3
4
4
5
5
3
5
4
4
5
3
4
4
5
3
2
4
2
3
3
3
5
3
4
4
3
3
3
4
3
4
3
3
5
3
3
3
3
3
2
2
2
4
4
4
3
4
3
3
4
4
3
4
3
3
4
3
4
4
4
2
2
4
3
3
3
3
4
3
3
4
3
2
3
4
2
2
1
1
4
4
5
5
5
5
4
4
5
Driving Distance to Restaurant
5
5
10
12
10
15
10
16
2
10
15
10
12
16
18
20
18
20
16
7
9
10
6
10
9
8
10
6
10
10
15
16
18
16
14
20
16
17
16
5
10
6
10
6
7
6
Total Bill
10
15
10
15
25
25
26
27
25
26
20
20
20
20
20
27
28
28
28
12
20
24
26
28
27
24
22
23
25
20
20
20
20
20
25
22
23
28
23
15
28
24
27
26
28
24
8
6
8
22
23
20
LP_min
Linear, Integer and Mixed Integer Programming
Signs
<
=
>
less than or equal to
equals (You need to enter an apostrophe first.)
greater than or equal to
Data
x1
Minimize
10 AM-1 PM
1:00 PM - 4:00 PM
4:00 PM- 7:00 PM
7:00 PM- 10:00 PM
10:00 PM- 1:00 AM
Results
Variables
Objective
x2
1
0
x3
1
x4
1
x5
1
1 sign
>
>
>
>
>
RHS
3
4
6
7
4
0
0
Page 1
LP_min
Results
LHS
Problem setup area
Slack/Surplus
0
0
0
0
0
0
3
4
6
7
4
< constraints
0
0
0
0
0
0
0
0
0
0
> constraints
0
0
0
0
0
Page 2
3
4
6
7
4
Forecasting
Moving averages - 4 period moving average
Data
Period
Period 1
Period 2
Period 3
Period 4
Period 5
Period 6
Period 7
Period 8
Period 9
Period 10
Period 11
Next period
4
Demand
Forecasts and Error Analysis
Forecast Error
Absolute
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Total
Average
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Bias
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
MAD
SE
Squared
Abs Pct Err
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
MSE
MAPE
1
#DIV/0!
0,9
Not enough data to compute the standard error
0,8
0,7
Value
Num pds
0,6
0,5
0,4
0,3
0,2
0,1
0
1
Forecasting
1
0,9
0,8
0,7
0,6
0,5
0,4
0,3
0,2
0,1
0
1
2
3
4
5
6
7
Time
Demand
Forecast
8
9
10
11
Forecasting
Data
Period
Period 1
Period 2
Period 3
Period 4
Period 5
Period 6
Period 7
Period 8
Period 9
Period 10
Period 11
Weighted moving averages - 2 period moving average
Demand
Weights
0,15
0,3
Forecasts and Error Analysis
Forecast Error
Absolute
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Total
Average
Bias
Next period
0
Squared
0
0
0
0
0
0
0
0
0
0
0
MAD
SE
Abs Pct Err
0
0
0
0
0
0
0
0
0
0
0
MSE
0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
MAPE
Forecasting
1
0,9
0,8
Value
0,7
0,6
0,5
0,4
0,3
0,2
0,1
0
1
2
3
4
5
6
7
Time
Demand
Forecast
8
9
10
11
Forecasting
Next period
0,05
Forecasts and Error Analysis
Forecast Error
Absolute Squared
Abs Pct Err
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
0
0
0
0 #DIV/0!
Total
0
0
0 #DIV/0!
Average
0
0
0 #DIV/0!
Bias
MAD
MSE
MAPE
SE
0
Demand
1
0,9
0
0,8
0,7
Value
Alpha
Data
Period
Period 1
Period 2
Period 3
Period 4
Period 5
Period 6
Period 7
Period 8
Period 9
Period 10
Period 11
Exponential smoothing
0,6
0,5
0,4
0,3
0,2
0,1
0
1
Forecasting
1
0,9
0,8
0,7
0,6
0,5
0,4
0,3
0,2
0,1
0
1
2
3
4
5
6
Time
0
7
8
9
10
Benchmark - Data Analysis Case Study
2
1
Less than
Unsatisfactory
Satisfactory
0.00%
74.00%
70.0 %Content
10.0 %Variables The
for Predicting
identification
Satisfaction
of variables for
predicting
satisfaction is
not included or
is
incomprehensi
ble.
3
Satisfactory
79.00%
4
Good
87.00%
5
Excellent
100.00%
The
The
The
The
identification identification identification identification
of variables for of variables for of variables for of variables for
predicting
predicting
predicting
predicting
satisfaction is satisfaction is satisfaction is satisfaction is
included, but present and
clearly
thoroughly
lacks detail or reasonable.
provided and developed
is incomplete.
wellwith
developed.
supporting
details.
10.0 %Prediction The
The
The
The
The
Equation and
development development development development development
Coefficient of
and
and
and
and
and
Determination
interpretation interpretation interpretation interpretation interpretation
(Benchmark
prediction
prediction
prediction
prediction
prediction
Competency 2.3) equation and equation and equation and equation and equation and
coefficient of coefficient of coefficient of coefficient of coefficient of
determination determination determination determination determination
are not
are included, are
are wellare thoroughly
included or are but lack detail appropriate. developed.
developed
incomprehensi or are
with
ble.
incomplete.
supporting
details.
15.0 %Areas of Recommended Recommended Recommended Recommended Recommended
Focus to Improve areas of focus areas of focus areas of focus areas of focus areas of focus
Customer
to improve
to improve
to improve
to improve
to improve
Satisfaction
customer
customer
customer
customer
customer
satisfaction are satisfaction are satisfaction are satisfaction are satisfaction are
not included or included, but adequate.
appropriate
rational and
are illogical. lack detail or
and wellthoroughly
are
developed.
developed
incomplete.
with
supporting
details.
15.0
The
The
The
The
The
%Recommended recommended recommended recommended recommended recommended
Method and
method to yield method to
method to
method to
method to
December
the lowest error yield the
yield the
yield the
yield the
Forecast
and the
lowest error lowest error lowest error lowest error
December
and the
and the
and the
and the
forecast are not December
December
December
December
included or are forecast are
forecast are
forecast are
forecast are
incomprehensi included, but relevant.
appropriate. purposeful and
ble.
lack detail or
clever.
are
incomplete.
15.0
The
The
The
The
The
%Recommendati recommendatio recommendati recommendati recommendati recommendati
ons of Staff for ns of staff for ons of staff for ons of staff for ons of staff for ons of staff for
Each Shift
each shift to
each shift to each shift to each shift to each shift to
accommodate accommodate accommodate accommodate accommodate
the minimum the minimum the minimum the minimum the minimum
requirements requirements requirements requirements requirements
for customer for customer for customer for customer for customer
service are not service are
service are
service are
service are
included.
illogical or
fairly
appropriate. smartly
incomplete.
reasonable.
chosen.
5.0 %Excel
The Excel
The Excel
The Excel
The Excel
The Excel
Spreadsheet
spreadsheet is spreadsheet is spreadsheet is spreadsheet is spreadsheet is
not included. incomplete.
understandable accurate.
accurate and
.
appropriately
organized.
20.0
%Organization
and Effectiveness
7.0 %Thesis
Paper lacks any Thesis is
Thesis is
Thesis is clear Thesis is
Development and discernible
insufficiently apparent and and forecasts comprehensive
Purpose
overall purpose developed or appropriate to the
and contains
or organizing vague. Purpose purpose.
development the essence of
claim.
is not clear.
of the paper. the paper.
Thesis is
Thesis
descriptive and statement
reflective of makes the
the arguments purpose of the
and
paper clear.
appropriate to
the purpose.
8.0 %Argument Statement of Sufficient
Argument is Argument
Clear and
Logic and
purpose is not justification of orderly, but
shows logical convincing
Construction
justified by the claims is
may have a
progressions. argument that
conclusion.
lacking.
few
Techniques of presents a
The conclusion Argument
inconsistencies argumentation persuasive
does not
lacks
. The argument are evident.
claim in a
support the
consistent
presents
There is a
distinctive and
claim made.
unity. There minimal
smooth
compelling
Argument is are obvious
justification of progression of manner. All
incoherent and
uses
noncredible
sources.
flaws in the
logic. Some
sources have
questionable
credibility.
claims.
claims from sources are
Argument
introduction to authoritative.
logically, but conclusion.
not
Most sources
thoroughly,
are
supports the authoritative.
purpose.
Sources used
are credible.
Introduction
and conclusion
bracket the
thesis.
5.0 %Mechanics Surface errors Frequent and Some
Prose is
Writer is
of Writing
are pervasive repetitive
mechanical
largely free of clearly in
(includes spelling, enough that
mechanical
errors or typos mechanical
command of
punctuation,
they impede
errors distract are present,
errors,
standard,
grammar,
communication the reader.
but they are although a few written,
language use)
of meaning.
Inconsistencies not overly
may be
academic
Inappropriate in language
distracting to present. The English.
word choice or choice
the reader.
writer uses a
sentence
(register) or Correct and variety of
construction is word choice varied
effective
used.
are present.
sentence
sentence
Sentence
structure and structures and
structure is
audiencefigures of
correct but not appropriate
speech.
varied.
language are
employed.
10.0 %Format
5.0 %Paper
Template is not Appropriate Appropriate Appropriate All format
Format (use of used
template is
template is
template is
elements are
appropriate style appropriately used, but some used.
fully used.
correct.
for the major and or
elements are Formatting is There are
assignment)
documentation missing or
correct,
virtually no
format is rarely mistaken. A although some errors in
followed
lack of control minor errors formatting
correctly.
with
may be
style.
formatting is present.
apparent.
5.0
Sources are not Documentatio Sources are
Sources are
Sources are
%Documentation documented. n of sources is documented, documented, completely
of Sources
inconsistent or as appropriate as appropriate and correctly
(citations,
incorrect, as to assignment to assignment documented,
footnotes,
appropriate to and style,
and style, and as appropriate
references,
assignment
although some
to assignment
bibliography,
etc., as
appropriate to
assignment and
style)
100 %Total
Weightage
and style, with formatting
format is
and style, and
numerous
errors may be mostly correct. format is free
formatting
present.
of error.
errors.