WIOA State Plan Common Elements

OPERATIONAL GUIDE TO DEVELOP THE ANNUAL ALLOCATIONS FOR WORKFORCE DEVELOPMENT AREAS 

WISCONSIN WIOA Allocation Process

Bureau of Workforce Training

Prepared by: Gary Denis, November 2009

Revised: March 2010, May 2015, Oct. 2015, Oct. 2016, Sept. 2017, Jan. 2018, Jan. 2020, Oct. 2021,

Feb. 2022, March 2023 Last Revised: July 2023

Introduction

The workforce development activities carried out in Wisconsin’s 11 workforce development areas (WDAs) are federally funded through the Workforce Innovation and Opportunity Act (WIOA). Funds provided are considered allotments and are announced through a U.S. Department of Labor (DOL) Training and Employment Guidance Letter (TEGL) usually in April, and are provided for Youth, Adult, and Dislocated Worker programs.

Definitions used in the preparation of allocations:

Allocations Developer: The developer is a DWD staff person who is responsible for collecting data from various sources, inputting the data into the allocations spreadsheet, adjusting the spreadsheet, and calculating the allocation shares and subsequent allocations.

Allocations Share: The percent of the funds that a WDA should expect to receive from the funds available for local distribution for that program in the state based on the funding formula and hold harmless provisions.

Allocations Team: A small group of workforce professionals from DWD and the WDBs who review the allocation methodology and allocation results prior to the release of the WIOA allocations for each program year.

Area of Substantial Unemployment (ASU): A collection of contiguous census tracts with a combined area population of at least 10,000 and with a combined unemployment rate of at least 6.45%.

Unemployment in ASUs is one of the three factors in the youth and adult formulae. ASUs are also considered as part of the excess unemployment factor in the youth and adult formulae. American Community Survey (ACS) and Local Area Unemployment Statistics (LAUS) data are used in the creation of ASUs.

Data Source Contacts: The key staff to communicate with at agencies that produce data used in the allocation formulae. (Attachment A contains a listing of contacts).

Declining Industries: Industry sectors (2-digit North American Industrial Classification System (NAICS) code) that have fewer jobs than the sector had in a criterion year. For the current PY allocations, for each county, the average monthly employment total in each NAICS sector in the most recent calendar year of available data is compared to the employment total from five years prior. If a NAICS sector in a county has less employment in the current data than the base year, then that difference factors into the total employment loss. If a NAICS sector in a county has more employment in the current year than the base year, then it contributes zero to the employment loss tally. The NAICS sector values within the counties within each WDA are summed to get WDA totals. Also factored into the WDA totals is the employment loss in "unknown county" by NAICS sector; this total is distributed into the WDA loss totals based on each WDA's percent of current year total employment. The percentage of the total employment loss by WDA is then calculated. Declining Industries uses data from the Quarterly Census of Employment and Wages (QCEW) and is one of the six dislocated worker formula f actors.

Disadvantaged Adults: Individuals aged 22 through 72 who received an income, or is a member of a family that received a total family income that, in relation to family size, does not exceed the higher of the poverty line, or 70% of the Lower Living Standard Income Level (LLSIL). WIOA requires college students and members of the Armed Forces to be excluded from the number of disadvantaged youth and adults to the extent practicable. Rural Concentrated Employment Program areas (WDA7 in WI) have a special adjusted LLSIL. Disadvantaged adult data come from a special tabulation of American Community Survey (ACS) data and is one of the three adult formula factors.

Disadvantaged Youth: Individuals aged 16 through 21 who received an income, or is a member of a family that received a total family income that, in relation to family size, does not exceed the higher of the poverty line, or 70 percent of the Lower Living Standard Income Level (LLSIL). WIOA requires college students and members of the Armed Forces to be excluded from the number of disadvantaged youth and adults to the extent practicable. Rural Concentrated Employment Program areas (WDA7 in WI) have a special adjusted LLSIL. Disadvantaged youth data come from a special tabulation of American Community Survey (ACS) data and are one of the three youth formula factors.

Excess Unemployment: Unemployment above a 4.5% unemployment rate in a WDA or within the WDA's ASU, whichever is greater. Excess unemployment is one of the three factors in the youth and adult formulae. ASU data and additional LAUS data are considered.

