Excel 4 Ur Business

Overview

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

Many companies manufacture products at locations (often called supply points) and ship their products to customers (often called demand points). A natural question is what is the least expensive way to produce and ship products to customers and meet customer 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 nonnegative.

How can a drug company determine the locations at which 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.xls. Let’s suppose a drug company produces a drug in Los Angeles, Atlanta, and New York. The Los Angeles plant can produce up to 10,000 pounds of the drug per month. Atlanta can produce up to 12,000 pounds of the drug per month, and New York can produce up to 14,000 pounds per month. Each month, the company must ship 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 the following figure. 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 are given in cells B4:E6. For example, it costs $3.50 to produce a 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 enteringin the cell range B10:E12 trial values for our shipments from each supply point to each region, we can compute total shipping cost as the following:

(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 changingcells 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 enteringin 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 Tools, Solver) and fill it in as shown in the following figure.

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 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 options. After clicking Solve in the Solver Parameters dialog box, we’re presented with the optimal solution shown earlier in the following figure. 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 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.

 

 

 

 

 

Explore posts in the same categories: MS Excel Tune Up