BUS660 GCU Data Evaluation Case Study For Evaluation 1 in this assignment, please use the attached data set rather than the one in the syllabus version of

BUS660 GCU Data Evaluation Case Study For Evaluation 1 in this assignment, please use the attached data set rather than the one in the syllabus version of the assignment. For Evaluations 2 and 3, please make sure not to use the templates from the syllabus. I want you to do your own work rather than relying on templates. Those templates will not help you on this revised assignment anyhow. For all three Evaluations, you may use whatever method you wish to compute them, up to and including Excel QM and Excel Data Analysis. There is no word minimum but it is imperative that you explain each of your answers succinctly and completely as you are acting as a consultant for the restaurant 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 attached 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? (DO NOT use the data accompanying the
assignment in your syllabus)
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
© 2019. Grand Canyon University. All Rights Reserved.
November
December
935
?
As you don’t have enough data to determine trend or seasonality, you have to assume that the
variations in the time series are due to random variations. You believe that a smoothing model
would be appropriate to forecast December volume. You’re not sure which is the most accurate
model to use. You’re considering a 3-month moving average, a 3-month weighted moving
average in which the most recent month is weighted double, or exponential smoothing with a
smoothing constant of 0.6 and an initial forecast of 650. Therefore, you want to run all three
models, predicting December volume for each and also determining which of the methods would
have been most accurate over the past year. (DO NOT use the template accompanying the
assignment in your syllabus)
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 3-hour periods. The table below shows the minimum number of
workers needed during those 3-hour periods.
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 at all times to ensure that there is sufficient
coverage. The workers must work a 6-hour shift and cannot start in the middle of a 3-hour
period. 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. (DO NOT use the template accompanying the assignment in
your syllabus)
2
Driving Distance to Restaurant
Dine In (1)/Take Out (2)
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
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
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
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
8
6
8
2
1
1
22
23
20
4
4
5
Satisfaction with Food
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
5
5
5
4
4
5
8
Linear
Programming
Applications
To accompany
Quantitative Analysis for Management, Twelfth Edition,
by Render, Stair, Hanna and Hale
Power Point slides created by Jeff Heyl
Copyright ©2015 Pearson Education, Inc.
LEARNING OBJECTIVES
After completing this chapter, students will be able to:
1. Model a wide variety of medium to large LP
problems.
2. Understand major application areas, including
marketing, production, labor scheduling, fuel
blending, transportation, and finance.
3. Gain experience in solving LP problems with Excel
Solver software.
Copyright ©2015 Pearson Education, Inc.
8–2
Introduction
• The graphical method of LP is useful for
understanding how to formulate and solve
small LP problems
• Many types of problems can be solved using
LP
• Principles developed here are applicable to
larger problems
Copyright ©2015 Pearson Education, Inc.
8–3
Marketing Applications
• Linear programming models have been used
in the advertising field as a decision aid in
selecting an effective media mix
• Media selection LP problems can be
approached from two perspectives
– Maximize audience exposure
– Minimize advertising costs
Copyright ©2015 Pearson Education, Inc.
8–4
Win Big Gambling Club
• Club promotes gambling junkets to the
Bahamas
– $8,000 per week to spend on advertising
– Goal is to reach the largest possible high-potential
audience
– Media types and audience figures shown below
– Place at least five radio spots per week
– No more than $1,800 can be spent on radio
advertising each week
Copyright ©2015 Pearson Education, Inc.
8–5
Win Big Gambling Club
• Advertising options
AUDIENCE
REACHED PER AD
COST PER
AD ($)
MAXIMUM ADS
PER WEEK
TV spot (1 minute)
5,000
800
12
Daily newspaper (fullpage ad)
8,500
925
5
Radio spot (30 seconds,
prime time)
2,400
290
25
Radio spot (1 minute,
afternoon)
2,800
380
20
MEDIUM
Copyright ©2015 Pearson Education, Inc.
8–6
Win Big Gambling Club
• Problem formulation
X1 = number of 1-minute TV spots taken each week
X2 = number of daily newspaper ads taken each week
X3 = number of 30-second prime-time radio spots taken each week
X4 = number of 1-minute afternoon radio spots taken each week
Objective:
Maximize audience coverage = 5,000X1 + 8,500X2 + 2,400X3 + 2,800X4
Subject to
X1 ≤ 12
(max TV spots/wk)
X2 ≤ 5
(max newspaper ads/wk)
X3 ≤ 25
(max 30-sec radio spots/wk)
X4 ≤ 20
(max 1-min radio spots/wk)
800X1 + 925X2 + 290X3 + 380X4 ≤ $8,000 (weekly advertising budget)
X3 + X4 ≥ 5
(min radio spots contracted)
290X3 + 380X4 ≤ $1,800 (max dollars spent on radio)
X1, X2, X3, X4 ≥ 0
Copyright ©2015 Pearson Education, Inc.
8–7
Solution
Win Big Gambling
Club
X1 = 1.97 TV spots
• Problem formulation
X2 = 5
newspaper ads
X1 = number of 1-minute TV spots
week
X3 =taken
6.2 each
30-second
radio spots
X2 = number of daily newspaper
week radio spots
X4 ads
= 0taken each
1-minute
X3 = number of 30-second prime-time radio spots taken each week
X4 = number of 1-minute afternoon radio spots taken each week
Objective:
Maximize audience coverage = 5,000X1 + 8,500X2 + 2,400X3 + 2,800X4
Subject to
X1 ≤ 12
(max TV spots/wk)
X2 ≤ 5
(max newspaper ads/wk)
X3 ≤ 25
(max 30-sec radio spots/wk)
X4 ≤ 20
(max 1-min radio spots/wk)
800X1 + 925X2 + 290X3 + 380X4 ≤ $8,000 (weekly advertising budget)
X3 + X4 ≥ 5
(min radio spots contracted)
290X3 + 380X4 ≤ $1,800 (max dollars spent on radio)
X1, X2, X3, X4 ≥ 0
Copyright ©2015 Pearson Education, Inc.
8–8
Solution in Excel 2013
PROGRAM 8.1 – Win Big Solution
Copyright ©2015 Pearson Education, Inc.
8–9
Management Sciences
Association
• MSA is a marketing research firm
• Several requirements for a statistical validity
1. Survey at least 2,300 U.S. households
2. Survey at least 1,000 households whose heads
are ≤ 30 years old
3. Survey at least 600 households whose heads are
between 31 and 50
4. Ensure that at least 15% of those surveyed live in
a state that borders Mexico
5. Ensure that no more than 20% of those surveyed
who are 51 years of age or over live in a state
that borders Mexico
Copyright ©2015 Pearson Education, Inc.
8 – 10
Management Sciences
Association
• MSA decides to conduct all surveys in person
• Estimates of the costs of reaching people in
each age and region category
• Goal is to meet the sampling requirements at
the least possible cost
COST PER PERSON SURVEYED ($)
AGE ≤ 30
AGE 31-50
AGE ≥ 51
State bordering Mexico
$7.50
$6.80
$5.50
State not bordering Mexico
$6.90
$7.25
$6.10
REGION
Copyright ©2015 Pearson Education, Inc.
8 – 11
Management Sciences
Association
• Decision variables
X1 = number of 30 or younger and in a border state
X2 = number of 31-50 and in a border state
X3 = number 51 or older and in a border state
X4 = number 30 or younger and not in a border state
X5 = number of 31-50 and not in a border state
X6 = number 51 or older and not in a border state
Copyright ©2015 Pearson Education, Inc.
8 – 12
Management Sciences
Association
Objective function
Minimize total
interview costs = $7.50X1 + $6.80X2 + $5.50X3
+ $6.90X4 + $7.25X5 + $6.10X6
subject to
X1 + X2 + X3 + X4 + X5 + X6 ≥ 2,300 (total households)
X1 +
X4
≥ 1,000 (households 30 or younger)
X2 +
X5
≥ 600 (households 31-50)
X1 + X2 + X3
≥ 0.15(X1 + X2+ X3 + X4 + X5 + X6)
(border states)
X3
≤ 0.20(X3 + X6) (limit on age group
51+ who can live
in border state)
X1, X2, X3, X4, X5, X6 ≥ 0
Copyright ©2015 Pearson Education, Inc.
8 – 13
Management Sciences
Association
• Optimal solution will cost $15,166
REGION
State bordering Mexico
State not bordering Mexico
Copyright ©2015 Pearson Education, Inc.
AGE ≤ 30
AGE 31-50
AGE ≥ 51
0
600
140
1,000
0
560
8 – 14
Solution in Excel 2013
PROGRAM 8.2 – MSA Solution
Copyright ©2015 Pearson Education, Inc.
8 – 15
Manufacturing Applications
• Production Mix
– LP can be used to plan the optimal mix of products
to manufacture
– Company must meet a myriad of constraints




