Excel Solver with a Case Study [Rate Your Sports Team]

Ratings are numerical representations of competitive strength which is directly comparable. It is often applied directly to predict the game outcome between any two teams. The Sports rating systems have been around for almost 80 years and use a variety of methods for rating teams. Today I’d like to have a discussion on how to rate sports team using Excel Solver.

1 The problem to be solved in this article

Suppose that we have scores (these are only dummy scores) as shown in Figure 1.1. Column D includes names for Home Team while column F displays names of Visitor Team. Column E and column G are filled with scores for Home Teams and Visitor Teams respectively. How to determine the ratings for NFL teams?

Excel Solver with a Case Study Image 1.1

Figure 1.1

2 The equation uses to calculate predicted outcome with team ratings available

When two teams of equal quality play, the team at home tends to win more often. The size of this kind of effect is called home-field edge. The number of points by which the home team outscores the visitor team can be predicted by using following equation:

(Predicted points by which home team outscores visitors) = (Home-field edge) + (Home team rating) – (Visitor team rating)

3 Set up the model for our problem

3.1 Determine By Changing Cells

From the problem, we can see that our objective is to give ratings for each team. But the ratings could not be applied to our target cell since there are a lot of teams. Home-field edge and Team ratings are By Changing Cells in our problem instead.

3.2 Determine Target cell

Now we have determined By Changing Cells, what is our Target Cell? We want the prediction to be as close as possible to the outcome of each game, right? It implies that to minimize the sum over all differences between Actual outcome and the Predicted outcome can be used as the target cell. However, there is a problem. The positive and negative prediction errors can cancel each other out. For example, if you over-predict the home team margin by 5 points on one game and under-predict the home team margin by 5 points in another game, the sum of the two differences will yield a value of 0. But in fact, you were off by 10 points for a game. To not cancel out both positive and negative errors, we can use another method – to add up 2 power of [(Actual Outcome) – (Predicted Outcome)] for all games.

Excel Solver with a Case Study Image 1.2

Figure 1.2

3.2.1 Actual Outcome

As shown in Figure 1.2, cells B3 and other cells B7 through B38 are By Changing Cells. Column E contains Home Team Score while Column G contains Visitor Team Score. By entering the formula “=E3 – G3” into cell H3, you can compute the outcome of the first game (the number of points by which the home team beats the visiting team). By copying this formula into cells H4 through H258, we can get the actual outcome for other games.

3.2.2 Predicted Outcome

Let’s calculate predicted outcome for the first game. Per the above equation, we need to know Home-field edge, ratings for Seattle Seahawks and ratings for Green Bay Packers. We can use VLOOKUP function to retrieve ratings for a specific Team. For example, “VLOOKUP(D3,$A$7:$B$38,2,FALSE)” can return ratings for Seattle Seahawks. In the same way, we can get ratings for Green Bay Packers. In summary, the predicted points for the first game can be calculated by entering formula “=$B$3+VLOOKUP(D4,$A$7:$B$38,2,FALSE)-VLOOKUP(F4,$A$7:$B$38,2,FALSE)” into cell I3. By copying this formula into range I4:I258, predicted outcome for other teams can also be calculated.

Read More: Schedule Your Workforce using Excel Solver [4 Case Studies]

3.2.3 Squared Error

In column J, compute the squared error (Actual outcome – Predicted outcome)^2  for each game. The squared error for the first game is computed in cell J3 with the “= (H3 – I3)^2” formula. Copy this formula down to row 258.

3.2.4 Sum all the Squared Errors

In cell K3, enter formula “=SUM(J3:J258)”.

3.3 Determine Constraints

A team with a positive rating is better than average and a team with the negative rating is worse than average. The average team rating should be 0. Compute the average team rating in cell B5 with the formula “=AVERAGE($B$7:$B$38)”.

4. Use Excel Solver to solve the problem

If you are not familiar with Excel Solver, you can read one of my previous articles – Using Excel Solver to Determine the Optimal Product Mixtures – for details.

Click on Solver in Analysis group to open Solver Parameters dialog box. The Solver Parameters dialog box for this model is shown in Figure 1.3. Please note that there is a comma between “$B$7:$B$38” and “$B$3” in By Changing Variable Cells filed. Another thing requires your attention is that this model is no longer a simple LP model. We selected GRG Nonlinear engine as shown in Figure 1.3.

Excel Solver with a Case Study Image 1.3

Figure 1.3

After clicking on the Solve and then OK in Solver Results dialog box, we can get the following results.  It shows that Seattle Seahawks has 4 points better than St Louis Ram.

Excel Solver with a Case Study Image 1.4

Figure 1.4

Read More: How to Use Solver in Excel (Solving Linear Programming Problems)!

Remark:

GRG stands for generalized reduced gradient algorithm, developed by Lasdon, Fox, and Ratner. You can read their paper – Nonlinear optimization using the generalized reduced gradient method – if you have interest.

It is helpful to understand what the GRG Nonlinear Solving method can and cannot do. At best, the GRG Solving method alone can find a locally optimal solution to reasonably well-scales, non-convex model. At times, Solver will stop before finding a locally optimal solution when it is making very slow progress.

When the message “Solver found a solution” appears in Solver Results dialog box, it means that the GRG method has found a locally optimal solution. When the message “Solver has converged to the current solution” appear, it means that the objective function value is changing very slowly for the last few iterations or trial solutions and it stops before getting the most satisfied solutions. In this case, we can change the default value of 0.0001 to 0.00001 or 0.000001 in the Convergence edit box in Options dialog box (Figure 2).  By clicking on Options in Solver Parameter dialog box, we can open Options dialog box.

Excel Solver with a Case Study Image 2

Figure 2

Read More…

GRG Multistart and Evolutionary Excel Solver Engines [2 Case Studies]

Sequencing problem using Johnson’s algorithm of scheduling n-jobs on 2-machines [Sol]

Download working file

Download the working file from the link below.

Rate-Sport-Team.xlsx


I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as an SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

      Leave a reply