Question
Hello, please show me the steps and also the excel output if possible. Thank you so much in advance. ((Hint: normdist is the cumulative normal
Hello, please show me the steps and also the excel output if possible. Thank you so much in advance.
((Hint: normdist is the cumulative normal distribution. For our purposes it is a function that takes numbers between -1 and 1 and maps them to percentages centered around 50 for each pair in the bracket bring in each team's adjusted score, using vlookup)
Construct a Monte Carlo Simulation for the 2015 World Series, which just started. Dont worry, it takes a long time to play out: you will be done modelling before they are done playing.
1) Use the ELO rankings to rate team quality (look it up).
2) Convert ELO to a (linear) score between zero and 1 with worst-ranked Atlanta Braves 0 and Toronto Blue Jays 1. We only really need a score for the 8 qualified teams.
3) The score between 0-1 will be the driver of a sample from a normal distribution. Use NORM.DIST(score differential,0,upset,1) where upset is a parameter to control upsets. The parameter should be between 0 and 3, with 3 representing almost coin-flips and 1 giving a reasonable edge to higher quality teams (upset is technically controlling the standard deviation of the distribution). Score differential is the (absolute) difference between scores. Given any two teams (text), produce a % probability of the favorite winning and use this to explore a single matchup of each team vs Toronto. For example Toronto vs itself should give you 50%, Toronto vs Houston should be a more than 50%.
4) Nothing is random yet: we are just translating distances to probabilities via the normal distribution. RAND() is a uniform random variable between 0 and 1.RAND() will be less than the matchup percentage with probability given by the matchup percentage itself. (i.e RAND() is less than 56%, 56% of the time).We can use this to work through the bracket: for any two teams - lookup their scores, compute the score differential, and pull a RAND() and compare it to the winning percentage. The output is text of the winning team name. To name the favorite and underdog of any pair you can use LARGE(), SMALL(), and VLOOKUP(). Now you have text team names and can do it again down the bracket to declare a winner.
5) Every time Excel recalculates you will get a new winner. Now we can run a few thousand iterations to get back to probabilities. The Monte Carlo bracket integrates pairwise probabilities (straight from ELO and the normal distribution) into overall winner probabilities. To automate thousands of iterations use data table under data > What-if-Analysis. The input cell for the table can just be any empty cell, the output is the winner name.
6) Compare your team probabilities to http://www.predictwise.com/node/4031 and http://fivethirtyeight.com/datalab/best-2015-mlb-teams-blue-jays-elo/ and play with the upset parameter.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started