Description
This is a question that is supposed to be answered with the use of solver, I have attached a spreadsheet template and the question that needs to be answered.
Unformatted Attachment Preview
Name:
Maximum Capacity
Plant
Akron
Greensboro
Topeka
Salt Lake City
(100,000 lbs.)
Prod. Costs
(1,000 lbs.)
17.5
13
14
22.3
$175.00
$225.00
$205.00
$155.00
Woburn
$109
$167
$184
$198
825
Louisville
$140
$125
$161
$175
1055
Woburn
Louisville
This sheet contains the data
from your problem and the template
to build a Solver model.
Type your name(s) here!
Transport Costs per 1,000 lbs.
From/To
Akron
Greensboro
Topeka
Salt Lake City
Total Demand (x 1,000 lbs.)
Minneapolis
$155
$147
$121
$169
1125
Dallas
$198
$173
$143
$140
975
Seattle
$213
$224
$183
$140
1350
Dallas
Seattle
Combined Costs per 1,000 lbs.
From/To
Minneapolis
Akron
Greensboro
Topeka
Salt Lake City
Total
Shipped
Decision Variables
From/To
Woburn
Louisville
Woburn
Louisville
Minneapolis
Dallas
Seattle
Dallas
Seattle
Akron
Greensboro
Topeka
Salt Lake City
Total Supplied:
Solution Costs
From/To
Minneapolis
Akron
Greensboro
Topeka
Salt Lake City
Total Solution Cost:
Put your answer to part b) in this text box. Delete this text and replace it with your answer. The box will autoexpand to fix your answer.
Transportation Problem with Solver
Part 1: Transportation Problem with Solver
Sycamore Plastic Products (SPP) is a manufacturer of various raw materials used by manufacturers of a wide
variety of plastic products. One particular product line is polyethylene pellets. Manufacturers will use this as the
base plastic in molded products. The pellets are melted down and then formed by various processes into the end
product. Coloring can be added to produce any color plastic the end item manufacturer wants. SPP currently
operates four manufacturing centers in Akron OH, Greensboro NC, Topeka KS, and Salt Lake City UT. The plants
have different capacities and production costs as indicated in the table below.
Maximum Capacity
(x 100,000 lbs.)
17.5
13.0
14.0
22.3
Plant
Akron
Greensboro
Topeka
Salt Lake City
Prod. Costs
(per 1,000 lbs.)
$175.00
$225.00
$205.00
$155.00
SPP currently has five contract customers located in Woburn MA, Louisville KY, Minneapolis MN, Dallas TX, and
Seattle WA. Transportation costs between the plants and various customers, as well as contracted demand from
each customer is shown in the table below.
From/To
Akron
Greensboro
Topeka
Salt Lake City
Woburn
$109.00
$167.00
$184.00
$198.00
Total Demand (x 1,000 lbs.)
825
a)
Transport Costs per 1,000 lbs.
Louisville
Minneapolis
Dallas
$140.00
$155.00
$198.00
$125.00
$147.00
$173.00
$161.00
$121.00
$143.00
$175.00
$169.00
$140.00
1055
1125
975
Seattle
$213.00
$224.00
$183.00
$140.00
1350
Using the spreadsheet template at the Canvas site, create a solver model to help SPP develop a
distribution plan that will minimize costs to supply the customers’ demand. Be sure to deal with the
difference in scale of some of the data.
b) Comment briefly on your solution. Beyond the obvious, does your proposed solution have any other
implications for SPP? Use the text box on your spreadsheet just under the model to write your answer.
Purchase answer to see full
attachment
