Question: PLEASE PROVIDE ME WITH AN EXCEL SOLUTION NN has been given a new contract for a large number of electric panels. To meet this demand,

PLEASE PROVIDE ME WITH AN EXCEL SOLUTION

PLEASE PROVIDE ME WITH AN EXCEL SOLUTION NN has

NN has been given a new contract for a large number of electric panels. To meet this demand, it will use its existing plants in Chicago and Houston, and consider new plants in Tuscaloosa, St. Louis, and Portland. Finished control panels are to be shipped to Seattle, Denver, and Kansas City. All related information is given in the table below: Shipping Cost to Destination Sources Construction Cost Seattle Denver Kansas City Capacity Chicago 5 7 8 2,500 Houston 10 8 6 2,500 Tuscaloosa 350,000 9 4 3 10,000 St. Louis 200,000 12 6 2 10,000 Portland 480,000 4 10 11 10,000 Demand 3,000 8,000 9,000 Develop a model whose solution would reveal which plants to build and the optimal shipping schedule. (Hints: all demand must be met, we have two cost components, shipping cost and the fixed construction costs if a new plant is chosen) Then answer these questions: a. What is your total cost? b. Which plants will be chosen? What is the excess capacity in each operating plant? d. If the capacity of the proposed 3 plants in Tuscaloosa, St.Louis and Portland) is to be reduced to 7000, how your answer will change? c

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!