The Korean conglomerate Hyundai sells vehicles under two affiliated brands: Kia and Hyundai. Although many models are increasingly produced in the U.S., certain lower volume or specialty models are imported from various factories in Korea. Today, Pacific ocean traffic is bottlenecked by long waits at key West Coast ports. So, Hyundais supply chain management organization set out to devise a new delivery network for these imports.

The new route involves longer ocean travel through the Panama Canal to East Coast Ports with good infrastructure, but less congestion: Jacksonville, New York and Norfolk. The deliveries made into these ports for one typical month include 440 to Jacksonville, 960 to New York and 620 to Norfolk. Upon port arrival and customs clearance, the vehicles are shipped via rail to destination cities in the Eastern U.S.

Hyundai has negotiated a distribution contract with the railroads that correlates freight charges to a fixed rate per mile traveled. (In this way, an optimization of the distribution network can be achieved based on miles without any cost data.)

In order to devise a delivery plan, Hyundai has targeted their top twelve markets in the East. Attachment 1 provides these top destinations in alpha sequence along with the forecast, monthly demand. Because of the inter-connectivity of the U.S. rail freight network, any port can serve any destination. Attachment 2 provides the distance in miles from the port city to the destination city.

Marketing is very concerned that dealer locations furthest from the port of entry should not be arbitrarily penalized in their deliveries due to distance. So, it is a company policy that at least 80% of monthly demand MUST be satisfied in every market. Nor, however, can supply exceed the demand for any city either. Your assignment is to design a model to achieve the lowest transportation cost (by minimizing distance) while satisfying Marketing conditions. The spreadsheet must provide a clear presentation of how many vehicles will reach each market and from which port in order to achieve the lowest overall cost (=distance).

1. Sheet 1 labeled NETWORK-
   • Please format the model so that PORT of origin is set as the column and DESTINATION cities are the rows. Formulate your model using Solver to minimize costs while satisfying all conditions. The model should be neat, and it should be clear what your decision variables and constraints are (i.e. be sure to label!)
   • Please ensure that the Solver solution is saved in your spreadsheet submission.

2. Sheet 2 labeled DELIVERY COSTS-
   • Assume that the railroads all charge $2.75 per mile traveled per each passenger car carried.
   • It is common practice in the auto industry to add on a fixed, average Delivery cost onto the sticker price, which is not negotiable. The use of an average implies that the lower cost destinations in the East will subsidize the more costly deliveries further away from the ports of entry. If Hyundai dealers are instructed to use $980 as the sticker Delivery cost, what is the total surplus (or deficit?) for the month profiled? Assume that the ocean freight is included in the base price and not part of this analysis. The format should be carefully labeled and show all calculations to support your answer, which should be a highlighted cell.

Attachment 1- Demand by Market

Destination	Demand
Atlanta	245
Boston	265
Charlotte	230
Chicago	255
Cleveland	115
Detroit	75
Memphis	95
Miami	265
New Orleans	75
New York	535
Philadelphia	85
Washington	140

Attachment 2- Mileage from Ports to Destinations

Jacksonville NYC Norfolk

Atlanta	355	966	566
Boston	1157	294	574
Charlotte	384	727	193
Chicago	1069	717	885
Cleveland	896	369	556
Detroit	1057	456	709
Memphis	669	1109	919
Miami	348	1436	961
New Orleans	546	1391	1036
New York	943	0	359
Philadelphia	851	98	277
Washington	709	374	196

**This is all the given information**