Homework answers / question archive / New Perspectives Office 2019 | Modules 1-4: SAM Integration Project 1a Wellpoint Medical Clinic INTEGRATE EXCEL, ACCESS, WORD, AND POWERPOINT DATA GETTING STARTED Open the files NP_EX19_1-4a_FirstLastName_1

New Perspectives Office 2019 | Modules 1-4: SAM Integration Project 1a Wellpoint Medical Clinic INTEGRATE EXCEL, ACCESS, WORD, AND POWERPOINT DATA GETTING STARTED Open the files NP_EX19_1-4a_FirstLastName_1

MS Excel

Share With

---

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

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”.
   
  • 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”.
   
  • 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:
   
  • Support_AC19_1-4a_Studies.accdb
  • Support_PPT19_1-4a_Heart.png
  • 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.
  • 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.
  • 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:
   1. In cell B1, apply the Title cell style.
   2. Enter the date 7/29/21 in cell C2.
   3. 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:
   1. Merge and center the range A4:A9.
   2. Rotate the text up so it reads from bottom to top.
   3. Change the fill color of the cell to Lime, Accent 2, Lighter 60%.
   4. Middle-align the text.
   5. 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:
   1. In the ranges C5:F5 and C9:F9, apply the Accounting number format.
   2. 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:
   1. In cell H5, enter a formula using the SUM function to calculate the total revenue for the General Medicine Department (range C5:F5).
   2. 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.
   3. 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.
   4. To display more precise percentages, increase the number of decimal places in the range I5 to I9 to show one decimal place.
6. Margarita also wants to show the average, maximum, and minimum revenue amounts per department and for all departments. Calculate these statistics in the range J5:L9 by entering formulas as follows:
   1. In cell J5, enter a formula using the AVERAGE function to average the quarterly revenue from the General Medicine Department (range C5:F5). Copy the formula without formatting from cell J5 to the range J6:J9.
   2. In cell K5, enter a formula using the MAX function to find the maximum quarterly revenue from the General Medicine Department (range C5:F5). Copy the formula without formatting from cell K5 to the range K6:K9.
   3. In cell L5, enter a formula using the MIN function to find the minimum quarterly revenue from the General Medicine Department (range C5:F5). Copy the formula without formatting from cell L5 to the range L6:L9.
7. Providers and staff collectively are eligible for a 20 percent bonus if the total quarterly revenue this year is more than $230,000. Otherwise, the bonus is 15 percent of the quarterly revenue. Next year, the departments will have to earn more than $250,000 per quarter to be eligible for the 20 percent bonus. Apply conditional formatting as follows to highlight the revenue totals eligible for the higher bonus:
   1. In the range C9:F9, apply a conditional formatting Highlight Cells Rule that formats cells greater than 230,000 with Green Fill with Dark Green Text.
   2. In the range C24:F24, apply the same conditional formatting to cells with totals greater than 250,000.
8. Next, Margarita asks you to complete the Bonuses Earned data in the range C12:H15. The amount eligible for a bonus depends on the quarterly revenue. The providers and staff reimburse the clinic $1250 per quarter for nonmedical services. The final bonus is 35 percent of the remaining amount. Complete the following tasks:
   1. Using the text in cell C12, fill the range D12:F12 with the names of the other three quarters.
   2. In cell C13, enter a formula using an IF function that tests whether cell C9 is greater than 230,000. If it is, multiply cell C9 by 0.20 to calculate the 20 percent eligible amount. If cell C9 is not greater than 230,000, multiply cell C9 by 0.15 to calculate the 15 percent eligible amount.
   3. Copy the formula in cell C13 to the range D13:F13 to calculate the other quarterly bonus amounts.
   4. In cell C15, enter a formula without using a function that subtracts the Share amount (cell C14) from the Amount Eligible (cell C13) and then multiplies the result by the Bonus Percentage (cell C16). Use an absolute reference to cell C16.
   5. Copy the formula in cell C15 to the range D15:F15 to calculate the bonuses for the other quarters.
9. Margarita has already added Column sparklines in the range G20:G24, but she thinks Line sparklines would show the trends more clearly, as they do in the range G13:G15. She also needs to add sparklines for the Quarterly Revenue data. Add and modify sparklines as follows:
   1. In the range G5:G9, insert Line sparklines for the data range C5:F9.
   2. Apply the Dark Green, Sparkline Style Accent 3, Darker 50% (3rd column, 1st row of the Sparkline Styles gallery) to match the other sparklines in the worksheet.
   3. Change the sparklines in the range G20:G24 to Line sparklines.
