My Simple(ish) Playoff Chances Simulator

A month ago, I submitted an article with something I came up with that I thought was pretty cool.  It was a simulator similar to the Coolstandings sim, except that it would use Steamer and ZiPS rest-of-season (RoS) projections instead of year-to-date statistics as the measure of each team’s true talent.  Well, as you may have noticed, the boss, David Appelman, must have thought it was a pretty cool idea too, as unbeknownst to me, he had been working on the same sort of thing since long before the idea popped into my head.  But my duplication of effort will hopefully not go entirely to waste, as I’ll be sharing and explaining the simulator I created.  You’ll be able to use it to analyze your own “what if” scenarios, if that’s your sort of thing.  Think ZiPS and/or Steamer is overly optimistic or pessimistic about some teams?  You can fix that by running your own simulations with this.  Or you can apply it to past or completely hypothetical teams.  Go nuts.

The Simulator

Warning: you need to use special Excel settings for this spreadsheet to work; otherwise it will definitely not work, and it might lock up your computer!  Seriously.  The spreadsheet uses circular references intentionally in order to do the simulations.  Excel normally hates circular references (i.e., a cell referring to itself, somewhere along the line), so you need to:

1) Go into Excel Options (in my version, I hit the round button in the top left corner of Excel, and the Excel Options button appears at the bottom of the menu that pops up)

2) Go to the “Formulas” section

3) Check the “Enable iterative calculations” box

4) Set maximum iterations to 1

I believe it would be safest to open Excel and do this before you open this spreadsheet.  I’m not exactly sure how that all works.

Thanks to Mike Podhorzer for showing me this iteration trick several months back (blame him if your computer freezes, OK).

You’ll need to download this for it to work (click the green icon at the bottom).  Here it is, Steve Staud’s Standings Stratifier (or something):

The “WC to Playoffs” column indicates the expected chance each team will actually win the one-game playoff, should they win one of their league’s two wildcard spots.  Based on home field advantage and perhaps being a slightly better team, I’m assuming the winner of the top wildcard slot will win that game 51% of the time.

Unlike most of my spreadsheets, you can’t play with this one right in your browser (it wouldn’t do you any good this time).  So, after downloading it via the green icon at the bottom right of the Excel Web App, you can run the simulation by hitting (or holding down) F9.  However, that’s not ideal if you want to do a lot of simulations.  To perform 100,000 simulations at a time, like I did, you can create and run a Macro like this:

Sub Iterate()

For i = 0 To 100000

Application.Calculate

Next i

End Sub

Completing the 100,000 simulations takes around 13 minutes on my computer, but if you don’t have that kind of patience, you can either change the number in the macro to something lower or just press Esc to stop the macro.  Whether you do 10,000 simulations or 100,000 doesn’t make much difference, anyway.

To update the inputs, just highlight, copy and paste the playoff odds page’s contents (starting at “AL East”) into the “Paste Data Here” sheet, making sure everything lines up the same way you found it.  My original setup used the projected standings page instead, but I believe the playoffs odds page’s version of each team’s RoS win percentage should be better, as it takes strength of schedule into account.

The Math

At the heart of the simulator are normal distributions, centered around each team’s expected RoS win percentage.  I use the results of the formula (p(1 – p)/n)^1/2 as the standard deviation of each normal distribution curve, in order to approximate the binomial distribution probability mass function for each (“n” being the remaining games, and “p” being the RoS W%).  That makes these projections pretty much the “best case scenario” for FanGraphs’ RoS estimates; it’s treating them as though they’re perfect estimates of the “true” win rate for each team.

If a team has exactly a 0.500 true talent win rate (p) according to the estimates, and it has 100 games remaining on its schedule (n), the above formula says the standard deviation is exactly five wins; this means that while you’d expect the team to win an average of 50 of its remaining games, there’s only about a 68% chance that it will be somewhere between 45 and 55 wins, due to pure chance (the 68% comes from a basic principle of how standard deviations work; see this link).  To get the standard deviation in terms of the win rate, you’d divide the standard deviation of 5 wins by the 100 remaining games to get 0.050; this is the standard deviation the simulator would use in its NORMINV calculation in Excel, along with the mean of 0.500, and a randomly generated probability in that normal distribution.

