Help with Optimization and Decision Support Modeling for Business project

profileRedhand
Case06-03_Template.xlsx
Home>Business & Finance homework help>Operations Management homework help>Help with Optimization and Decision Support Modeling for Business project

Case 6-3 a&b

Scheduling Flights at Northwest Commuter
Fixed Daily Cost of Operating Airplane 30 ($thousand)
Overnight Flight Cost 5 ($thousand)
Flight # From To Depart Arrive Revenue From To Flow Nodes Net Flow Supply/Demand Range Name Cells
1257 Seattle San Francisco 8:00am 10:00am 37 SEA0800 SFO1000 <= SEA0800 = FixedDailyCost F3
2576 Seattle Portland 9:30am 10:30am 20 SEA0930 POR1030 <= SEA0830 = FlightsFlown D31
8312 Seattle San Francisco 9:30am 11:30am 25 SEA0930 SFO1130 <= SEA0900 = Flow J7:J109
1109 Seattle San Francisco 12:00pm 2:00pm 27 SEA1200 SFO1400 <= SEA0930 = From H7:H109
3752 Seattle San Francisco 2:30pm 4:30pm 23 SEA1430 SFO1630 <= SEA1000 = MaxFlights L7:L28
2498 Seattle Portland 3:00pm 4:00pm 18 SEA1500 POR1600 <= SEA1030 = NetFlow O7:O81
8787 Seattle San Francisco 5:00pm 7:00pm 29 SEA1700 SFO1900 <= SEA1100 = NetProfit D38
8423 Seattle Portland 6:30pm 7:30pm 27 SEA1830 POR1930 <= SEA1130 = Nodes N81
7922 Portland Seattle 9:00am 10:00am 20 POR0900 SEA1000 <= SEA1200 = OvernightFlightCost F4
5623 Portland San Francisco 9:30am 11:00am 23 POR0930 SFO1100 <= SEA1230 = OvernightFlights D32
2448 Portland San Francisco 11:00am 12:30pm 19 POR1100 SFO1230 <= SEA1300 = PlanesOwned F33
1842 Portland Seattle 12:00pm 1:00pm 21 POR1200 SEA1300 <= SEA1330 = PlanesUsed D33
3487 Portland Seattle 2:00pm 3:00pm 22 POR1400 SEA1500 <= SEA1400 = SupplyDemand Q7:Q81
4361 Portland San Francisco 4:00pm 5:30pm 29 POR1600 SFO1730 <= SEA1430 = To I7:I109
4299 Portland Seattle 6:00pm 7:00pm 27 POR1800 SEA1900 <= SEA1500 = TotalFixedCost D36
1288 San Francisco Seattle 8:00am 10:00am 32 SFO0800 SEA1000 <= SEA1530 = TotalNetRevenue D35
3335 San Francisco Portland 8:30am 10:00am 26 SFO0830 POR1000 <= SEA1600 =
9348 San Francisco Seattle 10:30am 12:30pm 24 SFO1030 SEA1230 <= SEA1630 =
7400 San Francisco Seattle 12:00pm 2:00pm 27 SFO1200 SEA1400 <= SEA1700 =
7328 San Francisco Portland 12:00pm 1:30pm 24 SFO1200 POR1330 <= SEA1730 =
6386 San Francisco Portland 4:00pm 5:30pm 28 SFO1600 POR1730 <= SEA1800 =
6923 San Francisco Seattle 5:00pm 7:00pm 32 SFO1700 SEA1900 <= SEA1830 =
Ground Arcs: SEA0800 SEA0830 SEA1900 =
SEA0830 SEA0900 SEA1930 =
Flights Flown SEA0900 SEA0930 SEA2000 =
Overnight Flights Planes Owned SEA0930 SEA1000 POR0800 =
Planes Used <= SEA1000 SEA1030 POR0830 =
SEA1030 SEA1100 POR0900 =
Total Net Revenue SEA1100 SEA1130 POR0930 =
Total Fixed Cost SEA1130 SEA1200 POR1000 =
Overnight Flight Cost SEA1200 SEA1230 POR1030 =
Net Profit SEA1230 SEA1300 POR1100 =
($thousand/day) SEA1300 SEA1330 POR1130 =
SEA1330 SEA1400 POR1200 =
SEA1400 SEA1430 POR1230 =
SEA1430 SEA1500 POR1300 =
Planes Owned Net Profit SEA1500 SEA1530 POR1330 =
ERROR:#NAME? SEA1530 SEA1600 POR1400 =
4 306.9999999999 SEA1600 SEA1630 POR1430 =
5 351 SEA1630 SEA1700 POR1500 =
6 355 SEA1700 SEA1730 POR1530 =
7 355 SEA1730 SEA1800 POR1600 =
SEA1800 SEA1830 POR1630 =
SEA1830 SEA1900 POR1700 =
SEA1900 SEA1930 POR1730 =
SEA1930 SEA2000 POR1800 =
POR0800 POR0830 POR1830 =
POR0830 POR0900 POR1900 =
POR0900 POR0930 POR1930 =
POR0930 POR1000 POR2000 =
POR1000 POR1030 SFO0800 =
POR1030 POR1100 SFO0830 =
POR1100 POR1130 SFO0900 =
POR1130 POR1200 SFO0930 =
POR1200 POR1230 SFO1000 =
POR1230 POR1300 SFO1030 =
POR1300 POR1330 SFO1100 =
POR1330 POR1400 SFO1130 =
POR1400 POR1430 SFO1200 =
POR1430 POR1500 SFO1230 =
POR1500 POR1530 SFO1300 =
POR1530 POR1600 SFO1330 =
POR1600 POR1630 SFO1400 =
POR1630 POR1700 SFO1430 =
POR1700 POR1730 SFO1500 =
POR1730 POR1800 SFO1530 =
POR1800 POR1830 SFO1600 =
POR1830 POR1900 SFO1630 =
POR1900 POR1930 SFO1700 =
POR1930 POR2000 SFO1730 =
SFO0800 SFO0830 SFO1800 =
SFO0830 SFO0900 SFO1830 =
SFO0900 SFO0930 SFO1900 =
SFO0930 SFO1000 SFO1930 =
SFO1000 SFO1030 SFO2000 =
SFO1030 SFO1100
SFO1100 SFO1130
SFO1130 SFO1200
SFO1200 SFO1230
SFO1230 SFO1300
SFO1300 SFO1330
SFO1330 SFO1400
SFO1400 SFO1430
SFO1430 SFO1500
SFO1500 SFO1530
SFO1530 SFO1600
SFO1600 SFO1630
SFO1630 SFO1700
SFO1700 SFO1730
SFO1730 SFO1800
SFO1800 SFO1830
SFO1830 SFO1900
SFO1900 SFO1930
SFO1930 SFO2000
Overnight Arcs: SEA2000 SEA0800
POR2000 POR0800
SFO2000 SFO0800
SEA2000 POR0800
SEA2000 SFO0800
POR2000 SEA0800
POR2000 SFO0800
SFO2000 SEA0800
SFO2000 POR0800

