Assignment
You may need to make assumptions for some of the problems. You will not lose points as long as you state these assumptions, and your constraints are logical -according to your assumptions. YOUR MODELS MUST BE LINEAR!! THIS MEANS NO IF FUNCTIONS.
1. Solve problems 1-8 from homework 1 using excel solver.
2. 1. Barney Stinson owns a spa exclusive to premium members only. He provides two types of services: standard package, and silver package. Each of these packages involve manicures, massages and relaxation sessions. The silver package includes 1 hour in massages, 30 minutes in manicure and pedicure and 30 minutes in relaxation. The standard package includes 30 minutes in massages, 45 minutes in manicure and pedicure, and 1 hour in relaxation.
He employs manicurists that can work a total of 40 hours a week, masseuses with a total of 40 hours a week, and relaxation instructors with 60 hours a week. Assume all standard packages make a profit of $400, and all silver packages make a profit of $500. (The final values on the table show the optimal number of packages he schedules.)
Answer the following questions using the sensitivity report below: (Do not rerun!)
|
Microsoft Excel Sensitivity Report (Obtained Using Standard LP/Quadratic Engine)
|
|
|
|
|
|
|
|
|
|
|
|
Adjustable Cells
|
|
|
|
|
|
|
|
|
|
Final
|
Reduced
|
Objective
|
Allowable
|
Allowable
|
|
|
Cell
|
Name
|
Value
|
Cost
|
Coefficient
|
Increase
|
Decrease
|
|
|
$C$5
|
Silver Package
|
20
|
0
|
500
|
300
|
233.3333
|
|
|
$D$5
|
Standard Package
|
40
|
0
|
400
|
350
|
150
|
|
|
|
|
|
|
|
|
|
|
Constraints
|
|
|
|
|
|
|
|
|
|
Final
|
Shadow
|
Constraint
|
Allowable
|
Allowable
|
|
|
Cell
|
Name
|
Value
|
Price
|
R.H. Side
|
Increase
|
Decrease
|
|
|
$E$9
|
Massage Used
|
40
|
350
|
40
|
40
|
13.33333
|
|
|
$E$10
|
Manicure Used
|
40
|
300
|
40
|
6.666667
|
20
|
|
|
$E$11
|
Relaxation Used
|
50
|
0
|
60
|
1E+30
|
10
|
|
|
|
|
|
|
|
|
|
a. Are there alternate solutions? How can you tell?
b. If the profit on the silver package increases to $700, how would the objective function change?
c. If the profit on the standard package decreased by 200, how would the optimal solution change?
d. If Barney could hire another masseuse who would work 20 hours a week for an extra $400(cost) a week instead of one of his current masseuses who works 10 hours a week, should he do it?
e. Suppose somebody makes an offer to hire one of Barney's relaxation capacity for 10 hours a week for $200. Should he accept the offer?
f. Barney would like to introduce a new Gold package for $800, which includes 1.5 hours of massage, 45 minutes in manicure and pedicure, and 2 hours in relaxation.
Should he consider it?
g. Let's say Barney would like to reduce the manicure time for the standard package to 30 minutes, and reduce the price, which makes the profit $350 instead of $400. Should he consider scheduling more standard packages?
h. What is a binding constraint? Which ones are binding? Why are binding constraints important?
i. Suppose the profit on both of the packages decreases by $50. How would the optimal solution change?
j. Suppose the profit on standard decreases by $25, what is the maximum profit that can be earned on the silver packages without changing the optimal solution?
3. Health Care Systems of Florida (HCFS) is planning to build a number of new emergency care clinics in central Florida. HCSF management has divided a map of the area to seven regions. They want to locate the emergency centers so that all seven regions will be conveniently served by at least one facility. Five possible sites are available for constructing the new facilities. The regions that can be served conveniently by each site are indicated by 1 in the following table:
|
Region
|
Sanford
|
Altamonte
|
Apopka
|
Casselberry
|
Maitland
|
|
1
|
1
|
0
|
1
|
0
|
0
|
|
2
|
1
|
1
|
0
|
1
|
1
|
|
3
|
0
|
1
|
0
|
1
|
0
|
|
4
|
0
|
0
|
1
|
0
|
1
|
|
5
|
1
|
1
|
0
|
0
|
0
|
|
6
|
0
|
0
|
1
|
0
|
1
|
|
7
|
0
|
0
|
0
|
1
|
1
|
|
Cost($000s)
|
$450
|
$650
|
$550
|
$500
|
$525
|
a. Formulate an ILP problem to determine which sites should be selected in order to provide convenient service to all locations in the least costly manner.
b. Implement your model in a spreadsheet and solve it.
4. A manufacturer is considering alternatives for building new plants in order to be located closer to three of its primary customers with whom he intends to develop long-term relationships. The net cost of manufacturing and transporting each unit of the products to its customers will vary depending on where the plant is built and the production capacity of the plant. These costs are summarized in the following table:
|
|
Net Cost per unit to Supply Customer
|
|
|
X
|
Y
|
Z
|
|
1
|
35
|
35
|
45
|
|
2
|
45
|
30
|
45
|
|
3
|
70
|
60
|
50
|
|
4
|
20
|
45
|
25
|
|
5
|
65
|
45
|
45
|
The annual demand for products from customers X, Y, and Z is expected to be 40,000; 25,000; and 35,000 units; respectively. The annual production capacity and construction costs for each plant are given in the following table:
|
Plant
|
Production Capacity
|
Construction Cost($1000)
|
|
1
|
40,000
|
$1,325
|
|
2
|
30,000
|
$1,100
|
|
3
|
50,000
|
$1,500
|
|
4
|
20,000
|
$1,200
|
|
5
|
40,000
|
$1,400
|
a. Formulate an ILP problem that minimizes the sum of manufacturing, transportation and construction costs.
b. Solve using excel solver.
5. Model the following situations. Assume integer variables to be Xi and binary variables to be Yi.
a. A company wants to select no more than 2 projects from a set of 4 possible projects. (3 pts)
b. Consider a quantity discount model where the company gets $100 per unit profit if it produces less than 150, and $200 per unit profit if it produces more than 150.
c. What is the value of M in the following example?
X2 < M Y2
X1 + 10X2 + X3 < 48
5X1 + 3X2 + 12X3 < 25
X1 + X2 + 4X3 < 12