The RAND function used in the simulator returns a random number between 0 and 1.  Let’s say the randomly generated probability is 0.5, meaning the 50th percentile mark of the bell curve.  NORMINV returns 0.5 as the actual win rate in this scenario.  That means that in this particular simulation, things actually turned out exactly as expected, and the team won 50 games.  Now let’s say the random number is 0.2 in another simulation (probably not exactly, by the way, as it spits out nine decimal places); the actual win rate here comes out to 0.458, which we’ll round off to call 46 wins.  If the random number is 0.000001, the win total comes out to approximately 26 wins, although the chance of the random number being that low are one in a million (literally).

So, those are the basics of what’s at work in the model.  It’s then mainly just a matter of ranking, breaking ties, and counting up the division titles and wild card berths in all the simulations.  It doesn’t take specific matchups directly into account, which is why I call it simple(ish).  This means imbalances happen in individual simulations, with all the teams combining for more or fewer wins than is actually possible given the number of games.  However, everything balances out, given enough simulations, assuming the true win rates used are all balanced properly (all the teams put together need to average 0.500).

Let’s talk a bit about “true” win rates.  I used the example of coin flips in a previous article; just because you know a coin should come up heads 50% of the time doesn’t mean it actually will come up around 50% of the time, if you’re not flipping it very many times.  But at least you know that the true rate of heads should be about 50%, if you’re dealing with a typical coin.  For baseball teams, however, we can only guess their true win rates.  The point is, if you want to account for the fact that the RoS projections are somewhat uncertain estimates of the true win rates, I think you should use a somewhat higher standard deviation than the best-case scenario standard deviation the simulator uses by default.  How much higher?  I haven’t figured that one out.  You can play with that by changing the value in the “Standard Deviation Multiplier” box on the right edge of the “Calculations” sheet.

Here are my results (100,000 simulations apiece), current as of August 29th’s games, for the total playoff chances of each team, going by either the minimum standard deviation multiplier of 1, or with the standard deviations increased by 10% to reflect uncertainty in the projections:

Division	Team	RoS Win %	Playoff% @   Multiplier=1	Playoff% @   Multiplier=1.1	FanGraphs   Official PO%
AL East	Red Sox	0.569	89.54%	87.86%	87.90%
	Rays	0.552	53.42%	53.05%	57.60%
	Orioles	0.481	4.58%	5.88%	5.20%
	Yankees	0.520	3.57%	4.66%	4.10%
	Blue Jays	0.473	0.00%	0.00%	0.00%
AL Central	Tigers	0.599	98.99%	98.40%	98.50%
	Indians	0.541	9.68%	11.57%	9.60%
	Royals	0.462	0.48%	0.84%	0.40%
	Twins	0.419	0.00%	0.00%	0.00%
	White Sox	0.455	0.00%	0.00%	0.00%
AL West	Rangers	0.533	87.52%	85.87%	83.50%
	Athletics	0.550	52.22%	51.88%	53.10%
	Angels	0.516	0.00%	0.00%	0.00%
	Mariners	0.498	0.00%	0.00%	0.00%
	Astros	0.386	0.00%	0.00%	0.00%
NL East	Braves	0.568	99.98%	99.94%	100.00%
	Nationals	0.581	6.24%	7.67%	6.50%
	Phillies	0.472	0.00%	0.00%	0.00%
	Mets	0.458	0.00%	0.01%	0.00%
	Marlins	0.404	0.00%	0.00%	0.00%
NL Central	Cardinals	0.553	85.26%	83.48%	82.90%
	Pirates	0.516	60.54%	60.83%	62.60%
	Reds	0.521	46.41%	45.88%	46.90%
	Brewers	0.459	0.00%	0.00%	0.00%
	Cubs	0.452	0.00%	0.00%	0.00%
NL West	Dodgers	0.570	99.95%	99.84%	99.80%
	Diamondbacks	0.478	1.59%	2.31%	1.30%
	Rockies	0.459	0.01%	0.02%	0.00%
	Padres	0.449	0.00%	0.00%	0.00%
	Giants	0.518	0.01%	0.01%	0.00%

I think what this shows is that the effect of boosting the standard deviation is opening the door more not only for teams that are further back, but also for teams with lower expected win rates.

The official FanGraphs playoff odds are up there for comparison purposes.  They’re pretty close, I’d say, which makes me feel pretty good about my results. But, the real joy in this is that you can create your own results, with your own inputs.  Have fun!

Edit: here’s a new version of the simulator, using Jared Cross’ suggestions:

The default of this version gets very similar results to if you’d used a standard deviation multiplier of around 1.04 on the original version.  I think this version is more fundamentally solid, though.  It simulates wins via the binomial distribution directly, and accounts for uncertainty in rest-of-season win estimates by simulating a new RoS W% based on a normal distribution and some historically-based estimates of the model error in win percentage.