Instant Solution ? Click "Buy button" to Download the solution File
Only need help with question 3, would really appreciate the help.
Please You must follow the formatting instructions described in the file
1. Ten jobs are to be completed by three workers (Sam, Joy, and Kelly) during the next week.
Each person works up to 40 hours per week and is paid an hourly rate: Sam, Joy, and Kelly
earn $11, $13, and $14 per hour, respectively. Union rules require workers to be treated fairly;
to ensure that most of the work is not assigned to one person while others are too idle, make
sure that no person works more than 8 hours above any other worker. For example, if Joy works
20 hours, then Sam and Kelly should work within 12 to 28 hours, and Sam and Kelly should
have work-hours that are no more than 8 hours apart.
The times for the workers to complete the tasks are shown in the table below. The values in the
cells assume that each task is completed by a single person. However, tasks can be shared with
completion times being determined proportionally (e.g., if Joy and Kelly share task 1 equally,
then Joy works 6 hours and Kelly works 9 hours). If no entry exists in a particular cell, it means
that task cannot be performed by that worker.
a. Formulate the problem as a linear optimization model (that is, define the variables, and
write down the objective function and all constraints mathematically).
b. Create a spreadsheet model for this problem and solve with Excel Solver.
c. What is the optimal solution? What is the optimal value?
2. The director of a computer needs to schedule the staffing of the center. It is open from 8am
until midnight. Larry has monitored the usage of the center at various times of the day and
determined that the following number of computer consultants are required:
Time of Day
8am ? Noon
Noon ? 4pm
4pm ? 8pm
8pm - Midnight
Minimum Number of Consultants
Required to be on Duty
Two types of computer consultants can be hired: full-time and part-time. The full-time
consultants work for eight consecutive hours in any of the following shifts: morning (8am ?
4pm), afternoon (noon-8pm), and evening (4pm-midnight). Full-time consultants are paid $14
per hour. Part-time consultants can be hired to work any of the four shifts listed in the table.
Part-time consultants are paid $12 per hour.
An additional requirement is that during every time period, there must be at least two full-time
consultants on duty for every part-time consultant on duty. Larry would like to determine how
many full-time and part-time consultants should work each shift to meet the above requirements
at the minimum possible cost.
a. Formulate the problem algebraically (i.e., define the variables, objective and constraints
b. Implement your model in Excel and solve it.
c. What is the optimal solution? What is the optimal value?
3. Diana Dazzle, programming manager for GDUB-TV, must plan her schedule of television
shows for next Wednesday evening. Of the nine one-half hour shows listed below, she must
select exactly five for Wednesday evening. The estimated revenue (in $ million) is given below
for each show. Furthermore, the F.C.C. has classified each show as falling into one or more of
the categories of Public Interest, Violence, Comedy, and Drama as given in the table below.
Law and Order
Dharma and Greg
News Special:Middle East
Focus on Science
Urban Action for Education
Diana's task is to select exactly five shows in order to maximize revenue. However, she must
be mindful of the following limitations and issues:
? There must be at least as many shows aired that are categorized as drama as there are shows
aired that are categorized as comedy.
? She cannot air more than two of the shows "News Special:Middle East", "Focus on
Science" and "Urban Action for Education" (too serious!).
? If Diana airs "Focus on Science", she must air either "Spiderman" or "Law and Order" but
? If she airs four or more shows in the violence category, she pays F.C.C. a fine of $5 million,
which will be deduced from the revenue.
Formulate Diana Dazzle's selection problem as a binary optimization problem. Clearly define
your decision variables, constraints, and objective function. Use only linear constraints and a
linear objective function. You do not have to solve the model in Excel.
4. A shoe manufacturer produces three types of footwear: dress shoes, short boots, and high boots.
To maximize its short-term profits, the company solves the following linear program:
Let D = # of dress shoes to produce,
S = # of short boots to produce, and
H = # of high boots to produce.
max 20 + 30 + 40
s.t. 25 + 60 + 140 ? 5000 (leather)
5 + 5 + 10 ? 800 (rubber)
, , ? 0
Notice that there are two resources which are limited:
? Leather. A total of 5000 square inches of leather are available. Each pair of dress shoes,
short boots, and high boots uses 25, 60, and 140 square inches respectively.
? Rubber. A total of 800 cubic inches are available. Each pair of dress shoes, short boots,
and high boots uses 5, 5, and 10 cubic inches respectively.
Profits are $20 per pair of dress shoes, $30 per pair of short boots, and $40 per pair of high
boots. Given the following data in an Excel spreadsheet, please answer the following questions.
a. To model the shoe manufacturer?s optimization problem as a linear program in an Excel
spreadsheet, what Excel formulas would be placed in the following cells (write ?blank? if no
formula should be used in that cell):
b. Fill in the following Excel Solver window so that it looks like what you would expect to see
if you were solving this linear program on your computer.
The company generates a Sensitivity Report, which is displayed below.
Production Qty Dress
Production Qty Short
Production Qty High Boots
Leather (square inches)
Rubber (cubic inches)
c. Are there any resources remaining after production? If so, what are they and how much
remains of each? Explain how you arrived at this conclusion.
d. Would the company be willing to purchase extra rubber? If so, what is the highest price the
company would be willing to pay per cubic inch, and how much more rubber would the
company be willing to purchase? Explain how you arrived at this conclusion.
e. From the Sensitivity Report, we can see that high boots are not profitable enough to be
produced. How high would the profit of a pair of high boots need to be in order to get
produced? Explain how you arrived at this conclusion.
f. Assume the profit of dress shoes drops from $20 to $15. How many of each product does the
company produce? What is the company?s total profit in this case? Explain how you arrived
at this conclusion.
g. Consider that the values of the parameters remain all unchanged at the exception of the profit
per dress (set equal to $20 above). For which value(s) of the profit per dress, can you obtain
multiple optimal solutions? Explain how you arrived at this conclusion.
Paper#9208984 | Written in 27-Jul-2016Price : $22