Reference no: EM132244617
Assignment -
Introduction - U.S. Census data (Assignment2.xlsx) shows that 100 counties had enjoyed the largest population growth between 2010 and 2014. You work for Acme Inc., which is actively planning to expand its sales to new regions. It is natural to select a few top states that had the largest above-average growth. Which ones do you recommend? (Don't forget that there are multiple counties of a state listed in this top 100 list.) Download Assignment2.xlsx for the assignment. Follow the instructions below carefully.
Instructions -
(1) Data Preparation
1. The first task is to apply the text manipulation skills from CH5 to break up column B into County and State. Insert three columns after column B and follow the instructions below:
a. Column C: use the FIND() function to find the location of comma in column B.
b. Column D: use LEFT() and other functions to extract only county name from column B.
Make sure (a) the function(s) reference column C, and (b) there is no leading and trailing space in the result.
c. Column E: use RIGHT () and other functions to extract only state name from column B.
Make sure (a) the function(s) reference column C, and (b) there is no leading and trailing space in the result.
d. The result should look like the following. Again, don't just type the data in columns C, D and E. Use functions mentioned in the above steps instead. Note that all column headings should be in the same row for the PivotTable in the next section.
2. Column H: calculate the population growth between 2010 and 2014.
3. Column I: use IF statement to determine the growth in column H is above or below average of column H. If it is equal to the average, put it in the "below average" category.
4. The result at this point should look like the following (in attached file).
(2) PivotTable Analysis
1. Create a pivot table that looks like the following. Name the tab PivotTable.
2. Sort by Average of Growth in descending order so that the state with the largest average population growth is at the top.
(3) Analysis
1. Create a new worksheet called Analysis. Answer the following questions. Type the question and put your answer below it. Make sure you cite numbers from the PivotTable to support your answer. This must be done to receive points.
2. Q1: If you are asked to pick two states that had (a) the largest average population growth, and (b) the largest number of counties on this top 100 list, which states would you recommend?
Explain your reason in detail. Provide a screenshot of your pivot table to help illustrate your reason.
3. Q2: Since Acme, Inc. is a supermarket chain store, one good strategy is to expand its territory into the states near its home state - Illinois. This could reduce the cost of transportation. Recommend two states from this top 100 list (no matter they are in the Above Average or Below Average category). Explain your reason in detail.
a. Note: In order to answer this question, you will "reshape" the pivot table by removing the Above Average? column from the pivot table and sorting the result by Average of Growth in descending order. Provide a screenshot of your final pivot table to help illustrate your reason.
Note - All the needed tables and information are in attached file.
Attachment:- Assignment Files.rar