Farmer-Rancher Economic Hardship: For each county, the difference in the number of farms from the most recent Census of Agriculture to the previous Census of Agriculture is calculated. If a county has fewer farms in current data than the base year, then that difference factors into the total farm loss. If a county has more farms in the current data than the base year, then this county contributes zero to the farm loss tally. The county values within each WDA are summed to get WDA totals. The percentage of the total farm loss by WDA is then calculated. Farmer-rancher economic hardship is one of the six dislocated worker formula factors.

Insured Unemployment: The number of monetarily eligible persons in the state who submitted a claim for regular unemployment compensation in a benefit year that expired in the most recently completed calendar year. Insured unemployment is one of the six dislocated worker formula factors and is sourced from the UI Division in DWD.

Internal Review Team: A small group of DWD staff who review the allocation spreadsheets, DOL allotment announcements, and related policies to ensure that allocation shares calculation is correct prior to release to the Allocation Team for review and comment.

Long-term Unemployment: The number of persons in the state who received regular unemployment compensation payments for at least 15 weeks in a benefit year that expired in the most recently completed calendar year. Long-term unemployment is one of the six dislocated worker formula factors and is sourced from the UI Division in DWD.

Lower Living Standard Income Level (LLSIL): An income level determined annually by the Secretary of the Dept. of Labor based on the most recent lower living family budget issued by the Secretary of the Dept. of Labor.

Plant Closing and Mass Layoff : The number of persons in the state reported as being laid off by employers who filed layoff notices with DWD under the Wisconsin Business Closing and Mass Layoff (WBCML) law and/or the federal Worker Adjustment and Retraining Notification (WARN) Act. The figures come from the Bureau of Workforce Training (BWT) in DWD-DET. The timeframe is the most recently completed calendar year. Plant Closing and Mass Layoff is one of the six dislocated worker formula factors.

Unemployment Concentrations: The WDA must meet or exceed the state unemployment rate or have 10% or more of the state's unemployment in order to qualify. Unemployment Concentrations is one of the six dislocated worker formula f actors and is sourced from the most recently completed program year (July-Dec benchmarked, Jan-May final, June preliminary) LAUS data.

Factors Used in Calculating the WIOA Allocations for WDAs

Youth Formula

For each factor, the percent received by each WDA is determined by the percent of state total that a WDA has of the factor.

1. 33 1/3% from unemployment in ASUs
2. 33 1/3% from excess unemployment
3. 33 1/3% from disadvantaged youth

To reduce some of the volatility of the formula factors on WDAs, a hold harmless provision is applied to the results of the three-factor formula. A WDA shall not receive an allocation percentage for a program year that is less than 90% of the average allocation percentage of the WDA for the two program years.

Adult Formula

For each factor, the percent received by each WDA is determined by the percent of state total that a WDA has of the factor.

1. 33 1/3% from unemployment in ASUs
2. 33 1/3% from excess unemployment
3. 33 1/3% from disadvantaged adults

To reduce some of the volatility of the formula factors on WDAs, a hold harmless provision is applied to the results of the three-factor formula. A WDA shall not receive an allocation percentage for a program year that is less than 90% of the average allocation percentage of the WDA for the two preceding program years.

Dislocated Worker (DW) Formula

For each factor, the percent received by each WDA is determined by the percent of state total that a WDA has of the factor.

1. 25% from unemployment concentrations
2. 18.75% from long-term unemployment
3. 6.25% from insured unemployment
4. 25% from declining industries
5. 12.5% from plant closing and mass layoff
6. 12.5% from farmer-rancher economic hardship

To reduce some of the volatility of the formula factors on WDAs, a hold harmless provision is applied to the results of the formula. A WDA shall not receive an allocation percentage for a program year that is less than 90% of the average allocation percentage of the WDA for the two preceding program years.

Review Teams

Internal Review Team: Whenever internal review is needed, the following positions may be consulted.

• Policy Initiatives Advisor
• Bureau of Workforce Information and Technical Support (BWITS) Director or Deputy Director
• BWITS Economic Research Unit Lead Worker
• Governance/Compliance Section Chief
• DET Budget Analyst assigned to WIOA

