Policy Analysis and Alternatives

Assignment: DATA MEMO

Page limit: No more than 4 to 5 pages of text – double spaced you may use additional pages
for graphs and figures

You are a policy analyst working at the Illinois Department of Public Health. Your boss wants
you to summarize recent trends in child blood lead levels (BLLs) testing and exposure over time
in Illinois. She has given you some data and a set of questions to explore. She expects you to
produce a memo with your findings, and show your results in the form of tables, graphs and to
identify policy implications of your analysis.
Last page of this assignment provides an outline of what the memo should look like. This is a
different style of memo so no need to come up with criteria or alternatives. Just limit your
analysis to answering the questions below.
Data
You have data for years 2002 to 2006 in one excel file. Each year is on a separate tab and you
may need to do some basic re-arranging of the data to answer the questions below. The data
came from the State of Illinois Open Data Portal (https://data.illinois.gov) and I made no
alterations except saving all of the years in one excel file and removing some missing data. That
is, the data you’ll be working with is a fair representation of what you may encounter as policy
analysts in the real world.
Data has the following variables:
County = Name of Illinois counties, Chicago is separate from Cook County
Total_tested = Total number of children under 6 that were tested for blood lead levels
_2000_population_of_children_6_and_under = total population of children aged 6 and under
based on the 2000 US Census
_10_14_mcg_dl = number of children with BLLs between 10 and 14 mcg/dl
_15_19_mcg_dl = number of children with BLLs between 15 and 19 mcg/dl
_20_24_mcg_dl = number of children with BLLs between 20 and 24 mcg/dl
_25_44_mcg_dl = number of children with BLLs between 24 and 44 mcg/dl
_45_mcg_dl = number of children with BLLs at or above 45 mcg/dl
NOTE to interpret the data:
BLLs are measured as micrograms of lead per deciliter of blood: mcg/ dL = microgram per
deciliter.
In writing your memo answer ALL of the following questions:
1. Use the data about lead exposure in Illinois to complete the following table:
HINT: For each year you have to calculate total tested by adding all values in COLUM 2
=SUM(B2:B104). You calculate total children under 6 in the same way for COLUMN 3
=SUM(C2:C104). Divide these two values to compute share (multiply by 100 to obtain
percent).

a. Add a title to the table
b. Add corresponding explanatory notes
2. Examine the table. How did the share of children tested for lead changed over time?
3. Considering all children that were tested, produce a graph summarizing how the
percent of children with lead levels 10-14 mcg/dl changed over time.
Show this information in a line graph (years on the x axis and share 10-14 mcg/dl on
the y axis) and interpret the graph.
HINT: To make this graph you need to replicate the table from 1 but divide total with 10-
14 mcg/dl by total tested in IL to calculate share with 10-14 mcg/dl over children tested.
Then highlight the Year column and the share with 10-14 mcg/dl column and click insert
graph, choose a line graph
Title your graph, label all axes, and add necessary explanatory notes
4. Examining the graph you produced in 3 explain how the percent of those with lead
levels between 10 and 14 mcg/dl changed over time.
Knowing the percent of children with BLLs over those that were tested is important,
however, why is that we may want to examine the percent over the total?
5. Let’s focus on one year. Which is the county with the largest share of children with
BLLs 10 or above mcg/dl in 2006? What is the share of children in that county with
elevated BLLs? Which is the county with the smallest share of children with BLLs
at or above 10 mcg/dl? What is the share in that county?
a. First you need to add values for columns D though H (to obtain number of
children with lead levels at or above 10 mcg/dl) do this (in a new column I)
b. Second you need to calculate the share by dividing that number by total number
of children (not total tested) and multiply by 100 to obtain percent (in a new
column J)
c. Highlight this column and sort the data from highest to lowest (click yes on
expand selection if a dialogue box opens)
6. Using the data that you just sorted for 2006 produce a bar chart showing the five
counties with highest share of children with lead levels 10 or above as well as the
median share in Illinois. Chart should show five counties in the x axis plus the median
and percent with lead 10 mcg/dl or above on the y axis. Add a title and corresponding
labels and notes. To calculate the median =MEDIAN(J2:J104)
a. What do you conclude from this graph?
b. How do the values of these counties compare to the median?
c. If you wanted to identify some type of pattern. What other type of graphical
representation or data would you want?
7. Now let’s examine Chicago in more detail. Examine the map below:
a. What does the map show?
b. Add a title
8. What do you conclude about geographic exposure to lead in Chicago?
9. What are some factors that can explain this geographic distribution?
10. What do you conclude about lead exposure in Illinois and Chicago from the data
analysis conducted above? What are some policy implications of your findings? If
you could get more data, what other variables would you want?
KEEP READING SAMPLE MEMO STRUCTURE BELOW…
To: Heidi Clark, Division Chief of Health Data and Policy from the Illinois Department of Public
Health (IDPH)
From: YOUR NAME
Date:
Re:
Overview
Summarize your findings in a few short sentences.
Trends in Lead Exposure testing in Illinois
Summarize table 1 (answer to question 2) and paste table 1 after your answer (alternatively you
can put all tables in an appendix)
Summarize figure 1 (answer to question 4) and paste figure 1 here (or put in an appendix).
Not all counties are affected equally
… write you answer to question 5 and 6 and paste figure 2 here (or put in an appendix)
Lead exposure in Chicago
Write your answers to questions 7, paste map, and answer questions 8 through 9 (or put map in
an appendix)
Conclusion and Policy Implications
Write your answers to question 10