BYU Student Author: @Mark
Reviewers: @Andrew, @klayton, @Parker_Sherwood
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
You are currently volunteering on the campaign of a politician you greatly admire. She is running for one of the seats in the House of Representatives associated with your home state. The finance director for the campaign is very interested in data involving the correlation between campaign financing and the outcome of the elections. He has procured a large dataset containing information on campaign financing on elections around 2016 and, knowing of your exceptional data analytics skills, has asked you to summarize the data into a more digestible format. After considering the data, you find that many different campaigns are included (Presidential, Senate, etc.) that are not relevant to your analysis. Also, since your candidate is a member of one of the major parties, you determine that information on the Democratic and Republican parties is the most relevant. You finally determine that Pivot Tables would be appropriate to filter out irrelevant data and draw meaningful conclusions.
Instructions
The director has three specific questions he wants you to explore:
First, the director thinks that Challenger campaigns (where the candidate is challenging an incumbent) generally require more financing because of the incumbent’s track record and experience in the position. Since your candidate is a Challenger, the director wants you to verify whether House Challenger campaigns are on average more costly than incumbent campaigns or campaigns for open seats.
- Create a Pivot Table comparing the average contributions of all Challenger campaigns to those of Incumbent and Open campaigns. Include a breakout of whether the candidate was a Democrat or Republican within each candidate designation (Challenger, Incumbent, Open Seat). Remember to filter out the irrelevant data mentioned above. Is your director correct in assuming Challengers have higher average contributions compared to the other designations?
Second, the director wants to know how many dollars were spent on the campaign per vote for both Republican and Democrat House candidates who won their races.
- Create a Pivot Table that shows the sum of the total contributions, the sum of the votes received, and a calculated field showing the average amount of money spent for each vote. Break out each of these values by party and filter out irrelevant data. Include only winners. How much can the director expect to spend per desired vote?
Third, the director has told you that the candidate, a very wealthy businessperson, wants to personally contribute a substantial sum of money to her campaign. The director is hesitant to allow her to do so, worried that if the candidate loses the race, she will suffer a substantial financial loss. The director wants to know whether there is evidence that candidates who have personally contributed large sums of money to fund their own campaign have changed the outcome of their elections.
- Create a Pivot Table that separately displays the top ten candidates of each major party (Democratic and Republican) who have personally contributed the most to their campaigns. Determine which candidates won their race and whether there is an obvious correlation between personal contributions and winning the race.
Data Files
Solution
Challenge21_Solution.xlsx
Solution Video: Challenge 21|EXCEL – Campaign Finance