10. The total projected revenue for next year in cell H24 is less than $1,000,000, which is the clinic's goal for the coming year. Margarita wants to know how much more revenue the General Medicine Department would have to earn to result in total revenue of $1,000,000. Use Goal Seek to set cell H24 to 1,000,000 by changing the projected increase percentage for General Medicine (cell C28).
11. For the annual report, Margarita needs to create a chart comparing the actual quarterly revenue by department. Create the chart as follows:
    1. Create a Clustered Column chart for the quarterly revenue data in the range B4:F8.
    2. Move the chart to a new chart sheet and use Revenue Chart as the worksheet name.
    3. Move the Revenue Chart worksheet after the Department Analysis worksheet so the chart appears after the data.
    4. Use Revenue by Department as the chart title.
    5. Change the number format of the vertical axis to display 0 decimal places and a $ symbol.
    6. Add a primary vertical axis title and use REVENUE as the axis title text.
    7. Change the text direction of the vertical axis title to Stacked.
12. Save and close your Excel Project workbook, and then open the Access database file Support_AC19_1-4a_Studies.accdb. Margarita wants to include a list of clinic departments in the database. Create a table in Design View as follows:
    1. Add a new field named DepartmentID with the Short Text data type.
    2. Assign the DepartmentID field as the primary key.
    3. Change the field size of the DepartmentID field to 4.
    4. Add a new field called DepartmentName with the Short Text data type.
    5. Save the table using Departments as the name. (Hint: This step will be credited as correct upon the successful completion of step #18.)
13. Display the Departments table in Datasheet View. Add the records shown in Table 1. Save and close the Departments table. (Hint: This step will be credited as correct upon the successful completion of step #18.)

 

DepartmentID	DepartmentName
D500	General Medicine
D501	Laboratory
D502	Radiology
D503	Wellness

 

14. Margarita wants to create two queries to use in the Excel Project workbook. The new Departments table needs to be related to another table in a one-to-many relationship before creating the queries. Open the Relationships window and then create a relationship as follows:
    1. Add the Departments table to the Relationships window to the right of the Providers table.
    2. Create a one-to-many relationship between the primary DepartmentID field in the Departments table and the related DepartmentID field in the Providers table.
    3. Enforce inferential integrity on the relationship.
    4. Save and close the Relationships window.
15. Margarita wants to create two queries to use in the Excel Project workbook. First, she wants to list the department for each provider at the Wellpoint Medical Clinic. Use the Simple Query Wizard to create a query as follows:
    1. Include first the ProviderLast field and then the Degree field from the Providers table.
    2. Include the DepartmentName field from the Departments table.
    3. Use ProviderDepts as the query name.
    4. Display the query in Datasheet View to view the results and then close the query. (Hint: This step will be credited as correct upon the successful completion of step #18.)
16. Margarita also wants to show the number of patients participating in recent health studies at the clinic, including the Hypertension study. Create a query in Design View as follows:
    1. Add the Patients, Participants, and Studies tables to the query.
    2. First add the StudyName field from the Studies table to the design grid, and then add the PatientID field from the Patients table.
    3. Add criteria to select only those records where the StudyName field value is Arthritis or Cholesterol or Diabetes or Hypertension.
    4. Add a Total row to the query.
    5. Group the query by the values in the StudyName field and count the values in the PatientID field.
    6. Save the query using RecentStudies as the query name.
    7. Display the query in Datasheet View to view the results and then close the query. (Hint: This step will be credited as correct upon the successful completion of step #18.)
17. Save the queries to Excel as follows:
    1. Save the ProviderDepts query as an Excel file (.xlsx) with the same name to the folder that stores the database.
    2. Save the RecentStudies query as an Excel file (.xlsx) with the same name to the folder that stores the database.
    3. Compact and repair the Support_AC19_1-4a_Studies.accdb database, and then close it. (Hint: This step will be credited as correct upon the successful completion of step #18.)
18. Margarita wants to include the exported queries in the Excel Project workbook and then use them to create charts for the annual report. Include the query data as follows:
    1. Open your Excel Project workbook and go to the Studies worksheet.
    2. Open the Excel workbook ProviderDepts.xlsx.
    3. In the ProviderDepts.xlsx workbook, copy the data in the range A1:C10. Switch to the Studies worksheet in your Excel Project workbook, and then paste the data using no paste options starting in cell A2. Close the ProviderDepts.xlsx workbook.
    4. Open the Excel workbook RecentStudies.xlsx.
    5. In the RecentStudies.xlsx workbook, copy the data in the range A1:B5. Switch to the Studies worksheet in your Excel Project workbook, and then paste the data using no paste options starting in cell A14. Close the RecentStudies.xlsx workbook.
    6. Use Patients in Studies as the column heading in cell B14.
19. In the Studies worksheet, Margarita wants to include a chart showing the number of patients participating in recent studies. Create the chart as follows:
    1. Create a 2-D Pie chart based on the data in the range A14:B18.
    2. Move and resize the pie chart so the upper-left corner is in cell D13 and the lower-right corner is in cell K26.
    3. Change the style of the chart to Style 4 to coordinate with other content in the workbook.
    4. Add Data Labels to the chart at the Outside End of each pie wedge.
20. Save and close the Excel Project workbook and then open the Word document Support_WD19_1-4a_Hypertension.docx, which is a fact sheet for patients interested in preventing or managing hypertension. Margarita wants to prepare some of the content to use in a PowerPoint presentation.
    At the bottom of page 2, change the text formatting of "Fast Facts" by applying the Fill: Green, Accent color 1; Shadow text effect to the heading.
    [Hint: This step will be credited as correct upon the successful completion of step #30.]
21. Format and correct the next paragraph, which begins "According to the…", as follows to use in the presentation:
    1. Left-align the paragraph, as it does not need to be centered.
    2. Remove the hyperlink to the American Heart Association because the webpage information is summarized in the following list.
    3. Remove the space before the paragraph beginning "According to the…" to tighten the spacing.
       [Hint: This step will be credited as correct upon the successful completion of step #30.]
22. Revise the list of statistics as follows to make it easier to read:
    1. Clear the formatting from the sentence "81.5% are aware they have it."
    2. Add bullets to the four paragraphs citing statistics, beginning "81.5% are aware they have it." and ending "47.5% are not controlling it."
    3. In the bulleted list only, find and replace the % (percent sign) with a space followed by the word percent to use the correct style for these types of sentences.
       [Hint: This step will be credited as correct upon the successful completion of step #30.]
23. On page 3, Margarita needs to include a table comparing the prevalence of hypertension in the United States with the prevalence in Wellpoint patients. Add the table as follows:
    1. Delete the "[Insert table]" placeholder text and replace it with a blank table containing three columns and four rows.
    2. Enter the data shown in Table 2.
    3. Apply the List Table 4 – Accent 2 table style.
       [Hint: This step will be credited as correct upon the successful completion of step #29.]

 

Age (Years)	U.S. (%)	Wellpoint (%)
18-39	7.3	7.0
40-59	32.2	30.5
60 and older	64.9	61

 

24. On page 4, Margarita wants to modify two of the pictures before copying all of them to the PowerPoint presentation. Adjust the pictures as follows:
    1. Add the following alt text to the first picture, the one in the "Physical activity" row: Man enjoying exercise
    2. Flip the picture of the woman walking (in the "Healthy weight" row) horizontally so she is facing the text in the table.
       [Hint: This step will be credited as correct upon the successful completion of step #34.]
25. Save the Word document and keep it open, and then open your PowerPoint Project presentation. Margarita is helping the Wellness Department prepare a presentation on hypertension, one of the diseases the Wellpoint Medical Clinic focused on during a recent study. The Wellness Department will use the presentation to educate patients about high blood pressure.
    On Slide 1 ("Hypertension Basics"), ignore all suggested spelling changes to "Wellpoint", which is spelled correctly.
26. On Slide 2 ("What Is Hypertension?"), format and animate the content placeholder on the left as follows to make the text more meaningful and interesting:
    1. Add bullets to the text in the content placeholder on the left.
    2. Increase the indent of the second bullet ("Blood pressure readings…at least two occasions"), the fourth bullet ("Most common cardiovascular disease"), and the fifth bullet ("About 30% of adults").
    3. Increase the font size of the entire content placeholder on the left by clicking the Increase Font Size button one time.
    4. For the top-level bulleted text ("Definition" and "Prevalence in U.S."), apply Bold and change the font color to Green, Accent 1.
    5. Animate the content placeholder on the left using the Wipe entrance animation.
    6. Change the animation to animate the text All at Once.
    7. Change the direction of the animation to From Left.
27. Remaining on Slide 2, add a picture to the content placeholder on the right as follows:
    1. Use the content placeholder on the right to insert the picture in the file Support_PPT19_1-4a_Heart.png.
    2. Crop out the red rectangle at the bottom of the picture containing the text "Hypertension".
28. Margarita wants to insert an Excel chart and a table from Word containing information about Wellpoint patients. Insert a new slide for this content as follows:
    1. Insert a new Slide 3 using the Title Only layout.
    2. Type Wellpoint Patients as the slide title.
    3. Add the following speaker note to the slide as a reminder for the presenters: Data is based on a recent study.
29. Still on Slide 3 ("Wellpoint Patients"), switch to the Word document Support_WD19_1-4a_Hypertension.docx. Insert content from the document as follows:
    1. Copy the "Prevalence of Hypertension by Age" table on page 3 of the Word document.
    2. Keep source formatting as you paste the table on Slide 3 of the PowerPoint presentation.
    3. Change the font size of the table text to 18 points.
    4. Move the table so its right side aligns with the 6" mark on the ruler and the top is aligned as shown in Figure 1.

 

 

30. Margarita also wants to insert text from the Word document in the PowerPoint presentation. Return to the Word document Support_WD19_1-4a_Hypertension.docx, and then insert the text as follows:
    1. Copy the paragraphs from "Fast Facts" to the fourth bullet ("47.5 percent are not controlling it.") on page 2 of the Word document.
    2. Use the destination theme as you paste the text on Slide 3 of the PowerPoint presentation.
    3. Resize the text box to a width of 6.5" so it is about the same width as the table.
    4. Position the text box below the table as shown in Figure 2, aligning the right side of the text box with the right side of the table.

 

 

31. Margarita also wants to show an Excel chart on Slide 3. Open your Excel Project workbook, go to the Studies worksheet, and then copy and paste a chart as follows:
    1. Copy the "Patients in Studies" pie chart on the Studies worksheet.
    2. Paste the chart on Slide 3 of the PowerPoint presentation using the Use Destination Theme & Embed Workbook paste option.
    3. Without changing the width, resize the height to 4".
    4. Change the font size of the chart text to 16 points.
    5. Move the chart to the left of the table, and then align the top of the chart with the top of the table.
    6. Close the Excel workbook.
32. On Slide 4 ("Know the Numbers"), Margarita wants to show something more visually interesting than a bulleted list. She also wants to make it easy to access the website for the Centers for Disease Control and Prevention.
    Convert the bulleted list (from "Normal" to "Higher than 180/120") to SmartArt using the Step Up Process layout.
33. On Slide 5 ("Prevent and Manage"), Margarita plans to display four pictures and use text boxes as captions to identify the picture content. Edit and then arrange the text boxes as follows:
    1. Resize the "Manage stress" text box to a height of 0.4" and a width of 2".
    2. Align the right side of the "Manage stress" text box with the 5.5" mark on the ruler.
    3. Align the top of the four text boxes with the top of the "Physical activity" text box.
34. Switch to the Word document Support_WD19_1-4a_Hypertension.docx. Insert pictures from the Word document on Slide 5 as follows:
    1. On page 4 of the Word document, copy the first picture, which appears in the "Physical activity" row of the table.
    2. Paste the picture on Slide 5 of the PowerPoint Project presentation using the destination theme.
    3. Copy and paste the other three pictures from page 4 of the Word document to Slide 5 of the PowerPoint Project presentation using the destination theme.
    4. Resize all four pictures to a height of 4" and a width of 2.6".
    5. Move each picture below its corresponding text box as shown in Figure 3.
    6. Align the tops of the pictures.
    7. Close the Word document.

 

 

35. Still on Slide 5, adjust the pictures as follows to improve their appearance and unify them on the slide:
    Apply the Beveled Matte, White picture style to all four pictures.
36. Remaining on Slide 5, add a shape as follows to display a tip for maintaining a normal blood pressure:
    1. Insert a Rectangle: Rounded Corners shape.
    2. Resize the shape to a height of 0.65" and a width of 5.8".
    3. Type the following sentence in the rectangle: Moderation and consistency are the keys to success.
    4. Center the rectangle on the slide below the pictures.
    5. Apply the Subtle Effect – Lime, Accent 3 shape style to coordinate more closely with the pictures.
    6. Apply an Offset: Bottom shape effect from the Outer section of the Shadow gallery to include a shadow, which the pictures also have.
37. Still on Slide 5, Margarita wants to animate the text boxes and rectangle to make them more noticeable. Animate the objects as follows:
    1. Apply the Zoom entrance animation to the rectangle.
    2. Apply the Font Color emphasis animation to the four text boxes.
    3. Change the Start settings for the text box animations to After Previous so each plays in sequence from left to right after the slide is displayed.
38. Margarita wants to complete the presentation by inserting a footer and a transition. Add these elements as follows:
    1. Insert a footer that includes the slide number and the date.
    2. Display the footer on all slides except the title slide.
    3. Apply the Fade transition from the Subtle section of the Transitions gallery to all the slides in the presentation.

Your completed project should look like the Final Figures on the following pages. Save your changes, close the file(s), and then exit all Office applications. Follow the directions on the SAM website to submit your completed project.