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.
Problem Statement: Objective is to decide warehouse locations while minimizing the total cost (transportation cost and fixed weekly lease cost) considering the weekly capacity and meeting weekly demand.
Notations: Let warehouse locations be denoted as 1-Denver, 2-Memphis and 3-Richmond
Let markets be denoted as 1-New York City, 2-Los Angeles and 3-Chicago
Decision variables: There are two types of decision variables: decision variables for selection of a warehouse and decision variables for quantity to be demand units for a market be satisfied from a warehouse location
Warehouse selection variables are
- Y1 for selection of Denver warehouse (1 if selected, 0 otherwise)
- Y2 for selection of Memphis warehouse (1 if selected, 0 ...
The solution provides detailed guide on how to formulate a network design problem using linear programming. Excel Solver help is also provided.