Solve using excel and solver and show all work in excel. Textiles, Ltd$.,$ is a Hong Kong$$based firm that distributes textiles world$-$ wide. The company is owned by the Lao family. Should the People$$s Republic of China continue its economic renaissance, the company hopes to use its current base to expand operations to the mainland. International Textile has mills in the Bahamas, Hong Kong, Korea, Nigeria, and Venezuela, each weaving fabrics out of two or more raw fibers: cotton, polyester, and$/$or silk. The mills service eight company distribution centers located near the customers$$ geographical centers of activity.

Because transportation costs historically have been less than $10\%$ of total expenses, management has paid little attention to extracting savings through judicious routing of shipments. Ching Lao is returning from the United States, where he has just completed his bachelor$$s degree in business. He believes that each year he can save New Horizon hundreds of thousands dollars $$ perhaps millions $-$ by better routing of fabrics from mills to distribution centers. One glaring example of poor routing is the current assignment of fabric output to the Mexico City distribution center from Nigeria instead of from Venezuela, less than a third the distance. Similarly, the Manila center now gets most of its textiles from Nigeria and Venezuela, although the mills in Hong Kong itself are much closer.

Of course, the cost of shipping a bolt of cloth does not depend on distance alone. Table$-1$ provides the actual costs supplied to Lao from company headquarters. Distribution center demands are seasonal, so a new shipment plan must be made each month. Table$-2$ provides the fabric requirements for the month of March. New Horizon$$s mills have varying capacities for producing the various types of cloth. Table $3$ provides the capacities that apply during March.

Lao wants to schedule production and shipments in such a way that the most costly customers are shorted when there is insufficient capacity, and the least$-$efficient plants operate at less than full capacity when demand falls below maximum production capacity.

You have been retained by International to assist Lao.

Questions

$1.$ Find the optimal March shipment schedule and its total transportation cost for each of the following: a$.$ cotton, b$.$ polyester, c$.$ silk

$2.$ The company will be opening a silk$-$making department in the Nigeria mill. Although it will not be completed for several months, a current capacity of $1000$ bolts for that fabric might be used during March for an added one$-$time cost of $$2000.$ Find the new optimal shipment schedule and the total cost for that fabric. Should the Nigeria mill process silk in March?

$3.$ Lao learns that changes might have to be made to the March plans. If a new customer is obtained, the cotton demand in Manila and in Mexico City will increase by $10\%$ at each location. Meanwhile, a big New York customer might cut back, which would reduce polyester demand by $10\%$ in both New York and Chicago. Find the contingent optimal schedules and total costs $($a$)$ for cotton and $($b$)$ for polyester.

$4.$ New Horizon loses a profit of $$10$ for each bolt of cotton it falls short of meeting the distribution center$$s demand. For polyester, the loss is $$20$ per bolt; for silk, it is a whopping $$50$ per bolt. By running the mills on overtime, the company can produce additional bolts at the additional costs shown in Table$-4.$ Using only the original data from Tables $1$ through $3$ and the information in Table$-4,$ determine new production schedules to maximize overall profit for successively $($a$)$ cotton, $($b$)$ polyester, and $($c$)$ silk. Which fabrics and locations involve overtime production, and what are the overtime quantities?

$5.$ Offer Lao other suggestions for reducing costs of transportation. You don$$t have to do any more calculations.

Show the complete formulation clearly.

Table $1$ Shipping Cost Data $($Dollars Per Bolt$)$

Distribution Center

Mill Los Angeles Chicago London Mexico City Manila Rome Tokyo New York

Bahamas $22337471$

Hong Kong $678102948$

Korea $568114917$

Nigeria $141269117510$

Venezuela $43519611$

$4$

Table $2$ Fabric Demands For March $($Bolts$)$

Distribution Center

Fabric Los Angeles Chicago London Mexico City Manila Rome Tokyo New York

Cotton $500800900900800100200700$

Polyester $10002000300015004007009002500$

Silk $10010020050400200700$

$200$

Table $3$ March Production Capacities

Production Capacity $($Bolts$)$

Mill Cotton Polyester Silk

Bahamas $100030000$

Hong Kong $200025001000$

Korea $10003500500$

Nigeria $200000$

Venezuela $100020000$

Table $4$ Overtime Production Costs

Cost Per Bolt

Mill Cotton Polyester Silk

Bahamas $$10$ $$10$ NA

Hong Kong $$15$ $$12$ $$25$

Korea $$5$ $$8$ $$22$

Nigeria $$6$ NA NA

Venezuela $$7$ $$6$ NA