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 Hardgraves 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

Supply needed from new plant = 46,000 35,000 = 11,000 units per month

New Plant	Estimated Production Cost per Unit at Proposed Plants
Sudbury	$53
Abbotsford	$49

Transportation costs per unit from each plant to each warehouse are summarized in the following table:

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.

Formulate this same linear programming problem on a spreadsheet and solve using Excels 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).