Superior Consulting is a firm that specializes in developingcomputerized decision support systems for manufacturing companies.They currently operate offices in Chicago, Charlotte, Pittsburghand Houston. They are considering opening new offices in one ormore cities including: Atlanta, Boston, Denver, Detroit, Miami, St.Louis and Washington DC. They have $14 million available for thispurpose. The executive team ranked the prospective cities from 7 to1, with 7 being the highest preference.
Due to the specialized nature of their work, they must staff anynew offices with a minimum number of its employees from itsexisting offices. However, it has a limited number of employeesavailable to transfer to any new offices. See the tables below forthe costs for opening a new office, the start-up staffing needs,preference and available number of employees from existingoffices.
Prospective Office | Setup Cost (1,000,000's) | Staffing Needs (employees) | Preference (1 is lowest) |
Atlanta | $1.7 | 9 | 5 |
Boston | $3.6 | 14 | 4 |
Denver | $2.1 | 8 | 3 |
Detroit | $2.5 | 12 | 1 |
Miami | $3.1 | 11 | 6 |
St. Louis | $2.7 | 7 | 2 |
Washington, DC | $4.1 | 18 | 7 |
Existing Office | Available Employees |
Chicago | 24 |
Charlotte | 19 |
Pittsburgh | 16 |
Houston | 21 |
The HR team developed the following cost per employee (in1,000’s) to transfer them from an existing office to a prospectivenew office.
| Cost to move employee to new offices (in1,000's) |
Existing Office | Atlanta | Boston | Denver | Detroit | Miami | St. Louis | Washington, DC |
Chicago | $19 | $32 | $27 | $14 | $23 | $14 | $41 |
Charlotte | $14 | $47 | $31 | $28 | $35 | $18 | $53 |
Pittsburgh | $16 | $39 | $26 | $23 | $31 | $19 | $48 |
Houston | $22 | $26 | $21 | $18 | $28 | $24 | $43 |
In addition, the company would like at least one new office inthe Midwest (Detroit and/or St. Louis) and one new office in theSoutheast (Atlanta and/or Miami.)
Formulate and solve their problem using Excel and Solver.
