Workforce, Workforce, Workforce: How TIP Strategies Visualizes EMSI Employment Data

By Karen Beard
Senior Consultant, TIP Strategies

Note: This is a guest post from Karen Beard of TIP Strategies, an Austin, Texas-based economic development consulting firm. TIP has used EMSI data since 2007 for workforce- and economic-related components of projects. This piece walks through examples of the neat ways TIP visualizes EMSI employment data to answer key workforce-related questions. To learn more about TIP Strategies, visit www.tipstrategies.com.

The common wisdom for economic development has always been “location, location, location.” While sites and physical infrastructure remain a critical piece of the puzzle, the availability and quality of an area’s workforce has become a top driver of corporate investment decisions. TIP Strategies uses EMSI’s employment data, coupled with our experience conducting economic development and workforce planning engagements, to help our clients maximize their talent assets. While many questions can be answered within the Analyst tool itself, the following examples unleash the full power of EMSI by exporting detailed occupation and industry data sets to Excel.

How Do Our Labor Costs Compare?

Labor costs are an important consideration when dealing with site selectors and corporate expansion and recruitment prospects. Our wage comparison chart helps illustrate regional wages in a national context. Figure 1 shows an example of business and finance occupations in a four-county region in the Pacific Northwest. A large number of these positions have median wages that fall roughly in the middle of the national wage range (defined using the 10th and 90th percentiles). When viewed from the perspective of a potential employer, however, the region displays definite advantages with regard to several occupations. These include financial examiners (SOC 13-2061), financial analysts (SOC 13-2051), and tax examiners (SOC 13-2081), all of which have median wages that are lower than expected relative to the national wage range. From a talent recruitment standpoint, higher-than-average median wage rates would make the market more attractive to job candidates from other lower-wage areas (if the higher wage rates are not completely offset by higher living expenses).

Figure 1: Placing median hourly wage rates in a national context

Median wages (line) in the context of the national wage range (bar) for selected business & finance occupations in a four-county region of the Pacific Northwest

What is Driving Demand?

Digging a bit deeper, EMSI data can also help users explore the dynamics of future workforce needs. Broadly speaking, demand for workers is generated by two forces: new job growth (stemming from the addition of new employers or the expansion of existing firms) and the replacement of existing workers. By presenting EMSI’s data on annual openings by type of demand — new growth or replacement — our clients have a starting point for understanding the magnitude of demand and the underlying factors.

Figure 2 shows annual openings by type of demand for the same business & finance occupations presented previously. With the exception of management analysts (SOC 13-111), where demand is equally driven by growth and replacement needs, the majority of openings in this field are likely to be driven by the replacement of existing workers. This type of demand can exacerbate skills shortages, as employers are looking to replace experienced workers, a task that is typically harder than filling entry-level vacancies. Looking at replacement demand, as well as the projected change in employment, can also call attention to needs that might otherwise be obscured. For example, regional employment for claims adjusters (SOC 13-1031) is projected to decline during the period analyzed; however, replacement demand is expected to more than offset these losses.

Figure 2: Factors driving demand

How Do Our Target Industries Align with Our Labor Pool?

An important focus of our work is helping our clients align their industrial recruitment efforts with available labor. For a client targeting automotive expansion, we looked at the demands additional automotive employers could place on the local and regional workforce. The result of the analysis, summarized in Figure 3, was used to demonstrate the supply of labor in key occupations, as well as identify potential gaps where training or talent recruitment efforts may be required. For example, the addition of an assembly plant and likely suppliers could create demand for nearly 600 assemblers & fabricators (SOC 51-2099), far outstripping the availability of the occupation within the surrounding region.

Figure 3: Assessing labor availability for automotive expansion

Comparison of current employment levels with hypothetical staffing scenarios

How Can We Leverage Available Talent Pools?

While labor market advantages are most often thought of in terms of leveraging positive assets (i.e., identifying areas of concentration in key occupations), targeting can also be used to tap into specific talent pools. In our work for a military-dependent community, we used a similar approach to help them prepare for the anticipated loss of 1,200 positions. The layoffs were the result of the completion of a specific mission at the facility, a reduction in workload associated with the winding down of operations in Iraq and Afghanistan, and expected funding cuts resulting from sequestration. Using EMSI’s inverse staffing patterns, our analysis matched the affected occupations with industries that employ them. The results, an excerpt of which is shown in Figure 4, were used to inform the selection of target industries for the region.

