MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• How can a drug company determine at which location they should produce drugs, and from which they should ship drugs to customers?

Many companies manufacture products at different locations (often called supply points), and ship their products to customers (often called demand points). A natural question then is, what is the least expensive way to produce and ship products to customers and still meet demand? This type of problem is called a transportation problem. A transportation problem can be set up as a linear Solver model with the following specifications:

• Target cell. Minimize total production and shipping cost.

• Changing cells. The amount produced at each supply point that is shipped to each demand point.

• Constraints. The amount shipped from each supply point can’t exceed plant capacity. Each demand point must receive its required demand. Also, each changing cell must be non-negative.

• How can a drug company determine at which location they should produce drugs, and from which they should ship drugs to customers?

• You can follow along with this problem by looking at the file Transport.xlsx. Let’s suppose a company produces a certain drug at its Los Angeles, Atlanta, and New York facilities. Each month, the Los Angeles plant can produce up to 10,000 pounds of the drug. Atlanta can produce up to 12,000 pounds, and New York can produce up to 14,000 pounds. The company must make monthly shipments to the four regions of the United States-East, Midwest, South, and West-the number of pounds listed in cells B2:E2, as shown in Figure 29-1 on the next page. For example, the West region must receive at least 13,000 pounds of the drug each month. The cost per pound of producing a drug at each plant and shipping the drug to each region of the country is given in cells B4:E6.

  Figure 29-1: Data for a transportation problem

• For example, it costs $3.50 to produce one pound of the drug in Los Angeles and ship it to the Midwest region. What is the cheapest way to get each region the quantity of the drug they need?

• To express our target cell, we need to track total shipping cost. After entering in the cell range B10:E12 trial values for our shipments from each supply point to each region, we can compute total shipping cost as follows:

• (Amount sent from LA to East)*(Cost per pound of sending drug from LA to East)+

• (Amount sent from LA to Midwest)*(Cost per pound of sending drug from LA to Midwest)+

• (Amount sent from LA to South)*(Cost per pound of sending drug from LA to South)+

• (Amount sent from LA to West)*(Cost per pound of sending drug from LA to West)+…

• (Amount sent from New York City to West)*(Cost per pound of sending drug from New York City to West)

• The SUMPRODUCT function can multiply corresponding elements in two separate rectangles (as long as the rectangles are the same size) and add together the products. I’ve named the cell range B4:E6 as costs and the changing-cells range (B10:E12) as shipped. Therefore, our total shipping and production cost is computed in cell B18 with the formula SUMPRODUCT(costs,shipped).

• To express our constraints, we first compute the total shipped from each supply point. By entering the formula SUM(B10:E10) in cell F10, we compute the total number of pounds shipped from Los Angeles as (LA shipped to East)+(LA shipped to Midwest)+ (LA shipped to South)+(LA shipped to West). Copying this formula to F11:F12 computes the total shipped from Atlanta and New York City. Later I’ll add constraints (called supply constraints) that ensure the amount shipped from each location does not exceed the plant’s capacity.

• Next, I compute the total received by each demand point. I begin by entering in cell B13 the formula SUM(B10:B12). This formula computes the total number of pounds received in the East as (Pounds shipped from LA to East)+(Pounds shipped from Atlanta to East)+ (Pounds shipped from New York City to East). By copying this formula from B13 to C13:E13, I compute the pounds of the drug received by the Midwest, South, and West regions. Later, I’ll add constraints (called demand constraints) that ensure that each region receives the amount of the drug it requires.

• We now open the Solver Parameters dialog box (click Solver in the Analysis group, on the Data tab) and fill it in as shown in Figure 29-2.

  Figure 29-2: The Solver set up to solve our transportation problem.

• We want to minimize total shipping cost (computed in cell B18). Our changing cells are the number of pounds shipped from each plant to each region of the country. (These amounts are listed in the range named shipped, consisting of cells B10:E12.) The constraint F10:F12<=H10:H12 (the supply constraint) ensures that the amount sent from each plant does not exceed its capacity. The constraint B13:E13>=B15:E15 (the demand constraint) ensures that each region receives at least the amount of the drug it needs.

• Our model is a linear Solver model because our target cell is created by adding together the terms of the form (changing cell)*(constant), and both our supply and demand constraints are created by comparing the sum of changing cells to a constant.

• I now click Options in the Solver Parameters dialog box and check the Assume Linear Model and Assume Non-Negative boxes. After clicking Solve in the Solver Parameters dialog box, we’re presented with the optimal solution shown earlier in Figure 29-1. The minimum cost of meeting customer demand is $86,800. This minimum cost can be achieved if the company uses the following production and shipping schedule:

  • Ship 10,000 pounds from Los Angeles to the West region.

  • Ship 3000 pounds from Atlanta to the West region, and the same amount from Atlanta to the Midwest region. Ship 6000 pounds from Atlanta to the South region.

  • Ship 9000 pounds from New York City to the East region, and 3000 pounds from New York City to the Midwest region.