GETTING STARTED
Save the file SC_EX365_2021_1-3a_FirstLastName_1.xlsx as
SC_EX365_2021_1-3a_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.
Save the file SC_PPT365_2021_1-3a_FirstLastName_1.pptx as
SC_PPT365_2021_1-3a_FirstLastName_2.pptx a. Edit the file name by
changing “1” to “2”. b. If you do not see the .pptx file extension, do not type it.
The file extension will be added for you automatically.
To complete this Project, you will also need the following files:
Support_EX365_2021_1-3a_ProviderDepts.xlsx
Support_EX365_2021_1-3a_RecentStudies.xlsx
Support_PPT365_2021_1-3a_Heart.png
Support_WD365_2021_1-3a_Hypertension.docx
With the file SC_EX365_2021_1-3a_FirstLastName_2.xlsx open, ensure that
your first and last name is displayed in cell B6 of the Documentation worksheet.
If cell B6 does not display your name, delete the file and download a new copy.
With the file SC_PPT365_2021_1-3a_FirstLastName_2.pptx 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.
PROJECT STEPS
- 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 other worksheets
and insert charts to complete the workbook.
Open your Excel Project file workbook, go to the Department worksheet, and
then modify it as follows:
a. Rename the worksheet to use the more descriptive Department
Analysis as the worksheet name.
b. Unfreeze the top rows since Margarita does not need to display them
while scrolling.
- 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. Add a Bottom Border to cell B1 using the thick line style (2nd column,
5th entry in the Style list) and the Lime, Accent 2 color.
c. Enter the date 7/29/24 in cell C2.
d. Using the Number Format list, apply the Short Date number format to
cell C2 to display the date with a four-digit year. - 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. - 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. - 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. Copy the formula without formatting from cell H5 to the range H6: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 copy the 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. - 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:
a. 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.
b. 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.
c. 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.
Cashman Office 365/2021 | Modules 1-3: SAM WEP Integration Project 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:
a. 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.
b. In the range C24:F24, apply the same conditional formatting to cells with
totals greater than 250,000.
- 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.
a. Using the text in cell C12, fill the range D12:F12 with the names of the
other three quarters.
b. 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.
c. Copy the formula in cell C13 to the range D13:F13 to calculate the other
quarterly bonus amounts.
d. 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.
e. Copy the formula in cell C15 to the range D15:F15 to calculate the
bonuses for the other quarters. - 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:
a. In the range G5:G9, insert Line sparklines for the data range C5:F9.
b. 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.
c. Change the sparklines in the range G20:G24 to Line sparklines. - 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). - For the annual report, Margarita needs to create a chart comparing the actual
quarterly revenue by department. Create the chart as follows:
a. Create a Clustered Column chart for the quarterly revenue data in the
range B4:F8.
b. Move the chart to a new chart sheet and use Revenue Chart as the
worksheet name.
c. Move the Revenue Chart worksheet after the Department Analysis
worksheet so the chart appears after the data.
d. Use Revenue by Department as the chart title.
e. Change the number format of the vertical axis to display 0 decimal places
and a $ symbol.
f. Add a primary vertical axis title and use REVENUE as the axis title text.
g. Change the text direction of the vertical axis title to Stacked.
- Margarita wants to include data compiled by a colleague and use it to create
charts for the annual report. Include the data as follows:
a. Go to the Studies worksheet.
b. Open the Excel workbook Support_EX365_2021_1-
3a_ProviderDepts.xlsx.
c. 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 Support_EX365_2021_1-
3a_ProviderDepts.xlsx workbook.
d. Open the Excel workbook Support_EX365_2021_1-
3a_RecentStudies.xlsx.
e. 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 Support_EX365_2021_1-
3a_RecentStudies.xlsx workbook.
f. Use Patients in Studies as the column heading in cell B14. - In the Studies worksheet, Margarita wants to include a chart showing the
number of patients participating in recent studies. Create the chart as follows:
a. Create a 2-D Pie chart based on the data in the range A14:B18.
b. Move and resize the pie chart so the upper-left corner is in cell D13 and
the lower-right corner is in cell K26.
c. Change the style of the chart to Style 4 to coordinate with other content
in the workbook.
d. Add Data Labels to the chart at the Outside End of each pie wedge. - Save and close the Excel Project workbook and then open the Word document
Support_WD365_2021_1-3a_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 and formatting of the “Quick Facts”
heading as follows so it will better fit the PowerPoint presentation:
a. In the heading “Quick Facts”, use the Thesaurus to replace the word
“Quick” with a synonym that begins with the letter F.
b. Apply 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 #24.]
- Format and correct the next paragraph, which begins “According to the…”, to
use in the presentation:
a. Left-align the paragraph, which does not need to be centered.
b. Remove the hyperlink to the American Heart Association because the
webpage information is summarized in the following list.
c. Remove the space before the “According to the…” paragraph to tighten
the spacing. [Hint: This step will be credited as correct upon the successful
completion of step #24.] - Revise the list of statistics as follows to make it easier to read:
a. Clear the formatting from the sentence “81.5% are aware they have it.”
b. Add bullets to the four paragraphs citing statistics, beginning “81.5% are
aware they have it.” and ending “47.5% are not controlling it.”
c. 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 #24.] - On page 3, Margarita needs to include a table comparing the prevalence of
hypertension in the U.S. with the prevalence in Wellpoint patients. Add the table
as follows:
a. Delete the “[Insert table]” placeholder text and replace it with a blank
table containing three columns and four rows.
b. Enter the data shown in Table 1.
c. Apply the List Table 4 – Accent 2 table style. [Hint: This step will be
credited as correct upon the successful completion of step #23.]
Table 1: Data for Hypertension Table
- 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:
a. Add the following alt text to the first picture, the one in the “Physical
activity” row: Man enjoying exercise
b. 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 #28.] - 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.
a. On Slide 1 (“Hypertension Basics”), ignore all suggested spelling changes
to “Wellpoint”, which is spelled correctly.
b. Apply Italic to the “Prepared by the staff of the Wellness Department” text
box.
- On Slide 2 (“What Is Hypertension?”), format and animate the content
placeholder on the left to make the text more meaningful and interesting:
a. Add bullets to the text in the content placeholder on the left.
b. 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”).
c. Increase the font size of the entire content placeholder on the left by
clicking the Increase Font Size button one time.
d. For the top-level bulleted text (“Definition” and “Prevalence in U.S.”),
apply Bold and change the font color to Green, Accent 1.
e. Animate the content placeholder on the left using the Wipe entrance
animation.
f. Change the animation to animate the text All at Once.
g. Change the direction of the animation to From Left. - Remaining on Slide 2, add a picture to the content placeholder on the right as
follows:
a. Use the content placeholder on the right to insert the picture in the file
Support_PPT365_1-3a_Heart.png.
b. Crop out the red rectangle at the bottom of the picture containing the text
“Hypertension”. - 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:
a. Insert a new Slide 3 using the Title Only layout.
b. Type Wellpoint Patients as the slide title.
c. Add the following speaker note to the slide as a reminder for the
presenters: Data is based on a recent study. - Still on Slide 3 (“Wellpoint Patients”), switch to the Word document
Support_WD365_1-3a_Hypertension.docx. Insert content from the
document as follows:
a. Copy the “Prevalence of Hypertension by Age” table on page 3 of the Word
document.
b. Keep source formatting as you paste the table on Slide 3 of the PowerPoint
presentation.
c. Change the font size of the table text to 18 point.
d. 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.
Figure 1: Alignment of the Table
- Margarita also wants to insert text from the Word document in the PowerPoint
presentation. Return to the Word document Support_WD365_1-
3a_Hypertension.docx, and then insert the text as follows:
a. Copy the paragraphs from “Fast Facts” to the fourth bullet (“47.5 percent
are not controlling it.”) on page 2 of the Word document.
b. Use the destination theme as you paste the text on Slide 3 of the
PowerPoint presentation.
c. Resize the text box to a width of 6.5″ so it is about the same width as the
table.
d. 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.
Figure 2: Position of the Text Box
- 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:
a. Copy the Patients in Studies pie chart on the Studies worksheet.
b. Paste the chart on Slide 3 of the PowerPoint presentation using the Use
Destination Theme & Embed Workbook paste option.
c. Without changing the width, resize the height to 4″.
d. Change the font size of the chart text to 16 point.
e. Move the chart to the left of the table, and then align the top of the chart
with the top of the table.
f. Close the Excel workbook. - 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. Add SmartArt
and a hyperlink to the slide as follows:
a. Convert the bulleted list (from “Normal” to “Higher than 180/120”) to
SmartArt using the Step Up Process layout.
b. Change the SmartArt style to Subtle Effect.
c. Format the text “Centers for Disease Control and Prevention” as a
hyperlink to the cdc.cengage.gov webpage.
- On Slide 5 (“Prevent and Manage”), Margarita plans to display four pictures and
use text boxes as captions to identify the picture content. Add a fourth text box
and then arrange the text boxes as follows:
a. Insert a text box to the right of the “Healthy weight” text box, and then
type Manage stress in the new text box.
b. Resize the “Manage stress” text box to a height of 0.4″ and a width of 2″.
c. Align the right side of the “Manage stress” text box with the 5.5″ mark on
the ruler.
d. Align the top of the four text boxes with the top of the “Physical activity”
text box.
e. Distribute the four text boxes horizontally. - Switch to the Word document Support_WD365_1-3a_Hypertension.docx.
Insert pictures from the Word document on Slide 5 as follows:
a. On page 4 of the Word document, copy the first picture, which appears in
the “Physical activity” row of the table.
b. Paste the picture on Slide 5 of the PowerPoint Project presentation using
the destination theme.
c. 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.
d. Resize all four pictures to a height of 4″ and a width of 2.6″.
e. Move each picture below its corresponding text box as shown in Figure 3.
f. Align the tops of the pictures.
g. Close the Word document.
Figure 3: Position of the Pictures on Slide 5
- Still on Slide 5, adjust the pictures as follows to improve their appearance and
unify them on the slide:
a. Sharpen the “Healthy, low-salt diet” picture by 25%.
b. Apply the Beveled Matte, White picture style to all four pictures. - Remaining on Slide 5, add a shape as follows to display a tip for maintaining a
normal blood pressure:
a. Insert a Rectangle: Rounded Corners shape.
b. Resize the shape to a height of 0.65″ and a width of 5.8″.
c. Type the following sentence in the rectangle: Moderation and
consistency are the keys to success.
d. Center the rectangle on the slide below the pictures.
e. Apply the Subtle Effect – Lime, Accent 3 shape style to coordinate
more closely with the pictures.
f. Apply an Offset: Bottom shape effect from the Outer section of the
Shadow gallery to include a shadow, which the pictures also have.
- Still on Slide 5, Margarita wants to animate the text boxes and rectangle to
make them more noticeable. Animate the objects as follows:
a. Apply the Zoom entrance animation to the rectangle.
b. Apply the Font Color emphasis animation to the four text boxes.
c. 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. - Margarita wants to complete the presentation by inserting a footer and a
transition. Add these elements as follows:
a. Insert a footer that includes the slide number and the date.
b. Display the footer on all slides except the title slide.
c. Apply the Fade transition from the Subtle section of the Transitions gallery
to all the slides in the presentation.
