Question

A paint company has four manufacturing plants (W, X, Y, and Z) that require 30,000, 20,000, 10,000, and 20,000 paint cans, respectively. Three paint can suppliers (A, B, and C) have indicated their willingness to supply up to 40,000, 20,000, and 30,000 cans per month, respectively. The total cost (shipping plus price) of delivering 100 cans from each supplier to each manufacturing plant is as below.

	Manufacturing plant
Supplier	W	X	Y	Z
A	$54	$48	$50	$46
B	52	50	54	48
C	46	48	50	52

Currently, supplier A is shipping 20,000 cans to plant X and 20,000 cans to Z. Supplier B
is shipping 30,000 cans to W, and supplier C is shipping 10,000 cans to Z. Does the present delivery arrangement minimize the cost to the paint company? If not, find a plan that does minimize total costs.

Answer 1

For the current Prices,

The below table and the formulas used in the excel provides the total cost for transportation:

The formulas used are shown below:

Now,

we will use solver to find the optimal solution for the same problem,

We will create a blank table named Table 3: Optimum Solution.

Optimum table created is shown below:

Formulas used in the optimum table are shown below:

Now we will use solver:

Click on the solver function in the Data tab in excel,

Insert the soler parameters as shown in the below image:

In the next window, Click on keep solver values.

The solved optimum table will be shown in the excel as shown below:

The optimal solution for the problem is shown above.

Hence, the current system was not optimum and the optimum price for the transportation is $37800. Which was $ 39400 in the current system.