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”.
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.
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.
o
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:
4.
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.
Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a
5.
6.
7.
8.
9.
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.
Carla also wants to show the remaining amount as a percentage of the actual amount,
but wants to avoid an inadvertent divide by zero error. Enter this information as follows:
a.
In cell H5, enter a formula using the IFERROR function that divides the remaining
dollar amount (cell G5) by the estimated dollar amount (cell F5) and displays the
message "Enter estimated $" in the case of an error.
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.
Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a
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 wants to compare the actual dollar amounts in column E.
In the range E5:E13, use Conditional Formatting to create a Data Bars rule with the
Solid Blue Data Bar color option.
12.
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.
13.
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:
14.
15.
a.
Go to the Consultants worksheet and copy the data in the range B2:H12.
b.
Return to the Project Status worksheet. Paste the data in cell J3, keeping the
source formatting when you paste it.
c.
Delete the Consultants worksheet because you no longer need 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 also needs to list the level of each consultant, which depends on the last
evaluation score for the consultant. For scores higher than 89, the consultant is assigned
Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a
to Level 1. For scores higher than 79, the consultant is assigned to Level 2. For other
scores, the consultant is assigned to Level 3. Provide this information as follows:
16.
17.
a.
In cell P5, enter a formula that uses the IF function to test whether the evaluation
score (cell O5) is greater than 89 and assigns a Level of "1" if it is.
b.
For the false condition, enter another formula that uses the IF function to test
whether the evaluation score (cell O5) is greater than 79 and assigns a Level of
"2" if it is.
c.
Assign a Level of "3" for any other evaluation score.
d.
Fill the range P6:P13 with the formula in cell P5 without formatting.
Carla wants to include summary statistics about the project and the consultants. Include
this information as follows:
a.
In cell D16, enter a formula that uses the COUNTA function to count the number
of service IDs (range B5:B13).
b.
In cell D17, 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 B18:H32 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.
18.
Delete row 34 since Carla has reformatted the clustered column chart.
19.
Go to the Schedule worksheet. Rename the Schedule worksheet tab to Project
Schedule to use a more descriptive name.
20.
Carla needs to complete the schedule she started for performing the services remaining
on the project, which she has organized into four phases. Enter the date each phase
starts as follows:
a.
In cell C7, use the Insert Function dialog box to insert the WORKDAY function.
b.
Use the Phase 2 date (cell C6) as the Start_date argument.
c.
Use the number of workdays (cell C5) as the Days argument.
d.
Use the holiday dates (range C15:C16) as the Holidays argument.
e.
Use absolute references to cell C5 and to the range C15:C16.
f.
Fill the range C8:C9 with the formula in cell C7.
Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a
21.
In Phase 2, 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.
22.
In cell C11, enter a formula that uses the MIN function to find the earliest date in the
project schedule (range C6:G9).
23.
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
Shelly Cashman Excel 2019 | Modules 1- 3: SAM Capstone Project 1a
Ensight Healthcare Consultants
CREATE FORMULAS WITH FUNCTIONS
Author: Mauricio Paredes
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the
file from the SAM website.
w copy of the
Professional Therapies System Upgrade
Project Status Report
Everett Hospital
Service ID Description
G-131
G-240
P-255
P-314
P-400
P-245
F-125
F-130
H-305
General administrative
HIPAA compliance
Therapy practice evaluation
Strategic planning
New services development
Therapy analysis
New revenue generation
Expense reduction
Medical staff development
Total
Actual Hours
Actual $
25
100
40
35
84
40
125
95
40
Services performed
Average years of experience
Actual Hours
140
120
100
80
60
40
20
0
Reformat the clustered column chart.
Estimated $ Remaining $
$
2,500
8,000
6,800
5,500
7,400
6,800
10,000
10,000
10,200
Date:
Billing rate:
Remaining %
85
Consultants
Name
Kirk Deegan
Rhonda Meilani
Sherry Quam
Joe Shepard
Maya Gupta
Jacob Fay
Rosalie Dorne
Andre Patanka
Cam Wells
Services
F-125
P-314
P-255
G-131
P-245
F-130
H-305
G-240
P-400
Manager
Cisneros
Ottinger
Ottinger
Hetzel
Ottinger
Cisneros
Mika
Hetzel
Ottinger
Years
4.5
6.0
3.0
2.7
3.4
3.8
5.1
4.8
5.2
Role
Eval Score
88
91
80
79
92
83
75
90
85
Level
Professional Therapies System Upgrade
Project Schedule for Remaining Services
G-131
Workdays
Phase 2
Phase 3
Phase 4
Phase 5
10
5/17/2021
Start date
Completion date
Holidays:
5/31/2021
7/4/2021
P-400
F-125
F-130
H-305
12
5
14
15
1/17/1900
2/2/1900
2/20/1900
1/6/1900
1/13/1900
1/20/1900
1/19/1900
2/8/1900
2/28/1900
1/20/1900
2/10/1900
3/2/1900
...

Purchase answer to see full
attachment