Question

1. Suppose that each day, Northern, Central, and Southern California each use 100 billion gallons of water. Also assume that Northern California and Central California have available 120 billion gallons of water, whereas Southern California has 40 billion gallons of water available. The cost of shipping 1 billion gallons of water between the three regions is as follows. Also, you will not be able to meet all the demand for water, so assume that each billion gallons of unmet demand incurs the following shortage costs:

	Northern	Central	Southern
Northern	$5,000.00	$7,000.00	$10,000.00
Central	$7,000.00	$5,000.00	$6,000.00
Southern	$10,000.00	$6,000.00	$5,000.00
Shortage	$6,000.00	$5,500.00	$9,000.00

How should California’s water be distributed to minimize the sum of shipping and shortage costs and what is the total cost?

Answer 1

note: i am using excel to solve this problem

we have 2 cases here

case1: minimize shipping cost

we have to compute total shipping cost and minimize it.

Expression for total cost is

(no. of billion gallons of water sent from Northern to Northern)*(Cost per billion gallons of sending water from Northern to Northern) + (no. of billion gallons of water sent from Northern to Central)*(Cost per billion gallons of sending water from Northern to Central) + (no. of billion gallons of water sent from Northern to Southern)*(Cost per billion gallons of sending water from Northern to Southern) + (no. of billion gallons of water sent from Central to Northern)*(Cost per billion gallons of sending water from Central to Northern) + (no. of billion gallons of water sent from Central to Central)*(Cost per billion gallons of sending water from Central to Central) + (no. of billion gallons of water sent from Central to Southern)*(Cost per billion gallons of sending water from Central to Southern) + (Number of billion gallons of water sent from Southern to Northern)*(Cost per billion gallons of sending water from Southern to Northern) + (Number of billion gallons of water sent from Southern to Central)*(Cost per billion gallons of sending water from Southern to Central) + (Number of billion gallons of water sent from Southern to Southern)*(Cost per billion gallons of sending water from Southern to Southern)

we cant use the above formla in excel because it will make the problem more complecate so we are taking costs of shipping 1 billion gallons of water between those three regions were in range C3:E5 and shipments for each supply point to each region were put in cell range C8:E10 .these two range are in same dimension so we use SUMPRODUCT function to compute total cost here

formula := SUMPRODUCT (C3: E5, C8: E10)

which was entered into cell G3

now we have to determine the explicit or implicit constraints. one constraint is that water received by each region should be equal to or greater than 100 billion gallons of water. This constraint was put in range C12:E14.

formula in cell C12 : =SUM (C$8: C$10)

the formula in cell D12 and E12 were copied from that in C12. another one is that water sent from each region must be equal to or less than billion gallons of water available. we use

formula :=SUM (C8: E8)

to compute total billion gallons of water sent from northern california. by copying and pasting this formula into cells G9 and G10, we can get the amount of water sent from central or southern too.

Now let’s click on Data tab and then click on Solver in Analysis group to open Solver Parameters dialog box.

Fill the dialog box as given

It is well known that companies not only manufacture products at different locations but also ship their products to the customer at different locations. Distribution managers need to allocate and ship merchandise in the most efficient and cost-effective manner. This kind of problem is called Transportation problem. I will discuss how to use Excel Solver to solve Transportation problem. If you are not familiar with Excel solver, you can read one of my previous articles – How to solve LP using Microsoft Solver.

Table of Contents [show]

Case 1: Minimize total shipping cost while meeting requirements

Suppose that northern, central and southern California each uses 100 billion gallons of water each day. Also, assume that northern California and central California have 120 billion gallons of water available, whereas southern California has 40 billion gallons of water available. The cost of shipping 1 billion gallons of water between the three regions is as follows:

	Northern	Central	Southern
Northern	$5,000	$7,000	$8,000
Central	$7,000	$5,000	$6,000
Southern	$8,000	$6,000	$5,000

What is the cheapest way to deliver the quantity of the drug each region needs?

Our objective, in this case, is to compute total shipping cost and minimize it. The total cost can be expressed as follows:

(Number of billion gallons of water sent from Northern to Northern)*(Cost per billion gallons of sending water from Northern to Northern) + (Number of billion gallons of water sent from Northern to Central)*(Cost per billion gallons of sending water from Northern to Central) + (Number of billion gallons of water sent from Northern to Southern)*(Cost per billion gallons of sending water from Northern to Southern) + …… + (Number of billion gallons of water sent from Southern to Central)*(Cost per billion gallons of sending water from Southern to Central) + (Number of billion gallons of water sent from Southern to Southern)*(Cost per billion gallons of sending water from Southern to Southern)

