Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a Ensight Healthcare Consultants CREATE FORMULAS WITH FUNCTIONS GETTING STARTED ? Open the file SC_EX19_CS1-3a_ FirstLastName _1

Subject:MS ExcelPrice:19.99 Bought8

Share With

Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a

Ensight Healthcare Consultants

CREATE FORMULAS WITH FUNCTIONS

GETTING STARTED

?

Open the file

SC_EX19_CS1-3a_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

SC_EX19_CS1-3a_

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.

?

With the file

SC_EX19_CS1-3a_

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.

PROJECT STEPS

1.

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.

2.

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.

3.

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.

4.

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.

5.

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

.

6.

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.

7.

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.

8.

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.

9.

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.

10.

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.

11.

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

12.

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.

13.

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.

14.

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

).

15.

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.

16.

Delete row 33 since Carla has reformatted the clustered column chart.

17.

Go to the

Schedule

worksheet. Rename the

Schedule

worksheet tab to

Project

Schedule

to use a more descriptive name.

18.

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

19.

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.

20.

In cell C11, enter a formula that uses the

MIN

function to find the earliest date in the

project schedule (range

C6:G9

).

21.

In cell C12, enter a formula that uses the

MAX

function to find the latest date in the

project schedule (range

C6:G9

).

Your workbook should look like the Final Figures on the following pages. The value in cell J1 has

been intentionally blurred as it will never be constant. Save your changes, close the workbook,

and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a

Final Figure 1: Project Status Worksheet

Final Figure 2: Project Schedule Worksheet