Financial concerns
Sales demand
Material contracts
Union labor demands
– Primary goal is to generate the largest profit
possible
Copyright ©2015 Pearson Education, Inc.
8 – 16
Fifth Avenue Industries
• Produces four varieties of ties
– Expensive all-silk
– All-polyester
– Two are polyester-cotton or silk-cotton blends
• Cost and availability of the three materials
used in the production process
MATERIAL
Silk
Polyester
Cotton
COST PER YARD ($)
24
6
9
Copyright ©2015 Pearson Education, Inc.
MATERIAL AVAILABLE PER
MONTH (YARDS)
1,200
3,000
1,600
8 – 17
Fifth Avenue Industries
• The firm has contracts with several major
department store chains
– Contracts require a minimum number of ties
– May be increased if demand increases
• Goal is to maximize monthly profit
• Decision variables
X1 = number of all-silk ties produced per month
X2 = number all-polyester ties
X3 = number of blend 1 polyester-cotton ties
X4 = number of blend 2 silk-cotton ties
Copyright ©2015 Pearson Education, Inc.
8 – 18
Fifth Avenue Industries
TABLE 8.1 – Data for Fifth Avenue
VARIETY OF
TIE
SELLING
PRICE PER
TIE ($)
All silk
19.24
5,000
7,000
0.125
100% silk
All polyester
8.70
10,000
14,000
0.08
100% polyester
Poly-cotton
blend 1
9.52
13,000
16,000
0.10
50% polyester –
50% cotton
Silk-cotton
blend 2
10.64
5,000
8,500
0.11
60% silk – 40%
cotton
Copyright ©2015 Pearson Education, Inc.
MONTHLY
CONTRACT
MINIMUM
MATERIAL
REQUIRED
PER TIE
(YARDS)
MONTHLY
DEMAND
MATERIAL
REQUIREMENTS
8 – 19
Fifth Avenue Industries
• Establish profit per tie
SILK
REQ’D
COST
POLYESTER
REQ’D
COST
COTTON
REQ’D
COST
MATERIAL
COST
SELLING
PRICE
PROFIT
$3.00
$19.24
$16.24
$0.48
$8.70
$8.22
All-silk X1
0.125
$24.00
All-polyester X2
0.08
$6
0.05
$6
Poly-cotton blend X3
0.05
$9
$0.75
$9.52
$8.77
0.044
$9
$1.98
$10.64
$8.66
Silk-cotton blend X4
0.066
$24.00
Copyright ©2015 Pearson Education, Inc.
8 – 20
Fifth Avenue Industries
Objective function
Maximize profit = $16.24X1 + $8.22X2 + $8.77X3 + $8.66X4
Subject to
0.125X1+ 0.066X4 ≤ 1200 (yds of silk)
0.08X2 + 0.05X3 ≤ 3,000 (yds of polyester)
0.05X3 + 0.44X4 ≤ 1,600 (yds of cotton)
X1 ≥ 5,000 (contract min for silk)
X1 ≤ 7,000 (contract min)
X2 ≥ 10,000 (contract min for all polyester)
X2 ≤ 14,000 (contract max)
X3 ≥ 13,000 (contract min for blend 1)
X3 ≤ 16,000 (contract max)
X4 ≥ 5,000 (contract min for blend 2)
X4 ≤ 8,500 (contract max)
X1, X2, X3, X4 ≥
0
Copyright ©2015 Pearson Education, Inc.
8 – 21
Fifth Avenue Industries
• Optimal solution will result in a profit of
$412,028 per month
TIE
All-Silk
All-Polyester
Poly-Silk
Silk-Cotton
Copyright ©2015 Pearson Education, Inc.
QUANTITY PER
MONTH
5,112
14,000
16,000
8,500
8 – 22
Solution in Excel 2013
PROGRAM 8.3 – Fifth Avenue Solution
Copyright ©2015 Pearson Education, Inc.
8 – 23
Employee Scheduling
Applications
• Labor Planning
– Address staffing needs over a particular time
– Especially useful when there is some flexibility in
assigning workers that require overlapping or
interchangeable talents
Copyright ©2015 Pearson Education, Inc.
8 – 24
Hong Kong Bank
• Hong Kong Bank of Commerce and Industry
requires between 10 and 18 tellers depending
on the time of day
• The bank wants a schedule that will minimize
total personnel costs
– Lunch time from noon to 2 pm is generally the
busiest
– Bank employs 12 full-time tellers, many part-time
workers
Copyright ©2015 Pearson Education, Inc.
8 – 25
Hong Kong Bank
– Part-time workers must put in exactly four hours
per day, can start anytime between 9 am and 1
pm, and are inexpensive
– Full-time workers work from 9 am to 5 pm and
have 1 hour for lunch, half from 11 am to 12 pm,
the other half from 12 pm to 1 pm
– Part-time hours are limited to a maximum of 50%
of the day’s total requirements
– Part-timers earn $8 per hour on average
– Full-timers earn $100 per day on average
– It will release one or more of its full-time tellers if it
is profitable to do so
Copyright ©2015 Pearson Education, Inc.
8 – 26
Hong Kong Bank
• Labor requirements
TABLE 8.4
TIME PERIOD
NUMBER OF TELLERS REQUIRED
9 am – 10 am
10
10 am – 11 am
12
11 am – Noon
14
Noon – 1 pm
16
1 pm – 2 pm
18
2 pm – 3 pm
17
3 pm – 4 pm
15
4 pm – 5 pm
10
Copyright ©2015 Pearson Education, Inc.
8 – 27
Hong Kong Bank
• Variables
F
P1
P2
P3
P4
P5
= full-time tellers
= part-timers starting at 9 am (leaving at 1 pm)
= part-timers starting at 10 am (leaving at 2 pm)
= part-timers starting at 11 am (leaving at 3 pm)
= part-timers starting at noon (leaving at 4 pm)
= part-timers starting at 1 pm (leaving at 5 pm)
• Objective
Minimize total daily
personnel cost
Copyright ©2015 Pearson Education, Inc.
= $100F + $32(P1 + P2 + P3 + P4 + P5)
8 – 28
Hong Kong Bank
• Constraints
F + P1

