INTRODUCTION: The work you will do in this data exercise closely mirrors the work that is done by economists in understanding the state of our economy. While many more factors are normally included in this type of research, this exercise will give you just a taste of the type of analysis that goes into discernment of our economic situation. You should reserve a chunk of time for this work, as it may not be that easy to construct and analyze the data, but with efforts and a careful approach, most of you will be highly successful in doing so.
INSTRUCTIONS: Follow these instructions carefully and make sure to answer all FIVE of the requisite questions. You will be graded both on your answers to the questions as well as your data construction. See grading rubric for more information.
(1) Go to: www.bls.gov
(2) On the "Data Tools" tab, choose "multiscreen". This should take you to a page with many different datasets...
(3) Under "employment" you'll see "Employment, Hours, and Earnings-State and Metro Area". In this row, click on the blue "TABLES" picture. Next, choose table D6 from the monthly tables and click on the highlighted "(XLS)". This will open a very big table. You are only interested in the data that is exactly ONE YEAR APART (i.e. the same month in different years) on average weekly earnings columns as well as the first "state and area" column. Keep ONLY the rows corresponding to each state, and not the individual metropolitan areas (e.g. keep "Wyoming" row and cut "Casper" and "Cheyenne" rows). Now, calculate both the change in average earnings as well as the percentage change in average earnings over this one year period (use the midpoint formula to look at percentage changes). Display this information in new columns which you create in the same worksheet. Make sure that the formulas you used are still visible inside of the worksheet-i.e. you need to do your calculations in the cells themselves. Sort the data by state after removing any extra header information (it should be handed in sorted by state, although you may need to sort on other variables to answer the following questions, so make sure to hand it in sorted correctly...).
I want all of the questions (1-5) put into ONE additional worksheet within the same excel file.
QUESTION 1: Which three states had the largest (positive) change in average weekly earnings over this time period (remember to use the correct units in your answer)? How does Florida compare to the rest of the states in terms of the change in average weekly earnings? How does New York compare to the rest of the states in terms of the change in average weekly earnings?
QUESTION 2: How many states experienced an increase in average weekly earnings? How many experienced a decrease or no change? What do you think we can say about our national economic recovery based on this information?
QUESTION 3: Looking next at percentage changes over time, which three states had the largest increase in average weekly earnings? Are these three states the same as the three you picked for question 1? If they are different, explain how this can be the case. How do Florida and New York compare to the rest of the states in terms of their percentage changes in average weekly earnings? How do Florida and New York compare to one another?
QUESTION 4: Based on your answers to Question 3, how do you think the economic recovery is progressing in the state of Florida versus in the state of New York?
QUESTION 5: What other numeric information might you want to have in order to discuss the economic recovery adequately?