DATA ANALYSIS PROJECT — EXCEL PIVOT AND DASHBOARD
During our training at Nsonye Cohort 3, the students underwent series of assessments and this is the solution to one of those assessments.
Kindly find link to the dataset and instructions on my Github.
Before proceeding to answering the questions, I explored the dataset for errors by checking the data types, spaces and alignment using the TRIM function, and duplications. I found that the data was cleaned for use.
Although dashboard was not part of the questions given to us but I went further to prepare an excel dashboard using the US voter 2012 dataset.
Below are the solutions to the questions.
Question 1: Create a pivot table for the U.S. Voters case study and use it to answer the following:
A. How many states had a Voter Population % below 55%? Which states?
Answer: 5 states had a voter population of less than 55% and the states are West Virginia, Oklahoma, Texas, Hawaii, and Arkansas.
Steps: From the dataset given, I created a pivot table using the state which was place on the row label and a % of voter population column which I created by dividing the Confirmed Voters by Citizen Population. I used the value filter to show states with less the 55%.
B(i). How many confirmed voters in CA were over 65 years old in 2012?
Answer: Total confirmed voters above 65years in California were 2,902,000.
Steps: I created a pivot table, the state and the age was place on the row label and Confirmed Voters on the value label. I filtered the state to only California.
B(ii). What percentage does that represent out of the total confirmed voters in CA?
Answer: The percentage of total confirmed Voters is 21.56%
Steps: I created a pivot table, the state and the age was place on the row label and Confirmed Voters on the value label then I used % of column total to get percentage of confirmed voters for all ages. I filtered the state to only California.
B(iii). What percentage out of the confirmed voters in the entire country?
Answer: 22.30% represents age 65 of the confirmed voters in the state.
Steps: I created a pivot table, the state was place on the row label, the age on the column label and Confirmed Voters on the value label then I used % of grand total to get percentage of confirmed voters for all ages.
C. Show both Citizen Population and Confirmed Voters by Age, as % of Column Total. What percentages of the citizen population do 45- to 64-year-old represent?
Answer: The percentage of the Citizen Population age 45 to 65 is 35.63% while the percentage of confirmed voters is 39.12%
Steps: I created a pivot table, the age was place on the row label, Citizen Population and Confirmed Voters on the value label then I used % of column total to get percentage of citizen population and confirmed voters for all ages.
D. As a politician seeking to improve voter turnout rates among young adults (18–24), which states would you target first?
Answer: As a politician, I will target West Virginia, Arkansas, Texas, South Dakota because they have lowest young voter turnout.
Steps: Turnout is the participation rate (often defined as those who cast a ballot) at a given election. We need to calculate the turnout to get the percentage voter turnout.
% Turnout = Confirmed Voters / Registered Voters * 100
The state was placed in the row labels, the % turnout was added through the insert menu and then placed on the value. The age was placed on the filter pane and then filtered to Ages 18–24. The % turnout was sorted in ascending order.
Question 2: Pivot table for the San Francisco Salaries case study
A. Filter and sort the Pivot to show the 5 employees who earned the highest Base Pay in 2011. Who were they?
Answer: The five employees who earned the highest Base Pay in 2011 are John J Loftus, Naomi M Kelly, Barbara A Garcia, George Gascon and Kenneth A Lombardi
Steps: The employee name was placed on the row label, the base pay was placed on the value pane, and the year was place in the filter. I used the value filter to get my top five employees and filtered the year to 2011.
B. Among employees with >=$100k Base Pay in 2012, did any employee earn more than 50% of their salary from Other Pay? If so, who?
Answer: Anna L Cuthbertson, James Bosch, Frank E Lee, and Garrett Dowd, all earned more than 50% of their Base Pay
Steps: I placed the employee name on the row label, the base pay on the value label, and the year in filter. To get the percentage of employees who earned more than 50% of their base pay, I use the insert to calculate the percentage by dividing other pay by base pay and then placed it in the value pane. Then I used the value filter to filter greater than 50%
C. Clear all filters and pull in Job Category and Job Title as row labels (Titles sorted alphabetically), then group any titles including the word “Curator” into a new category called “Curator”. How many employees held some sort of Curator position in either 2012 or 2013? Among those, who earned the highest average base pay?
Answer: 9 employees held some sort of Curator position either 2012 or 2013. Dennis G Sharp earned the highest average base pay with the job title of Curator 3
Steps: I placed the employee name, job category and job title on the row label, the base pay on the value pane. I group the job category to get all the jobs title under curator. I filter my job category to curator and filtered out year 2011. I then sort the base pay in descending order, and also did the count of employees.
Question 3: Pivot table for the Shark Attack case study
A(i). Show the count of attacks by country — which 3 countries had the highest number of reported attacks over the past 5 years (2012–2016)?
Answer: The 3 countries with the highest number of reported attacks over the past 5 years (2012–2016) are USA, Australia, and South Africa.
Steps: I created a pivot table by placing the country on the row label, case number on the value and date in the filter. I summarized the values by count to get the total count of cases. I then filtered the date 2012–2016. I sorted the case number by descending order.
A(ii). During this period, what % of reported attacks occurred in Spain?
Answer: During this period Spain reported 1.49% of attacks within 2012–216.
Steps: I created a pivot table by placing the country on the row label, case number on the value and date in the filter. I summarized the values by count to get the total count of cases then changed the case number to % of column total. I then filtered the date 2012–2016. I sorted the case number by descending order.
B. Drag the “Area” field to the PivotTable row labels, change the Report Layout to Outline, and filter to show the top 5 areas by count of Case Number, by country. Where in South Africa were shark attacks most frequently reported over the past 5 years?
Answer: Western Cape Province in South Africa was attacked frequently over the past 5 years.
Steps: I created a pivot table by placing the country and area in the row label, case number on value and date in the filter pane. I filtered the country to South African and sorted the case number. I also filtered the date to the last 5 years.
C. Replace “Area” with “Type” and show the Count of Case Number values as % of Parent Total for each country. What % of attacks in New Zealand was unprovoked? How many cases?
Answer: 57.29% of attacks were unprovoked in New Zealand and there were 55 cases
Steps: I copied the previous pivot table and paste then changed the area to Type. I added case number again to the value label then changed the values to % of parent total. I filtered the country to New Zealand.
Question 4: Pivot table for the Stock Market case study
A. Create a calculated field named “Daily Spread” (High — Low), formatted as currency with two decimal places. On which date in the sample did Amazon (AMZN) see the largest price spread? (note: you may have to remove the Daily Change field)
Answer: AMZN (Amazon) saw the largest price spread on 23/10/2009 with 9.03 daily spread
Steps: I dragged the date to row label, the symbol to filter; I then inserted the daily spread column by subtracting the low from high (high — low). I then filtered the symbol to AMZN. I also sorted the daily spread in descending order.
B. Sort dates oldest to newest, and conditionally format the Daily Spread field as a Color Scale, from white (lower values) to green (higher values). Since a large price spread isn’t necessarily good or bad, edit the color scale to show shades from white to blue (instead of green).
Answer/Steps: I copied the previous pivot table and did a conditional formatting to show shades from white (lowest) to blue (highest).