Allocations Team: The Allocations Team consists of DWD and WDB staff who will review the process and results to ensure accuracy and compliance with the requirements that govern the development of WIOA allocations. Members include:

• Administrator, Div. of Employment and Training
• Director, Bureau of Workforce Training
• BWITS Economic Research Unit Lead Worker
• Policy Initiatives Advisor
• Bureau of Workforce Information and Technical Support (BWITS) Director or Deputy Director
• Directors from two WDBs

Calculating the Allocations

The chart below lists the tabs within the 16-tabbed MS-Excel workbook that calculates the allocations for the youth, adult, and dislocated worker programs. Each tab is classified by its primary purpose and the program it influences. A tab that is classified as a calculator generally takes data that has been input in another location and processes it to produce a factor for the formula. An input tab generally is a storage location for data that has been provided by a data source and will be used by a calculator tab. In some instances, a tab will be both calculator and input. The info/print tabs contain information of interest or summarize results in a printable format.

Columns 2, 3, and 4 describe the purpose of the tab. Columns 5,6, and 7 describe the program that tab applies to

Specific Instruction for each Tab

Reference: This tab is a location to enter data that will be referenced throughout the workbook. For example, every year the program year must be changed manually on virtually every tab. By referencing a cell on this sheet, all the program years can be changed with one entry. Similar mass references are included in this tab.

DOL Allotment: This tab calculates the gain/loss from the prior year; calculates the state admin., 15% discretionary fund, special response fund and funds for local distribution; and provides a history of allotments received from DOL.

The allocation developer has a number of procedures to execute on this sheet. They are: Change the column C5-C12 formula reference so it refers to the correct information in the History Section. Enter new PY allotments from the DOL TEGL into the first free column to the right of the “History Section.”

1. Copy the cell references from this new column in the History Section to D5-D12.
2. Enter the TEGL number and date as the information source in Cell B15 and at the end of the source list starting in A27.
3. The DOL Allotment Tab references the local distribution amounts in column K5-K7. Allocation shares multiplied by the local distribution amounts generates WDA allocations by program.

Print Allocations: The Print Allocations tab calculates and displays each WDA’s allocation shares, allocation amount, and hold harmless status for each program. All the calculations on this tab are automatic. The allocation shares come from the Calc Adult, Calc Youth and Calc DW tabs. Allocations are generated by multiplying the allocation share by the allotment available for local distribution from column K on the DOL Allotment Tab. The source notation comes from the DOL Allotment tab and the program year comes from the Reference tab.

Calc Adult: This tab applies the hold harmless provision to the Adult allocation shares calculated on the 3-Part tab, maintains a history of prior allocation shares, and exports the allocation shares and the hold harmless designation to the Print Allocations tab. The tab has three sections.

• Section 1 calculates whether a WDA will be held harmless and adjusts the allocation shares of those WDAs not held harmless down to make up for the hold harmless adjustment. This iterative process may cause some WDAs not initially held harmless to be protected as their share falls to provide the hold harmless to eligible WDAs. This section compares the allocation shares calculated on the 3-Part tab to 90% of the Average of Prior 2 Years Share and chooses the higher of the two shares. If any WDA is held harmless, the sum of the shares will exceed 100%. Each subsequent column checks if each WDA share in the previous column exceeds the hold harmless share and if so, reduces the share in proportion to its contribution to exceeding 100% in the sum of the prior column. This process progressively reduces the sum of the column closer to 100%. When the column sum rounds to 100.00000%, or there have been 25 recalculations, whichever is later, recalculation can stop.
• Section 2 is a historical table of allocation shares by WDA. The shares from the most recent 2 years prior to the allocation year provide the data for the Average of Prior 2 Years Share in column C in Section 1.
• Section 3 is used to double check the final adjustment against the 90% hold harmless amount and label the hold harmless WDAs. This section contains all of the exportable data from this sheet for the Print Allocations tab.

Most of the calculations are automatic in this sheet though there are some maintenance functions that the Allocations developer must perform. They are:

