Question

9-7 The management of the Executive Furniture Corporation decided to expand the production capacity at its Des Moines factory and to cut back the production capacities at its other two factories. It also recognizes a shifting market for its desks and revises the requirements at its three warehouses.

The table on this page provides the requirement at each of the warehouses, the capacity at each of the factories, and the shipping cost per unit to ship from each factory to each warehouse. Find the least-cost way to meet the requirements given the capacity at each factory.

TO FROM	ALBUQUERQUE	BOSTON	CLEVELAND	CAPACITY
DES MOINES	$5	$4	$3	300
EVANSVILLE	$8	$4	$3	150
FORT LAUDERDALE	$9	$7	$5	250
REQUIREMENTS	200	200	300

Could you show me how to formulate and how the Excel should look like as well?

Thank you!

Answer 1

We formulate the problem as shown:

Let the units shipped from Des Moines to Albuquerque be x11, Des Moines to Boston be x12 and so on

We get the decision variables as shown below:

Total cost of shipping will be product of shipping cost per unit * corresponding shipped unit

Total cost = 5*x11 + 4*x12 + 3*x13 + 8*x21 + 4*x22 + 3*x23 + 9*x31 + 7*x32 + 5*x33

We have to minimize this cost

We get constraints on capacity and requirements from table as shown:

x11 + x12 + x13 = 300..........Total Capacity for Des Moines

x21 + x22 + x23 = 150..........Total Capacity for Evansville

x31 + x32 + x33 = 250..........Total Capacity for Fort Lauderdale

x11 + x21 + x31 = 200..........Total Requirement for Albuquerque

x12 + x22 + x32 = 200..........Total Requirement for Boston

x13 + x23 + x33 = 300..........Total Requirement for Cleveland

All quantities are non-negative.

Hence, we get formulation as:

Minimize total cost z = 5*x11 + 4*x12 + 3*x13 + 8*x21 + 4*x22 + 3*x23 + 9*x31 + 7*x32 + 5*x33

Subject to constraints:

x11 + x12 + x13 = 300

x21 + x22 + x23 = 150

x31 + x32 + x33 = 250

x11 + x21 + x31 = 200

x12 + x22 + x32 = 200

x13 + x23 + x33 = 300

x11, x12, x13, x21, x22, x23, x31, x32, x33 >= 0

We solve the given problem in excel as shown below. We tabulate the data in excel as shown:

Since the capacity and requirements are same, it is a balanced transportation problem

We prepare the excel tables as follows using relevant formulas for requirement, capacity and total costs

The above tables in form of formulas are shown below:

We solve the given problem in excel solver as follows:

The above solution with excel solver extract is shown below:

The above-shown solution under the table Variable is the least cost way for shipping the units.