Currency Spending on durable medical equipment. Equipment Non-Durable Currency Spending on non-durable medical equipment.

Loading....

Loading....

Loading....

Loading....

Loading....

Full text

(1)

Introduction to Computer Applications

West Virginia University Page 1 of 7 Modified 9/14/2015 Version 4.6

Background Information

Every year, there is a growth in healthcare expenditures in the United States. In 2008, spending on healthcare amounted to 15.2% of the economy, the largest of any developed country. Bringing healthcare costs under control is a major concern for government and industry alike.

Problem Statement

In this project, students will analyze the healthcare expenditures for a span of 30 years to understand the factors that affect the annual increase in expenditures.

Project Instructions

IMPORTANT: Complete the below steps in the order they are given. Completing the steps out of order may complicate the project or result in an incorrect result.

1. Download the following files onto your computer:

a. expenditures.xml โ€“ Information on healthcare expenditures in the United States from 1979 to 2009.

Column Name Type Description

Year Number Year for the statistics.

Hospital Care Currency Spending on hospital care.

Clinical Svcs Currency Spending on physician and clinical services.

Professional

Svcs Currency Spending on professional services. Dental Svcs Currency Spending on dental services.

Personal Care Currency Spending on other services like health, residential and personal care.

Home Health Currency Spending on home health.

Nursing Care Currency Spending on nursing care.

Prescriptions Currency Spending on prescription medicine.

Durable Equipment

Currency Spending on durable medical equipment.

Non-Durable

Equipment Currency Spending on non-durable medical equipment. Total

Expenditures Currency Sum of all healthcare spending.

2. Begin by creating a new Microsoft Excel workbook named lastname_firstname_nhp.xlsx .

(2)

Introduction to Computer Applications

West Virginia University Page 2 of 7 Modified 9/14/2015 Version 4.6 a. Rename Sheet1 to Expenditures .

b. Add a new sheet named Analysis Questions .

4. We must import the personal healthcare expenditures statistics into the Expenditures sheet.

Using the DATA ribbon, import the data from expenditures.xml and place it starting in cell A3. Excel will have to create a schema based on the XML source data. The data will be imported as an XML table.

5. We wish to apply some additional formatting to the Expenditures sheet. a. We need to add additional columns to store calculated statistics.

i. Insert two new table columns to the right of existing column L. b. For the table, turn on the Total Row option.

c. Enter text in the cells as indicated below: i. A1: Healthcare Expenditures ii. M3: Prescriptions Pctg iii. N3: Prescriptions Class iv. A35: Annual Change

d. Merge-and-center cells A1 through N1. e. Apply the Title cell style to cell A1.

f. Format the table using a style of your choice other than the default table style.

6. We need to perform some additional calculations to analyze the Expenditures sheet.

a. In column M, calculate the percentage of total expenditures comprised of prescriptions using the formula:

[๐‘ƒ๐‘Ÿ๐‘’๐‘ ๐‘๐‘Ÿ๐‘–๐‘๐‘ก๐‘–๐‘œ๐‘›๐‘ ] [๐‘‡๐‘œ๐‘ก๐‘Ž๐‘™ ๐ธ๐‘ฅ๐‘๐‘’๐‘›๐‘‘๐‘–๐‘ก๐‘ข๐‘Ÿ๐‘’๐‘ ]

b. There is nothing to do for this step. Please proceed to the next step. c. There is nothing to do for this step. Please proceed to the next step. d. There is nothing to do for this step. Please proceed to the next step.

(3)

Introduction to Computer Applications

West Virginia University Page 3 of 7 Modified 9/14/2015 Version 4.6 e. In column N, calculate the prescriptions class by nesting IF() functions to

assign a ranking according to the following rules:

i. Class of A if less than 7.5% of total expenditures were on prescriptions.

ii. Class of B if at least 7.5% but less than 10% of total expenditures were on prescriptions.

iii. Class of C if at least 10% of total expenditures were on prescriptions.

f. We would like to summarize the prescription percentage data.

i. There is nothing to do for this step. Please proceed to the next step. ii. In the total row, average column M.

iii. There is nothing to do for this step. Please proceed to the next step. iv. In the total row, do not display any statistics in column N.

g. Enter the compound growth formula into the cells as indicated below. You must modify the cell references as necessary to be column-absolute, row-absolute, or fully absolute. All references will be modified; there are no relative references.

HINT: To avoid errors, copy-and-paste the provided formula. i. B35: =((B34/B4)^(1/(A34-A4)))-1

ii. B35 through L35: AutoFill the formula from cell B35. 7. We must apply additional formatting to the Expenditures sheet.

a. Add borders to the cells as indicated below: i. L3 through L35: left โ€“ thin solid line ii. M3 through M35: left โ€“ thick solid line b. Format the cells as indicated below:

i. B4 through L34: currency with no decimal places ii. M4 through M35: percentage with 1 decimal place

Fully Absolute References Row-Absolute Mixed References

(4)

Introduction to Computer Applications

West Virginia University Page 4 of 7 Modified 9/14/2015 Version 4.6 c. AutoFit the width of columns A through N.

d. Apply the Green-Yellow-Red color scale conditional formatting option to cells L4 through L34.