1. In Section 2, ensure that the Allocation Shares for the most recent program year are actual values, not cell references. If they are cell references or formulas, copy the Allocation share column onto itself using Paste Special, Values and Formatting. This will convert the relative references to values.
2. Copy the two most recent Program Years data from this Section to columns C & D of this Section. Section 1, column C uses this data to calculate the Average of the Prior 2 Years Share.
3. After the calculations have been run, copy column AE or appropriate column (see note below) from Section 1 to the appropriate PY blank column in section 2. Use the Paste Special, Values and Number Formatting when copying to ensure that the allocation shares are recorded as values rather than relative reference cells or formulas. Note: if the sum of column AE in Section 1 does not round to 100.00000%, copy the first column in Section 1 where the total rounds to 100.00000% to Section 2.
4. The Allocations developer must change the references in Section 3, column C to reflect the Section 1 column used or use Paste Special, Values and Number Formatting to place the correct numbers here.

Calc Youth: This tab applies the hold harmless provision to the Youth allocation shares calculated on the 3-part tab, maintains a history of prior allocation shares, and exports the allocation shares and the hold harmless designation to the Print Allocations tab. The tab has three sections.

• Section 1 calculates whether a WDA will be held harmless and adjusts the allocation shares of those WDAs not held harmless down to make up for the hold harmless adjustment. This iterative process may cause some WDAs not initially held harmless to be protected as their share falls to provide the hold harmless to eligible WDAs. This section compares the allocation shares calculated on the 3-part tab to 90% of the Average of Prior 2 Years Share and chooses the higher of the two shares. If any WDA is held harmless, the sum of the shares will exceed 100%. Each subsequent column checks if each WDA share in the previous column exceeds the hold harmless share and if so, reduces the share in proportion to its contribution to exceeding 100% in the sum of the prior column. This process progressively reduces the sum of the column closer to 100%. When the column sum rounds to 100.00000%, or there have been 25 recalculations, whichever is later, recalculation can stop.
• Section 2 is a historical table of allocation shares by WDA. The shares from the most recent 2 years prior to the allocation year provide the data for the Average of Prior 2 Years Share in column C in Section 1.
• Section 3 is used to double check the final adjustment against the 90% hold harmless amount and label the hold harmless WDAs. This section contains all of the exportable data f rom this sheet for the Print Allocations tab.

Most of the calculations are automatic in this sheet though there are some maintenance functions that the Allocations developer must perform. They are:

1. In Section 2, ensure that the Allocation Shares for the most recent program year are actual values, not cell references. If they are cell references or formulas, copy the Allocation share column onto itself using Paste Special, Values and Formatting. This will convert the relative references to values.
2. Copy the two most recent Program Years data from this Section to columns C & D of this Section. Section 1, column C uses this data to calculate the Average of the Prior 2 Years Share.
3. After the calculations have been run, copy column AE or appropriate column (see note below) from Section 1 to the appropriate PY blank column in section 2. Use the Paste Special, Values and Number Formatting when copying to ensure that the allocation shares are recorded as values rather than relative reference cells or formulas. Note: if the sum of column AE in Section 1 does not round to 100.00000%, copy the first column where the total rounds to 100.00000% in Section 1 to Section 2.
4. The Allocations developer must change the references in Section 3, column C to reflect the Section 1 column used or use Paste Special, Values and Number Formatting to place the correct numbers here.

Calc DW: This tab applies the hold harmless provision to the Dislocated Worker allocation shares calculated on the DW 6-Part tab, maintains a history of prior allocation shares, and exports the allocation shares and the hold harmless designation to the Print Allocations tab. The tab has three sections.

