Forecasting: Time series and trend analysis

profilesri999
TransportaionTemplate.xlsx
Home>Business & Finance homework help>Operations Management homework help>Forecasting: Time series and trend analysis

Sensitivity Report 1

Microsoft Excel 15.0 Sensitivity Report
Worksheet: [Transportation.xlsx]Transportation
Report Created: 2/27/2014 9:12:54 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$7 Morgan Valley 0 80 50 1E+30 80
$C$7 Morgan Town 35 0 30 50 1E+30
$D$7 Morgan Junction 0 100 60 1E+30 100
$E$7 Morgan Burg 0 50 70 1E+30 50
$B$8 Youngs Valley 30 0 20 80 1E+30
$C$8 Youngs Town 5 0 80 20 40
$D$8 Youngs Junction 25 0 10 100 1E+30
$E$8 Youngs Burg 0 20 90 1E+30 20
$B$9 Pitts Valley 0 120 100 1E+30 120
$C$9 Pitts Town 5 0 40 40 20
$D$9 Pitts Junction 0 110 80 1E+30 110
$E$9 Pitts Burg 20 0 30 20 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$B$10 Shipped Valley 30 20 30 0 30
$C$10 Shipped Town 45 80 45 0 5
$D$10 Shipped Junction 25 10 25 0 25
$E$10 Shipped Burg 20 70 20 0 5
$F$7 Morgan Used 35 -50 35 5 0
$F$8 Youngs Used 60 0 60 1E+30 0
$F$9 Pitts Used 25 -40 25 5 0

Transportation

Cost $/unit Valley Town Junction Burg Capacity
Morgan 50 30 60 70 35
Youngs 20 80 10 90 60
Pitts 100 40 80 30 25
Demand 30 45 25 20 Shipping
Solution Valley Town Junction Burg Used Cost
Morgan 0 35 0 0 35 1050
Youngs 30 5 25 0 60 1250
Pitts 0 5 0 20 25 800
Shipped 30 45 25 20 Total 3100

Sensitivity trans plus Prod

Microsoft Excel 15.0 Sensitivity Report
Worksheet: [Transportation.xlsx]Trans plus Prod
Report Created: 2/27/2014 9:30:33 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$7 Morgan Valley 0 60 170 1E+30 60
$C$7 Morgan Town 40 0 150 20 1E+30
$D$7 Morgan Junction 0 80 180 1E+30 80
$E$7 Morgan Burg 0 50 190 1E+30 50
$B$8 Youngs Valley 30 0 130 60 1E+30
$C$8 Youngs Town 0 20 190 1E+30 20
$D$8 Youngs Junction 25 0 120 80 1E+30
$E$8 Youngs Burg 0 40 200 1E+30 40
$B$9 Pitts Valley 0 100 230 1E+30 100
$C$9 Pitts Town 5 0 170 20 20
$D$9 Pitts Junction 0 90 210 1E+30 90
$E$9 Pitts Burg 20 0 160 40 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$B$10 Shipped Valley 30 130 30 10 30
$C$10 Shipped Town 45 170 45 5 5
$D$10 Shipped Junction 25 120 25 10 25
$E$10 Shipped Burg 20 160 20 5 20
$F$7 Morgan Used 40 -20 40 5 5
$F$8 Youngs Used 55 0 65 1E+30 10
$F$9 Pitts Used 25 0 30 1E+30 5

Trans plus VC

Cost $/unit Valley Town Junction Burg Capacity Production $/unit
Morgan 50 30 60 70 40 120
Youngs 20 80 10 90 65 110
Pitts 100 40 80 30 30 130
Demand 30 45 25 20 Shipping Production
Solution Valley Town Junction Burg Used Cost Cost
Morgan 0 40 0 0 40 1200 4800
Youngs 30 0 25 0 55 850 6050
Pitts 0 5 0 20 25 800 3250
Shipped 30 45 25 20 Total 2850 14100
Total Prod plus Ship 16950