Provide a printout of the formulation of each problem in a word document and provide a printoutof the Excel solution where asked to solve using Excel Solver.
Eaton Corp. has to satisfy demand of clutch systems to 3 General Motors plants. They havetwo warehouses where they stock the product. The demand at plant 1 is 600 units. Thedemand at plant 2 is 800 units, and the demand at plant 3 is 400 units. Warehouse 1 has aninventory of 800 units and Warehouse 2 has an inventory of 1000 units. Given the shippingcosts/unit below, they need to determine how product should be shipped between thewarehouses and the plants.From To PlantWarehouse $1 $2 $31 $4 $4 $22 $3 $5 $5Transportation Costs ($)a. Solve this problem with a heuristic. Explain your heuristic and provide the heuristicsolution.b. Write a formulation for this problem. Write it out with equations as well as in tableauformat.c. Solve this problem with the Excel Solver. What is the difference in cost between theheuristic solution and the optimal solution?A company faces the following demands during the next 4 months, with the followingproduction costs. They incur inventory costs of $3/month/unit. At the beginning of Januarythey have 500 units on-hand.Jan Feb Mar AprDemand 1000 3000 2500 3500Prod. $ 40 25 35 30They wish to determine the optimal production schedule that will minimize production andinventory costs.a. Write a formulation for this problem. Write it out in equations.b. Try using logic to determine what the optimal answer will be.Highland’s TV-Radio Store must determine how many TVs and radios to keep in stock (thestore stocks only TVs and radios). A TV requires 10 sq. ft. of floor space, whereas a radiorequires 4 sq. ft; 200 sq. ft. of floor space is available. A TV will earn Highland $60 inprofits, and a radio will earn $20. Marketing requirements dictate that at least 60% of allappliances in stock be radios. Finally, a TV ties up $200 in capital, and a radio $50.Highland wants to have at most $3000 worth of capital tied up at any time.a. Formulate an IP that can be used to maximize Highland’s profit. Solve it using Excel.b. How much more profit could be attained if Highland rented another 50 sq. ft. of floorspace? How much should Highland be willing to pay for this extra footage?c. How much more profit could be attained if marketing changed their requirement so thatonly 40% of all appliances in stock be radios (keeping the floor space at 200 sq. ft.)?d. How much more profit could be attained if Highland was willing to have $3500 worth ofcapital tied-up (keeping the floor space at 200 sq. ft. and the marketing requirement at40%)? How should Highland decide if it is worthwhile to increase this value?You are in charge of loading cargo ships at a major East Coast port. You have been asked toprepare a loading plan for a freighter bound for Africa. An agricultural commodities dealerwould like to transport the following products aboard this ship:Commodity Tons Avail. Volume/ton(cubic ft)Profit/ton($)1 4000 40 702 3000 25 503 2000 60 604 1000 50 80You can elect to load any and/or all of the available product. However, the ship has 3 cargoholds with the following capacity restrictions:Cargo Hold Weight Capacity(tons)Vol. Capacity(cubic ft)Forward 3000 100,000Center 5000 150,000Rear 2000 120,000More than one type of commodity can be placed in the same cargo hold. However, the shipmust be balanced, so the weight in the forward hold must be within 10% of the weight in therear hold, and the center hold must be assigned between 40 and 60% of the total weight.Write a linear program that will maximize profits. You don’t need to solve it.Hint: Define your variables as: Xij = tons of commodity i loaded into cargo hold j
General Motors plants