Athens Bulldogs
After a season in which he made extensive use of all 12 players on his team, a coach is trying to select the starting team for postseason tournament play. The following are the season statistics on the players:
Player Position Height Rebounds Points Assists FGA FTA Turnovers Fouls
Dave, Guard, 70, 1.3, 1.6, 3.3, 58, 90, 2.1, 3.2
Al, Guard, 72, 1.3, 3.2, 4.5, 53, 85, 1.2, 2.1
Tom, Guard, 69, 2.8, 12.1, 2.2, 47, 70, 3.3, 1.2
Joe, Guard, 73, 3.3, 3.2, 3.6, 41, 72, 2.0, 2.4
Paul, Guard, 68, 1.1, 9.6, 2.0, 42, 68, 2.2, 3.4
Sean, Forward, 76, 7.7, 6.7, 2.2, 38, 70, 2.8, 4.1
Ron, Forward, 75, 4.5, 13.0, 2.7, 49, 75, 1.5, 2.1
Bob, Forward, 77, 2.0, 15.6, 2.8, 52, 85, 4.0, 3.0
Jim, Forward, 79, 7.5, 16.2, 2.3, 47, 60, 2.1, 2.2
Ben, Center, 80, 3.0, 3.9, 4.8, 45, 65, 2.3, 2.2
Herb, Center, 82, 8.9, 12.8, 1.0, 40, 60, 1.0, 3.6
John, Center, 83, 5.0, 8.2, 1.2, 56, 88, 2.2, 1.9
In choosing his starting team, the coach has 8 goals he would like to meet. They are:
1. Average starting team height at least 75 inches.
2. Total team average rebounds per game at least 24.
3. Total team average points per game at least 58.
4. Total team average assists per game at least 13.
5. Team field goal average (FGA) at least 50.
6. Team free throw goal average (FTA) at least 75.
7. Total team average turnovers per game no more than 11.
8. Total team average fouls per game no more than 12.
The coach also intends to impose the following restriction on the starting team:
1. At least one guard must start.
2. Exactly one center must start.
3. If either Al or Ron starts, Jim cannot start.
Create a Solver-based spreadsheet model based on goal programming methods to determine which players should start and solve the model.
Comments and Hints:
1. For those unfamiliar with basketball, exactly five players must be selected.
2. For those unfamiliar with basketball, it is desirable to overachieve the first 6 goals and desirable to underachieve the final 2 goals.
3. Take care when calculating actual values for the goals, noting the SUMPRODUCT calculation must be divided by 5 for goals 1, 5, and 6.
4. Build a truth table for the logically tricky constraint of If Al, or Ron, then not Jim. Try combinations of adding and subtracting the three terms. You might need to multiple one term by 2 in order to create logic to achieve desirable relationships.