Shelly Cashman Excel 365 | Module 5: SAM Project A York Medical Center

GETTING STARTED

1. Save the file SC_EX365_5A_FirstLastName_1.xlsx as SC_EX365_5A_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_5A_Hours.xlsx

3. With the file SC_EX365_5A_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 in the range F20:F26. Copy the formula in cell F19 and then paste it into the range F20:F26, pasting only formulas and number formatting.

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. The first change is to display today's date. In cell G3 of the Houston worksheet, enter a formula using the TODAY function to display today's date.

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 cell G3 that displays a three-character day name (ddd), a space, and the standard short date month and day numbers (m/d).

4. Use the month name in cell H6 to fill the range I6:M6 with 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 (range H8:M8) 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 (range H10:M10) 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. In cell C7, enter a formula using the SUM function and a 3-D reference to total the Cardiology Department attendance (cell C7) for the Houston, Dallas, and San Antonio locations.

b. Copy the formula in cell C7 to calculate the first-quarter attendance for the other departments (ranges C8:C14 and D7:E14), pasting the formula only.

c. In cell C19, enter a formula using the SUM function and a 3-D reference to total the contact hours of the Cardiology Department in January (cell C19) for the Houston, Dallas, and San Antonio locations.

d. Copy the formula in cell C19 to calculate the first-quarter contact hours for the other departments (ranges C20:C26 and D19:E26), pasting the formula only.

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

a. In cell C27, add the ROUNDUP function to display the total contact hours for January rounded up to 0 decimal places.

b. Fill the range D27:F27 with the formula in cell C27.

9. In cell F29, 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_5A_Hours.xlsx.

b. In cell F29 of Veda's workbook, insert a formula using an external reference to cell F27 in the Quarter 1 worksheet in the Support_EX365_5A_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 the WORKDAY function that displays the date 7 workdays after the date of the last session (cell J5).

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 (range B19:B23) contributed to the corresponding total contact hours (range F19:F23).

b. Move and resize the chart so that the upper-left corner is in cell H17 and the lower-right corner is in cell 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 a Data Callout 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 1 decimal place.

e. Explode the smallest slice (Cardiology, 15.3%) 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 range C7:E14 and cell H3.

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