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
Female
Male
3179 (49.7%)
3221 (50.3%)
Weight
(pounds)
1
year ago
6 months ago
Current
149.63 + 25.09
144.22 + 25.07
140.55 + 25.08
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)