Question

Superior Consulting is a firm that specializes in developing computerized decision support systems for manufacturing companies. They currently operate offices in Chicago, Charlotte, Pittsburgh and Houston. They are considering opening new offices in one or more cities including: Atlanta, Boston, Denver, Detroit, Miami, St. Louis and Washington DC. They have $14 million available for this purpose. The executive team ranked the prospective cities from 7 to 1, with 7 being the highest preference.

Due to the specialized nature of their work, they must staff any new offices with a minimum number of its employees from its existing offices. However, it has a limited number of employees available to transfer to any new offices. See the tables below for the costs for opening a new office, the start-up staffing needs, preference and available number of employees from existing offices.

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 (in 1,000’s) to transfer them from an existing office to a prospective new office.

	Cost to move employee to new offices (in 1,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 in the Midwest (Detroit and/or St. Louis) and one new office in the Southeast (Atlanta and/or Miami.)

Formulate and solve their problem using Excel and Solver.

Answer 1