|Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002
Sample files You can download the sample files that relate to excerpts from Microsoft Excel Data Analysis and Business Modeling from Microsoft Office Online. This article uses the file fantasy2.xls.
What is fantasy football?
To play fantasy football, you choose a team (usually 10 players) and receive rewards based on player performances during the current season. In the league I entered, each participant (or "manager") chooses the following positions:
- One quarterback (QB)
- One place kicker (K)
- Three running backs (RB)
- Three wide receivers (WR)
- One tight end (TE)
- One team's defense (Team)
Each choice of player or team is assigned a price based on the player's or team's ability. For example, the great quarterback Michael Vick is assigned a price of $19 million. You have a total of $100 million to spend on your team.
During a game, you earn points on the basis of the following rules:
||25 yards passing
||TD pass or TD run
||10 yards rushing
||Field goal of 50 yards or longer
|WR or TE
||10 yards receiving or rushing
||TD receiving or rushing
||Defensive or special team TD
||Fumble recovery or interception
In addition, your defense team is awarded points on the basis of the game's score:
|Points allowed by defense
|42 or more
What information do I need to pick a good team?
Essentially, you need to predict the performance of each player or team for the upcoming season. These predictions can be made using performances from recent seasons or human judgment.
In the worksheet fantasy2.xls (which is included in the sample files download), I entered predictions for 167 players or teams during the 2003 season, as shown in Figure 1.
I included these column headings: Pick? (column A), which will hold a binary value of 0 or 1; Pts (column B), in which I calculate total predicted points earnings; POS (column C); Player (column D); and Cost (column E) — along with sections to record performance predictions for player or team. For example, as shown in row 55, I predicted that Priest Holmes (who costs $16.5 million) would gain 1,450 yards rushing and 550 yards passing, and score 14 rushing and 2 receiving TDs.
Figure 1: Enter predictions for players and teams in a spreadsheet like this one.
For each team, I've computed — based on points per game yielded last season — the probability of each range of points given up (0, 1–6, and so on) in a game. I assumed that the number of points given up during a game follows a Poisson random variable with a mean equal to the average number of points given up per game last season. For example:
- In cell K110, I computed the probability that Tampa Bay gives up 0 points in a game with the formula:
- In cell L110, I computed the probability that Tampa Bay gives up between 1 and 6 points in a game with the formula:
This formula computes the probability of giving up 6 or less points and subtracts the probability of yielding 0 points to derive the probability of giving up 1–6 points during a game.
What is the Excel Solver tool?
You use Solver when you want to find the best way to do something. Or, more formally, when you want to find the values of certain cells in a spreadsheet that optimize (maximize or minimize) a certain objective.
An optimization model has three parts: a target cell, changing cells, and constraints.
- The target cell represents the objective or goal. In your fantasy football spreadsheet, the goal of the target cell is to maximize the predicted total points scored by your selections. (In Fantasy2.xls, the target cell is L112, shown in Figure 3.)
- Changing cells are the spreadsheet cells that we can change or adjust to optimize the target cell. In your spreadsheet, the changing cells are the 0 or 1 binary variables for each player or team. A value of 1 indicates that a player or team is selected; a value of 0 indicates that the player or team is not selected. (In Fantasy2.xls, these are entered in column A, shown in Figure 2.)
- Constraints are restrictions you place on the changing cells. In my league, the constraints are: spend at most $100 million; and, choose exactly one K, one TE, one QB, three WR, three RB, and one Team. (In Fantasy2.xls, these constraints are represented in cells M5:P13, shown in Figure 3.)
Note Because Solver can handle only up to 200 changing cells (and 200 constraints) in linear models, I prescreened the players and teams in order to reduce the number of available options to below 200.
How can I use Solver to pick my team?
To proceed, enter trial values of 0 or 1 for each player or team, as shown in column A of Figure 2.
Figure 2: The changing cells in column A enable you (and Solver) to model the results of various player and team choices.
Subtotal the points for each player
Next, enter formulas that compute the number of points that will be earned by each player or team if your forecasted performance becomes reality. In Fantasy2.xls, I calculated these totals in column B, as shown in Figure 3.
- To compute the points for each place kicker, I entered the formula
3*G3+3*H3+F3 in cell B3, and then copied it to cells B4:B29.
- To compute the points for each quarterback, I entered the formula
6*G31-2*H31+(F31/ 25)+6*J31+(I31/10)in B31, and then copied it to cells B32:B53.
- To compute the points for each running back, I entered the formula
(F55/10)+6*(G55+I55)+(H55/10)in cell B55, and then copied it to cells B56:B89.
- To compute the points for each tight end, I entered the formula
(F91/10)+6*G91 in cell B91, and then copied it to cells B92:B108.
- To compute the points for each defense team, I entered the formula
6*G110+6*H110+4*I110+2*J110+16*(10*K110+6*L110+4*M110+2*N110+0*O110-2*P110-4*Q110-6*R110) in cell B110, and then copied it to cells B111:B136.
Note I multiplied the probability of each range of points given up by 16 because each team plays 16 games.
Subtotal the points for your picks
In another part of the worksheet, you will subtotal the predicted points for the chosen players and team. Because the formulas that calculate these point subtotals rely on the values in the binary changing cells in column A, I created the following range names to help keep track of the the range for each position type.
||Tracks binary changing cells for
||Cells in range
You can use the SUMPRODUCT function to multiply each binary value in column A with the corresponding point value in column B, and then add these together. For example, in cell L9, I compute the points earned by the chosen place kicker with the formula
SUMPRODUCT(KICK,B3:B29), as shown in Figure 4.
Figure 4: Calculations for constraints and target cell of Solver model.
After you compute the point subtotals for each position type, you can compute the target cell (L12) as the sum of the points earned by each player and team pick.
Similarly, in cells M6:M11, I subtotal the cost for each player and team pick, and in cell M12, add up the total cost of all picks. For example, in cell M9, I compute total cost of wide receivers with the formula
Finally, I compute the number of players of each type (or number of teams) selected in cells N6:N11. For example, to compute the number of teams selected, I entered the formula
SUM(TEAM) in cell N11. In cells P6:P11, I entered the number of players (or teams) of each type needed.
Set your Solver parameters
Now you're ready to enter your model into Solver. (Click Solver on the Tools menu. If the Solver command isn't visible, click Add-Ins on the Tools menu, and then select the Solver Add-in check box.)
To enter my model, I used the settings shown in Figure 5.
Figure 5: Solver window for fantasy football team selection.
- The target cell (L12) is set to maximize total points earned by the team.
- The changing cells are the named ranges KICK, QB, RUN, TE, TEAM, and WR.
Tip To enter multiple ranges of changing cells, separate them by commas or hold down CTRL while you enter the ranges.
- The constraint
$M$12<=100 ensures that I spend at most $100 million.
- The constraint
$N$6:$N$11= $P$6:$P$11 ensures that I choose the right number of players or team of each type.
- The constraints KICK=binary, QB=binary, and so on specify that each range of changing cells is binary.
- Finally, I specify that my model is linear. (Click the Options button, and then select the Assume Linear Models check box.)
Note This model is linear for the following reasons:
- The target cell is computed by adding together terms of the form: (0 or 1 changing cell for player or team)*(predicted points for player or team).
- The budget constraint is constructed by adding up terms such as: (0 or 1 changing cell for player or team)*(price of player).
- The constraints on the number of each type of player simply add up changing cells, so they are linear as well.
Press the Solve button
After pressing Solve, the Solver selects the following team and players:
- Kicker: Paul Edinger
- QB: Jeff Garcia
- RBs: Clinton Portis, Edgerin James, and Stacey Mack
- TE: Alge Crumpier
- Team: Philadelphia Eagles
- WRs: Torry Holt, Keeshawn Johnson, and Bill Schroeder
Given my predictions, these are the players with the highest total point value, that do not exceed a total price of $100 million.
Of course, the same methodology I've described for selecting a fantasy football team can be applied to baseball or basketball fantasy leagues. Enjoy!