The Texas Consolidated Electronics Company is contemplating a research and development program encompassing eight research projects. The company is constrained from embarking on all projects by the number of available management scientists (40) and the budget available for R&D projects ($300,000). Further, if project 2 is selected, project 5 must also be selected (but not vice versa). Following are the resource requirements and the estimated profit for each project:
|
Project
|
Expense (in thousands of dollars)
|
Management Scientists Required
|
Estimated Profit (in thousands of dollars)
|
|
1
|
60
|
7
|
36
|
|
2
|
110
|
9
|
82
|
|
3
|
53
|
8
|
29
|
|
4
|
47
|
4
|
16
|
|
5
|
92
|
7
|
56
|
|
6
|
85
|
6
|
61
|
|
7
|
73
|
8
|
48
|
|
8
|
65
|
5
|
41
|
Reference: Introduction to Management Science by Bernard Taylor. 11th Edition.
a) Word-process the integer linear programming model for the problem below.
Variables
Xi = 1, if project i is selected; 0 otherwise for i = 1, 2, ..., 8.
Objective Function
Maximize Total Profit Z =
Constraints
b) Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
c) Use Solver to find the optimal solution. Copy and paste the Answer report below.
d) Write the optimal solution.
Projects Selected =
Total Profit Z =
Taylor Problem:
The Heartland Distribution Company is a food warehouse and distributor that has a contract with a grocery store chain in several Midwest and Southeast cities. The company wants to construct new warehouses/distribution centers in some of the cities it services to serve the stores in those cities plus all the other stores in the other cities that don't have distribution centers. A distribution center can effectively service all stores within a 300-mile radius. The company also wants to limit its fixed annual costs to under $900,000. The company wants to build the minimum number of distribution centers possible.
The following table shows the cities within 300 miles of every city and the projected fixed annual charge for a distribution center in each city:
|
City
|
Annual Fixed Charge($1,000s)
|
Cities Within 300 Miles
|
|
1. Atlanta
|
276
|
Atlanta, Charlotte, Nashville
|
|
2. Charlotte
|
253
|
Atlanta, Charlotte, Richmond
|
|
3. Cincinnati
|
394
|
Cincinnati, Cleveland, Indianapolis, Louisville, Nashville, Pittsburgh
|
|
4. Cleveland
|
408
|
Cincinnati, Cleveland, Indianapolis, Pittsburgh
|
|
5. Indianapolis
|
282
|
Cincinnati, Cleveland, Indianapolis, Louisville, Nashville, St. Louis
|
|
6. Louisville
|
365
|
Cincinnati, Indianapolis, Louisville,Nashville, St. Louis
|
|
7. Nashville
|
268
|
Atlanta, Cincinnati, Indianapolis, Louisville, Nashville, St. Louis
|
|
8. Pittsburgh
|
323
|
Cincinnati, Cleveland, Pittsburgh, Richmond
|
|
9. Richmond
|
385
|
Charlotte, Pittsburgh, Richmond
|
|
10. St. Louis
|
298
|
Indianapolis, Louisville, Nashville, St. Louis
|
Reference: Introduction to Management Science by Bernard Taylor. 11th Edition.
a) Word-process the integer linear programming model for the problem below.
Variables
Xi = 1, if a distribution center is constructed in city i, 0 otherwise for i = 1, 2, ..., 10.
Objective Function
Minimize Total Number of Distribution Centers Z =
Constraints
b) Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
c) Use Solver to find the optimal solution. Copy and paste the Answer report below.
d) Write the optimal solution.
Names of cities, where distribution center should be constructed =
Total Number of Distribution Centers =
Total Cost =
Note: Add up the costs for the selected distribution centers to determine the total cost.
e) What is the optimal solution if the cost constraint is removed from the original model formulation? What is the difference in cost?
Names of cities, where distribution center should be constructed =
Total Number of Distribution Centers =
Total Cost =
Difference in Cost =