Linear Optimization
A company has four distribution centers, located in Atlanta, Lexington, Milwaukee, and Salt Lake City, and ships to 12 retail stores, located in Seattle, San Francisco, Las Vegas, Tucson, Denver, Charlotte, Minneapolis, Fayetteville, Birmingham, Orlando, Cleveland, and Philadelphia. The company wants to minimize the transportation costs of shipping one of its products. The per-unit shipping cost from each distribution center to each retail location and the amounts currently in inventory and ordered at each retail location are shown in the accompanying table.
Develop and solve an optimization model to minimize the total transportation cost and answer parts a and b.
a. What is the minimum cost of shipping?
First, develop a linear optimization model to determine the optimal mix to minimize total cost. Copy the shipping data into Excel. For the purposes of this solution, create an Excel spreadsheet as shown here .2
Next, add cells to function as the variables, in this case, the number of units shipped from each distribution center to each city. The cell B17 references the number of units from Atlanta to Seattle, the cell B18 references the number of units from Atlanta to San Francisco, the cell C17 references the number of units from Lexington to Seattle, and so on. The static value 0 has been entered into these cells as shown here as a starting point.
Note that this table can be created by copying the original table and filling the entries with zeroes.
Next, identify the objective function. In this case, the objective function is the sum of the number of units from each distribution center to each city times their respective costs. While the individual cells and cell ranges can be referred to using cell references or names, for this exercise, use cell references to write the objective function.
Next, write each constraint as a mathematical equation or inequality. In this case, the constraints are the number of units available at each distribution center and the number of units needed in each city.
Write the constraint for the number of units that can be shipped from Atlanta.
Write the constraint for the number of units that can be shipped from Lexington.
Write the constraint for the number of units that can be shipped from Milwaukee.
Write the constraint for the number of units that can be shipped from Salt Lake City.
Write the constraint for the number of units supplied to Seattle.
Write the constraint for the number of units supplied to San Francisco.
Write the constraint for the number of units supplied to Las Vegas.
