Kenya Cruz
GE ver. 17.1.0-rc0000
1.
0/2
Unfreeze columns.
In the Project Status worksheet, the first column should be unfrozen.
2.
2/2
Create a formula using a function.
3.
1/2
Change the fill color of a cell.
Change the font color.
Merge and center a range.
Change the row height using AutoFit.
In the Project Status worksheet, row 2 should be resized using AutoFit.
4.
3/3
Change the font style.
Change the number format.
5.
3/3
Merge a range.
Change the orientation of merged cell content.
Align cell content vertically.
Align cell content horizontally.
Remove the border from the merged cell.
Change the column width.
6.
3/3
Update a value in a cell.
Apply a cell style.
Change the column width using AutoFit.
7.
3/3
Create a formula without using a function.
Copy a formula into a range.
Apply a cell style.
8.
2/3
Create a formula without using a function.
Copy a formula into a range.
Apply a cell style.
9.
3/3
Create a formula using arithmetic operators.
Copy a formula into a range.
10.
3/3
Create a formula using a function.
Copy a formula into a range.
Change the number format.
11.
0/3
Apply a conditional formatting rule.
Format a conditional formatting highlight cells rule.
12.
3/3
Copy and paste values in a range.
Copy and paste formatting.
13.
2/3
Create a formula using a function.
Copy a formula into a range.
Change the column width using AutoFit.
In the Project Status worksheet, the width of column N should be increased using AutoFit.
14.
3/3
Create a formula using a function.
15.
1/3
Change the chart type.
Change a chart title.
The Project Status worksheet should contain a Clustered Bar chart.
Add an axis title to a chart.
The Project Status worksheet should contain a Clustered Bar chart.
Change the position of the data labels.
The Project Status worksheet should contain a Clustered Bar chart.
16.
3/3
Delete a row.
17.
3/3
Change the name of a worksheet.
18.
3/3
Create a formula without using a function.
Create a formula without using a function.
Create a formula without using a function.
Create a formula without using a function.
19.
3/3
Copy a formula into a range.
Copy a formula into a range.
Copy a formula into a range.
Copy a formula into a range.
20.
3/3
Create a formula using a function.
21.
3/3
Create a formula using a function.
SUBMISSION #1 | SCORE IS:
50
OUT OF
60
Carla Arranga is a senior account manager at Ensight Healthcare Consultants, a consulting firm that
works with hospitals, clinics, and other healthcare providers around the world. Carla has created a
workbook summarizing the status of the consulting project for Everett Hospital. She asks for your help in
completing the workbook.
Go to the
Project Status
worksheet.
Unfreeze
the first column since it does not display information that
applies to the rest of the worksheet.
In cell J1, enter a formula using the
NOW
function to display today's date. Apply the
Short Date
number
format to display only the date in the cell.
Format the worksheet title as follows to use a consistent design throughout the workbook:
a. Fill cell B2 with the
Dark Red, Accent 6, Lighter 40%
shading color.
b. Change the font color to
White, Background 1
.
c.
Merge and center
the contents of cell B2 across the range B2:H2.
d. Use
AutoFit
to resize row 2 to its best fit.
Format the billing rate data as follows to suit the design of the worksheet and make the data easier to
understand:
a.
Italicize
the contents of cell I2 to match the formatting in cell I1.
b. Apply the
Currency
number format to cell J2 to clarify that it contains a dollar amount.
Format the data in cell A4 as follows to display all of the text:
a. Merge the cells in the range A4:A13.
b. Rotate the text up in the merged cell so that the text reads from bottom to top.
c.
Middle-align
and
center
the text.
d. Remove the border from the merged cell.
e. Resize column A to a width of
4.00
.
Format the data in row 4 as follows to show that it contains column headings:
a. Change "Description" to use
Service Description
as the complete column heading.
b. Apply the
Accent 6
cell style to the range B4:H4.
c. Use
AutoFit
to resize column D to its best fit.
Carla wants to include the actual dollar amount of the services performed in column E. Enter this
information as follows:
a. In cell E5, enter a formula without using a function that multiplies the actual hours (cell
D5
) by the
billing rate (cell
J2
) to determine the actual dollar amount charged for general administrative services.
Include an absolute reference to cell J2 in the formula.
b. Use the Fill Handle to fill the range E6:E13 with the formula in cell E5 to include the charges for the
other services.
c. Format the range E6:E13 using the Comma number format and no decimal places to match the
formatting in column F.
Carla needs to show how much of the estimate remains after the services performed. Provide this
information as follows:
a. In cell G5, enter a formula without using a function that subtracts the actual dollars billed (cell
E5
)
from the estimated amount (cell
F5
) to determine the remaining amount of the estimate for general
administrative services.
b. Use the Fill Handle to fill the range G6:G13 with the formula in cell G5 to include the remaining
amount for the other services.
c. Format the range G6:G13 using the
Comma
number format and no decimal places to match the
formatting in column F.
In the Project Status worksheet, the range G6:G13 should be formatted using the Comma cell style
with zero decimal places.
Carla also wants to show the remaining amount as a percentage of the actual amount. Enter this
information as follows:
a. In cell H5, enter a formula that divides the remaining dollar amount (cell
G5
) by the estimated dollar
amount (cell
F5
).
b. Copy the formula in cell H5 to the range H6:H14, pasting only the formula and number formatting to
display the remaining amount as a percentage of the actual amount for the other services and the total.
Calculate the project status totals as follows:
a. In cell D14, enter a formula using the
SUM
function to total the actual hours (range
D5:D13
).
b. Use the Fill Handle to fill the range E14:G14 with the formula in cell D14.
c. Apply the
Accounting
number format with no decimal places to the range E14:G14.
Carla also wants to identify the services for which Ensight has billed more than the full estimate amount.
In the range H5:H13, use
Conditional Formatting
Highlight Cells Rules to format values less than 1%
(0.01) in
Light Red Fill with Dark Red Text
.
In the Project Status worksheet, a conditional formatting rule that formats any value less than 1%
should be applied to the range H5:H13.
In the Project Status worksheet, the conditional formatting rule in the range H5:H13 should apply
the Light Red Fill with Dark Red Text font color Formatting.
Carla imported data about the consultants working on the Everett Hospital project and stored the data on
a separate worksheet, but wants to include the data in the
Project Status
worksheet.
Copy and paste the data as follows:
a. Go to the
Consultants
worksheet and copy the data in the range B2:G12.
b. Return to the
Project Status
worksheet. Paste the data in cell J3, keeping the source formatting when
you paste it.
Carla needs to list the role for each consultant. Those with four or more years of experience take the
Lead role. Otherwise, they take the Associate role. List this information as follows:
a. In cell N5 on the
Project Status
worksheet, enter a formula that uses the
IF
function to test whether
the number of years of experience (cell
M5
) is greater than or equal to
4
.
b. If the consultant has four or more years of experience, display
"Lead"
in cell N5.
c. If the consultant has less than four years of experience, display
"Associate"
in cell N5.
d. Copy the formula in cell N5 to the range N6:N13, pasting the formula only.
e. Use
AutoFit
to resize column N to its best fit.
Carla wants to include summary statistics about the project and the consultants. Include this information
as follows:
In cell D16, enter a formula that uses the
AVERAGE
function to average the number of years of
experience (range
M5:M13
).
Make the 3-D Clustered Column chart in the range B17:H31 easier to interpret as follows:
a. Change the chart type to a
Clustered Bar chart
.
b. Use
Actual Project Hours
as the chart title.
c. Add a primary horizontal axis title to the chart, using
Hours
as the axis title text.
d. Add data labels in the
center
of each bar.
Delete row 33 since Carla has reformatted the clustered column chart.
Go to the
Schedule
worksheet. Rename the
Schedule
worksheet tab to
Project Schedule
to use a more
descriptive name.
Each service starts on a different date because the services depend on each other. Enter the starting
dates for the remaining services as follows:
a. In cell D6, enter a formula without using a function that adds
4
days to the value in cell
C6
.
b. In cell E6, enter a formula without using a function that subtracts
3
days from the value in cell
C6
.
c. In cell F6, enter a formula without using a function that adds
2
days to the value in cell
E6
.
d. In cell G6, enter a formula without using a function that adds
2
days to the value in cell
C6
.
Copy the formulas in Phase 2 to the rest of the schedule as follows:
a. Copy the formula in cell D6 to the range D7:D9.
b. Copy the formula in cell E6 to the range E7:E9.
c. Copy the formula in cell F6 to the range F7:F9.
d. Copy the formula in cell G6 to the range G7:G9.
In cell C11, enter a formula that uses the
MIN
function to find the earliest date in the project schedule
(range
C6:G9
).
In cell C12, enter a formula that uses the
MAX
function to find the latest date in the project schedule
(range
C6:G9
).
Shelly Cashman Excel 365/2021 | Modules 1-3: SAM Capstone Project 1a