10
(9 am – 10 am needs)

12
(10 am – 11 am needs)

14
(11 am – noon needs)

16
(noon – 1 pm needs)
F + P1
+ P2
0.5F + P1
+ P2
+ P3
0.5F + P1
+ P2
+ P3
+ P4
+ P2
+ P3
+ P4
+ P5

18
(1 pm – 2 pm needs)
+ P3
+ P4
+ P5

17
(2 pm – 3 pm needs)
+ P4
+ P5

15
(3 pm – 4 pm needs)
+ P5

10
(4 pm – 5 pm needs)

12
(12 full-time tellers)
+ 4P5

0.50(112)
(max 50% part-timers)
F, P1, P2, P3, P4, P5

0
(nonnegativity)
F
F
F
F
F
4P1 + 4P2
+ 4P3
Copyright ©2015 Pearson Education, Inc.
+ 4P4
8 – 29
Solution in Excel 2013
PROGRAM 8.5 – Labor Planning Solution
Copyright ©2015 Pearson Education, Inc.
8 – 30
Hong Kong Bank
• Alternate solutions are possible for this problem
• Each has the same total cost – $1,448/day
SOLUTION 1
SOLUTION 2
Full-Time Tellers
10
10
P1 Tellers
0
6
P2 Tellers
7
1
P3 Tellers
2
2
P4 Tellers
5
5
P5 Tellers
0
0
Copyright ©2015 Pearson Education, Inc.
8 – 31
Financial Applications
• Portfolio Selection
– Bank, investment funds, and insurance companies
often have to select specific investments from a
variety of alternatives
– Overall objective is generally to maximize the
potential return on the investment given a set of
legal, policy, or risk restraints
Copyright ©2015 Pearson Education, Inc.
8 – 32
International City Trust
• International City Trust (ICT) invests in shortterm trade credits, corporate bonds, gold
stocks, and construction loans
• ICT has $5 million to invest and wants to
accomplish two things
– Maximize the return on investment over the next
six months
– Satisfy the diversification requirements set by the
board
• The board of directors has placed limits on
how much can be invested in each area
International City Trust
• Investment possibilities
INTEREST
RETURN
MAXIMUM
INVESTMENT
($ MILLIONs)
Trade credit
7%
1.0
Corporate bonds
11%
2.5
Gold stocks
19%
1.5
Construction loans
15%
1.8
INVESTMENT
Copyright ©2015 Pearson Education, Inc.
8 – 34
International City Trust
• Variables
X1 = dollars invested in trade credit
X2 = dollars invested in corporate bonds
X3 = dollars invested in gold stocks
X4 = dollars invested in construction loans
Copyright ©2015 Pearson Education, Inc.
8 – 35
International City Trust
• The board has also decided that at least 55%
of the funds must be invested in gold stocks
and construction loans and no less than 15%
be invested in trade credit
Copyright ©2015 Pearson Education, Inc.
8 – 36
International City Trust
• Formulation
Maximize
dollars of
interest
earned
subject to:
= 0.07X1 + 0.11X2 + 0.19X3 + 0.15X4
X1
X2
X3
X4
X3 + X4
X1
X1 + X2 + X3 + X4
X1, X2, X3, X4
Copyright ©2015 Pearson Education, Inc.








