The goal of this homework assignment is to set up and solve a Transportation Model problem in MS Excel using the Solver Add-in. MS Excel provides users with the Solver Add-in, giving them super powers to solve linear, integer, and nonlinear optimization problems. Attached is the instructions for this as

MSIS 301 Homework #5 Dr. Rice

The goal of this homework assignment is to set up and solve a Transportation Model problem in MS

Excel using the Solver Add-in. MS Excel provides users with the Solver Add-in, giving them super powers

to solve linear, integer, and nonlinear optimization problems.

The Transportation model is a special case of Linear Programming (LP). The key to solving LP on a

spreadsheet is to:

? Set up a worksheet that tracks everything of interest (e.g., costs, distance, capacity, demand)

? Identify decision variables?that is, cells that will change in solving the LP

? Identify the objective function?the target cell that will be maximized, minimized, or set to

? Identify the constraints

? Use the MS Excel Solver Add-in to solve the problem

? The optimal solution to our problem?that is, optimal values for decision variables?will be

placed on the spreadsheet

The information of interest for this problem is in Figure 1 and the MS Excel spreadsheet set-up should

be similar to Figure 2. The spreadsheet has 4 parts; (1) data table on the top, (2) decision variables

(changing cells) highlighted yellow, (3) objective function (cost), and (4) capacity/demand constraints on

the bottom. Next, we?ll use solver to find the optimal (cost minimizing) changing cells that meet the

constraints. Set up the problem and find the optimal solution user Solver and fill in the highlighted cells.

Additional information about the Transportation Model and this problem is on the next couple of pages.

A B C Factory Capacity

DM 5 4 3 100

E 8 4 3 300

FL 9 7 5 300

300 200 200

A B C Factory Capacity

DM 100

E 300

FL 300

300 200 200

Cost 0

Constraints

Capacity

DM 0 <= 100

E 0 <= 300

FL 0 <= 300

Demand

A 0 >= 300

B 0 >= 200

C 0 >= 200

Figure 1 – Problem Information

Figure 2- Excel Spreadsheet Setup

lfAANING

OBJf~TlVfS

LOC.1

LOC.2

LOC.3

LOC.4

Theproblemfacingrentalcompanieslike

Avis,Hertz,andNationaliscross-country

travel.LotsofIt.CarsrentedinNewYork

endupinChicago,carsfromL.A.cometo

Philadelphia, andcarsfromBostoncome-to

Miami.Thesceneisrepeatedinover100 .

citiesaroundtheU.S. As a result,thereare

toomanycarsinsomecitiesandtoofew in

others.Operationsmanagershavetodecide

howmanyoftheserentalsshouldbe truGked

(bycostlyautocarriers)fromeachcitywith

excesscapacitytoeachcity thatneedsmore

rentals.Theprocessrequiresquickactionfor

themosteconomicalrouting,so rentalcar

companiestumto transportationmodeling.

Transportation modeling

Aniterativeprocedureforsolving

problemsthatInvolvesminimizing

the costofshippingproductsfrom

a seriesofsourcesto a seriesof

destinations.

730

Develop an initial solution to a transportation model with the northwest-corner and intuitive

lowest-cost methods 732

Solve a problem with the stepping-stone method 734

BaJance a transportation problem 737

Deal with a problem that has degeneracy 737

Transportation Modeling

Because location of a new factory, warehouse, or distribution center is a strategic issue wirz

substantial cost implications, most companies consider and evaluate several locations. With *
wide variety of objective and subjective factors to be considered, rational decisions are aida:
by a number of techniques. One of those techniques is transportation modeling.
The transportation models described in this module prove useful when considering alternative
facility locations within the framework of an existing distribution system. Each new potential
plant, warehouse, or distribution center will require a different allocation of shipments.
depending on its own production and shipping costs and the costs of each existing facility. The
choice of a new location depends on which will yield the minimum cost for the entire system.
Transportation modeling finds the least-cost means of shipping supplies from several origins (
several destinations. Origin points (or sources) can be factories, warehouses, car rental agencies
like Avis, or any other points from which goods are shipped. Destinations are any points thz;
receive goods. To use the transportation model, we need to know the following:
1. The origin points and the capacity or supply per period at each.
2. The destination points and the demand per period at each.
3. The cost of shipping one unit from each origin to each destination.
The transportation model is one form of the linear programming models discussed in Business
Analytics Module B. Software is available to solve both transportation problems and the more
general c1ass of linear programming problems. To fully use such programs, though, you neec
to understand the assumptions that underlie the modeL To illustrate the transportation problem,
we now look at a company called Arizona Plumbing, which makes, among other products,
MODULE C I.TRANSPORTATION MODELS 731
TABLE (,1 Transportation Costs Per Bathtub for Arizona Plumbing
I
! ALBUQUERQUE I BOSTON I CLEVELAND
D~ MOII~~s _$5 =-?* $’!__

Evansville $8 $4

Fort Lauderdale J ~ -$]-

_– — ~- — —-

$3

$3

$5

a full line of bathtubs. In our example, the firm must decide which of its factories should supply

which of its warehouses. Relevant data for Arizona Plumbing are presented in Table C.l and

Figure C.l. Table C.I shows, for example, that it costs Arizona Plumbing $5 to ship one bathtub

from its Des Moines factory to its Albuquerque warehouse, $4 to Boston, and $3 to Cleveland.

Likewise, we see inFigure C.I that the 300 units required by Arizona Plumbing’s Albuquerque

warehouse may be shipped in various combinations from its Des Moines, Evansville, and

.Fort Lauderdale factories.

The first step in the modeling process is to set up a transportation matrix. Its purpose is to

summarize all relevant data and to keep track of algorithm computations. Using the information

displayed in Figure C.l and Table C.l, we can construct a transportation matrix as shown

in Figure C.2.

Des Moines

capacity

Des Moines constraint

Evansville

representing

a possible

source-todestination

shipping

assignment

(Evansville

to Cleveland)

Fort Lauderdale

700

Cost of shipping 1 unit from Fort

Lauderdale factory to Boston warehouse

Cleveland

warehouse demand

Total demand

and total supply

F.i9.:~r.~:::?~f

Transportation Problem

.f.i9.:~r.~:::?~.

Transportation Matrix for

Arizona Plumbing