Unformatted Attachment Preview
Hinfo.org. Healthcare Research
Market Study Using Census Data
Assignment
Market Study Using Census Data.
Background ................................................................................................................................................ 2
1.1
Introduction...................................................................................................................................... 2
1.2
Parameters ........................................................................................................................................ 2
2 Data Project ............................................................................................................................................... 3
2.1
Introduction...................................................................................................................................... 3
Template file .................................................................................................................................... 4
2.2
2.3
Choice of variables ........................................................................................................................ 5
2.4
Graphs comparing key variables by neighborhood......................................................... 6
Distribution Graphs comparing zip codes within neighborhood ............................... 7
2.5
2.6
Worksheet Format ......................................................................................................................... 8
2.7
Submitting the Worksheet ......................................................................................................... 9
2.8
Team Work ....................................................................................................................................... 9
2.9
Grade Sheet ....................................................................................................................................10
3 Report Project .........................................................................................................................................11
3.1
General Requirements................................................................................................................11
3.2
Sections ............................................................................................................................................11
3.2.1 Executive Summary.................................................................................................................11
3.2.2 Key Variables .............................................................................................................................12
3.3
Submit the Document ................................................................................................................13
3.4
Team Work .....................................................................................................................................13
3.5
Grade Sheet ....................................................................................................................................14
.
There is a video tutorial and template worksheet that accompany this project. Those
materials may not precisely match the details of this project. The intent of the support
material is only to demonstrate methods.
1
© Copyright Patrick M. Bernet, Hinfo.org, 2020. All rights reserved. No part of this document or the accompanying materials
(including worksheets, video tutorials or samples) may be reproduced without express written permission.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 1 of 14
Hinfo.org. Healthcare Research
1
Market Study Using Census Data
Assignment
Background
1.1
Introduction
You work as a medical practice administrator.
The practice wants to open a new location. There are two partners (Renee and
Ahmad, the bosses). They are considering several neighborhoods. They have
discussed this for some time and finally decide to bring you in as the tie-breaker.
Here is what they tell you:
o "Ahmad and I cannot agree on where the new location should be," Renee
said. "So we want you to tell us which neighborhood you think is better.
Do not worry about possible competition in each neighborhood, as we
both agree that competition is the same everywhere. What we really need
is for you to tell us which neighborhood will provide more patients."
o "Patients with money," Ahmad chimed in. "Or at least with good
insurance."
o "And remember we can only speak English and Spanish," said Renee,
referring to the fact that both physicians speak both languages. "So factor
that in and let us know what you think."
o "And if you're wrong… Well, let's just say we'll both be upset,” Renee
warned, leaving you with little doubt that your job would be the first thing
they cut.
1.2
Parameters
This is a two-part project.
o "Data Project". For the first part, you will use Census data, do number
crunching, and create some graphs.
o "Report Project." In the second part of this project, you will take these
numbers and create a report.
You should review the entire project before starting work.
Assigned neighborhoods, the zip codes that make up those neighborhoods, and
practice specialties are in the [Step 0] area of the s10-A worksheet in the
template file. For teams, use the name of the student whose last name comes
alphabetically last.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 2 of 14
Hinfo.org. Healthcare Research
Market Study Using Census Data
Assignment
2
Data Project
2.1
Introduction
For the Data Project, you are 'running the numbers' for the final document which
you will create in the Report Project. The doctors have already told you several
of their key variables. You will have to come up with a few of your own.
Key Variables
o You must have 6 key variables; No more. No less.
o The doctors explicitly mentioned several, so make sure those are among
your key variables. These include:
Number of patients
Which neighborhood has more potential patients? These
patients must be appropriate for the practice: IEPediatricians don't get visits from older patient.
Language
The practice is bi-lingual. Create a new variable that
combines English and Spanish. This will be just one variable.
Ability to pay
You decide on what you think indicates that patients will be
insured and/or have enough money to pay directly.
o At least one of your key variables must be something you developed on
your own (and was not already provided in the template).
It should be a % variable, and not a simple count. For example,
Number of Father-led single households with young children is not
a good custom variable because it is a simple count. But Percent
of all single parent households led by men IS a good custom
variable because it is a %. (And it is probably way too specific to be
practical.)
You have 4943 Census variables to pick from, so should have no
problem developing a unique measure.
o In addition, there are other variables that determine the attractiveness of
different neighborhoods. Part of your job is to find those variables.
See which variables are significantly different for the different
neighborhoods.
This may require some trial-and-error. If the neighborhoods do not
differ on a potential key variable, you should look at other variables.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 3 of 14
Hinfo.org. Healthcare Research
.
2.2
Market Study Using Census Data
Assignment
Template file
A template file is associated with the project, as are tutorial videos explaining
how to use the Excel template.
The [s10a] area comes pre-loaded with almost 5000 Census variables for target
zip codes (neighborhoods are simply groupings of zip codes).
The [s10b] area is your 'work bench'.
o Compute variables that you think might make a good key variable here.
Several samples are included in the template; come up with more
on your own.
o Computations must cell-reference the component variable values in the
[s10a] area. Do not copy values from [s10a] to [s10b].
o Suggest you 'show your work'. For example, to compute "% of singleparent households led by men", my work bench had
Total # of single-parent households
= cell-reference to cell in s10a area with those values
Total # of single-parent households led by men
= cell-reference to cell in s10a area with those values
Then add a formula to divide one by the other to get percent.
o Weight computed variables where appropriate.
Template contains several samples.
For example
Average per-capita income for a neighborhood is not simply
the average of the per-capita income in each zip code in that
neighborhood. This would erroneously give equal weight to
zip codes regardless of the number of people in each zip
code.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 4 of 14
Hinfo.org. Healthcare Research
Market Study Using Census Data
Assignment
The [s10c] area should contain only the six key variables; no more and no less.
o You've already developed these in the [s10b] workbench area, so are just
cell-referencing the values.
[s10d] lets you reformat [s10c] key variables into a format you think will look
better in final reports.
[s10e] is an area where you can develop summary graphs comparing each key
variable between neighborhoods (no need for zip-code level detail). See section
2.4.
[s10f] is an area where you can develop the distribution graphs that explode the
details, showing the key variable value for each separate zip code in a
neighborhood, with the neighborhood average superimposed. See section 2.5.
2.3
Choice of variables
One of the 6 key variables should be a COUNT of the number of potential
patients. For example, a dermatology practice might want a count of the number
of teenagers.
o None of the other key variables can be a count. For example, I could not
combine number of teenagers (pimples) with the number of people who
work in construction (sun exposure). First of all, that might be doublecounting people who are both teenagers and working construction.
Further, if you could be sure of getting a teenager-free construction
worker count, you should combine the two into ONE single variable =
'number of potential patients from all sources".
All other variables should be one of the following
o percentage, such as the % of single-parent households led by men; or
o average, such as average per capita income or average age.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 5 of 14
Hinfo.org. Healthcare Research
.
2.4
Market Study Using Census Data
Assignment
Graphs comparing key variables by neighborhood
For each key variable, generate a graph similar to that in Exhibit 1.
o It shows the value of the key variable for each neighborhood considered.
o One column per target neighborhood.
See tutorial video for graph production instructions.
Exhibit 1. Simple Graph Sample
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 6 of 14
Hinfo.org. Healthcare Research
2.5
Market Study Using Census Data
Assignment
Distribution Graphs comparing zip codes within neighborhood
For one of your key variables, generate a separate 'distribution graph' for each
neighborhood. (Resulting in a separate graph for each neighborhood, each graph
showing the zip codes that make up the neighborhood.)
Distribution graphs should look like Exhibit 2.
o One column per zip code.
o One graph per neighborhood.
Do not combine zip codes from two areas on one graph.
o Zip codes must be sorted by the variable value (and not by zip code
number).
See tutorial video for graph production instructions.
Exhibit 2. Distribution Graph Sample
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 7 of 14
Hinfo.org. Healthcare Research
2.6
Market Study Using Census Data
Assignment
Worksheet Format
You are not required to use the template. It is there to help show how
computations work. If you prefer, you can just copy the raw data ([s10a]) into a
worksheet of your own. If you do so, that worksheet must include:
o Raw data from Census.
o Analysis section / bench test area with formulas computing key variables.
Do not copy values from raw Census data. Access via cell reference.
o Summary section with ONLY the key variables.
Graphs
o Put all graphs in a clearly designated area, such as s10e and s10f. Or in a
completely separate tab.
This worksheet must be easy to understand.
o Rows and columns must be well-labeled so anyone else looking at it will
know exactly what each field is.
o Computations must be shown. No values and no literals. Anyone else
must be able to look at a subtotal and see the formula used to compute it.
o The 'key variables' [s10d] section should be 'publication ready', meaning
that it can be cut-and-pasted directly into a report.
o Graphs must be clearly labeled and self-explanatory.
o Fonts and colors (both in worksheet and in graphs) must be easy-to-read
and professional.
Footnote.
o Area [s10a] heading already has a footnote. That must also appear in the
report prepared in the second half of this project.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 8 of 14
Hinfo.org. Healthcare Research
2.7
Market Study Using Census Data
Assignment
Submitting the Worksheet
General requirements.
o File must be in .XLS or .XLSX format (MS Excel 2003, 2007 or 2010).
o File size must be less than 2 Mg.
Submit the Worksheet.
o File name must your last name. (IE – Smith.doc)
o If you hyphenate, use an underscore (IE - Pinkett_Smith).
o For teams, use the name of the student whose last name comes
alphabetically last.
2.8
Team Work
This project may be performed by teams of students.
o Team work is optional. The instructor will not assign students to teams.
Students do not have to join teams if they do not wish to.
o It is the student's responsibility to insure that team work is completed on
time.
o Entire team receives the same grade.
o Maximum team size is 2 students.
o If there are problems with the team, it is the student's responsibility to
insure the quality of work submitted. Please choose your team member's
carefully, as your grade depends on them. Problematic team members are
the team's responsibility – instructor will not get involved or grant special
exceptions.
Required notification.
o If you will be performing this project as a team, you must e-mail instructor
at least five days prior to the due date. This e-mail must list team
members. This e-mail must also CC all other team members.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 9 of 14
Hinfo.org. Healthcare Research
2.9
Market Study Using Census Data
Assignment
Grade Sheet
The following table summarizes grading criterion. A commented copy of this table will
be pasted into each submitted project.
Category
Minimum
Requirements
Comments
Timely submission. File size maximum.
comments
Writing Basics. Grammar. Spelling. Footnotes. Etc.
comments
Writing Style
Depth
Theory
Application
Points
Weight
0
0%
100
10%
Easy to understand?
Sections and variables clearly labeled.
Graphs properly formatted?
comments
100
20%
Include all needed variables, graphs?
comments
100
30%
100
40%
Choose meaningful variables?
Compute weighed averages correctly?
comments
Total
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
100
(P5-Assign.docx)
page 10 of 14
Hinfo.org. Healthcare Research
3
Market Study Using Census Data
Assignment
Report Project
3.1
General Requirements
This project requires you to take work done for the Data Project and create a
report.
o You already have most of the number crunching and graphs from the Data
Project. You can modify anything in that worksheet (list of key variables,
formatting, graphs) if you wish. Or, you can use what you originally
submitted. Or you can use the posted good example from the Data
Project.
The grade for this part of the project is based solely on submitted document.
Tables and graphs must be integrated into the document.
o Do not put all tables and graphs at the end. Like this assignment, exhibits
should be directly beneath the words that describe them.
Graphs and tables should be easy to read, but you must also use words to explain
to the reader what they are looking at. All tables and graphs must be labeled and
referred to by that label (IE - "Exhibit 1").
3.2
Sections
3.2.1 Executive Summary
Summarize the project requirements. For example:
o What were the important criteria specified by the practice?
o Where did you get data from?
A paragraph or two summarizing why one neighborhood is the best choice.
o Refer to the summary table.
o If there are risks (IE – lots of patients, but not much money), say that here.
Summary table of the key variables.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 11 of 14
Hinfo.org. Healthcare Research
Market Study Using Census Data
Assignment
3.2.2 Key Variables
Prepare the following for each key variable.
Define the variable thoroughly.
o Include a footnoted reference to the Census website.
Although you are already provided the data, the [dd1] workbook in
the template contains the full Census variable id and description.
With that variable id, you can get a very detailed description on the
Census website. Do not simply repeat the official explanation;
interpret it into something simple and easy to remember.
o If the variable is computed (such as a weighted average age), thoroughly
explain the computation and provide a simple example.
Explain why this variable is important to practice location.
o Do you want a high value or low value; and why.
o State which neighborhood is a 'winner' according to this variable.
o Explain how you came to that conclusion; Were you looking at numeric
differences in averages, distributions? Explain your reasoning to the
reader.
Provide a graph comparing the neighborhoods on that key variable.
o For some variables, a simple graph tells the story (sample in Exhibit 1).
o For same variables, a distribution graph tells a more interesting story
(sample in Exhibit 2). For example, it can show it income is evenly
distributed across all zip codes in a neighborhood, or if it is several very
poor areas and just one rich one.
o Explain how to interpret the graph. How does it show which
neighborhood or zip code is 'better'?
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 12 of 14
Hinfo.org. Healthcare Research
.
3.3
Market Study Using Census Data
Assignment
Submit the Document
File must be in.DOCX format.
File size must be less than 2 Mg.
o If you are inserting graphs, you may find that the file grows large. If this
happens, delete the graph, then paste-special as a “Picture (Windows
Metafile)”. Then look at other resources for help on using Word (such as
https://support.office.com/en-us/article/reduce-the-file-size-of-a-picturein-microsoft-office-8db7211c-d958-457c-babd-194109eb9535).
Submit the document.
o File name must your last name. (IE – Smith.doc)
If you hyphenate, use an underscore (IE - Pinkett_Smith).
For teams, use the name of the student whose last name comes
alphabetically last.
3.4
Team Work
This project may be performed by teams of students.
o Team work is optional. The instructor will not assign students to teams.
Students do not have to join teams if they do not wish to.
o It is the student's responsibility to insure that team work is completed on
time.
o Entire team receives the same grade.
o Maximum team size is 2 students.
o If there are problems with the team, it is the student's responsibility to
insure the quality of work submitted. Please choose your team member's
carefully, as your grade depends on them. Problematic team members are
the team's responsibility – instructor will not get involved or grant special
exceptions.
Required notification.
o If you will be performing this project as a team, you must e-mail instructor
at least five days prior to the due date. This e-mail must list team
members. This e-mail must also CC all other team members.
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
(P5-Assign.docx)
page 13 of 14
Hinfo.org. Healthcare Research
3.5
Market Study Using Census Data
Assignment
Grade Sheet
The following table summarizes grading criterion. An commented copy of this table will
be pasted into each submitted project.
Category
Minimum
requirements
Writing Basics
Writing Style
Depth
Theory
Application
Comments
Timely submission. Appropriate length.
Maximum file size limits.
Grammar, spelling, proper footnoting, etc.
Comments.
Was it easy to understand?
Were graphs explained so the reader knew how
to interpret them?
Headings, exhibit labeling?
Comments.
Cover in adequate depth?
Include all required topics, tables and graphs?
Comments.
Display understanding of theory.
Properly create graphs?
Properly compute weighted variables?
Properly interpret graphs and numbers?
Adequately explain findings?
Comments.
TOTAL
©Hinfo.org. Date: 03-27-2020. Version. 2020-B
Points
Weight
100
10%
100
20%
100
30%
100
40%
100%
(P5-Assign.docx)
page 14 of 14
toc
s10
References:
flo
dd1
End of worksheet.
This workbook contains worksheets for use in Census Data Case Projects.
Main working template. Contains raw data and recommended variables.
Start here.
Inventory of all Census files included in the ACS extract worksheet.
(This is a very small subset of all census data available).
Data Dictionary. Official census data definitions, shortened descriptions, and ID name for
every (over 5000) variables in the ACS extract worksheet.
d of worksheet.
…
…
s10. Transform raw data into area analysis. (step 1.0)
Step 0.
Click link to jump to step 0 at bottom of page. There, you be shown which zip c
Step 1,
s10b. Work area where you compute variables for your analysis (leaving raw da
You must develop at least one variable of your own design (in addition to
s10c. Once done computing key variables, list ONLY those few considered 'key'
s10d. (To the right, at top). Delete columns relating to unused zip codes (the on
s10e. Use cell references to create mini-table expressly for generating graphs (in
s10f. Use cell references to manually create mini-table expressly for generating
Step 2.
Step 3.
Step 4.
Step 4.
s10c. Key Variables
Area➔➔➔
Variable
Zip code➔➔➔
General variables
Per capita income
% with health insurance
Language-compatible households (% of total)
Housing tenure
Other ideas (from below):
Transportation availability,
Household size
Specialty related variables
Expected # births
Average age of children 17 and under
Average years of education
Other ideas (from below):
Disability status
Employment occupation
Employment industry
Age 65+
Custom variable of your own design
% HH with children