QSO 520 Management Science Winter 2014 Final Exam Page 2
Problem 1 (10 points)
A furniture manufacturer has warehouses in cities represented by nodes 1, 2, and 3 in the figure given below. The values on the arcs indicate the per-unit shipping costs required to transport living room suites between the various cities. The supply of living room suites at each warehouse is indicated by the negative number next to nodes 1, 2, and 3. The demand for living room suites is indicated by the positive numbers next to the remaining nodes. Determine the least costly shipping plan.
a) Word-process the linear programming model for the network o minimize the total shipping cost below.
Variables
Xij = Number of living room suites transported from node i to node j,
i = 1, 2, …, 6 and j = 1,2, …, 6
Objective Function
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. Do not copy and paste from Excel. Note: Write clearly how many units will be moved from what city to what and the total transportation cost. There is no need to include the arcs that have zero flows in writing the optimal solution.
Problem 4 (10 points)
Radford Castings can produce brake shoes on six different machines. The following table summarizes the manufacturing costs associated with producing the brake shoes on each machine along with the available capacity on each machine. If the company has received an order for 1,800 brake shoes, how should it schedule these machines?
Machine / Fixed Cost / Variable Cost / Capacity1 / $1,000 / $21 / 500
2 / $ 950 / $23 / 600
3 / $ 875 / $25 / 750
4 / $ 850 / $24 / 400
5 / $800 / $20 / 600
6 / $ 700 / $26 / 800
a) Word-process the Integer linear programming model below.
Variables
Xi = Number of brake shoes produced on Machine, where i = 1, 2, …, 6
Yi = 1, if Machine i is used to produced brake shoes and 0, otherwise, where i = 1, 2, …, 6
Objective Function
Constraints
b) Set up the spreadsheet for Excel Solver. Copy and paste the spreadsheet below.
c) Use Excel Solver to find the optimal solution. Copy and paste the Answer report below.
d) Write the Optimal Solution below. Do not copy and paste from Excel. Note: Clearly state how many brake shoes will be produced on each machine and what is the total cost.
Ragsdale6 Problem 6-14
A developer of video game software has seven proposals for new games. Unfortunately, the company cannot develop all the proposals because its budget for new projects is limited to $950,000, and it has only 20 programmers to assign to new projects. The financial requirements, returns, and the number of programmers required by each project are summarized in the following table. Projects 2 and 6 require specialized programming knowledge that only one of the programmers has. Both of these projects cannot be selected because the programmer with the necessary skills can be assigned to only one of the projects. (Note: All dollar amounts represent thousands.)
Project / Programmers Required / Capital Required / Estimated NPV1 / 7 / $250 / $650
2 / 6 / $175 / $550
3 / 9 / $300 / $600
4 / 5 / $150 / $450
5 / 6 / $145 / $375
6 / 4 / $160 / $525
7 / 8 / $325 / $750
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, …, 7.
Objective Function
Maximize Total NPV =
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. Do not copy and paste from Excel. Note: Clearly write which projects are selected and what is the total NPV.
Problem 4
Garden City Beach is a popular summer vacation destination for thousands of people. Each summer, the city hires temporary lifeguards to ensure the safety of the vacationing public. Garden City's lifeguards are assigned to work five consecutive days each week and then have two days off. However, the city's insurance company requires them to have at least the following number of lifeguards on duty each day of the week:
Minimum Number of Lifeguards Required Each DaySunday / Monday / Tuesday / Wednesday / Thursday / Friday / Saturday
18 / 17 / 16 / 16 / 16 / 14 / 19
The city manager would like to determine the minimum number of lifeguards that will have to be hired.
a) Word-process the integer linear programming model for the problem below.
Variables
X1 = Number of life guards that work Sunday through Thursday (Friday and Saturday off)
X2 = Number of life guards that work Monday through Friday (Saturday and Sunday off)
X3 = Number of life guards that work Tuesday through Saturday (Sunday and Monday off)
X4 = Number of life guards that work Wednesday through Sunday (Monday and Tuesday off)
X5 = Number of life guards that work Thursday through Monday (Tuesday and Wednesday off)
X6 = Number of life guards that work Friday through Tuesday (Wednesday and Thursday off)
X7 = Number of life guards that work Saturday through Wednesday (Thursday and Friday off)
Objective Function
Minimize the total number of lifeguards employed 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. Do not copy and paste from Excel. Note: Clearly write which projects are selected and what is the total NPV.
X1 =
X2 =
X3 =
X4 =
X5 =
X6 =
X7 =
Total number of lifeguards employed Z =
E ) Several lifeguards have expressed a preference to be off on Saturdays and Sundays. What is the maximum number of lifeguards that can be off on the weekend without increasing the total number of life guards required?