Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Entrepreneur's Growth Startup Handbook 7 Secrets To Venture Funding And Successful Growth

Authors: David N. Feldman

1st Edition

1118445651, 978-1118445655

More Books

Students also viewed these Finance questions