Answer To: 11.16 Consider the following transportation problem. What is the cost minimizing distribution plan?...
Himanshu answered on Jun 04 2021
11.16
Consider the following transportation problem.
What is the cost minimizing distribution plan? Please show your selections in Solver.
Design
Parameters
Per Unit Shipping Cost
Des Moines Kansas City St.Louis Supply
Jefferson City $14.00 $16.00 $7.00 30
Omaha $8.00 $10.00 $5.00 20
Demand 25 15 10
Model
Des Moines Kansas City St.Louis Total Shipped
Jefferson City 0 0 0
Omaha 0 0 0
Total Shipped
Total Cost
Solution Total demand 50
Parameters Total supply 50
Per Unit Shipping Cost Balanced Problem
Des Moines Kansas City St.Louis Supply
Jefferson City $14.00 $16.00 $7.00 30
Omaha $8.00 $10.00 $5.00 20
Demand 25 15 10
Model
Des Moines Kansas City St.Louis LHS RHS
Jefferson City 20 0 10 30 30
Omaha 5 15 0 20 20
LHS 25 15 10
RHS 25 15 10
Total Cost $540
Answer Report 1
Microsoft Excel 14.0 Answer Report
Worksheet: [Solution 85785.xlsx]11.16
Report Created: 04-06-2021 05:19:27
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.046 Seconds.
Iterations: 6 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min)
Cell Name Original Value Final Value
$B$41 Total Cost Des Moines ₹0 ₹540
Variable Cells
Cell Name Original Value Final Value Integer
$B$36 Jefferson City Des Moines 0 20 Contin
$C$36 Jefferson City Kansas City 0 0 Contin
$D$36 Jefferson City St.Louis 0 10 Contin
$B$37 Omaha Des Moines 0 5 Contin
$C$37 Omaha Kansas City 0 15 Contin
$D$37 Omaha St.Louis 0 0 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$B$38 LHS Des Moines 25 $B$38=$B$39 Binding 0
$C$38 LHS Kansas City 15 $C$38=$C$39 Binding 0
$D$38 LHS St.Louis 10 $D$38=$D$39 Binding 0
$E$36 Jefferson City LHS 30 $E$36=$F$36 Binding 0
$E$37 Omaha LHS 20 $E$37=$F$37 Binding 0
11.18.
The following gives the demand in 10 cities for power supplied from Los Angeles, Tulsa and Seattle power plants, as well as the associated distribution costs.
Aggie Power
Parameters
Distribution Costs
Los Angeles Tulsa Seattle Demand Requested (Mwatts)
Seattle $ 356.25 $ 593.75 $ 59.38 950
Portland $ 356.25 $ 593.75 $ 178.13 831.25
San Francisco $ 178.13 $ 475.00 $ 296.88 2375
Boise $ 356.25 $ 475.00 $ 296.88 593.75
Reno $ 237.50 $ 475.00 $ 356.25 950
Bozeman $ 415.63 $ 415.63 $ 296.88 593.75
Laramie $ 356.25 $ 415.63 $ 356.25 1187.5
Park City $ 356.25 $ 356.25 $ 475.00 712.5
Flagstaff $ 178.13 $ 475.00 $ 593.75 1187.5
Durango $ 356.25 $ 296.88 $ 593.75 1543.75
A. If there are no restrictions on the amount supplied from any power plant, what is the solution that minimizes the distribution costs and meets demand in each city? What is the minimized cost?
Model
Los Angeles Tulsa Seattle LHS Demand
Seattle 0 0 950 950 950
Portland 0 0 831.25 831.25 831.25 Demand
San Francisco 2375 0 0 2375 2375 Supply
Boise 0 0 593.75 593.75 593.75
Reno 950 0 0 950 950
Bozeman 0 0 593.75 593.75 593.75
Laramie 1187.5 0 0 1187.5 1187.5
Park City 0 712.5 0 712.5 712.5
Flagstaff 1187.5 0 0 1187.5 1187.5
Durango 0 1543.75 0 1543.75 1543.75
Total 5700 2256.25 2968.75
Cost $2,552,382.81
B. If at most 4000 can be supplied by any one of the power plants, what is the solution? By how much does the costs increase? Please show your selections in Solver.
Plant Capacity 4000
Model
Los Angeles Tulsa Seattle LHS
Seattle 0 0 950 950 950
Portland 0 0 831.25 831.25 831.25
San Francisco 2375 0 0 2375 2375 Cost increase 60,859.375
Boise 0 0 593.75 593.75 593.75
Reno 437.5 0 512.5 950 950
Bozeman 0 0 593.75 593.75 593.75
Laramie 0 0 1187.5 1187.5 1187.5
Park City 0 712.5 0 712.5 712.5
Flagstaff 1187.5 0 0 1187.5 1187.5
Durango 0 1543.75 0 1543.75 1543.75
LHS 4000 2256.25 4668.75
Cost $2,613,242.19
Answer Report 2
Microsoft Excel 14.0 Answer Report
Worksheet: [Solution 85785.xlsx]11.18.
Report Created: 04-06-2021 05:26:59
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.015 Seconds.
Iterations: 17 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min)
Cell Name Original Value Final Value
$B$35 Cost Los Angeles ₹0.00 ₹2,552,382.81
Variable Cells
Cell Name Original Value Final Value Integer
$B$22 Seattle Los Angeles 0 0 Contin
$C$22 Seattle Tulsa 0 0 Contin
$D$22 Seattle Seattle 0 950 Contin
$B$23 Portland Los Angeles 0 0 Contin
$C$23 Portland Tulsa 0 0 Contin
$D$23 Portland Seattle 0 831.25 Contin
$B$24 San Francisco Los Angeles 0 2375 Contin
$C$24 San Francisco Tulsa 0 0 Contin
$D$24 San Francisco Seattle 0 0 Contin
$B$25 Boise Los Angeles 0 0 Contin
$C$25 Boise Tulsa 0 0 Contin
$D$25 Boise Seattle 0 593.75 Contin
$B$26 Reno Los Angeles 0 950 Contin
$C$26 Reno Tulsa 0 0 Contin
$D$26 Reno Seattle 0 0 Contin
$B$27 Bozeman Los Angeles 0 0 Contin
$C$27 Bozeman Tulsa 0 0 Contin
$D$27 Bozeman Seattle 0 593.75 Contin
$B$28 Laramie Los Angeles 0 1187.5 Contin
$C$28 Laramie Tulsa 0 0 Contin
$D$28 Laramie Seattle 0 0 Contin
$B$29 Park City Los Angeles 0 0 Contin
$C$29 Park City Tulsa 0 712.5 Contin
$D$29 Park City Seattle 0 0 Contin
$B$30 Flagstaff Los Angeles 0 1187.5 Contin
$C$30 Flagstaff Tulsa 0 0 Contin
$D$30 Flagstaff Seattle 0 0 Contin
$B$31 Durango Los Angeles 0 0 Contin
$C$31 Durango Tulsa 0 1543.75 Contin
$D$31 Durango Seattle 0 0 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$E$22 Seattle LHS 950 $E$22=$F$22 Binding 0
$E$23 Portland LHS 831.25 $E$23=$F$23 Binding 0
$E$24 San Francisco LHS 2375 $E$24=$F$24 Binding 0
$E$25 Boise LHS 593.75 $E$25=$F$25 Binding 0
$E$26 Reno LHS 950 $E$26=$F$26 Binding 0
$E$27 Bozeman LHS 593.75 $E$27=$F$27 Binding 0
$E$28 Laramie LHS 1187.5 $E$28=$F$28 Binding 0
$E$29 Park City LHS 712.5 $E$29=$F$29 Binding 0
$E$30 Flagstaff LHS 1187.5 $E$30=$F$30 Binding 0
$E$31 Durango LHS 1543.75 $E$31=$F$31 Binding 0
Answer Report 3
Microsoft Excel 14.0 Answer Report
Worksheet: [Solution 85785.xlsx]11.18.
Report Created: 04-06-2021 05:33:23
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.015 Seconds.
Iterations: 22 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min)
Cell Name Original Value Final Value
$B$55 Cost Los Angeles ₹0.00 ₹2,613,242.19
Variable Cells
Cell Name Original Value Final Value Integer
$B$42 Seattle Los Angeles 0 0 Contin
$C$42 Seattle Tulsa 0 0 Contin
$D$42 Seattle Seattle 0 950 Contin
$B$43 Portland Los Angeles 0 0 Contin
$C$43 Portland Tulsa 0 0 Contin
$D$43 Portland Seattle 0 831.25 Contin
$B$44 San Francisco Los Angeles 0 2375 Contin
$C$44 San Francisco Tulsa 0 0 Contin
$D$44 San Francisco Seattle 0 0 Contin
$B$45 Boise Los Angeles 0 0 Contin
$C$45 Boise Tulsa 0 0 Contin
$D$45 Boise Seattle 0 593.75 Contin
$B$46 Reno Los Angeles 0 437.5 Contin
$C$46 Reno Tulsa 0 0 Contin
$D$46 Reno Seattle 0 512.5 Contin
$B$47 Bozeman Los Angeles 0 0 Contin
$C$47 Bozeman Tulsa 0 0 Contin
$D$47 Bozeman Seattle 0 593.75 Contin
$B$48 Laramie Los Angeles 0 0 Contin
$C$48 Laramie Tulsa 0 0 Contin
$D$48 Laramie Seattle 0 1187.5 Contin
$B$49 Park City Los Angeles 0 0 Contin
$C$49 Park City Tulsa 0 712.5 Contin
$D$49 Park City Seattle 0 0 Contin
$B$50 Flagstaff Los Angeles 0 1187.5 Contin
$C$50 Flagstaff Tulsa 0 0 Contin
$D$50 Flagstaff Seattle 0 0 Contin
$B$51 Durango Los Angeles 0 0 Contin
$C$51 Durango Tulsa 0 1543.75 Contin
$D$51 Durango Seattle 0 0 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$B$52 LHS Los Angeles 4000 $B$52<=4000 Binding 0
$E$42 Seattle LHS 950 $E$42=$F$42 Binding 0
$E$43 Portland LHS 831.25 $E$43=$F$43 Binding 0
$E$44 San Francisco LHS 2375 $E$44=$F$44 Binding 0
$E$45 Boise LHS 593.75 $E$45=$F$45 Binding 0
$E$46 Reno LHS 950 $E$46=$F$46 Binding 0
$E$47 Bozeman LHS 593.75 $E$47=$F$47 Binding 0
$E$48 Laramie LHS 1187.5 $E$48=$F$48 Binding 0
$E$49 Park City LHS 712.5 $E$49=$F$49 Binding 0
$E$50 Flagstaff LHS 1187.5 $E$50=$F$50 Binding 0
$E$51 Durango LHS 1543.75 $E$51=$F$51 Binding 0
11.19.
Calhoun Mills needs to decide on a production schedule.
There are two looms: Dobbie and Regular, and 15 fabrics. Demand for fabrics are shown, as well as the variable cost for each fabric. Rate of production for each fabric on each type of loom is also given.
There are 90 regular looms and 15 Dobbie looms.
They need to know how to allocate the looms to the fabrics and which fabrics to purchase outside to minimize the demand. What is the minimized total cost?
Please show your selections in Solver.
Parameters
Dobbie Regular
Fabric Demand (yd) Dobbie Rate (yd/hr) Regular Rate (yd/hr) Hrs./Yd. Hrs./Yd. Mill Cost ($/yd) Cost to purchase outside ($/yd)
1 16500 4.65 0.00 0.21 0.00 0.66 0.80
2 52000 4.65 0.00 0.21 0.00 0.56 0.70
3 45000 4.65 0.00 0.21 0.00 0.66 0.85
4 22000 4.65 0.00 0.21 0.00 0.55 0.70
5 76500 5.19 5.19 0.19 0.19 0.61 0.75
6 110000 3.81 3.81 0.26 0.26 0.62 0.75
7 122000 4.19 4.19 0.24 0.24 0.65 0.80
8 62000 5.23 5.23 0.19 0.19 0.49 0.60
9 7500 5.23 5.23 0.19 0.19 0.50 0.70
10 69000 5.23 5.23 0.19 0.19 0.44 0.60
11 70000 3.73 3.73 0.27 0.27 0.64 0.80
12 82000 4.19 4.19 0.24 0.24 0.57 0.75
13 10000 4.44 4.44 0.23 0.23 0.50 0.65
14 380000 5.23 5.23 0.19 0.19 0.31 0.45
15 62000 4.19 4.19 0.24 0.24 0.50 0.70
Hours Available
Dobbie 32760.00
Regular 196560.00
Model
Hrs. Hrs.
Fabric Dobbie Regular Purchase Outside Total Cost Used Leftover
1 0.00 0.00 16500.00 16500.00 $13,200 Dobbie Hrs. 32760 0
2 0.00 0.00 52000.00 52000.00 $36,400 Reg. Hrs. 196560 0
3 0.00 0.00 45000.00 45000.00 $38,250
4 0.00 0.00 22000.00 22000.00 $15,400
5 0.00 0.00 76500.00 76500.00 $57,375
6 0.00 0.00 110000.00 110000.00 $82,500
7 0.00 0.00 122000.00 122000.00 $97,600
8 0.00 0.00 62000.00 62000.00 $37,200
9 15.00 0.00 7485.00 7500.00 $5,247
10 0.00 0.00 69000.00 69000.00 $41,400
11 0.00 0.00 70000.00 70000.00 $56,000
12 0.00 0.00 82000.00 82000.00 $61,500
13 0.00 0.00 10000.00 10000.00 $6,500
14 0.00 0.00 380000.00 380000.00 $171,000
15 0.00 90.00 61910.00 62000.00 $43,382
15.00 90.00 Total $762,954.30
Answer Report 5
Microsoft Excel 14.0 Answer Report
Worksheet: [Solution 85785.xlsx]11.19.
Report Created: 04-06-2021 14:08:42
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.016 Seconds.
Iterations: 37 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min)
Cell Name Original Value Final Value
$F$48 Total Cost ₹ 0.00 ₹ 762,954.30
Variable Cells
Cell Name Original Value Final Value Integer
$B$33 Dobbie 0.00 0.00 Contin
$C$33 Regular 0.00 0.00 Contin
$D$33 Purchase...