New Perspectives Office 2019 | Modules 1-4: SAM Integration Project 1a | Wellpoint Medical Clinic

Wellpoint Medical Clinic
INTEGRATE EXCEL, ACCESS, WORD, AND POWERPOINT DATA

GETTING STARTED
• Open the files NP_EX19_1-4a_FirstLastName_1.xlsx and NP_PPT19_1-4a_FirstLastName_1.pptx, available for download from the SAM website.
• Save the file NP_EX19_1-4a_FirstLastName_1.xlsx as NP_EX19_1-4a_FirstLastName_2.xlsx by changing the “1” to a “2”.

o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• Save the file NP_PPT19_1-4a_FirstLastName_1.pptx as NP_PPT19_1-4a_FirstLastName_2.pptx by changing the “1” to a “2”.

o If you do not see the .pptx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

o Support_AC19_1-4a_Studies.accdb
o Support_PPT19_1-4a_Heart.png
o Support_WD19_1-4a_Hypertension.docx
• With the file NP_EX19_1-4a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• With the file NP_PPT19_1-4a_FirstLastName_2.pptx still open, ensure that your first and last name is displayed in the presenter notes on Slide 1.
o If the presenter notes do not display your name, delete the file and download a new copy from the SAM website.
• When downloading Access support files, your browser may display a warning message similar to "This type of file can harm your computer." SAM Project files are always considered safe, so you can disregard this message.
PROJECT STEPS
1. Margarita Romero is the clinic manager for the Wellpoint Medical Clinic in Columbia, Maryland. The clinic has recently completed its first year of operation, and Margarita is using an Excel workbook to prepare for the annual report, which will include data about revenue and the clinic's health studies. She asks for your help to calculate totals, incorporate data from Access, and insert charts to complete the workbook.
Open your Excel Project file workbook, go to the Department worksheet, and then rename the worksheet to use the more descriptive Department Analysis as the worksheet name.
2. Format and insert data in the first two rows as follows to provide a date and a more striking worksheet title:
a. In cell B1, apply the Title cell style.
b. Enter the date 7/29/21 in cell C2.
c. Using the Number Format list, apply the Short Date number format to cell C2 to display the date with a four-digit year.
3. Margarita wants to format cell A4 to match cells A12, A19, and A27. Format the cell as follows:
a. Merge and center the range A4:A9.
b. Rotate the text up so it reads from bottom to top.
c. Change the fill color of the cell to Lime, Accent 2, Lighter 60%.
d. Middle-align the text.
e. Autofit column A to reduce the width of the worksheet contents.
4. Format the Quarterly Revenue and Projected Revenue data as follows to make it easier to read:
a. In the ranges C5:F5 and C9:F9, apply the Accounting number format.
b. Copy the formatting from the range B5:H9 to the range B20:H24 so that the format of the Projected Revenue data matches the format of the Quarterly Revenue data.
5. In the range H5:I9, Margarita wants to calculate the annual revenue per department and show the percentage each department contributed to total revenue. Insert formulas to provide these calculations as follows:
a. In cell H5, enter a formula using the SUM function to calculate the total revenue for the General Medicine Department (range C5:F5).
b. Enter a formula using the SUM function to calculate the total revenue for the Laboratory, Radiology, and Wellness Departments in the range H6:H8 (remove the $ symbol if necessary). Also enter SUM function to calculate the total of all departments in cell H9.
c. In cell I5, enter a formula without using a function that divides the General Medicine total in cell H5 by the annual total in cell H9 to display the department's percentage of total revenue. Use an absolute reference for cell H9, and then enter the same formula without formatting from cell I5 to the range I6:I9.
d. To display more precise percentages, increase the number of decimal places in the range I5 to I9 to show one decimal place.

#Microsoft
#MicrosoftOffice
#MicrosoftExcel
#MicrosoftPowerPoint
#MicrosoftIntegration
#SAMIntegrationProject
#NewPerspectives
#MicrosoftAccess
#SAMIntegrationProject
#Techniques
#Solutions