Show excel solution

Because of your expertise in the application of analytic models, you have been hired as an independent consultant by the New York Times to analyze chances of former Secretary of State Hillary Clinton to win the 2016 United States Presidential Election.

In this problem, for illustrative purposes, you will consider a simplified model of the U.S. electorate consisting of only the largest 12 states. According to the U.S. Constitution, presidential elections are decided according to electoral votes. Each state is allocated a certain number of electoral votes, based on the state's population. The 12 states and their electoral votes are listed in Table 2.

Table 2: Number of electoral votes by state. State Electoral Votes California 55 Texas 38 New York 29 Florida 29 Illinois 20 Pennsylvania 20 Ohio 18 Michigan 16 Georgia 16 North Carolina 15 New Jersey 14 Virginia 13 Total 283

Table 3: Probability of Secretary Clinton winning by state. State Probability of Win California 90% Texas 9% New York 93% Florida 29% Illinois 95% Pennsylvania 77% Ohio 61% Michigan 72% Georgia 16% North Carolina 27% New Jersey 87% Virginia 45% If a candidate wins the election in a particular state, they are awarded all of that state's electoral votes. In our simplified model, there are 283 electoral votes total. If Secretary Clinton wins more than half of these electoral votes, she will win the overall election. Otherwise, we will assume that she will lose the overall election.

Based on polling data of likely voters, the New York Times has developed a model that predicts the probability of Secretary Clinton winning each state. These probabilities are listed in Table 3. It is assumed that the election outcomes in each state are independent and uncorrelated with each other.

Answer the following questions:

a) Using the Excel rand() function, develop a model to simulate the electoral votes Secretary Clinton will win in a single election year.

b) Using the BA simulation add-in, simulate 10,000 trials of the election year. Based on your model, what is your estimate of the expected number of electoral votes Secretary Clinton will win?

c) According to your model, what is the probability that Clinton wins the overall election?

d) Historically, the "Big 3" swing states of Florida, Pennsylvania, and Ohio have played an important role in US elections. Indeed, since the election of John F. Kennedy, no one has won a presidential election without winning at least two of these three states. Using your model, estimate the probability that Clinton wins at least two of the Big 3 swings states.

e) Use your model to estimate the probability that Clinton wins at least two of the Big 3 but loses the overall election.