Colorado County Unemployment Data Guidance

Add thirty-six (36) columns between the PLUG and the “series_id” in the original dataset and title them as
follows:
Column
Entry
Column
Entry Column Name
Column
Entry
01 Key -- CountyYearAndMeasureCode 13 03 - Northeast 25 Seasonality
02 State ID 14 04 - Greater Metro 26 Series--Prefix
03 State Desc 15 05 - Central Mountains 27 State
04 State ID and Desc 16 06 - I-70 West 28 County
05 Key -- RegionCountyIDYearAndSeries 17 07 - West Central 29 Series-Suffix
06 RegionCountyIDAndYear 18 08 - Southwest 30 Series Complete
07 Region and County ID 19 09 - San Luis Valley 31 Measure Code
08 County ID Repeated 20 10 - Southeast 32 County Desc
09 County ID 21 Region 33 County and Desc
10 00 - Statewide 22 Region and Desc 34 Seasonality
11 01 - Northwest 23 Area Code 35 Series Name
12 02 - North Central 24 Area Type Code 36 Measure Code and Desc
3) Insert the following files as reference tabs into the file titled:
“00 - la.data.11.Colorado (Annual -- State and County Only)--Data Only.xlsx”.
• 01 – Seasonality (CO).xlsx
• 02 - Series (CO).xlsx
• 03 - State and Counties (CO).xlsx
• 04 - Measure Code (CO).xlsx
4) In the “State and County (CO)” tab, 1) extract the “County Code” from the “area_code”, 2) Concatenate
the “County and Desc”, 3) Identify the “Region Desc” (Vlookup), 4) Concatenate “Region and Desc”, and
5) Concatenate “State ID and Desc” (the Desc is the Area Text)—all currently shown in grey.
5) Parse the Series ID using the following guide into the corresponding fields added to the model:
Series ID Example:
LAUCN080010000000003 (Unemployment Rate: Adams County, CO (U))
series_id--> L A U C N 0 8 0 0 1 0 0 0 0 0 0 0 0 0 3
Character Position -->01 02 030405060708091011121314151617181920
Area Code
Area Type Code
Seasonality
Model Column Nbr--> 23 2425 26 27 28 29 31
Series--Prefix
State
County
Series Suffix
Measure Code
6) To create the data in “Series Complete” (Column 30), either use the trim command along with all but
the last two characters of the series_id or CONCATENATE columns 23-29.
7) In columns 21 and 22, perform the following: for Column 21, use the use the County ID (column 28) to
return the region from the “State and County” tab. Then use the County ID (column 28) to return the
“Region and Desc” from the “State and County” tab for Column 22.
8) Sort ALL of the data by the following fields:
9) In columns 10 to 20, write code that correctly identifies each county in the correct region.
10) In column 09 (“County ID”), take the sum total of the columns 10-14 but force the calculated value to
be a two-digit result (Hint: you are going to need to use the “RIGHT” function for this one).
11) In column 08 (County ID Repeated”), write code that will show the value above if the “County ID” is
equal to “00”; otherwise, the same value as the “County ID”.
12) For columns 05 to 07, use the field name to show the proper analytics keys.
13) For columns 02 and 04, use the County ID to identify the “State ID” found in the “State and County” tab.
The data for column 03 uses the same data as column 32 (“County Desc”).
14) For column 01, use the field name to identify the appropriate entries for the key.
Data Presentation
1) Create a Regional Data Presentation that satisfies the following requirements:
• A Pull-Down which shows all of the regions available
• An entry field for the user to enter a four-digit year.
• A two-digit ID code from 01-65 (Column 1).
• An automatic list which shows the counties in each region (Column 2).
• A column for each of the following items (Columns 3-6):
 03 - unemployment rate
 04 - unemployment
 05 - employment
 06 - labor force
• A field titled “Calc UE Rate” which is equal to [(04 – Unemployment)/(06 – Labor Force)].
2) Create a pie chart visualization of the Total Labor Force. The pie chart must have a header describing what
is being displayed (top), a label on the pie chart entries displaying the county name with the value on a
separate line of the label, and a legend of the colors (to the right).
EXTRA CREDIT: Write a hidden layer of the data that will automatically rank the data from greatest
populations to least (5% Extra) and then have the label only show the top 5 entries with the greatest 5 of
the pie chart pulled out of the rest of the pie (2% Extra).
TIME SERIES
3) Create a Time Series Summary that satisfies the following requirements:
• A Pull-Down which shows all of the regions available
• A Pull-Down which shows the valid counties available for the region selected in the previous pull-down.
4) Create a table that shows:
• “# of People Employed”
• “# of People Unemployed”
• “Total Laborforce”
• “Unemployment Rate”
• Display data from 1990 to 2020 in five (5) year increments, then from 2020 to 2025, display all years
(i.e. 2020, 2021, 2022, 2023, 2024, & 2025).
5) Create a “Dual-Axis” chart which the "# of People Employed" and "# of People Unemployed" on a stacked
bar chart (with the "# of People Unemployed" appearing on the top of the stack), then add the
corresponding unemployment rate as a secondary axis.
6) Add an appropriate title, add labels to the chart entries, label all axes (vertical and horizontal), and ensure
the chart is visually appealing.
EXTRA CREDIT: Add a “Fixed” overlaid statewide unemployment rate line chart to be used as a
“comparison” to the selected county (3% Extra).
(Be sure your charts and tables are all properly labeled and titled—PRESENTATION IS VERY IMPORTANT!!!)