Shelly Cashman Excel 365 | Module 5: SAM Critical Thinking Project C York Medical Center

GETTING STARTED

1. Save the file SC_EX365_CT5C_FirstLastName_1.xlsx as SC_EX365_CT5C_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. To complete this Project, you will also need the following files:

Support_EX365_CT5C_Hours.xlsx

3. With the file SC_EX365_CT5C_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Veda Chandra is a nursing manager for York Medical Center, a hospital with three locations in Texas. York requires nurses and other health professionals to complete a certain number of continuing education hours per month. Veda is tracking these hours for the first quarter and asks for your help in consolidating the data and setting attendance goals. Go to the Houston worksheet. The Contact Hours by Department totals need to be completed. Copy the formula calculating the Cardiology total and paste only formulas and number formatting to calculate the totals for the other departments.

2. The Houston, Dallas, and San Antonio worksheets have the same structure and contain similar data. Group the Houston, Dallas, and San Antonio worksheets to make changes to the three worksheets at the same time. Enter a function to display today's date in the cell below the Date label.

3. Veda asks you to display the date with an abbreviated day name and no year, as in Thu 4/13. Apply a custom format to today's date that displays a three-character day name and the standard short date month and day numbers.

4. In the Attendance Goals section, use the existing month name to autofill the month names for the next two quarters.

5. Veda has set a goal of having 1,600 nurses attend continuing education sessions in September. For April, she estimates 1,426 nurses will attend sessions, which is the average attendance from January to March. Project the monthly attendance in May to September by filling the series for the first projection with a linear trend.

6. Veda asks you to determine how the attendance would increase if nurses attended 2.5% more continuing education sessions each month from May to September. Project the monthly attendance in May to September for the second projection based on a growth series using 1.025 as the step value.

7. Ungroup the worksheets, go to the All Centers worksheet, and then consolidate the data from the Houston, Dallas, and San Antonio locations as follows:

a. Find the cell that represents attendance in January for all Cardiology Departments. Enter a formula using a function and a 3-D reference to total the Cardiology Department attendance for the Houston, Dallas, and San Antonio locations.

b. Copy the formula to calculate the first-quarter attendance for the other departments, pasting the formula only.

c. Find the cell that represents contact hours in January for all Cardiology Departments. Enter a formula using a function and a 3-D reference to total the contact hours of the Cardiology Department in January for the Houston, Dallas, and San Antonio locations.

d. Copy the formula to calculate the first-quarter contact hours for the other departments, pasting the formula only.

8. Round the total contact hour values so that they are easier to remember.

a. For the January total contact hours, add a function around the existing formula to display the total contact hours for January rounded up to zero decimal places.

b. Fill the remaining monthly and quarterly totals with the formula used for January.

9. Veda asks you to display the total contact hours from the previous year for the same period. This data is stored in another workbook. Insert the total as follows:

a. Open the file Support_EX365_CT5C_Hours.xlsx.

b. In cell F29 of Veda's workbook, insert a formula using an external reference to the total contact hours in the Support_EX365_CT5C_Hours.xlsx workbook.

10. Veda wants you to determine the start date of the next continuing education session, which is seven workdays after the date of the last session. In cell J6, insert a formula using a function that displays the date seven workdays after the date of the last session.

11. Veda also wants you to show how the contact hours in the Cardiology, Critical Care, Emergency, General Surgery, and Maternity Departments contributed to the total hours for January to March. Create a chart as follows to illustrate this information:

a. Create a 3-D pie chart that shows how the first five departments contributed to the corresponding total contact hours.

b. Move and resize the chart so that it covers the range H17:L29.

12. Format the 3-D pie chart as follows to make it easier to interpret:

a. Use Total Hours as the chart title.

b. Add data labels to the chart as data callouts for each slice.

c. Display only the category name and percentage values in the data labels.

d. Change the number format of the data labels to Percentage with one decimal place.

e. Explode the smallest slice by 15 percent.

f. Remove the legend, which repeats information in the data labels.

13. Veda wants you to use a copy of the Houston worksheet as a template to track attendance for doctors. Copy the worksheet as follows:

a. Create a copy of the Houston worksheet at the end of the workbook and rename the copy using Doctors as the worksheet name.

b. On the Doctors worksheet, clear only the contents from the cells containing data, not formulas, in the Attendance by Department section.

Your workbook should look like the Final Figures on the following pages. Note: When opening your file or the Graded Summary report for this Project, you may be prompted to update external links. Select Don't Update in the dialog box to open your file or view your report. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Houston Worksheet

Final Figure 2: Dallas Worksheet

Final Figure 3: San Antonio Worksheet

Final Figure 4: All Centers Worksheet

Final Figure 5: Doctors Worksheet