The hospital administrator at Riverside General must appoint head nurses to four newly established departments: urology, cardiology, orthopedics, and obstetrics. In anticipation of this staffing problem, she had hired four nurses: Hopkins, Berry, Clooney, and Estephan. Believing in the decision modeling approach to problem solving (i.e., prescriptive analytics), the administrator has interviewed each nurse, considered his or her background, personality, and talents, and developed a cost scale ranging from 0 to 100 points rating to be used in the assignment.
A 0 for Nurse Berry being assigned to the cardiology unit implies that she/he would be perfectly suited for that task. A value close to 100, on the other hand, would imply that she/he is not at all suited to head that unit. The table below gives the complete set of cost (i.e., rating) figures that the hospital administrator felt represented all possible assignments. Which nurse should be assigned to which unit?
Nurse |
Urology |
Cardiology |
Orthopedics |
Obstetrics |
Hopkins |
28 |
18 |
15 |
75 |
Berry |
32 |
48 |
23 |
38 |
Clooney |
31 |
36 |
24 |
36 |
Estephan |
25 |
38 |
55 |
12 |
a. Define the decision variables of this problem. (3 points)
b. Provide the algebraic formulation of the objective function. (2 points)
c. Formulate this problem as an “Assignment Model” on a spreadsheet and use Excel’s Solver to determine which nurse should be assigned to which unit and the optimal rating. (Provide the corresponding “Excel Spreadsheet” and the “Answer Report”). Include “managerial statements” that communicate the results of the analyses (i.e. describe verbally the results). (15 points)
The Hardgrave Machine Company produces computer components at its plants in Kingston, ON, Quebec City, QC and Prince George, BC. These plants have not been able to keep up with demand for orders at Hardgrave’s four warehouses in Hamilton, Winnipeg, Edmonton, and Regina. The firm has therefore decided to build one new plant to expand its productive capacity to meet the demand. The two sites being considered are Sudbury, ON and Abbotsford, BC.
The tables below present the production costs and capacities for: (i) each of the three existing plants; (ii) demand at each of the four warehouses; and (iii) estimated production costs for the new proposed plants.
Warehouse |
Monthly Demand (Units) |
Hamilton |
10,000 |
Winnipeg |
12,000 |
Edmonton |
15,000 |
Regina |
9,000 |
Total = 46,000 units
Production Plant |
Monthly Supply |
Production Cost per Unit ($) |
Kingston |
15,000 |
$48 |
Quebec City |
6,000 |
50 |
Prince George |
14,000 |
$52 |
Total = 35,000 units
New Plant |
Estimated Production Cost per Unit at Proposed Plants |
Sudbury |
$53 |
Abbotsford |
$49 |
To: From: |
Hamilton |
Winnipeg |
Edmonton |
Regina |
Kingston Quebec City Prince George |
$25 $35 $66 |
$40 $30 $45 |
$55 $50 $36 |
$60 $40 26 |
Sudbury Abbotsford |
$30 $60 |
30 38 |
$41 $30 |
$50 $27 |
Hardgrave estimates that the monthly fixed cost of operating the proposed facility in Sudbury would be $400,000. The Abbotsford plant would be somewhat cheaper due to the cheaper cost of living at that location. Hardgrave therefore estimates the monthly fixed cost of operating the proposed facility in Abbotsford would be $325,000. Note that the fixed costs at existing plants (i.e., plants in Kingston, Quebec City and Prince George) need not be considered since they will be incurred regardless of which plant Hardgrave decides to open.
a. Formulate algebraically the above problem to help Hardgrave decide which one of the new locations, Sudbury OR Abbotsford, will yield to the lowest production and transportation cost in combination with the existing plants and warehouses. (22 points)
b. Formulate this same linear programming problem on a spreadsheet and solve using Excel’s Solver (Provide the corresponding “Excel Spreadsheet” and the “Answer Report”). Include “managerial statements” that communicate where to build the new plant to expand its productive capacity, the optimal shipping quantities from the plants to the warehouses, and the corresponding optimal shipping, production and fixed operating costs. (i.e., describe verbally the results). (13 points)
Hillier Chapter 7, 7.16 (a) and (b) ONLY (e-book page 361/362)
For (b) provide the corresponding “Excel Spreadsheet” formulation and the “Answer Report”. Include “managerial statements” that communicate which books should be published and how many of each. (i.e., describe verbally the results).
The board of directors of a large manufacturing firm is considering a set of investments shown in the following table. Let Ri be the total revenue from investment i and Ci be the cost to make investment i.The board wishes to maximize the total revenue and invest no more than a total of M dollars. Formulate this model as a Binary Integer Programming Problem (BIP). Define your decision variables and write down the algebraic formulation of the model.