Problem 1 (10 points)
A cellular phone company wants to locate two new communications towers to cover 4 regions. The company wants to minimize the cost of installing the two towers. The regions that can be covered by each tower site are indicated by a 1 in the following table:
Tower SitesRegion / 1 / 2 / 3 / 4
A / 1 / 1
B / 1 / 1 / 1
C / 1 / 1 / 1
D / 1 / 1
COST ($000s) / 200 / 150 / 190 / 250
a)Word-process the linear programming model below.
Variables
Xi = 1, if the tower site 1 is selected and 0 otherwise.
wherei = 1, 2, 3 and 4
Objective Function
Constraints
b)Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
c)Copy and paste the Answer report below.
d)Write the Optimal Solution below. Note: The Optimal Solutions must include the optimal values for the non-zero variables as well as the objective function.
Problem 2 (10 points)
An investor has $500,000 to invest and wants to maximize the money they will receive at the end of one year. They can invest in condos, apartments and houses. The profit after one year, the cost and the number of units available are shown below.
Variable / Investment / Profit($1,000) / Cost
($1,000) / Number Available
X1 / Condos / 6 / 50 / 10
X2 / Apartments / 12 / 90 / 5
X3 / Houses / 9 / 100 / 7
a)Word-process the linear programming model below.
Variables
X1 = Number of condos purchased
X2 = Number of apartments purchased
X3= Nummber of houses purchased
Objective Function
Constraints
b)Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
c)Copy and paste the Answer report below.
d)Write the Optimal Solution below. Note: The Optimal Solutions should include the optimal values for the non-zero variables as well as the objective function.
Problem 3 (10 points)
Finnish Furniture manufactures tables in facilities located in three cities—Reno, Denver, and Pittsburgh. The tables are then shipped to three retail stores located in Phoenix, Cleveland, and Chicago. Management wishes to develop a distribution schedule that will meet the demands at the lowest possible cost. The shipping cost per unit from each of the sources to each of the destinations is shown in the following table:
FROM / PHOENIX / CLEVELAND / CHICAGORENO / 10 / 16 / 19
DENVER / 12 / 14 / 13
PITTSBURGH / 18 / 12 / 12
The available supplies are 120 units from Reno, 200 from Denver, and 160 from Pittsburgh. Phoenix has a demand of 140 units, Cleveland has a demand 'of 160 units, and Chicago has a demand of 180 units. How many units should be shipped from each manufacturing facility to each of the retail stores if cost is to be minimized? What is the total cost?
a)Word-process the linear programming model below.
Variables
Xij = Number of tables shipped from Plant in City i to Retail Store in City j,
wherei = 1, 2, 3 and j = 1, 2, 3
Objective Function
Constraints
b)Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
c)Copy and paste the Answer report below.
e)Write the Optimal Solution below. Note: The Optimal Solutions should include the optimal values for the non-zero variables as well as the objective function.
Problem 4 (10 points)
A company needs to ship 100 units from Seattle to Denver at the lowest possible cost. The costs associated with shipping between the cities are:
ToFrom / Portland / Spokane / Salt Lake City / Denver
Seattle / 100 / 500 / 600 / -
Portland / - / 350 / 300 / -
Spokane / - / - / 250 / 200
Salt Lake City / - / - / - / 200
a)Use the following nodes to complete the diagram for this problem: Node 1: Seattle, Node 2: Portland, Node 3: Spokane, Node 4: Salt Lake City, and Node 5: Denver.
b)Word-process the linear programming model below. Note: This is a shortest route problem. It can be formulated assuming one unit or 100 units.
Variables
Xij = Flow from City i to City j,
wherei = 1, 2, 3, 4 and j = 2, 3, 4, 5
Objective Function
Constraints
c)Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
d)Copy and paste the Answer report below.
e)Write the Optimal Solution below. Include the optimal shortest route and the value of the objective function.
Problem 5 (10 points)
A railroad needs to move the maximum amount of material through its rail network. The numbers given on the arcs are capacities (the maximum amount that can flow along those arcs). Formulate the LP model to determine this maximum possible material that can be moved through the network based on the following network diagram.
a)Word-process the linear programming model below.
Variables
Xij = Flow from Node i to Node j,
Objective Function
Constraints
b)Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
c)Copy and paste the Answer report below.
d)Write the Optimal Solution below. Note: The Optimal Solutions should include the optimal values for the non-zero variables as well as the objective function.