Problem:
A company is trying to design its supply chain network in the United States. They are thinking about three possible locations for their warehouses in Denver, Memphis, and Richmond. They have three major markets at New York City, Los Angles and Chicago. If the company decides to have a warehouse at one location, there will be a fixed weekly leasing cost and the weekly flow is restricted by a maximum capacity. The following table summarizes data relevant to the location and distribution problem. The numbers in the following table are the transportation costs per unit from each possible warehouse to each market. The last two columns show the supply capacities and the fixed weekly leasing cost. The last row shows the weekly demand quantities.
To New York Los Chicago Weekly Fixed Weekly
From City Angles Capacity Cost
Denver 89 54 48 750 30,000
Memphis 49 65 35 800 25,000
Richmond 29 101 39 500 35,000
Weekly Demand 500 450 300
Formulate an optimization model to solve this problem such that the total cost, including transportation costs from warehouses to markets and fixed warehouse costs, is minimized while demand is satisfied and capacity is not exceeded.. Please note that there are four parts for an optimization model: notation (decision variable definition), objective function, constraints, and sign restrictions. Please use all numbers in the table rather than define parameters.
a. Formulate a linear programming (LP) model to solve this problem
b. Please use the Excel Solver to solve your model. Please include the spreadsheet in your submission.