Entry Level Excel

Case 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%

 

  1. Formulate and solve a linear programming model for this problem on a spreadsheet.
  2. Formulate this same model algebraically.

 

  1. Use the graphical method by hand to solve this model.

 

 

 

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?

  1. Formulate and solve a linear programming model for this problem in a spreadsheet.
   

Milk

(gallons)

 

Cheese

 

Apples

Minimum

Daily

Requirement

Niacin (mg) 3.2 4.9 0.8 13.0
Thiamin (mg) 1.12 1.3 0.19 1.5
Vitamin C (mg) 32.0 0.0 93.0 45.0

 

Cost ($) 2.50 0.10 0.22  

 

  1. Formulate this same model algebraically.

 

 

 

 

 

 

 

 

 

 

 

 

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

Management

Science

 

Finance

Operations

Management

 

Marketing

George 60 10 10 20
Fred 20 20 40 20
Ann 45 45 5 5
Eric 50 20 5 25
Susan 30 30 30 10
Liz 50 50 0 0
Ed 70 20 10 0
David 25 25 35 15
Tony 35 15 35 15
Jennifer 60 10 10 20

 

  1. 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.
  2. Does the resulting solution seem like a fair assignment?
  3. Which alternative objectives might lead to a fairer assignment?
Powered by WordPress