What types of analyses might you be able to do with this dataset. Identify at least two questions you might be able to answer with it that would apply to a healthcare management problem or need.
HCM 446 Day 1 Activity – Introduction to Excel
Part 1
Directions: Using the information below, create an excel file that includes the raw data and a data dictionary. This data is based on real data but information has been altered for the purpose of this assignment. You will also answer the following question on a separate worksheet.
Question: What types of analyses might you be able to do with this dataset. Identify at least two questions you might be able to answer with it that would apply to a healthcare management problem or need.
What you should submit for Part 1: 1 Excel document that has 3 worksheets that you will label: Raw Data, Data Dictionary, Question Answer. You should enter the raw data below on the raw data sheet. You should create and enter the data dictionary on the data dictionary sheet. All data come from the state health department as the source. Last, you should type your response into cell A1 to the question on the Question Answer Sheet.
Variables
Hospital ID: Hospital ID Number
Year: Year data was collected
Hospital: Hospital name
City: City where hospital is located
Health Service Area: state health service area code
County: County where hospital is located
Medicare: Cases with primary patient payer was Medicare
Medicaid: Cases with primary patient payer was Medicaid
Other Public Payment: Cases with primary patient payer was other public payment
Private Insurance: Cases with primary patient payer was Private insurance
Private Payment: Cases with primary patient payer was Private Payment
Need-Based Care: Cases with primary patient payer was Need Based Care
Total Inpatients: Total inpatients served
id |
yr |
hname |
hcity |
hsa |
hcounty |
Medicare |
Medicaid |
Other Public Payment |
Private Insurance |
Private Payment |
Charity Care |
Total Inpatients |
23 |
2010 |
A-23 |
Quiet |
3 |
Niagara |
7,585 |
3,118 |
88 |
3,238 |
199 |
268 |
14,496 |
24 |
2010 |
A-24 |
Ava |
4 |
Wyoming |
9,493 |
5,330 |
91 |
6,548 |
218 |
2,788 |
24,468 |
32 |
2010 |
A-32 |
Forrest |
5 |
Jackson |
514 |
116 |
0 |
147 |
22 |
17 |
816 |
12 |
2010 |
A-12 |
Elton |
6 |
Shelby |
4,922 |
4,061 |
0 |
5,508 |
0 |
141 |
14,632 |
16 |
2010 |
A-16 |
Elton |
6 |
Shelby |
3,216 |
3,092 |
0 |
2,203 |
0 |
134 |
8,645 |
21 |
2010 |
A-21 |
Elton |
6 |
Shelby |
62 |
5,759 |
76 |
5,258 |
128 |
244 |
11,527 |
22 |
2010 |
A-22 |
Elton |
6 |
Shelby |
453 |
729 |
2,844 |
1,064 |
2 |
69 |
5,161 |
33 |
2010 |
A-33 |
Elton |
6 |
Shelby |
4,567 |
1,260 |
0 |
755 |
298 |
137 |
7,017 |
50 |
2010 |
A-50 |
Elton |
6 |
Shelby |
0 |
288 |
620 |
19 |
0 |
0 |
927 |
46 |
2010 |
A-46 |
Eltonia |
7 |
Shelby |
1,026 |
325 |
85 |
5,142 |
18 |
832 |
7,428 |
31 |
2010 |
A-31 |
Declan |
7 |
Shelby |
834 |
2,058 |
0 |
867 |
8 |
55 |
3,822 |
18 |
2010 |
A-18 |
Elby |
7 |
Shelby |
9,584 |
3,806 |
34 |
4,364 |
277 |
125 |
18,190 |
41 |
2010 |
A-41 |
Ethan |
7 |
Shelby |
3,456 |
1,983 |
0 |
7,815 |
237 |
892 |
14,383 |
15 |
2010 |
A-15 |
Happy |
7 |
Shelby |
6,280 |
2,047 |
0 |
2,419 |
37 |
140 |
10,923 |
17 |
2010 |
A-17 |
Hilbert |
7 |
Shelby |
1,252 |
416 |
58 |
3,457 |
74 |
414 |
5,671 |
5 |
2010 |
A-5 |
Lakeland |
7 |
Shelby |
5,084 |
741 |
20 |
2,158 |
33 |
82 |
8,118 |
7 |
2010 |
A-7 |
Cedar |
7 |
Shelby |
17,134 |
10,517 |
0 |
12,765 |
1,334 |
659 |
42,409 |
47 |
2010 |
A-47 |
Maple |
7 |
Shelby |
1,400 |
77 |
50 |
4,456 |
12 |
1,055 |
7,050 |
13 |
2010 |
A-13 |
Ridge |
7 |
Shelby |
10,943 |
4,029 |
56 |
9,886 |
2,800 |
608 |
28,322 |
35 |
2010 |
A-35 |
Raild |
5 |
Clayton |
424 |
199 |
0 |
208 |
195 |
114 |
1,140 |
10 |
2010 |
A-10 |
Davids |
7 |
Decatur |
6,828 |
2,054 |
43 |
5,503 |
304 |
281 |
15,013 |
40 |
2010 |
A-40 |
Elmira |
7 |
Decatur |
8,324 |
1,790 |
0 |
5,820 |
178 |
165 |
16,277 |
3 |
2010 |
A-3 |
Glenda |
7 |
Decatur |
2,317 |
1,981 |
20 |
878 |
203 |
110 |
5,509 |
4 |
2010 |
A-4 |
Hokey |
7 |
Decatur |
4,309 |
1,204 |
54 |
5,965 |
42 |
73 |
11,647 |
39 |
2010 |
A-39 |
Nathan |
7 |
Decatur |
8,280 |
1,839 |
50 |
11,597 |
264 |
304 |
22,334 |
29 |
2010 |
A-29 |
Winifred |
7 |
Decatur |
8,203 |
3,134 |
118 |
9,429 |
204 |
290 |
21,378 |
43 |
2010 |
A-43 |
Velma |
5 |
Erie |
303 |
56 |
0 |
68 |
28 |
68 |
523 |
48 |
2010 |
A-48 |
Franklin |
5 |
Cayahoga |
237 |
41 |
24 |
40 |
0 |
4 |
346 |
36 |
2010 |
A-36 |
Milton |
5 |
Fredericks |
479 |
161 |
13 |
209 |
4 |
5 |
871 |
14 |
2010 |
A-14 |
Evelun |
8 |
Mecklenburg |
6,355 |
2,481 |
0 |
4,766 |
112 |
283 |
13,997 |
38 |
2010 |
A-38 |
Gail |
8 |
Mecklenburg |
3,819 |
683 |
41 |
3,257 |
49 |
41 |
7,890 |
49 |
2010 |
A-49 |
Gregory |
2 |
Albany |
1,335 |
472 |
24 |
254 |
0 |
2 |
2,087 |
11 |
2010 |
A-11 |
Jones |
8 |
Napa |
4,644 |
603 |
0 |
4,180 |
204 |
117 |
9,748 |
8 |
2010 |
A-8 |
Leon |
8 |
Napa |
7,792 |
2,921 |
143 |
5,063 |
23 |
426 |
16,368 |
1 |
2010 |
A-1 |
Lily |
3 |
Sonoma |
618 |
205 |
0 |
184 |
8 |
25 |
1,040 |
37 |
2010 |
A-37 |
Dillon |
4 |
Cedars |
4,472 |
1,625 |
0 |
1,993 |
125 |
435 |
8,650 |
25 |
2010 |
A-25 |
Fredericks |
3 |
Smith |
544 |
61 |
0 |
75 |
12 |
5 |
697 |
34 |
2010 |
A-34 |
Steeple |
3 |
Smith |
236 |
28 |
0 |
29 |
6 |
0 |
299 |
19 |
2010 |
A-19 |
Smith |
11 |
Hillisborough |
4,022 |
1,590 |
86 |
1,544 |
158 |
280 |
7,680 |
20 |
2010 |
A-20 |
Maiden |
11 |
Hillisborough |
2,133 |
1,425 |
77 |
3,133 |
77 |
531 |
7,376 |
26 |
2010 |
A-26 |
Houston |
8 |
Daded |
772 |
43 |
162 |
610 |
12 |
16 |
1,615 |
27 |
2010 |
A-27 |
Mary |
8 |
Daded |
5,005 |
317 |
1,081 |
2,798 |
60 |
237 |
9,498 |
28 |
2010 |
A-28 |
Charlie |
8 |
Daded |
2,211 |
310 |
717 |
1,362 |
66 |
166 |
4,832 |
6 |
2010 |
A-6 |
Nonna |
4 |
McBride |
3,701 |
1,613 |
0 |
3,372 |
0 |
116 |
8,802 |
44 |
2010 |
A-44 |
Edith |
5 |
Oleans |
506 |
115 |
1 |
102 |
15 |
5 |
744 |
45 |
2010 |
A-45 |
Barbs |
1 |
Simmers |
2,270 |
752 |
58 |
700 |
49 |
1 |
3,830 |
42 |
2010 |
A-42 |
Felton |
5 |
Whitesburg |
415 |
83 |
3 |
123 |
4 |
15 |
643 |
30 |
2010 |
A-30 |
Sexton |
1 |
Fulton |
2,276 |
669 |
0 |
880 |
0 |
274 |
4,099 |
2 |
2010 |
A-2 |
Petersburg |
9 |
Fryer |
2,633 |
1,439 |
53 |
1,690 |
100 |
111 |
6,026 |
9 |
2010 |
A-9 |
Erica |
2 |
Welby |
189 |
11 |
0 |
30 |
2 |
3 |
235 |
Part 2
Directions: Using the dataset that you created Part 1, complete the following questions.
Questions:
1. Create a series of new variables that represent the percentage of patients within each facility that are represented by each payer type. (Hint: To calculate the % of patients on Medicare in a Specific facility, you would need to divide the total number of Medicare patients, by the total number of patients seen. You would do this for each row.)
2. Calculate the appropriate descriptive statistic for each variable in the date set.
3. Create a descriptive statistic table that you would use to publish your analysis of your data set. (Example provided below with fake variable example. You would keep the headers and then add rows for your variables in your data set.)
Table 1. ADD APPROPRIATE TITLE HERE
VARIABLE |
N (%), Median (25th percentile, 75th percentile), Mean + SD |
Sex |
|
Weight
(pounds) |
|
Health
Rating
(10 = perfect health) 1
year ago 6
months ago Current |
6.00 (4.00, 8.00) 7.00 (5.00, 10.00) 8.00 (7.00, 10.00) |
What you should submit for Part 2:
1 Excel document that contains all completed calculations (Q1 and Q2)
1 Word document that provides a table presentation that you would see in a journal article or annual report (Q3)