+1(978)310-4246 credencewriters@gmail.com
  

New Perspectives Excel 2019 | Modules 1-4: SAM Capstone Project 1a
Tallridge Regional Medical Center
FORMATTING, FORMULAS, AND CHARTS
GETTING STARTED
•
Open the file NP_EX19_CS1-4a_FirstLastName_1.xlsx, available for download from
the SAM website.
•
Save the file as NP_EX19_CS1-4a_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 NP_EX19_CS1-4a_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.
Carmelo Miraglia is an executive consultant at Tallridge Regional Medical Center.
Carmelo is building a workbook that summarizes income and expenses for the
organization.
Change the theme of the workbook to Office.
2.
On the Departments worksheet, change the width of columns C through H to 12.00
characters.
3.
Change the height of row 1 to 21 points.
4.
Merge and center the contents of the range A2:J2.
5.
Format the merged range A2:J2 as described below:
6.
a.
Apply the 40% – Accent 5 cell style.
b.
Apply bold formatting.
c.
Change the font size to 14.
Enter the values shown in Table 1 into the corresponding cells in the range A5:E5.
Table 1: Data for the Range A5:E5
5
A
B
C
D
E
Abbreviation
Department
MD
RN
Support
New Perspectives Excel 2019 | Modules 1-4: SAM Capstone Project 1a
7.
Format the range A5:J5 as described below:
a.
Center cell contents.
b.
Change the font size to 11 pt.
c.
Change the background color to Blue, Accent 5, Lighter 40% (9th column, 4th
row of the Theme Colors palette).
d.
Apply Wrap Text to the text in each cell.
8.
Select the range A6:J14 and then add a White, Background 1 border to all sides of each
cell.
9.
Select the range A14:J14 and then add a thin top border to each cell using the
Automatic color.
10.
Select the range H6:H13 and then format the range as described below:
a.
Format the range with the Percentage number format with zero decimal places.
b.
Add a Highlight Cells conditional formatting rule that formats cells that are
greater than 0.24 as light red fill with dark red text.
11.
Select the range I6:I13 and then use conditional formatting to add solid orange data
bars.
12.
Select the range J6:J13 and then add top/bottom conditional formatting rules to format
the top 10% of values as green fill with dark green text and the bottom 10% of values
as light red fill with dark red text.
13.
Enter a formula in cell M7 using the VLOOKUP function to find an exact match for the
department abbreviation. Look up the department abbreviation (cell M6) using an
absolute reference, search the staff table data (the range A6:J13) using an absolute
reference, and return the department name (the 2nd column).
14.
Copy the formula in cell M7 to the range M8:M15, and edit the copied formulas to return
the value from the column indicated by the label in column L.
15.
In cell C17, enter a formula using the TODAY function that displays the current date.
16.
Delete column O.
17.
Hide row 18.
18.
On the Budget worksheet, create a 2-D pie chart based on the non-adjacent range
A5:A12 and F5:F12. Modify the chart as described below:
19.
a.
Resize and reposition the chart so that the upper-left corner is located within cell
H4 and the lower-right corner is located within cell O22.
b.
Apply Chart Style 3 to the chart.
c.
Enter 2022 Budget by Department as the chart title.
In the 2018 Budget by Department 2-D pie chart (located in the range H23:O43), make
the following changes:
a.
Change the data labels to display only the percentage and a label position of
Center.
b.
Reposition the legend on the right side of the chart.
New Perspectives Excel 2019 | Modules 1-4: SAM Capstone Project 1a
20.
21.
Update the Departmental Budget Comparison 2018-2022 combo chart in the range
A15:G36 as follows:
a.
Change the minimum bound of the right vertical axis to 40,000,000.
b.
Add axis titles to the chart. Use Department Budgets as the left vertical title, use
Total Budget as the right vertical title, and remove the horizontal axis title.
c.
Apply a shape fill to the chart using the Blue, Accent 5, Lighter 80% fill color.
Delete the Hiring worksheet.
Your workbook should look like the Final Figures on the following pages. Save your changes,
close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your
completed project.
New Perspectives Excel 2019 | Modules 1-4: SAM Capstone Project 1a
Final Figure 1: Departments Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2018 Cengage Learning.
All Rights Reserved.
Final Figure 2: Budget Worksheet
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.
New Perspectives Excel 2019 | Modules 1-4: SAM Capstone Project 1a
Tallridge Regional Medical Center
FORMATTING, FORMULAS, AND CHARTS
Luke Monson
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.
e file from the
Tallridge Regional Medical Center
Staffing and Financial Summary by Department
Staff
Total
CCU
EMR
END
ICU
NEU
OBS
ONC
RAD
Coronary Care
Emergency
Endocrinology
Intensive Care
Neurology
Obstetrics
Oncology
Radiology
Total
5
12
3
17
6
9
5
2
59
Last update
Today’s date
Next update
5/13/2021
8
27
6
22
15
17
3
8
106
15
33
4
15
8
17
9
27
128
Turnover
28
72
13
54
29
43
17
37
293
Turnover %
4
21
0
5
1
3
8
4
46
0.143
0.292
0.000
0.093
0.034
0.070
0.471
0.108
15%
2021 Budget
$
$
$
$
$
$
$
$
$
5,250,768
18,782,039
2,847,592
9,282,172
5,283,109
11,010,924
12,448,265
10,927,453
75,832,322
% change from 2020
5.20%
9.30%
0.20%
2.30%
-1.50%
3.30%
6.10%
1.20%
3.26%
Department summary
Abbreviation
ICU
Department
MD
RN
Support
Total
Turnover
Turnover %
2021 Budget
% change
from 2020
Averages
Tallridge Regional Medical Center
Budget
Department
Coronary Care
Emergency
Endocrinology
Intensive Care
Neurology
Obstetrics
Oncology
Radiology
Total
2018
2019
$
4,590,049
14,003,329
2,775,520
8,755,483
5,095,866
10,258,041
10,617,497
10,670,979
$ 66,766,764
$
5,108,725
16,089,825
2,850,459
8,895,571
5,289,509
10,093,912
10,734,289
10,606,953
$ 69,669,243
2020
$
4,991,224
17,183,933
2,841,908
9,073,482
5,363,562
10,659,171
11,732,578
10,797,878
$ 72,643,736
2021
$
5,250,768
18,782,039
2,847,592
9,282,172
5,283,109
11,010,924
12,448,265
10,927,453
$ 75,832,322
Departmental Budget Comparison 2018-2022
$25,000,000
$20,000,000
$15,000,000
$10,000,000
$5,000,000
$0
2018
2019
2020
2021
Department
Coronary Care
Emergency
Endocrinology
Intensive Care
Neurology
Obstetrics
Oncology
Radiology
Total
nter
2022
Trend
$
5,124,750
19,702,359
2,893,153
9,254,325
5,864,251
11,308,219
13,518,816
12,402,659
$ 80,068,532
$85,000,000
$80,000,000
$75,000,000
$70,000,000
2018 Budget by Department
Coronary Care
7%
Radiology
16%
$65,000,000
Emergency
21%
$60,000,000
2022
Intensive Care
Oncology
16%
Endocrinology
4%
Total
Obstetrics
15%
Neurology
8%
Intensive Care
13%
Coronary Care
Emergency
Endocrinology
Intensive Care
Neurology
Obstetrics
Oncology
Radiology

Purchase answer to see full
attachment

  
error: Content is protected !!