## Friday, October 19, 2018

### Simulations of Senate Control with RAND and IF Statements in Excel

Question:  The table below has an analyst’s assumptions on the probability of the Democrat winning in 9 Senate races that are currently in contention.   Assume all races are independent.   Currently the Democrats have 5 seats and need to gain 2 seats to get control of the Senate.

 State Prob Dem Win Dum if State is currently held by Democrat AZ 0.55 0 FL 0.55 1 IN 0.55 1 MO 0.55 1 NV 0.55 0 MT 0.6 1 ND 0.5 1 TN 0.4 0 TX 0.4 0

Based on these probabilities and the assumption of independent outcomes for state races use the RAND and IF statement to generate the frequency of potential senate outcomes after the November 2018 election.

Discuss how the spreadsheet is created.

Discuss how the assumption of independent state outcomes impacts results.

Use Rand() statement to create at least 2000 random outcome numbers  between 0 and 1 for each state.   Columns E through M starting at row 3 have the random outcomes for the nine states.

Row 2 of Columns 2 through M have the probability of the Democrat candidate winning the election.

Use if statement to create a dummy outcome variable 1 if Dem wins 0 if Rep wins for all random outcomes for all states.  Outcome variables are in Row O to Row W.

Remember row 2 has the probability of Democratic Candidate winning.

The syntax for first observation on outcome AZ first observation in cell O3 is

If(E3<=E\$2,1,0)

Copy O3 to P3:W3.

Copy O3:W3 down at least 2000 cells.

In column X sum columns O to W.

Final step is take frequency of totals for the 2000 plus observations Use the FREQUENCY function.

 Total Democratic Wins # Observations 0 3 1 11 2 84 3 310 4 514 5 602 6 382 7 135 8 20 9 0 Sum 2061 7, 8  or 9 0.075206211 6,7,8 9 0.26055313

To take Control of the Senate Democrats need to win 7 of these races because Pence the Veep breaks the tie,

The likelihood of this happening with these optimistic probability assumptions is around 7.5%.

Under these assumptions, the probability of a 50/50 Senate with the tie going to the Republican is 26%.

The most likely outcome. based on these probabilities and the assumption of independent state outcomes, is Democrats take 5 seats and Senate remains at 51-49 Republican:

The role of independent state outcomes:

The results presented here assume state outcomes are independent.  State outcomes can be influenced by national or world events.   The Kavenaugh hearings appeared to help Republicans in several states.   A surprise event like continual declines in the stock market or perhaps solid proof the Putin has been controlling Trump might help Democrats in more than one states.

Also, the big unknown is who will go to the polls.  Pollsters rely on the concept of the likely voter.  However, new people are registering, both Democrats and Republicans, and we don’t know who will go to the polls.   The difference between likely and actual voters may not be independent across states.