Read More: Deal with Sequencing Problems Using Excel Solver!

If we translate above model into excel formula, it will not only cost us a lot of time but also expose us to a high risk of making a mistake. In order to make computation easier, I put the costs of shipping 1 billion gallons of water between those three regions were in range C3:E5. And shipments for each supply point to each region were put in cell range C8:E10. These two ranges are of the same dimensions and therefore SUMPRODUCT function can be used to compute total cost here. The formula used in this case to compute total cost is “=SUMPRODUCT (C3: E5, C8: E10)” which was entered into cell G3.

So far, we have determined our objective and by changing cells. What we need to do now is to determine the explicit or implicit constraints. One constraint is that water received by each region should be equal to or greater than 100 billion gallons of water. This constraint was put in range C12:E14. The formula in cell C12 is “=SUM (C$8: C$10)”. The formula in cell D12 and E12 were copied from that in C12. Another one is that water sent from each region must be equal to or less than billion gallons of water available. We use formula “=SUM (C8: E8)” to compute total billion gallons of water sent from Northern California. By copying and pasting this formula into cells G9 and G10, we can get the amount of water sent from Central or Southern too.

Figure 1.1

Now let’s click on Data tab and then click on Solver in Analysis group to open Solver Parameters dialog box.

Fill the dialog box as given. Total shipping cost is computed by adding together the terms changing cell*constant. Two explicit constraints are created by comparing sums of changing cells with a constant. You can see that both our objective and constraints can be represented by a linear relationship. Therefore, our model is a linear model and Simplex LP engine was selected. Obviously, shipments must be positive and that’s the reason why we selected Make Unconstrained Variables Non-Negative check box.

After clicking on Solve and Ok, Excel returned below optimal solution. The minimum cost of meeting requirements is $1,580,000 and can be achieved by applying below schedule:

1. 100 billion gallons of water sent from Northern to Northern and 20 billion gallons of water sent from Northern to Sothern
2. 100 billion gallons of water sent from Central to Center and 20 billion gallons of water sent from Central to Sothern
3. Southern keep all available water for its own use

Case 2: Maximize after-tax profit with limited production capacity

A company produces and sells drugs at several locations. The decision of where to produce goods for each sales location can have a huge impact on profitability. Suppose that we need to produce drugs at six locations and sell to customers in six areas. The tax rate and variable production cost depend on where the drug is produced. The sales price of each drug depends on where the drug is sold.

Since tax rate, variable production cost and sales price relate to the location where the drugs are produced or sold, we can simplify the problem by computing profit for each plant and then add them together. Below Figure 2.1 shows how to arrange data. Shipping costs were filled into range C3:H8. The amount of drugs sent from plants to locations were entered into range C13:H18. Formula “=SUM (C13: C18)” was typed into cell C20 to calculate the number of drugs received at location 1. By copying the formula into cells from D20 through H20, we can get all the amount of drugs received at other locations. Formula “=SUM (C13: H13)” was used to compute how many drugs are produced in plant 1. To compute how many drugs are produced in other plants, the formula in cell J13 was copied into range J14: J18

I put sales price in range C9:H9 because it depends on where the drug is sold. There are entered into the same row so that I can use SUMPRODUCT function to compute gross revenue. Similarly, tax rate and variable production cost were listed in the same column because they depend on where the drugs are produced and the plant names are in one column.

The formula =SUMPRODUCT(C13:H13,$c$9:$H$9)-J13*13-SUMPRODUCT(C13:H13,C3H3))*J3 was entered into cell L3 to compute after-tax profit for Plant1. Thanks to the good data arrangement, I can copy this formula into cells L4 through L8 to calculate after-tax profit for other plants. And finally by adding the profits together using formula “=SUM (L3: L8)”, we can get total profit which is our objective.

after that perform solve perameters , Both the two explicit constraints can be represented in a linear relationship and thus I selected Simplex LP engine. The first constraint is the drugs received at each location should be equal to or greater than demands. This can be described using “$C$20:$H$20 >= $C$22:$H$22”. Another constraint is that drugs produced cannot be greater than the capacity which can be represented by “$J$13:$J$18 <= $L$13:$L$18”.

The maximum after-tax profit can be as much as $332,630,000. All six plants produce 6,000,000 drugs. For example, Drugs produced in Plant 1 should be sold only at location 1. Drugs produced in Plant 2 will be sold at location 1 and location 2.