Entry Level Excel Using

PROJECT 2Case Studies
Independent Project
Please answer the following questions. Share screenshots from your work on Excel and/or graphs
or other work and email the Excel files as well. This must be your own work only. This is due
on December 6th by 11:59 pm EST.
Conducting an Apple focus group survey
The marketing group for Apple plans to conduct a telephone survey to determine consumer
attitudes toward a new cell phone that is currently under development. In order to have a
sufficient sample size to conduct the analysis, they need to contact at least 300 young males
(under age 40), 140 older males (over age 40), 200 young females (under age 40), and 250 older
females (over age 40). It costs $2.4 to make a daytime phone call and $3.20 to make an evening
phone call (due to higher labor costs). This cost is incurred whether or not anyone answers the
phone. The table below shows the likelihood of a given customer type answering each phone
call. Assume the survey is conducted with whoever first answers the phone. Also, because of
limited evening staffing, at most one-third of phone calls placed can be evening phone calls.
How should the marketing group conduct the telephone survey so as to meet the sample size
requirements at the lowest possible cost?
Who Answers?
Daytime Calls
Evening Calls
Young Male
10%
20%
Older Male
15%
30%
Young Female
20%
20%
Older Female
35%
25%
a. Formulate and solve a linear programming model for this problem on a spreadsheet.
b. Formulate this same model algebraically.
c. Use the graphical method by hand to solve this model.
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior
written consent of McGraw-Hill Education
1
Class Diet Problem
The cafeteria manager for Baruch High School is trying to decide what to feed its students. She
would like to offer some combination of milk, cheese, and apples. The goal is to minimize cost,
subject to meeting the minimum nutritional requirements imposed by law. The cost and
nutritional content of each food, along with the minimum nutritional requirements, are shown
below. What diet should be fed to each student?
a. Formulate and solve a linear programming model for this problem in a spreadsheet.
Niacin (mg)
Thiamin (mg)
Vitamin C (mg)
Cost ($)
Milk
(gallons)
3.2
1.12
32.0
Cheese
Apples
4.9
1.3
0.0
0.8
0.19
93.0
2.50
0.10
0.22
Minimum
Daily
Requirement
13.0
1.5
45.0
b. Formulate this same model algebraically.
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior
written consent of McGraw-Hill Education
2
Bidding for Classes
In the graduate program at Baruch, students bid for electives in the second year of their
program. Each student has 100 points to bid (total) and must take two electives. There are four
electives available: Management Science, Finance, Operations Management, and Marketing.
Each class is limited to 5 students. The bids submitted for each of the 10 students are shown in
the table below.
Student
George
Fred
Ann
Eric
Susan
Liz
Ed
David
Tony
Jennifer
Management
Science
60
20
45
50
30
50
70
25
35
60
Finance
10
20
45
20
30
50
20
25
15
10
Operations
Management
10
40
5
5
30
0
10
35
35
10
Marketing
20
20
5
25
10
0
0
15
15
20
a. Formulate and solve a spreadsheet model to determine an assignment of students to classes so as
to maximize the total bid points of the assignments.
b. Does the resulting solution seem like a fair assignment?
c. Which alternative objectives might lead to a fairer assignment?
Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior
written consent of McGraw-Hill Education
3