Case 6-3 c

Scheduling Flights at Northwest Commuter
Fixed Daily Cost of Operating Airplane 30 ($thousand)
Overnight Flight Cost 5 ($thousand)
Flight # From To Depart Arrive Revenue From To Flow Nodes Net Flow Supply/Demand Range Name Cells
1257 Seattle San Francisco 8:00am 10:00am 37 SEA0800 SFO1030 <= SEA0800 = FixedDailyCost F3
2576 Seattle Portland 9:30am 10:30am 20 SEA0930 POR1100 <= SEA0830 = FlightsFlown D31
8312 Seattle San Francisco 9:30am 11:30am 25 SEA0930 SFO1200 <= SEA0900 = Flow J7:J109
1109 Seattle San Francisco 12:00pm 2:00pm 27 SEA1200 SFO1430 <= SEA0930 = From H7:H109
3752 Seattle San Francisco 2:30pm 4:30pm 23 SEA1430 SFO1700 <= SEA1000 = MaxFlights L7:L28
2498 Seattle Portland 3:00pm 4:00pm 18 SEA1500 POR1630 <= SEA1030 = NetFlow O7:O81
8787 Seattle San Francisco 5:00pm 7:00pm 29 SEA1700 SFO1930 <= SEA1100 = NetProfit D38
8423 Seattle Portland 6:30pm 7:30pm 27 SEA1830 POR2000 <= SEA1130 = Nodes N81
7922 Portland Seattle 9:00am 10:00am 20 POR0900 SEA1030 <= SEA1200 = OvernightFlightCost F4
5623 Portland San Francisco 9:30am 11:00am 23 POR0930 SFO1130 <= SEA1230 = OvernightFlights D32
2448 Portland San Francisco 11:00am 12:30pm 19 POR1100 SFO1300 <= SEA1300 = PlanesOwned F33
1842 Portland Seattle 12:00pm 1:00pm 21 POR1200 SEA1330 <= SEA1330 = PlanesUsed D33
3487 Portland Seattle 2:00pm 3:00pm 22 POR1400 SEA1530 <= SEA1400 = SupplyDemand Q7:Q81
4361 Portland San Francisco 4:00pm 5:30pm 29 POR1600 SFO1800 <= SEA1430 = To I7:I109
4299 Portland Seattle 6:00pm 7:00pm 27 POR1800 SEA1930 <= SEA1500 = TotalFixedCost D36
1288 San Francisco Seattle 8:00am 10:00am 32 SFO0800 SEA1030 <= SEA1530 = TotalNetRevenue D35
3335 San Francisco Portland 8:30am 10:00am 26 SFO0830 POR1030 <= SEA1600 =
9348 San Francisco Seattle 10:30am 12:30pm 24 SFO1030 SEA1300 <= SEA1630 =
7400 San Francisco Seattle 12:00pm 2:00pm 27 SFO1200 SEA1430 <= SEA1700 =
7328 San Francisco Portland 12:00pm 1:30pm 24 SFO1200 POR1400 <= SEA1730 =
6386 San Francisco Portland 4:00pm 5:30pm 28 SFO1600 POR1800 <= SEA1800 =
6923 San Francisco Seattle 5:00pm 7:00pm 32 SFO1700 SEA1930 <= SEA1830 =
Ground Arcs: SEA0800 SEA0830 SEA1900 =
SEA0830 SEA0900 SEA1930 =
Flights Flown SEA0900 SEA0930 SEA2000 =
Overnight Flights Planes Owned SEA0930 SEA1000 POR0800 =
Planes Used <= SEA1000 SEA1030 POR0830 =
SEA1030 SEA1100 POR0900 =
Total Net Revenue SEA1100 SEA1130 POR0930 =
Total Fixed Cost SEA1130 SEA1200 POR1000 =
Overnight Flight Cost SEA1200 SEA1230 POR1030 =
Net Profit SEA1230 SEA1300 POR1100 =
($thousand/day) SEA1300 SEA1330 POR1130 =
SEA1330 SEA1400 POR1200 =
SEA1400 SEA1430 POR1230 =
SEA1430 SEA1500 POR1300 =
SEA1500 SEA1530 POR1330 =
SEA1530 SEA1600 POR1400 =
SEA1600 SEA1630 POR1430 =
SEA1630 SEA1700 POR1500 =
SEA1700 SEA1730 POR1530 =
SEA1730 SEA1800 POR1600 =
SEA1800 SEA1830 POR1630 =
SEA1830 SEA1900 POR1700 =
SEA1900 SEA1930 POR1730 =
SEA1930 SEA2000 POR1800 =
POR0800 POR0830 POR1830 =
POR0830 POR0900 POR1900 =
POR0900 POR0930 POR1930 =
POR0930 POR1000 POR2000 =
POR1000 POR1030 SFO0800 =
POR1030 POR1100 SFO0830 =
POR1100 POR1130 SFO0900 =
POR1130 POR1200 SFO0930 =
POR1200 POR1230 SFO1000 =
POR1230 POR1300 SFO1030 =
POR1300 POR1330 SFO1100 =
POR1330 POR1400 SFO1130 =
POR1400 POR1430 SFO1200 =
POR1430 POR1500 SFO1230 =
POR1500 POR1530 SFO1300 =
POR1530 POR1600 SFO1330 =
POR1600 POR1630 SFO1400 =
POR1630 POR1700 SFO1430 =
POR1700 POR1730 SFO1500 =
POR1730 POR1800 SFO1530 =
POR1800 POR1830 SFO1600 =
POR1830 POR1900 SFO1630 =
POR1900 POR1930 SFO1700 =
POR1930 POR2000 SFO1730 =
SFO0800 SFO0830 SFO1800 =
SFO0830 SFO0900 SFO1830 =
SFO0900 SFO0930 SFO1900 =
SFO0930 SFO1000 SFO1930 =
SFO1000 SFO1030 SFO2000 =
SFO1030 SFO1100
SFO1100 SFO1130
SFO1130 SFO1200
SFO1200 SFO1230
SFO1230 SFO1300
SFO1300 SFO1330
SFO1330 SFO1400
SFO1400 SFO1430
SFO1430 SFO1500
SFO1500 SFO1530
SFO1530 SFO1600
SFO1600 SFO1630
SFO1630 SFO1700
SFO1700 SFO1730
SFO1730 SFO1800
SFO1800 SFO1830
SFO1830 SFO1900
SFO1900 SFO1930
SFO1930 SFO2000
Overnight Arcs: SEA2000 SEA0800
POR2000 POR0800
SFO2000 SFO0800
SEA2000 POR0800
SEA2000 SFO0800
POR2000 SEA0800
POR2000 SFO0800
SFO2000 SEA0800
SFO2000 POR0800

