You will use Excel to create a spreadsheet to organize, summarize, and calculate data for quality improvement.
Use “Homework B dataset Information” file attached. You can copy/paste the information to your file as appropriate.
The Homework B Information file contains survey questions 1-9 and their scores for each unit by quarter. Your goal is to identify areas for improvement before implementing strategies to increase patient satisfaction on each unit. Before you can analyze patterns in the data, you must put the data in a spreadsheet to organize and summarize the scores. The scores will be summarized by calculating the mean (aka. average) scores in each unit for each question, quarter, and overall.
Open Excel and use the dataset in the Homework B information file to create a new spreadsheet. Units and their data are organized from left-to-right with the quarter data in rows and question data in columns
Unit, category, and question labels
In the first/leftmost column, enter the labels “Category”, “Quarters”, “Jan-Mar”, “Apr-Jun”, “Jul-Sep”, “Oct-Dec”, and “Mean” in the appropriate cells.
In the first/top row, use the merge function to merge the cells for each unit and add the unit name from the Homework B Information file.
In the second row labeled “Category”, use the merge function to merge cells for each question category and label categories as:
Questions 1-4 are labeled “Nurse Questions”
Questions 5-7 are labeled “Physician Questions”
Questions 8-9 are labeled “Environment Questions”
For each category label, use the insert line break feature for each cell to add a second line in the cell so that the word “Questions” will appear on the second (bottom) line in the cell
Use the center function to center all labels.
Unit scores
Enter scores for each unit from the Homework B Information file
Use the number rounding function to limit/round values to 1 place to the right of the decimal point (e.g., a value of 3.82 would be rounded to 3.8 in the cell)
Use the center function to center all values.
Mean score calculations and labels
Quarter/row mean scores: Use the average formula function to calculate mean scores for each quarter/row means) in each unit. Each quarter mean will be to the right (rightmost column) of that unit’s data, and that column is labeled “Mean” for each unit. For each unit and quarter, nine (9) scores are used to calculate each quarter/row mean.
Question/column mean scores: Use the average formula function to calculate the means scores for each question (question/column means) in each unit. For each unit and question, four (4) scores are used to calculate each question/column mean.
Overall mean score: Use the average formula function to calculate the overall unit mean score for each unit. In each unit, the overall unit mean calculation includes the values for all individual questions in all quarters. The overall unit mean is placed in the bottom of the rightmost column of that unit’s data, and that column is labeled “Mean” for each unit. For each unit, all 36 scores are used to calculate the overall unit mean.
Use the number rounding function to limit/round values to 1 place to the right of the decimal point (e.g., a value of 3.82 would be rounded to 3.8 in the cell) for calculated mean scores. Do NOT manually round calculated average because doing so removes evidence of the formula function in the cell and results in point deductions for not using the formula and rounding functions as instructed.
Use the center function to center all calculated mean scores
Cell attributes
Bold
Use the bold function to bold all labels in the top 3 rows (i.e., Unit, Category, Questions)
Use the bold function to bold all labels in the first/leftmost column
Use the bold function to bold all calculated mean scores (i.e., question/column means, quarter/row means, and overall/unit means) for each unit
Italics
Use the italics function to italicize the question category labels (i.e., Nurse Questions, Physician Questions, Environment Questions)
Border and lines
Use the border function to apply a single thin line to all inside borders of all cells
Use the border function to apply a single thick line to the outside border of the overall spreadsheet to encompass the entirety of the labels and values
Color and shading
Use the color palette function to apply medium blue shading to all cells and labels in the top row (i.e., row 1)
Use the color function to apply light blue shading to all cells and labels in the second and third rows from the top (i.e., rows 2 and 3)
Use the color pallet function to apply light blue shading to the individual cells that contain the overall mean score for each unit (bottom right cell for each unit’s scores)
Font style and size
Use the font type and size function to use Calibri 14-point for labels in the top row (i.e., row 1 unit names)
Use the font type and size function to use Calibri 14-point for the cells labeled “Category”, “Quarter”, and “Mean” in the first/leftmost column (i.e., column 1)
Use the font type and size function to use Calibri 11-point for all other cell labels and values
Create three (3) vertical column/bar graphs
For each unit, use the chart function to create a vertical column/bar chart showing the mean score for each individual question only (not the overall mean)
In each chart, use the chart formatting function to provide the unit’s name as a chart title at the top of the chart (within the chart frame) to show the name of each unit
In each chart, use the chart formatting function to provide a title for each axis as follows
Y-axis title is “Mean Score”
X-axis title is “Questions”
In the Y-axis (vertical axis) of each unit, use the chart formatting function to ensure the Y-axis has the same range for all three charts
In the X-axis (horizontal axis) of each chart, use the chart formatting function to change the axis labels on the X-axis to Q1, Q2, Q3, etc. (under each bar)
In each chart, use the chart formatting function to change the bars to the color indicated in the Homework B dataset file.