• Section 1 calculates whether a WDA will be held harmless and adjusts the allocation shares of those WDAs not held harmless down to make up for the hold harmless adjustment. This iterative process may cause some WDAs not initially held harmless to be protected as their share falls to provide the hold harmless to eligible WDAs. This section compares the allocation shares calculated on the DW 6-Part tab to 90% of the Average of Prior 2 Years Share and chooses the higher of the two shares. If any WDA is held harmless, the sum of the shares will exceed 100%. Each subsequent column checks if each WDA share in the previous column exceeds the hold harmless share and if so, reduces the share in proportion to its contribution to exceeding 100% in the sum of the prior column. This process progressively reduces the sum of the column closer to 100%. When the column sum rounds to 100.00000%, or there have been 25 recalculations, whichever is later, recalculation can stop.
• Section 2 is a historical table of allocation shares by WDA. The shares from the most recent 2 years prior to the allocation year provide the data for the Average of Prior 2 Years Share in column C in Section 1.
• Section 3 is used to double check the final adjustment against the 90% hold harmless amount and label the hold harmless WDAs. This section contains all of the exportable data from this sheet for the Print Allocations tab.

Most of the calculations are automatic in this sheet though there are some maintenance functions that the Allocations developer must perform. They are:

1. In Section 2, ensure that the Allocation Shares for the most recent program year are actual values, not cell references. If they are cell references or formulas, copy the Allocation share column onto itself using Paste Special, Values and Number Formatting. This will convert the relative references or formulas to values.
2. Change the references in Section 1, column C to reference the allocation shares from the two most recent program years prior to the year allocations are being calculated for.
3. After the calculations have been run, copy column AE or appropriate column (see note below) from Section 1 to the appropriate PY blank column in section 2. Use the Paste Special, Values and Number Formatting when copying to ensure that the allocation shares are recorded as values rather than relative reference cells or formulas. Note: if the sum of column AE in Section 1 does not round to 100.00000%, copy the f irst column in Section 1 where the total rounds to 100.00000% to Section 2.
4. The Allocations developer must change the references in Section 3, column C to reflect the Section 1 column used.

3-Part: This tab is used to calculate allocation shares for the adult and youth formulae. For adult (youth), the three factors are unemployment in ASUs, excess unemployment, and disadvantaged adults (youth). Each f actor is 1/3 of the share. The results of this page are exported to Column E in Section 1 in the Calc Adult and the Calc Youth tabs where they are compared to the hold harmless levels.

The Allocations developer has very little to do on this sheet. Everything is either calculated by formula from data brought into this tab from the ASU-EU Calculator tab, data imported directly from the ASU-EU Calculator, or data hard coded into columns I & J. The data in column I, “Adult Disadvantaged,” & J, “Youth Disadvantaged,” change once every five years and must be updated manually based on instructions from the U.S. Department of Labor.

DW 6-Part: This sheet is used to aggregate the six dislocated worker factors together to generate an allocation share based on economic factors only. Each of the columns represent results from other tabs and their weighting factor. For each WDA, Column C is the sum of each factor value multiplied by its weighting in the 6-Part formula. The current weighting numbers in row 20 are informational. The values in Column C are exported to the Calc DW tab, Column E for use in adjusting the allocation shares by hold harmless.

Most of the work on this tab is managed by the spreadsheet itself. The Allocations developer only updates dates.

The data for this tab comes from a variety of other tabs. The data from those other tabs are brought to this tab to calculate the DW allocation share before the hold harmless provision is applied.

• Insured Unemp comes from column D of the Insured and LT Unemp tab.
• The % Insured Unemp comes from column C of the Insured and LT Unemp tab.
• The data for LT Unemp comes from column F of the Insured and LT Unemp tab.
• The % LT Unemp comes from column E of the Insured and LT Unemp tab.
• The Number Affected in WBCML and/or WARN Notices comes from column E of the Plant Closing and Mass Layoff tab.
• Percent of Total Affected by WBCML and/or WARN Notices is accessed from column F of the Plant Closing and Mass Layoff tab.
• The % of Unemp with threshold comes from column C of the Unemp Concentrations Tab.
• WDAs where unemployed concentrations are a factor is imported from the Unemp Concentrations tab, column D.
• The # of Farm Loss comes from the Farmer-Rancher Economic Hardship tab, column E.
• The % of Farm Loss comes from the Farmer-Rancher Economic Hardship tab, column F.
• Employment Decline is from the Declining Industries tab, column I.
• The % Tot Decline comes from column J of the Declining Industries tab.
• Unemployed (LAUS) is from the ASU-EU Calculator tab, column E. The percent of unemployed is based on the unemployed numbers in column Q of the DW-6-Part Tab. The figures here are just for reference.

