0:00

In the last video, we show how a description can be translated

into a mathematical formulation of an optimization problem.

Personally, I believe that there is a lot of value in creating these formulations

before using optimization software to solve the problem.

However, the reality is that many analysts go directly from the problem description

to the software, and skip the formulation step.

Either way you do it, ultimately,

these optimization models require specialized software.

Our optimization software is embedded in the Analytic Solver Platform and

it operates on a spreadsheet model of the problem.

So in this video, we are going to develop a spreadsheet model that can be used for

the optimization of the transportation problem.

Locate and open the Excel file titled Transportation Problem-Data.

0:50

This file contains a spreadsheet with the problem data shown in a single table.

The values in C6 to

F10 are the transportation costs from each supplier to each customer.

The values in G6 to G10 are the capacity of each supplier.

And the values in C11 to F11 are the demands.

The first thing that we're going to do is to select the entire table and copy it.

1:40

Our 20 decision variables will be in the range from C16 to F20.

In our models,

we have been using light gold to indicate that the cells are decision variables.

So we select the decision variable range and click on light gold.

The values for the decision variables will be determined by the optimization process.

But it is good practice when building models to

enter some values to help us verify that our spreadsheet model works.

So enter the solution that I showed to you in the introduction to this module.

In that solution, supplier A sends its 50 units to customer 4.

Therefore, enter 50 in cell F16.

2:22

Supplier B sends 20 units to customer 2 and 20 units to customer 3.

Enter 20 in cell D17 and 20 in E17.

Supplier C sends its 60 units to customer 1.

Enter 60 in C18.

Supplier D sends 15 units to customer 1 and 15 units to customer 3.

Enter 15 in cell C19 and 15 in cell E19.

Finally, supplier E sends 5 units to customer 2 and 15 to customer 4.

So enter 5 in D20 and 15 in F20.

3:06

We have a complete solution, but we need to verify that it is feasible.

A feasible solution is one that satisfies all the constraints.

Let's first see if the solution satisfies the capacity constraints.

We first change the title from Capacity to Shipped in cell G14.

The cells in the range from D16 to D20 will contain

the total number of units shipped from each supplier.

To calculate what a supplier ships,

add all the quantities that are shipped to all the customers.

This can be done with the sum function.

Go to cell G16 and enter =sum(,

and select the range C16 to F16 and close the parentheses.

3:54

Cells G16 should now show 50.

Select G16 and

copy the cell down to G20 by dragging it from the bottom right corner marker.

The amounts shipped should not exceed the supplier capacities shown

on the data table.

This tells us that the solution is feasible with

respect to the capacity constraints.

We are now going to verify that the demand constraints are satisfied.

We start by changing the title Demand in B21 to Received.

Then, in cell C21, use again the function sum to calculate

the total number of units that customer 1 will receive.

Enter =sum(, then select the range

C16 to C20 and close parentheses.

Then, copy the formula by dragging it from the bottom right corner of C21 to F21.

The values received by the customers should be

at least as large as the demand values on the data table.

This means that the solution is also feasible with respect

to the demand constraints.

Now we only need to add the objective function.

Recall that the objective in the model

is to find a feasible shipment that minimize the total cost.

Also recall that we calculated the total cost as the sum of the products

of unit shipped multiplied by cost per unit.

Excel includes a very useful function to calculate this in one step.

The function is called sumproduct.

Go to cell B23 and enter Total Cost.

Then go to cell C23 and enter =sumproduct( and

first select the cost in the range C6 to F10.

5:48

Enter a comma, and then select the shipments in cells C16

to F20 and close parentheses.

After pressing Enter,

you will see that the function correctly calculates the total cost of 1085.

As the last step, we will add some useful formatting to the objective function,

and we will also define names for the cells in our model.

Select cells B23 and C23 and add borders,

6:33

click the name box at the left end of the formula bar, and type Capacity.

Select the range C11 to F11, click the name box, and type Demand.

Select the range C16 to F20, click the name box, and type Shipments.

6:54

Select the range G12 to G20, click the name box, and type Shipped.

Select the range C21 to F21, click the name box, and type Received.

And then select the cell C23, click the name box, and type Total_Cost.

Our spreadsheet model is able to evaluate the total cost of any set of shipments.

We just need to change the values of the decision variables.

And the spreadsheet calculates the total cost and

updates the quantities shipped and received.

We could do some What-If Analysis in this way.

But it wouldn't be practical to try to find the minimum cost solution

by manually changing the shipment values.

There are just way too many shipment combinations.