You will discuss the followings during the 10-minute presentation:
Introduction/Motivation/Problem Definition
o What is it that you are trying to solve/achieve and why does it matter?
Model Formulation/Method
o This is where you give a detailed description of your primary contribution. It is especially
important that this part be clear and well-presented so that we can fully understand what
you did.
Results/Findings/Implications
o How do you evaluate your solution to whatever empirical, algorithmic, or theoretical
question you have addressed and what do these evaluation methods tell you about your
solution? It is not so important how well your method performs but rather how interesting
and clever your experiments and analysis are.
o Make sure to interpret the results and talk about what can we conclude and learn from
your evaluations.
o Although the case study does not specifically ask, you may conduct extra analysis (e.g.,
what-if analysis)
Project Presentation Slides/Report:
Presentation slides and a completed Excel spreadsheet are required, while the report is optional. If the 10-
min if the time limit is tight and your group desire to provide a complete view of your analysis, you may
organize a report and submit it with the slides.
Uploaded files:
1-The case
2-The Excel template you need to use.
3- Examples of the project needed.
Needed items:
1-Powerpoint slide of the solution.
2- Excel solution file. Note: Do not create another Excel file, use the template.
Confirming Pages
Case 6-3 Airline Scheduling 229
exist, what method should Jake use to convert the Asian
holdings from the respective currencies into dollars?
d. In response to the World Trade Organizations mandate
forbidding transaction limits, the Indonesian government
introduces a new tax to protect its currency that leads to a
500 percent increase in transaction costs for transactions of
rupiahs. Given these new transaction costs but no transaction
limits, what currency transactions should Jake perform to
convert the Asian holdings from the respective currencies
into dollars?
e. Jake realizes that his analysis is incomplete because he has
not included all aspects that might influence his planned
currency exchanges. Describe other factors that Jake should
examine before he makes his final decision.
Case 6-3
Airline Scheduling
Richard Cook is very concerned. Until recently, he has always
had the golden touch, having successfully launched two start-
up companies that made him a very wealthy man. However,
the timing could not have been worse for his latest start-up
a regional airline called Northwest Commuter that operates on
the west coast of the United States. All had been well at the
beginning. Four airplanes had been leased and the company
had become fairly well established as a no-frills airline provid-
ing low-cost commuter flights between the west coast cities of
(including some new ones) for the coming year that could feasi-
bly be flown by the four airplanes.
A little over a decade ago, Richard had been an honor gradu-
ate of a leading MBA program. He had enjoyed the management
science course he took then and he has decided to apply spread-
sheet modeling to analyze his problem.
The leasing cost for each airplane is $30,000 per day. At the
end of the day, an airplane might remain in the city where it
landed on its last flight. Another option is to fly empty overnight
Flight
Number From To Depart Arrive
Expected
Revenue ($000)
1257 Seattle San Francisco 8:00 AM 10:00 AM 37
2576 Seattle Portland 9:30 AM 10:30 AM 20
8312 Seattle San Francisco 9:30 AM 11:30 AM 25
1109 Seattle San Francisco 12:00 PM 2:00 PM 27
3752 Seattle San Francisco 2:30 PM 4:30 PM 23
2498 Seattle Portland 3:00 PM 4:00 PM 18
8787 Seattle San Francisco 5:00 PM 7:00 PM 29
8423 Seattle Portland 6:30 PM 7:30 PM 27
7922 Portland Seattle 9:00 AM 10:00 AM 20
5623 Portland San Francisco 9:30 AM 11:00 AM 23
2448 Portland San Francisco 11:00 AM 12:30 PM 19
1842 Portland Seattle 12:00 PM 1:00 PM 21
3487 Portland Seattle 2:00 PM 3:00 PM 22
4361 Portland San Francisco 4:00 PM 5:30 PM 29
4299 Portland Seattle 6:00 PM 7:00 PM 27
1288 San Francisco Seattle 8:00 AM 10:00 AM 32
3335 San Francisco Portland 8:30 AM 10:00 AM 26
9348 San Francisco Seattle 10:30 AM 12:30 PM 24
7400 San Francisco Seattle 12:00 PM 2:00 PM 27
7328 San Francisco Portland 12:00 PM 1:30 PM 24
6386 San Francisco Portland 4:00 PM 5:30 PM 28
6923 San Francisco Seattle 5:00 PM 7:00 PM 32
Seattle, Portland, and San Francisco. Achieving fast turnaround
times between flights had given Northwest Commuter an impor-
tant competitive advantage. Then the cost of jet fuel began spi-
raling upward and the company began going heavily into the red
(like so many other airlines at the time). Although some of the
flights were still profitable, others were losing a lot of money.
Fortunately, jet fuel costs now are starting to come down, but
it has become clear to Richard that he needs to find new ways
for Northwest Commuter to become a more efficient airline.
In particular, he wants to start by dropping unprofitable flights
and then identifying the most profitable combination of flights
to another city to be ready to start a flight from there the next
morning. The cost of this latter option is $5,000.
The table above shows the 22 possible flights that are being
considered for the coming year. The last column gives the esti-
mated net revenue (in thousands of dollars) for each flight, given
the average number of passengers anticipated for that flight.
a. To simplify the analysis, assume for now that there is vir-
tually no turnaround time between flights so the next flight
can begin as soon as the current flight ends. (If an immediate
next flight is not available, the airplane would wait until the
hil24064_ch06_194-231.indd 229hil24064_ch06_194-231.indd 229 09/11/12 1:29 PM09/11/12 1:29 PM
Confirming Pages
230 Chapter Six Network Optimization Problems
next scheduled flight from that city.) Develop a network that
displays some of the feasible routings of the flights. ( Hint:
Include separate nodes for each half hour between 8:00 !”
and 7:30 #” in each city.) Then develop and apply the corre-
sponding spreadsheet model that finds the feasible combina-
tion of flights that maximizes the total profit.
b. Richard is considering leasing additional airplanes to achieve
economies of scale. The leasing cost of each one again would
be $30,000 per day. Perform what-if analysis to determine
whether it would be worthwhile to have 5, 6, or 7 airplanes
instead of 4.
c. Now repeat part a under the more realistic assumption that
there is a minimum turnaround time of 30 minutes on the
ground for unloading and loading passengers between the
arrival of a flight and the departure of the next flight by the
same airplane. (Most airlines use a considerably longer turn-
around time.) Does this change the number of flights that can
be flown?
d. Richard now is considering having each of the four airplanes
carry freight instead of flying empty if it flies overnight to
another city. Instead of a cost of $5,000, this would result in
net revenue of $5,000. Adapt the spreadsheet model used in
part c to find the feasible combination of flights that maxi-
mizes the total profit. Does this change the number of air-
planes that fly overnight to another city?
Case 6-4
Broadcasting the Olympic Games
The management of the WBC television network has been
celebrating for days. What a coup! After several unsuccessful
attempts in recent decades, they finally have hit the big jackpot.
They have won the bidding war to gain the rights to broadcast
the next Summer Olympic Games!
The price was enormous. However, the advertising income
also will be huge. Even if the network loses some money in the
process, the gain in prestige should make it all worthwhile. After
all, the entire world follows these games closely every four years.
Now the entire world receiving the feed of the broadcast from the
WBC network will learn what a preeminent network it is.
each link in the network is shown in the diagram below (in
GB/s). WBC can divide the transmission and route it through
multiple paths of the network from A to G, so long as the total
bandwidth required on each link does not exceed the capacity
of that link.
a. By utilizing the entire computer network, what is the maxi-
mum bandwidth available (in GB/s) for transmission from
the general site of the Olympic Games (node A) to the home
studios (node G)? Set up and solve a linear programming
spreadsheet model.
A
B
C
D
E
F
G
12
9
7
5
10
9
8
13
6
3
4
6
However, reality also is setting in for WBC management.
Telecasting the entire Olympic Games will be an enormously
complex task. Many different sporting events will be occurring
simultaneously in far-flung venues. An unprecedented amount
of live television and live-on-the-Internet coverage of the vari-
ous sporting events needs to be planned.
Due to the high amount of bandwidth that will be required
to transmit the coverage of the games back to its home studios,
WBC needs to upgrade its computer network. It operates a pri-
vate computer network as shown in the network diagram in the
right-hand column. The games will be held near node A. WBCs
home studios are located at node G. At peak times, coverage
of the games will require 35 GB/s (GB per second) to be sent
through the network from node A to node G. The capacity of
b. WBC would like to expand the capacity of the network so it
can handle the peak requirement of 35 GB/s from the Olym-
pics site (A) to the home studios (G). WBC can increase the
capacity of each link of the computer network by install-
ing additional fiber optic cables. The table on the next page
shows the existing capacity of each network segment (in
GB/s), the maximum additional capacity that can be added
(in GB/s), and the cost to increase the capacity (in millions of
dollars per unit GB/s added). Make a copy of the spreadsheet
model used to solve part a and make any revisions necessary
to solve this new problem.
Note: This case will be continued in the next chapter (Case 7-4),
so we suggest that you save your spreadsheet model from part b.
hil24064_ch06_194-231.indd 230hil24064_ch06_194-231.indd 230 09/11/12 1:29 PM09/11/12 1:29 PM 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 Maximizing UA Mens Basketball Performance (Authors' names are anonymized) Agenda Introduction Problem Definition Motivation Model Formulation Data Model Results/Findings/Implications Solution Findings Solution Implications Model Validation Conclusion Summary Future Work Introduction Problem: The University of Arizona Mens Basketball team wants to improve their average overall team performance. Maximize combined team +/- per game played given player statistics and position and play time constraints. Motivation: To remain a leader in the PAC-12 To make it to at least the Final Four in March Madness Conferences receive NCAA funding based on tournament performance, which is then distributed to schools. Each team in conference earns one unit per game played in the tournament and this is paid for the next 6 years with some annual adjustments.[1] 2022 Unit Size= $338,887. Paid out to teams over 6 years $2,033,322 EX: Pac-12s earned 11 units in 2016, resulting in a total six-year payout of $18,762,162 by 2022 Model Formulation The data that are gathered in this investigation come from two separate sources: Season Average Team Stats: Sportsradar.com Individual Player Total Stats: sports-reference.com Stat Categories of Interest: Field Goals Made (FGM) 3 Point Field Goals Made (3PM) 2 Point Field Goals Made (2PM) Free Throws Made (FTM) Offensive Rebounds (OR) Defensive Rebounds (DR) Personal Fouls (PF) Rebounds ( R) Assists (A) Steals (Stl) Blocks Points (Pts) Turnovers (T) Model Formulation Once the data were gathered, each players season total amount for a particular stat category was divided by the total amount of minutes they played this season. Example: Adama Bal Total Minutes: 104 Total Field Goals Made: 13 Field Goals Per Minute Played: 13/104 = 0.125 After performing this calculation for each player and stat category, this information was input as the project data Model Formulation Objective Function: Maximize the combined team +/- statistic Big Idea: Using each players +/- statistic per minute, we can maximize combined +/- Decision Variable: Xi, how many minutes player i should play. Model Formulation Constraints: Stat Constraints At least achieve season averages for each stat category to give the team the best chance to win a game. Two Negative Stat Categories: Personal Fouls and Turnovers Fatigue & Injury Constraints Limit amount of time players can play to limit fatigue and injuries Generally Accepted Value: 36 minutes Assuming overtime does not happen Minute Constraints: There is a limited amount of minutes available to give to the entire team 200 Minutes (40 minutes regulation * 5 players) Front Court: No More Than 80 Minutes (Conditioning Concerns) Back Court: No Less Than 120 Minutes (More Durable) Minutes Played Integer Constraint Results- Findings Linear Programming Solution: Total +/- of Game= 58.35 Integer Programming Solution: Total +/- of Game= 57.44 LP slightly more optimal but less realistic Results- Implications Solution is sensitive Team performance will worsen if Pelle Larsson plays Hes a key 6th man but less efficient than starters Results- Implications If minimum 3 point field goals per game decreases to 7.70, overall team performance increases to 59.5 If minimum defensive rebounds per game decreases to 28.26, overall team performance increases to 59.67 If minimum assists per game decreases to 19.66, overall team performance increases to 61.01 Players with less assists on average but overall better +/- get more playing time Results- Implications Results- Implications Increased Minutes: Bennedict, Kerr, Jordan, Justin, Azuolas, & Christian Largest Increases: Christian, Azuolas, & Justin Decreased Minutes: Adama, Dalen, Grant, Tautvilas, Shane, Oumar, & Pelle Largest Decreases: Pelle, Oumar, & Shane Big Idea: Maximize minutes of most productive players and decrease minutes of least productive players. Most Competitive Minutes: Backcourt Conclusion Investigative Question How should we assign UA player minutes to exceed the average statistical performance? Big Idea: Maximize team +/- by assigning minutes based on player per minute stat contributions Solution & Findings Player Minute Increases: Bennedict, Kerr, Jordan, Justin, Azuolas, & Christian Player Minute Decreases: Adama, Dalen, Grant, Tautvilas, Shane, Oumar, & Pelle Big Idea: Maximize minutes played by most productive players Garbage Time Minutes Most Competitive Allocation of Minutes: Backcourt Future work Develop regression and classification models to predict overall team performance Add more constraints after conferring with coaching staff Add more statistical categories that may be of interest to the coaching staff Develop a model for the UA Womens Basketball Team References 1. https://www.latimes.com/sports/story/2021-04-03/march-madness-pac-12-succ ess-additional-money 2. https://developer.sportradar.com/member/register 3. https://www.sports-reference.com/cbb/schools/arizona/2022.html https://www.latimes.com/sports/story/2021-04-03/march-madness-pac-12-success-additional-money https://www.latimes.com/sports/story/2021-04-03/march-madness-pac-12-success-additional-money https://developer.sportradar.com/member/register https://www.sports-reference.com/cbb/schools/arizona/2022.html Season & Player Data Season Data - SportsRadar.com Application Programming Interface Home_ID Home_Team Home_FGM Home_FGA Home_FGP Home_3PM Home_3PA Home_3PP Home_2PM Home_2PA Home_2PP Home_Blocked_Att Home_FTM Home_FTA Home_FTP Home_OR Home_DR Home_R Home_A Home_T Home_Stl Home_Blocks Home_ATR Home_PF Home_FBPts Home_SCPts Home_POT Home_FFoul Home_TFoul Home_PaintPts 9b166a3f-e64b-4825-bb6b-92c6f0418263 Arizona 30.44 61.38 0.4959270121 7.76 21.94 0.353691887 22.68 39.44 0.5750507099 2.79 15.91 21.56 0.7379406308 10.44 28.35 38.79 19.9 13.18 6.71 5.71 1.5098634294 16.47 15.03 12.35 15.12 0.03 0.2058823529 42.65 Per minute 0.1522 0.3069 0.0024796351 0.0388 0.1097 0.0017684594 0.1134 0.1972 0.0028752535 0.01395 0.07955 0.1078 0.0036897032 0.0522 0.14175 0.19395 0