Plant Closing and Mass Layoff: WDAs receive a prorated share of the total number of persons reported as being laid off from their employment by employers who file a layoff notice with DWD under the WBCML law and/or the federal WARN Act. The data used reflects impacted employee counts from the initial layoff notices filed with DWD during the previous calendar year; the data does not cover subsequent updates that alter the impacted employee counts. The primary reason for adopting this approach is that updates filed with DWD may adjust impacted employee counts from a prior calendar year. When an employer notice affects multiple WDAs and the employer does not specify the location of the affected employees, all impacted WDAs receive an equal share from the notice. The Program and Policy Section within BWT supplies this data. The Allocations Developer copies in this data by WDA from the electronic report provided by the Program and Policy Section to the next available column in the Historic Information section.

The number of employees impacted by WBCML/WARN notices for the most recently completed calendar year are entered in the appropriate calendar year columns. The percent shares are calculated automatically in column F. This data automatically exports to the DW 6-Part Tab, columns H & I.

Insured and LT Unemp: The purpose of this tab is to calculate the relative share each WDA will receive of the state’s long-term unemployment and insured unemployment for the Dislocated Worker calculation. Long-term unemployment and insured unemployment data are sourced from the Unemployment Insurance Division.

• Insured Unemployment must be entered into cells D7 to D17 and D19 if applicable. Column C calculates the WDA shares of the state total.
• Long-term unemployment is entered for each WDA in cells F7 through F17 and F19 if applicable. Column E calculates the WDA shares.

The tab exports Columns C, D, E and F automatically to the DW 6-Part tab.

Unemp Concentrations: The purpose of this tab is to determine whether a WDA qualifies as having a concentration of unemployed. The tab calculates the state’s unemployment rate for the prior program year, and then calculates the value of 10% of the state’s unemployment for the prior program year. If a WDA’s unemployment rate (as calculated in column G) equals or exceeds the state rate or if the WDA number of unemployed (column H) equals or exceeds 10% of the state’s total unemployment, the WDA qualifies for this factor. The WDAs that qualify receive a relative share based on their share of the total unemployment of the qualifying WDAs.

The Allocations Developer has nothing to manipulate on this tab. All data used to determine this factor comes from other tabs or is the result of within tab calculations. No input required. The tab will export cells C8 through D19 to DW 6-Part cells J7 to K18.

The box that is marked informational in this tab illustrates how each WDA qualifies for the factor. Some WDAs qualify by having 10% or more of the state’s total unemployment, some because their unemployment rate is equal to or higher than the state’s unemployment rate, and in some cases because they meet both. A WDA does not qualify a larger share by meeting both benchmarks.

Farmer-Rancher Econ Hardship: This tab calculates each WDA’s share of farmer-rancher economic hardship experienced in the state. For each county, the difference in the number of farms from the most recent Census of Agriculture to the previous Census of Agriculture is calculated. If a county has fewer farms in current data than the base year, then that difference factors into the total farm loss. If a county has more farms in the current data than the base year, then this county contributes zero to the farm loss tally. The county values within each WDA are summed to get WDA totals. The percentage of the total f arm loss by WDA is then calculated. Cells E7:F18 are exported to the DW 6-Part tab to columns L & M.

Declining Industries: This tab has two sections.

Section A calculates WDAs' relative shares of the State's declining industries total.

• Column I sums the two-digit NAICS declining industry figures by WDA.
• Column J is the WDA share of the State's declining industry total.
• Data in columns I & J move to the DW 6-Part tab in columns N & O.

Section B provides the raw data by two-digit NAICS. The most recent calendar year for which data is available is compared to the employment total from five years prior. If a NAICS sector in a county has less employment in the current data than the base year, then that difference factors into the total employment loss. If a NAICS sector in a county has more employment in the current year than the base year, then it contributes zero to the employment loss tally. The NAICS sector values within the counties within each WDA are summed to get WDA totals. Also factored into the WDA totals is the employment loss in "unknown county" by NAICS sector; this total is distributed into the WDA loss totals based on each WDA's percent of current year total employment. The percentage of the total employment loss by WDA is then calculated.

