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 or pdf 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 $11$ or $12)$ 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 or a pdf 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. Only one group member needs to submit files for your group.

$6.$ You must label your variables with numbers, e$.$g$.,$ X$18,$ X$19,$ etc. using the node values given in the tables below.

Plant Information:

Plant $1$: Production Cost per Unit $=$ $$23\mathrm{.}50$ & Quarterly Capacity $=30,000$ units.

Plant $2$: Production Cost per Unit $=$ $$24\mathrm{.}25$ & Quarterly Capacity $=31,000$ units.

Plant $3$: Production Cost per Unit $=$ $$22\mathrm{.}30$ & Quarterly Capacity $=28,000$ units.

Plant $4$: Production Cost per Unit $=$ $$21\mathrm{.}85$ & Quarterly Capacity $=29,000$ units.

Plant $5$: Production Cost per Unit $=$ $$22\mathrm{.}25$ & Quarterly Capacity $=28,000$ units.

Plant $6$: Production Cost per Unit $=$ $$19\mathrm{.}60$ & Quarterly Capacity $=13,000$ units.

Plant $7$: Production Cost per Unit $=$ $$19\mathrm{.}62$ & Quarterly Capacity $=13,000$ units.

$$

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

Distribution Center

Plant Fort Worth $(8)$ Santa Fe $(9)$ Las Vegas $(10)$ Pittsburgh $(11)$

Plant $1(1)$ $$1\mathrm{.}32$ $$1\mathrm{.}42$ $$1\mathrm{.}19$ $$1\mathrm{.}08$

Plant $2(2)$ $$1\mathrm{.}52$ $$1\mathrm{.}32$ $$1\mathrm{.}13$ $$1\mathrm{.}02$

Plant $3(3)$ $$1\mathrm{.}40$ $$1\mathrm{.}36$ $$1\mathrm{.}05$ $$0\mathrm{.}95$

Plant $4(4)$ $$1\mathrm{.}62$ $$1\mathrm{.}37$ $$1\mathrm{.}07$ $$0\mathrm{.}94$

Plant $5(5)$ $$1\mathrm{.}55$ $$1\mathrm{.}35$ $$1\mathrm{.}01$ $$0\mathrm{.}99$

Plant $6(6)$ $$1\mathrm{.}42$ $$1\mathrm{.}39$ $$1\mathrm{.}20$ $$1\mathrm{.}04$

Plant $7(7)$ $$1\mathrm{.}71$ $$1\mathrm{.}79$ $$1\mathrm{.}60$ $$1\mathrm{.}54$

$$

Demand at Customer Warehouses

Customer Zone Warehouse Quarterly Demand $($units$)$

Dallas $(12)12,100$

San Antonio $(13)17,500$

Wichita $(14)8,200$

Kansas City $(15)8,100$

Denver $(16)9,100$

Salt Lake City $(17)9,100$

Phoenix $(18)9,520$

Los Angeles $(19)8,700$

San Diego $(20)7,550$

Boston $(21)12,100$

Portsmouth $(22)8,300$

Charlotte $(23)6,300$

Atlanta $(24)10,700$

Panama City Beach $(25)8,900$

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

Customer Zone Warehouse

DC

Dallas

$(12)$

San Antonio

$(13)$

Wichita

$(14)$

Kansas City

$(15)$

Denver

$(16)$ Salt Lake City

$(17)$

Phoenix

$(18)$

Los Angeles

$(19)$

San Diego

$(20)$

Boston

$(21)$

Ports

$(22)$

Charlotte

$(23)$

Atlanta

$(24)$

PCB $(25)$

Fort Worth

$(8)$

$$0\mathrm{.}99$ $$0\mathrm{.}79$ $$0\mathrm{.}99$ $$0\mathrm{.}80$ $$1\mathrm{.}02$ $$1\mathrm{.}02$ $$1\mathrm{.}00$ $$1\mathrm{.}05$ $$1\mathrm{.}06$ $$1\mathrm{.}45$ $$1\mathrm{.}44$ $$1\mathrm{.}48$ $$1\mathrm{.}42$ $$1\mathrm{.}76$

Santa Fe $(9)$

$$0\mathrm{.}95$ $$0\mathrm{.}95$ $$0\mathrm{.}98$ $$0\mathrm{.}82$ $$1\mathrm{.}11$ $$1\mathrm{.}09$ $$1\mathrm{.}05$ $$1\mathrm{.}10$ $$1\mathrm{.}15$ $$1\mathrm{.}55$ $$1\mathrm{.}51$ $$1\mathrm{.}55$ $$1\mathrm{.}51$ $$1\mathrm{.}80$

Las Vegas $(10)$

$$0\mathrm{.}99$ $$1\mathrm{.}11$ $$1\mathrm{.}17$ $$1\mathrm{.}01$ $$0\mathrm{.}99$ $$0\mathrm{.}99$ $$0\mathrm{.}78$ $$0\mathrm{.}69$ $$0\mathrm{.}79$ $$1\mathrm{.}82$ $$1\mathrm{.}80$ $$1\mathrm{.}83$ $$1\mathrm{.}81$ $$1\mathrm{.}91$

Pittsburgh

$(11)$ $$1\mathrm{.}31$

$$1\mathrm{.}29$ $$1\mathrm{.}38$ $$1\mathrm{.}32$ $$1\mathrm{.}45$ $$1\mathrm{.}50$ $$1\mathrm{.}55$ $$1\mathrm{.}60$ $$1\mathrm{.}66$ $$0\mathrm{.}68$ $$0\mathrm{.}71$ $$0\mathrm{.}81$ $$0\mathrm{.}91$ $$1\mathrm{.}01$

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$:

a$)$ When you ship from a Plant to a destination, Cost per Unit $=$ Manufacturing Cost per Unit at the Plant $+$ Shipping Cost per Unit.

b$)$ The Distribution Center capacities are not needed. We are assuming that the Distribution Centers are able to handle all of the volume.