1. 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.
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.