All of the county data is copied in from a WisConomy data pull or QCEW internal file for suppressed data.

• Column I: This column calculates the number of job losses that have occurred by subtracting industry employment for the base year data set (column G) from the industry employment in the most current year (H). If jobs have been lost in the comparison, the number lost will show. If there are job gains or no loss, the cell will appear blank.

ASU-EU Calculator: This tab is used to take ASU data (Column O from ASU calculations tab) and LAUS data (Column N from ASU calculations tab) to calculate the WDA share of the state’s ASU and Excess Unemployment totals. The Allocations Developer's role on this tab is checking and validating as all actions are done automatically.

• Column G calculates the ASU Unemployment Rate for each geographic area listed in column A by dividing column C data by the sum of columns C & D. The resultant ASU unemployment rate is checked by column I to discern if the rate is at least 6.45%. If it is, the total number of ASU unemployed in the WDA (column C) is entered into column I in the summed row for each WDA. If the ASU unemployment rate is less than 6.45%, a zero is entered into sum row of column I. This value is then divided by the state total ASU unemployment to report the WDA share of ASU unemployment in the cell below the sum row for each WDA.
• Column H calculates the LAUS unemployment rate for each geographic area listed in column A by dividing the LAUS unemployment in column E by the sum of columns E & F. Column J calculates the number of unemployed equivalent to 4.5% of the labor force and subtracts that number from the number of LAUS unemployed in column E. If this value exceeds zero it is entered into column J, otherwise zero is entered. This value is LAUS Excess Unemployment.
• Column K calculates ASU Excess Unemployment. Column K calculates the number of unemployed equivalent to 4.5% of the labor force and subtracts that number from the number of ASU unemployed in column C. If this value exceeds zero it is entered into column K, otherwise zero is entered. This value is ASU Excess Unemployment.
• Column L compares LAUS Excess Unemployment (column J) to ASU Excess Unemployment (column K) and puts the greater value in column L.
• Columns E & F data in the “sum” row for each WDA is exported to the Unemp Concentrations tab columns H & I respectively and are used to calculate an unemployment rate which should match the LAUS unemployment rate in the “sum” row of column H on this tab.
• Columns I & L data in the “sum” row are exported to the 3-Part tab, columns L & K respectively. There this data is used to calculate the WDA share of excess unemployment (column E) and share of substantial unemployment (column F). The shares calculated here should equal the shares in the row just below the “sum” line for each WDA on this tab.

ASU Calculations: The Local Area Unemployment Statistics (LAUS) program manager conducts the ASU exercise every year for the US Department of Labor using county level unemployment data from the LAUS program and the most recent American Community Survey 5-year estimates from the US Census Bureau. In order for an area to qualify as an ASU, the total unemployment rate has to be at least 6.45%, the population has to be at least 10,000, and all components of the ASU area have to be contiguous. An ASU can be a county, city, or combination of areas, such as census tracts or counties.

In order to develop a qualifying ASU, the 12-month average of the LAUS data for the most recent period ending in June is calculated for each county in the state. July-December of the prior year uses LAUS benchmarked data; data for the current year January-May uses revised data; and June uses preliminary data (If the LAUS data are reviewed in the following year, the newer figures are likely not to match exactly to the LAUS data in the ASU Calculations tab because of revisions and/or benchmarking). Census data is available by census tract. If an area does not have a 12-month average unemployment rate of at least 6.45%, surrounding census tracts can be added (or subtracted) until the contiguous area reaches a rate of at least 6.45%. It is possible for a WDA to not have an ASU.

Since monthly LAUS data is not available at the census tract level, the ratio of unemployment in census tracts in a given county that are being included in the ASU area to total unemployment in the county (column AB) is multiplied by the total LAUS unemployment and employment (12-month average) for that county. This total is called the ACS-Share and is column O in this tab.

Once as many areas of the state as possible have been exhausted into ASUs and the data has been submitted and approved by the US Department of Labor, the ASU designations and the corresponding data are used internally in the allocation process.

Updates: This tab will be used to document changes to the workbook when notable changes are performed.

Attachments

Attachment A: Data Source Matrix