e. Apply conditional formatting to the prescription percentages in cells M4

through M34.

i. If prescriptions were less than 7.5% (< 0.075) of total expenditures,

change the cell fill color to green and text color to white.

ii. If prescriptions were at least 10% (โ‰ฅ 0.10) of total expenditures,

change the cell fill color to red and text color to white.

f. There is nothing to do for this step. Please proceed to the next step. g. There is nothing to do for this step. Please proceed to the next step. 8. To better understand our data, we wish to create a PivotChart with an

associated PivotTable.

a. Create a new PivotChart and PivotTable based on the data in cells A3

through M34 of the Expenditures sheet. Place the PivotTable on a new sheet named Expenditures PivotTable and move the PivotChart to a new sheet named Expenditures PivotChart.

b. On the PivotTable do the following: i. Add the year as a Rows field.

ii. Add the personal care expenditures, home health expenditures, nursing care expenditures, prescriptions expenditures, durable equipment expenditures, and non-durable equipment expenditures as Values fields.

c. We need to perform some formatting on our PivotTable.

i. There is nothing to do for this step. Please proceed to the next step.

ii. Summarize the personal care expenditures, home health expenditures, nursing care expenditures, prescriptions

expenditures, durable equipment expenditures, and non-durable equipment expenditures values fields by averaging them.

iii. Format the cells as indicated below:

(1) Average of Personal Care, Average of Home Health,

Average of Nursing Care, Average of Prescriptions,

Average of Durable Equipment, and Average of Non-Durable Equipment columns: currency with no decimal places

(5)

Introduction to Computer Applications

West Virginia University Page 5 of 7 Modified 9/14/2015 Version 4.6 i. Ensure the chart is a 2-D clustered column chart.

ii. Specify appropriate chart and axis titles.

9. We wish to create a chart to plot the total expenditures, and prescriptions versus the year in which statistics is taken.

a. Create a 2-D line chart based on the non-contiguous range of cells A3

through A34, cells I3 through I34, and cells L3 through L34 of the Expenditures sheet. Move the chart to a new sheet named Expenditures Chart .

Ensure that the years are shown as labels for the horizontal (category) axis, not plotted as chart data. Specify appropriate chart and axis titles. b. There is nothing to do for this step. Please proceed to the next step. c. Add a trendline based on the Total Expenditures column. Use the

trendline type that best fits the data. Display the R-squared value on the chart.

NOTE: You cannot use the Moving Average type for your trendline. d. Add a trendline based on the Prescriptions column. Use the trendline

type that best fits the data. Display the R-squared value on the chart.

NOTE: You cannot use the Moving Average type for your trendline. 10. We need to setup the Analysis Questions sheet so that it can store responses

to the analysis questions.

a. Enter text in the cells as indicated below: i. A1: Question Number

ii. B1: Answer

b. Bold the contents of row 1.

c. AutoFit the width of column A. Set the width of column B to 100. d. Set the height for rows 2 through 5 to 110.

e. Change the vertical alignment setting for columns A and B so that the text is displayed at the top of each row.

(6)

Introduction to Computer Applications

West Virginia University Page 6 of 7 Modified 9/14/2015 Version 4.6 of the five below questions. Respond to one question per row and indicate which question you are answering in column A.

a. On Expenditures Chart, which trendline type did you use for the total expenditures? Why did you pick this type?

b. There is a continuous increase in annual total healthcare expenditures. What do you think is the main reason behind this?

c. The annual growth rate for healthcare costs has slowed in recent years. The average annual increase from 1979 to 1985 was 12% but was only 6% from 2003 to 2009. Explain some potential reasons for this slowed growth rate.

d. Home healthcare experienced the greatest growth of all categories, with 2009 spending being almost 36 times the amount spent in 1979. What is a possible cause for this drastic upsurge?

e. In 2009, prescriptions accounted for 12% of total healthcare spending. This is more than double the 5.7% figure of 1979. What is a possible reason for this increase?

Curriculum Information

Project Type

Microsoft Excel

Relationship to GEC Objective 2

In this assignment, students analyze national health expenditure for 30 years. Through the use of critical thinking skills, they are expected to understand and support the conclusions they draw from the data.

Relationship to GEC Objective 4

In this assignment, students are expected to explore statistics of national health care and analyze the trends in the growth of this expenditure.

(7)

Introduction to Computer Applications

West Virginia University Page 7 of 7 Modified 9/14/2015 Version 4.6

Grading Rubric

This project is worth 100 points and will be graded based upon the following components. The instructor may adjust the below values as he or she feels appropriate.

Step 3a-c 3 points total Steps 7a-c 2 points total

Step 4 5 points Steps 7d-g 7.5 points total

Step 5a-f 5 points total Steps 8a-d 15 points total

Step 6a-d 5 points total Steps 9a-d 12.5 points total

Step 6e 5 points Steps 10a-f 5 points total

Steps 6f(i) 2.5 points Steps 11a-e (pick 4 of 5) 5 points each

Steps 6g(i)-(ii) 12.5 points total

Acknowledgments

Image is taken from http://topnews.us/content/231975-recession-leaves-impact-health-care-spending-2009.

Figure

Updating...

Related subjects :