Research Project 1
Geography 207Name______
Autumn 2014Quiz Section Time______
Research Project #1
Due: October 24, 2014
County Economic Profile
1. Objectives: The first objective of this research project is for you to construct an economic profile for a WashingtonState county, utilizing federal government economic statistics, prepared by the U.S. Bureau of Economic Analysis. The second objective is related to differences in measurement--the issue of alternative measures for similar categories. This exercise is intended to give you experience with and exposure to economic statistical information. However, it makes use of only a tiny fraction of the economic data sources available; you are encouraged to explore additional data sources on your own.
2. Location of Data Sources. The data required to complete the research project are available on the World Wide Web. Paper copies of the same data sources may still become available in the Government Publications collection in Suzzallo Library, and are often found on CD's located at Government Publications or at CSSCR.
Computers in Geography (Smith 401, Smith 411, Smith 415). When not in use for a class, you can log in to these computers. Use this login protocol: Netid\”your UW network id,” following this you should get a prompt for a password, and enter “temporary.” Then follow instructions to establish a permanent password.
3. About data sources.
U.S. Government
[Web-site:]
The U.S.Census Bureau measures economic activity in most industries in a census of the economy every five years. In addition, it makes annual estimates of economic activity for many industries. The censuses are organized by lines of industry; 2007 is the most recent year for which complete economic census data are available. Here is a list of the 2007 Census industries, available in the NAICS classification system:
NAICS codes / Economic Sector11 / Agriculture, Forestry, Fishing and Hunting (separate census)
21 / Mining
22 / Utilities
23 / Construction
31-33 / Manufacturing
42 / Wholesale Trade
44-45 / Retail Trade
48-49 / Transportation & Warehousing
51 / Information
52 / Finance & Insurance
53 / Real Estate and Rental and Leasing
54 / Professional, Scientific, and Technical Services
55 / Management of Companies and Enterprises
56 / Administrative and Support and Waste Management and Remediation Services
61 / Educational Services
62 / Health Care and Social Assistance
71 / Arts, Entertainment and Recreation
72 / Accommodation and Food Services
81 / Other Services (except public administration)
92 / Public Administration (separate Census of Government)
In addition, there are two annual series of importance:
Annual Survey of Manufactures
CountyBusiness Patterns.
The Annual Survey of Manufactures contains detailed statistics at the national and state level on manufacturing output and trade. CountyBusiness Patterns provides county level data at up to a six-digit level of detail on employment, wage payments, and establishment size. Paper copies of the historic census and County Business Patterns are found in Government Publications.
In addition to the Census Bureau, there are two other major sources of federal economic statistics: the U.S. Department of Labor (DOL) ([Website:] ) and the U.S. Bureau of Economic Analysis (BEA)([Website:] http:/ The DOL publishes an online monthly series entitled Employment and Earnings, which provides current measures of employment by industry and information on local labor markets. The DOL also publishes bulletins focusing on employment trends and prospects, and occupational outlooks. It also publishes the Monthly Labor Review, a journal describing industrial trends and special studies undertaken by the DOL. BEA publishes the primary source of statistical information on GNP and national income (entitled Survey of Current Business), which also contains much regional economic information. The BEA also publishes economic forecasts and supplies much of its information on compact discs.
WashingtonState agencies
WashingtonState has a number of agencies that supply economic information. The Dept. of Employment Security administers the unemployment compensation fund, and provides labor market assistance in this state. Almost all employers provide monthly tabulations of wages paid and the number of employees. These data are aggregated across establishments and published online as spreadsheets; the series State and Local Area Industry Employment is a key series. The Department of Revenue provides statistics on sales by industry (by jurisdiction), the Revenue and Forecast Council provide a quarterly bulletin of economic statistics, and the Office of Financial Management provides demographic statistics and analyses of labor force and personal income trends.
4. Assignment: A County Economic Profile & Data Sources Differences
One of the goals of this assignment is the creation of location quotients for a county economy, using either WashingtonState or the United States as a benchmark. Location quotients are index numbers, which describe the concentration of some category of economic or social activity in a region compared to a benchmark. They are simply the % of total activity in a region divided by the same % in the benchmark region. In cases where the resulting calculation has a value greater than 1, then there is a concentration of the activity in the region greater than in the benchmark. In cases where the value is less than 1, then there is less of a concentration of the activity in the region than in the benchmark.
The data you need for this research project are found (1) on the BEA website, and (2) also on the class website (data from the Washington State Department of Employment Security). After obtaining the data you are instructed to collect, you will need to do some simple calculations; these can be done on any computer supporting a spreadsheet program such as Excel.
A. Select a county in WashingtonState for the purpose of this exercise from the following list:
ChelanKitsap
ThurstonYakima
Write the county name here.
B. Employment by Industry for the year 2012
Connect up to the following website:
On the opening page, click on State and Local Area Personal Income in the Regional set of data. This will take you to a webpage entitled Regional Economic Accounts. Under the menu labeled Data, click on Local Area Personal Income and Employment. This will take you to another web page, that has links to a number of data series. Select series CA25/CA25N, total full and part time employment by industry. Then you will have another webpage, with a choice of data for SIC or NAICS classifications. Select the NAICS link, and click “Next Step”. The next screen will give you a choice of geography; select county and then click “Next Step.” Then you will get a screen with states; scroll down in the geographic file, and click on WashingtonState. Click on the Next Step button. The screen will now change again, and a list of counties will pop up. Scroll down to the county you have selected, and click Next Step. Now the next page will have various years for which NAICS data are available; select 2012 and click Next Step. The data you have selected will now come up on screen, with options for saving the file displayed. You can download the data either as a .csv file or as an Excel spreadsheet. Click on the Save button, and then save the file to whatever location you want to put it, and RENAME the file to something like the county name or whatever the geography is that you are saving.
Now you need to get data for your benchmark (either the U.S. or WashingtonState). Repeat this process just described to obtain data for either WashingtonState or the United States, to be used as your benchmark. Go to the State tab on the CA25 website and you will see the U.S. total data; on the Area/Statistic tab you will find the state total. Save whichever of these totals you want to use for your computations. The classification will be exactly as in the table below.
The result of these requests should be data which has the format of the table below.
Note that these data are reported in a nested format. Thus: Total employment = wage and salary employment + proprietors employment. Proprietors employment is split into farm and nonfarm proprietors employment. Total employment is split into Farm employment and nonfarm employment. Nonfarm employment is further divided into private employment and government employment. Private employment is divided into the categories from “forestry, fishing, and related activities” through “other services.” Government employment is divided into federal civilian and military, and state and local government (which is further divided into state government and local government).
LineCode / LineTitle10 / Total employment
By Type:
20 / Wage and salary employment
40 / Proprietors employment
50 / Farm proprietors employment
60 / Nonfarm proprietors employment 2/
By Industry
70 / Farm employment
80 / Nonfarm employment
90 / Private employment
100 / Forestry, fishing, related activities, and other 3/
200 / Mining
300 / Utilities
400 / Construction
500 / Manufacturing
600 / Wholesale trade
700 / Retail trade
800 / Transportation and warehousing
900 / Information
1000 / Finance and insurance
1100 / Real estate and rental and leasing
1200 / Professional, scientific, and technical services
1300 / Management of companies and enterprises
1400 / Administrative and waste services
1500 / Educational services
1600 / Health care and social assistance
1700 / Arts, entertainment, and recreation
1800 / Accommodation and food services
1900 / Other services, except public administration
2000 / Government and government enterprises
2001 / Federal, civilian
2002 / Military
2010 / State and local
2011 / State government
2012 / Local government
After saving the two files, You will now need to undertake the calculation of the location quotients. So, you will need to open both your county file and the state or U.S. file. Copy the column of data for your benchmark, and then paste this into the county spreadsheet, aligning the rows so that the data items for the county and the benchmark are the same. When pasting the data use the Edit-Paste-Special-Values selection in Excel. Now in a blank column, put the active cell to the wage and salary employment row. Now type in the formula for the location quotients: =(county column value and county row value/ county column value & $county total value)/(benchmark column value & benchmark row value/benchmark total column & $benchmark row value). Be sure to put a $ sign before the total row value for the county and for the total employment value for the benchmark. This holds the denominators constant when you extend the computation to all the industries. Now, having obtained the location quotient for the first row of data, you want to extend your computations to the rest of the industries. Put the active cell over the first location quotient, and holding down the left mouse button, drag the selection of cells down to the last industry (local government). Now take the cursor and go to the Edit tab, and select Edit-Fill-Down (or ctrl-D). This will extend your calculations to the rest of the industries. Try to be orderly about the organization of your data, so that you can copy your work from Excel into Word and have a nice table with your location quotients in it. (Truncate the number of digits to the right of the decimal to no more than 3).
B. An Alternative Data Source, Washington State Department of Employment Security. Let us now obtain data for the same county from another source, the Washington State Department of Employment Security. On the class website you will find a spreadsheet labeled qcew-annual-averages-2012-revised.xls
Let us obtain the annual average estimate of employment in Finance and Insurance from the ESD data. ESD ______(Each county has its own sheet; you may need to use the scroll buttons to get to your county). Now, compare this estimate with the value for Finance and Insurance in the BEA data you downloaded. BEA ______Are they nearly the same or quite different? If quite different, what is the explanation for this difference?
5. Write an interpretive essay, which summarizes your results, indicating industries in which the county is specialized or lacks compared to the benchmark you have used. Assume that you were trying to provide someone unfamiliar with this county a picture of its distinctive economic characteristics- what is it specialized in and what does it lack specialization in compared to the benchmark you have used? How does the choice of the benchmark affect your computations? Your essay should build upon the results obtained above to "paint this picture" of your county. ESD and the State of Washington Department of Commerce also have descriptions of county economies in the state on their websites. You can probably use Google to track these down. Regarding the two sources of employment data for your county, what are the differences? Why do you think that they are different (clue: read the comments on-line about definitions of the categories/coverage). Please attach copies of your location quotients when you turn in this exercise.
1