Instructions:

1. Note: You must use the data below and answer the questions below rather than those in the textbook.
2. You must define the decision variables, the objective function, and the constraints in your Word document in the same manner displayed in all of the Word examples posted on Canvas.
3. You must show any additional work needed for objective function coefficients and the transshipment constraints in your Word document also.
4. Your spreadsheets and Solver models must use the format that was used in all of the examples posted on Canvas. You must take an existing spreadsheet posted on Canvas (Chapter 6 Problem 17, 18, or 19) and modify it. If you use a spreadsheet from any other source, you will receive 0 points. You may work in a group (maximum of 5 students).
5. When you are finished, you will need to submit the following files to the Canvas assignment: (a) your case analysis in a Word document (formatted as described in the course syllabus), (b) a spreadsheet with a working Solver model for Question 1 below, & (c) a spreadsheet with a working Solver model for Question 2 Extra Credit below.
6. You must label your variables with numbers, e.g., X14, X15, etc. using the node values given in the tables below.

Plant Information:

Plant 1: Production Cost per Unit = $22.00 & Quarterly Capacity = 29,500 units.

Plant 2: Production Cost per Unit = $20.25 & Quarterly Capacity = 30,000 units.

Plant 3: Production Cost per Unit = $19.10 & Quarterly Capacity = 26,000 units.

Shipping Cost per Unit ($) from Plants to Distribution Centers

	Distribution Center
Plant	Fort Worth (4)	Santa Fe (5)	Las Vegas (6)
Plant 1 (1)	5.55	4.63	4.28
Plant 2 (2)	5.08	5.20	4.72
Plant 3 (3)	5.25	5.54	5.26

Demand at Customer Warehouses

Customer Zone Warehouse	Quarterly Demand (units)
Dallas (7)	8,111
San Antonio (8)	6,203
Wichita (9)	5,200
Kansas City (10)	4,100
Denver (11)	4,008
Salt Lake City (12)	3,003
Phoenix (13)	5,010
Los Angeles (14)	6,025
San Diego (15)	5,032

Shipping Cost per Unit ($) from Distribution Centers (DCs) to Customer Zone Warehouses

	Customer Zone Warehouse
DC	Dallas	San Antonio	Wichita	Kansas City	Denver	Salt Lake City	Phoenix	Los Angeles	San Diego
Fort Worth	1.82	1.92	3.62	4.62	6.09	6.42	5.80	6.20	6.21
Santa Fe	5.00	5.50	4.90	5.10	4.71	4.80	4.50	5.80	5.90
Las Vegas	5.56	5.70	5.20	5.40	5.92	4.30	3.10	3.18	3.30

Question 1) Using the information given above, including shipping costs, manufacturing costs, capacity, and demand, how should product be produced and shipped from the Plants to the Distribution Centers and from the Distribution Centers to the Customer Zone Warehouses? What will the total cost (manufacturing cost + shipping cost) of your plan equal? Define your variables, objective function, & constraints in your Word document. Ensure that you show your additional work for any objective function coefficients and the transshipment constraints. Then, list your answers in a separate table in your Word document. Note: The Excel spreadsheet is too large to copy into your Word document. Also, you do not need to draw the network.

Hints on Question 1:

1. When you ship from a Plant to a destination, Cost per Unit = Manufacturing Cost per Unit at the Plant + Shipping Cost per Unit.
2. The Distribution Center capacities are not needed. We are assuming that the Distribution Centers are able to handle all of the volume.

Question 2 (Extra Credit 7.5 Points) Add the following assumptions simultaneously to your model from Question 1 and re-run your Solver model.

1. There was a strike at Plant 1 that increased the manufacturing cost per unit by $3.00 per unit.
2. Plant 2 needed to increase wages to retain talent. The manufacturing cost per unit increased by $2.25 per unit.
3. Product manufactured in Plant 2 could be shipped directly to the Los Angeles Customer Zone Warehouse (shipping cost = $0.58/unit).
4. Product manufactured in Plant 3 could be shipped directly to the Kansas City Customer Zone Warehouse (shipping cost = $2.20/unit).