1,000,000
2,500,000
1,500,000
1,800,000
0.55(X1 + X2 + X3 + X4)
0.15(X1 + X2 + X3 + X4)
5,000,000
0
8 – 37
Solution in Excel 2013
PROGRAM 8.6 – ICT Portfolio Solution
Copyright ©2015 Pearson Education, Inc.
8 – 38
International City Trust
• Optimal solution
– Make the following investments
X1 = $750,000
X2 = $950,000
X3 = $1,500,000
X4 = $1,800,000
– Total interest earned = $712,000
Copyright ©2015 Pearson Education, Inc.
8 – 39
Ingredient Blending Applications
• Diet Problems
– One of the earliest LP applications
– Used to determine the most economical diet for
hospital patients
– This is also known as the feed mix problem
Copyright ©2015 Pearson Education, Inc.
8 – 40
Whole Food Nutrition Center
• Uses three bulk grains to blend a natural cereal
• Advertises that the cereal meets the U.S.
Recommended Daily Allowance (USRDA) for four key
nutrients
• Select the blend that will meet the requirements of a
2-ounce serving of the cereal at the minimum cost
NUTRIENT
Protein
Riboflavin
Phosphorus
Magnesium
Copyright ©2015 Pearson Education, Inc.
USRDA
3 units
2 units
1 unit
0.425 unit
8 …
Purchase answer to see full
attachment

"Order a similar paper and get 100% plagiarism free, professional written paper now!"

Order Now