Figure 4: Identifying potential industry targets

Industries that utilized impacted occupations for military-dependent community anticipating layoffs

Our goal with each analysis is to present useful data in a clear and engaging way. Since 2007, we have relied on EMSI to provide comprehensive employment data to help us achieve this goal. For additional details on how each chart was created, see the appendix below. To learn more about TIP Strategies, visit www.tipstrategies.com.

TIP STRATEGIES, INC. (TIP) is a privately held Austin-based economic development consulting firm committed to providing quality solutions for public and private-sector clients. Established in 1995, the firm’s primary focus is strategic planning for economic development and workforce organizations.

Appendix: How the Charts Were Created

Figure 1: This chart requires three pieces of information: the median hourly wage rate for the region and the 10th and 90th percentile hourly wage rates for the US. The data are used to create the following four columns, which form the basis of the bar chart:

• Column 1: the wage rate representing the 10th percentile for the US.
• Column 2: a formula subtracting Column 1 from the median wage rate for the region (located in a separate column), with an additional 50 cents (0.50) subtracted from the result. This last step is taken to account for the $1.00 median marker in Column 3.
• Column 3: represents the median using a figure of $1.00 in order to make a segment that is broad enough to detect easily.
• Column 4: a formula subtracting the median wage rate for the region from the 90th percentile for the US (located in a separate column), with an additional 50 cents subtracted to accommodate the median marker.

To achieve the appearance shown in Figure 1, the fill color of the first segment of the stacked bar chart was removed and the $1.00 marker (the third segment) was filled with a darker shade. This creates a bar that shows the national wage range from the 10th to the 90th percentile with the local median wage rate highlighted.

Figure 2: This chart requires employment data by occupation and annual openings for two time periods. First translate the net change in employment for the desired time period into an annual figure (divide the projected net change for each occupation by the number of years in the period being analyzed). Then subtract this figure from the number of annual openings for each occupation. The result shows the estimated openings expected each year due to replacement demand. The two figures — annualized projections and the estimate of annual replacement demand — are then graphed using Excel’s stacked bar chart, with different fill colors for each segment. Negative data points are shown in red. This approach helps highlight areas where net jobs are projected to decline, yet demand for the occupation remains.

Figure 3: For this analysis, percentages from EMSI’s staffing patterns data are applied to a hypothetical employment situation to demonstrate increased demand that could result from the expansion of the automotive industry in the region. Specifically, the distribution of employment by occupation for relevant industries — automotive assembly (NAICS 3361) and core automotive suppliers (NAICS 3362 and 3363) — was used to create estimated staffing needs based on typical firm sizes (supplied by the client). We calculated the percentage these estimated staffing needs represented of the current job base in the client community and surrounding labor shed. An “IF” statement was used to create a graphic representation of the result using symbols to highlight occupations where additional automotive demand was likely to exceed existing supply.

Figure 4: Inverse staffing patterns were downloaded for each of the impacted occupations and used to create a pivot table. A table of source data was created by cutting and pasting the nine columns of staffing patterns data for each occupation into a single worksheet, starting with Column C and pasting each occupation’s data into the row below the final row of the previous occupation’s data. (Column headings are deleted for each subsequent occupation.) For each set of staffing patterns, the SOC code and the name of the occupation are added as Columns A and B, respectively using the “fill” command. Once all occupations are added, the source data is used to create a pivot table in order to translate the raw data into a matrix. The fields for the pivot table are as follows:

• Row labels: NAICS code and name of industry. (Use field settings to eliminate subtotals and change the layout to tabular form.)
• Column labels: SOC code and name of affected occupations.
• Values: Sum of the percent of occupation in the industry. (This data is in the 8th column of the inverse staffing patterns data as presented in Analyst and should be the 10th column of the source table.)

In this example, conditional formatting was used to add visual interest and simplify the results by color-coding the values.

Access EMSI’s full archive of client best practices and case studies here. For more on EMSI’s employment data — available at the county, MSA, and ZIP code level — email Josh Wright. Follow EMSI on Twitter (@DesktopEcon) or check us out on LinkedIn and Facebook.