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