Management Statistic

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

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"