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.

Â