Case 6-3 d

Scheduling Flights at Northwest Commuter
Fixed Daily Cost of Operating Airplane 30 ($thousand)
Overnight Flight Cost 5 ($thousand)
Flight # From To Depart Arrive Revenue From To Flow Nodes Net Flow Supply/Demand Range Name Cells
1257 Seattle San Francisco 8:00am 10:00am 37 SEA0800 SFO1030 <= SEA0800 = FixedDailyCost F3
2576 Seattle Portland 9:30am 10:30am 20 SEA0930 POR1100 <= SEA0830 = FlightsFlown D31
8312 Seattle San Francisco 9:30am 11:30am 25 SEA0930 SFO1200 <= SEA0900 = Flow J7:J109
1109 Seattle San Francisco 12:00pm 2:00pm 27 SEA1200 SFO1430 <= SEA0930 = From H7:H109
3752 Seattle San Francisco 2:30pm 4:30pm 23 SEA1430 SFO1700 <= SEA1000 = MaxFlights L7:L28
2498 Seattle Portland 3:00pm 4:00pm 18 SEA1500 POR1630 <= SEA1030 = NetFlow O7:O81
8787 Seattle San Francisco 5:00pm 7:00pm 29 SEA1700 SFO1930 <= SEA1100 = NetProfit D38
8423 Seattle Portland 6:30pm 7:30pm 27 SEA1830 POR2000 <= SEA1130 = Nodes N81
7922 Portland Seattle 9:00am 10:00am 20 POR0900 SEA1030 <= SEA1200 = OvernightFlightCost F4
5623 Portland San Francisco 9:30am 11:00am 23 POR0930 SFO1130 <= SEA1230 = OvernightFlights D32
2448 Portland San Francisco 11:00am 12:30pm 19 POR1100 SFO1300 <= SEA1300 = PlanesOwned F33
1842 Portland Seattle 12:00pm 1:00pm 21 POR1200 SEA1330 <= SEA1330 = PlanesUsed D33
3487 Portland Seattle 2:00pm 3:00pm 22 POR1400 SEA1530 <= SEA1400 = SupplyDemand Q7:Q81
4361 Portland San Francisco 4:00pm 5:30pm 29 POR1600 SFO1800 <= SEA1430 = To I7:I109
4299 Portland Seattle 6:00pm 7:00pm 27 POR1800 SEA1930 <= SEA1500 = TotalFixedCost D36
1288 San Francisco Seattle 8:00am 10:00am 32 SFO0800 SEA1030 <= SEA1530 = TotalNetRevenue D35
3335 San Francisco Portland 8:30am 10:00am 26 SFO0830 POR1030 <= SEA1600 =
9348 San Francisco Seattle 10:30am 12:30pm 24 SFO1030 SEA1300 <= SEA1630 =
7400 San Francisco Seattle 12:00pm 2:00pm 27 SFO1200 SEA1430 <= SEA1700 =
7328 San Francisco Portland 12:00pm 1:30pm 24 SFO1200 POR1400 <= SEA1730 =
6386 San Francisco Portland 4:00pm 5:30pm 28 SFO1600 POR1800 <= SEA1800 =
6923 San Francisco Seattle 5:00pm 7:00pm 32 SFO1700 SEA1930 <= SEA1830 =
Ground Arcs: SEA0800 SEA0830 SEA1900 =
SEA0830 SEA0900 SEA1930 =
Flights Flown SEA0900 SEA0930 SEA2000 =
Overnight Flights Planes Owned SEA0930 SEA1000 POR0800 =
Planes Used <= SEA1000 SEA1030 POR0830 =
SEA1030 SEA1100 POR0900 =
Total Net Revenue SEA1100 SEA1130 POR0930 =
Total Fixed Cost SEA1130 SEA1200 POR1000 =
Overnight Flight Revenue SEA1200 SEA1230 POR1030 =
Net Profit SEA1230 SEA1300 POR1100 =
($thousand/day) SEA1300 SEA1330 POR1130 =
SEA1330 SEA1400 POR1200 =
SEA1400 SEA1430 POR1230 =
SEA1430 SEA1500 POR1300 =
SEA1500 SEA1530 POR1330 =
SEA1530 SEA1600 POR1400 =
SEA1600 SEA1630 POR1430 =
SEA1630 SEA1700 POR1500 =
SEA1700 SEA1730 POR1530 =
SEA1730 SEA1800 POR1600 =
SEA1800 SEA1830 POR1630 =
SEA1830 SEA1900 POR1700 =
SEA1900 SEA1930 POR1730 =
SEA1930 SEA2000 POR1800 =
POR0800 POR0830 POR1830 =
POR0830 POR0900 POR1900 =
POR0900 POR0930 POR1930 =
POR0930 POR1000 POR2000 =
POR1000 POR1030 SFO0800 =
POR1030 POR1100 SFO0830 =
POR1100 POR1130 SFO0900 =
POR1130 POR1200 SFO0930 =
POR1200 POR1230 SFO1000 =
POR1230 POR1300 SFO1030 =
POR1300 POR1330 SFO1100 =
POR1330 POR1400 SFO1130 =
POR1400 POR1430 SFO1200 =
POR1430 POR1500 SFO1230 =
POR1500 POR1530 SFO1300 =
POR1530 POR1600 SFO1330 =
POR1600 POR1630 SFO1400 =
POR1630 POR1700 SFO1430 =
POR1700 POR1730 SFO1500 =
POR1730 POR1800 SFO1530 =
POR1800 POR1830 SFO1600 =
POR1830 POR1900 SFO1630 =
POR1900 POR1930 SFO1700 =
POR1930 POR2000 SFO1730 =
SFO0800 SFO0830 SFO1800 =
SFO0830 SFO0900 SFO1830 =
SFO0900 SFO0930 SFO1900 =
SFO0930 SFO1000 SFO1930 =
SFO1000 SFO1030 SFO2000 =
SFO1030 SFO1100
SFO1100 SFO1130
SFO1130 SFO1200
SFO1200 SFO1230
SFO1230 SFO1300
SFO1300 SFO1330
SFO1330 SFO1400
SFO1400 SFO1430
SFO1430 SFO1500
SFO1500 SFO1530
SFO1530 SFO1600
SFO1600 SFO1630
SFO1630 SFO1700
SFO1700 SFO1730
SFO1730 SFO1800
SFO1800 SFO1830
SFO1830 SFO1900
SFO1900 SFO1930
SFO1930 SFO2000
Overnight Arcs: SEA2000 SEA0800
POR2000 POR0800
SFO2000 SFO0800
SEA2000 POR0800
SEA2000 SFO0800
POR2000 SEA0800
POR2000 SFO0800
SFO2000 SEA0800
SFO2000 POR0800