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
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)