Use Case
Maria is a 25-year-old US Army veteran, newly returned to the civilian workforce. She has recently completed a six-year commitment with the Army. During her time in theArmy, she worked in supply management and logistics. She has decided to pursue a degree in Management Systems and Information Technology.Maria has asked you to use your skill with data to help her search for the best school for her. She is willing to relocate anywhere in the continental United States, but she has a few criteria that her ideal schools must satisfy: 1) safety (low crime), 2) urban –Maria wants to live the big city life, and 3) start-ups –the school should be in a metropolitan area that ranks highly in entrepreneurialism (she plans to find an internship at a startup while she studies).Maria would like you to help her narrow down her search to a list of schools that she can investigate more closely before making her decision.
Your Task
1.Produce a dataset of schools which satisfy all of Maria’s criteria
2.Rank them from best to worst according to the same criteria.Maria’s schools must:1.be in an urban/metropolitan area.2.be in a state(drill down for city for an extra challenge) that ranks 70thpercentile or higher on Kauffman’s start-up rankings.
3.The 50thpercentileand belowin overall crime.4.offer a 2-year or 4-year degree in Information Technology/Science.You will need to know your cip codes
All the data you need is provided with the exception of the entrepreneurial data, which can be accessed at334: Business AnalyticsJ.Dickson2index/about/archive/kauffman-index-of-entrepreneurial-activity-data-files(Metro Area Components Data 2015)Tips:
1.Read the data dictionaries or codebooks to figure out what the variables mean and which ones you will need to use.
2.Eliminate unneeded columns.
3.Perform any cleaning and standardizationneeded, be sure to documentyour process and reasoning.
4.Engineer a summary variable for school crime so that we can compare schools by levels of crime overall.
5.Engineer a method for ranking the schools in consideration of all of Maria’s criteria taken together.Explain your ranking scheme and reasoning.
6.Develop an Excel model which will allow a user to adjust variables to search for possible schools.(i.e. Urban/Metro, Rural), City Ranking using KauffmansStart Up Rankings, Crime rate. Test using Maria’s use case. Model should show rankings starting with the top recommendation.We will assume to simplify the model that all students want to take an IT or CS degree. Essentially this model will allow Maria to tweak the city size, crime rate and kauffmans rankings to create and adjust her list. You can house data in a database (access or SQL Server)if you want but the goalis to build at minimum an interface in Excel. Document your process!
7.For submission, you need to write a report explaining your process and highlighting steps 1 –7 as necessary. The purpose of your report is to explain how the excel model you build works, why you built the model with the layout and functionality you did. You will also include your user guide on how to manage and use the excel model. Any research needs to be in APA format and the paper must be written in APA. You will also need to submit your excel model for review and validity testing.