Assignment
1. How many total employees did Smart-Buy have in 2015?
2. How many employees did Smart-Buy have in each of the states that it operated in 2015?
3. How many employees did Smart-Buy have in each department in 2015?
4. How many employees does Smart-Buy have by company (e.g. Largemart and Bullseye)?
5. Which department had the most employees? How many employees did that department have by company?
6. Which departments were unique to Largemart and Bullseye? (Meaning a department exists in one company, but not in the other.) Hint: Simple query taken over to excel to analyze in a pivot table will find this or use a subquery with a NOT IN argument.
7. What was the average salary by department in the 4/15/2015 period?
8. How many employees in Oklahoma and California made over $125,000 in 2015?
9. Which department had the highest gross payroll in 2015? What % of the gross payroll did it represent?
10. How much federal income tax did Smart-Buy pay on behalf of its employees?
11. What was the state income tax paid by quarter by state?
12. Which departments in which states had the highest payroll (gross_pay) costs? List only the top 10.
13. Where should Smart-Buy's new headquarters be and why should it be located there? Prepare a geographic heat map to support your solution for extra credit.
14. Bonus Question: Is the social security tax calculated correctly for employees? If not, how many employees was the security tax incorrectly calculated? Hint: research what the social security cap is.
15. Write your own query answering a question with the data. Find your own insight into the data.
Prepare a report that answers all of these questions including visualizations. Also include a Process Memo that details the technical details of how you arrived at your solutions.