NP_EX365_2021_CT_CS1-4c_AnnaJames_Report_2

Anna James GE ver. 17.2.0-rc0000 1. 4/4 Change the workbook theme. 2. 4/4 Change the column width. 3. 4/4 Change the row height. 4. 4/4 Merge and center a range. 5. 4/4 Apply a cell style. Change the font style. Change the font size. 6. 5/5 Enter values into a range. 7. 5/5 Center the cell contents. Change the font size. Change the background color of a range. Wrap text in a range. 8. 5/5 Add a border to a range. 9. 5/5 Add a border to a range. 10. 5/5 Change the number format. Apply a conditional formatting rule. 11. 5/5 Change the fill color of Conditional Formatting Data Bars. 12. 5/5 Apply a conditional formatting rule. Apply a conditional formatting rule. 13. 5/5 Create a formula using a function. 14. 5/5 Copy a formula into a range. 15. 5/5 Create a formula using a function. 16. 5/5 Delete a column. 17. 5/5 Hide a row. 18. 5/5 Insert a chart. Resize and reposition a chart. Change the chart style. Add a chart title. 19. 5/5 Change the data label options. Define the legend entries for chart data. 20. 4/5 Change the minimum bounds of the vertical axis. Add an axis title to a chart. Add an axis title to a chart. Change the fill color of a chart area. 21. 5/5 Delete a worksheet. SUBMISSION #2 | SCORE IS: 99 OUT OF 100 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. Switch to the Departments worksheet, which contains a header and several tables that need to be completed and formatted. Columns C through H could use a bit more room. Change the width of columns C through H to 12.00 characters. Change the height of the first row to 21 points. Merge and center the contents of the second row to match the merged range in the first row. Format the newly merged range using the 40% - Accent 5 cell style, Bold formatting, and a 14 point font size. The first few headings of the Staff table need to be entered. Enter the data shown in Table 1 in the appropriate cells. Format the headings in the Staff table as follows: a. Center the headers and change the font size to 11 piont. b. Change the background color to one shade darker than the subtitle in the second row, and then wrap the text. From the CCU abbreviation to the end of the table (including the total row), add a White, Background 1 border to all sides of each cell. Be sure to include the total row. Add a thin top border to the total row (including the abbreviation column) using the Automatic color. Format the last three columns of the staff table data (not including the total row). a. Display the turnover percentage data as a percentage with no decimal places. b. Use a conditional formatting rule to highlight departments with a turnover rate greater than 24 percent using the default formatting. Use a conditional formatting rule to add solid orange data bars to the data for the 2024 budget. Don't include the total row. Finish by adding conditional formatting rules to the change from 2023 data that highlights the top and bottom 10% of values. Show 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. The Department summary table should look up the department abbreviation and return all of the data listed. It needs to be filled out. To begin, next to the cell listed "Department", use a function to search the staff table data (not including the row of headings and the total row) and return the department name based on the abbreviation above. The formula will be copied, so make sure it always references the abbreviation. Copy the formula down to complete the Department summary table, and edit the copied formulas to return the values from the headings listed in the left-hand column. In the appropriate cell below the staff table, use a function to display the current date without displaying the time. The averages will not be needed, so delete column O. The date for the next update isn't certain, so hide row 18. Switch to the Budget worksheet, which contains the department budgets from 2021 to 2025. There's already a pie chart showing 2021 budget information, and Carmelo wants another one to compare. Create a 2-D pie chart based on the data in the Department and 2025 columns. Move the chart so that it covers the range H4:O22. Use the third chart style, and use 2025 Budget by Department as the chart title. The pie chart for 2021 now needs to be changed. Format this chart the same way as the 2025 chart. The Departmental Budget Comparison 2021-2025 combo chart needs to be updated as well. a. Change the minimum bounds of the right vertical axis to 40,000,000 , and then add axis titles to the chart. b. 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. In the Budget worksheet, the combo chart's chart area should be formatted using the Blue, Accent 5, Lighter 80% fill color. The Hiring worksheet won't be needed, so delete it from the workbook. New Perspectives Excel 365/2021 | Modules 1-4: SAM Critical Thinking Capstone Project 1c

Author: Note New Per

Tallridge Regional Medical Cente Staffing and Financial Summary by Depar Staff Department MD RN Support Total Turnover CCU Coronary Ca 5 8 15 28 4 EMR Emergency 12 27 33 72 21 END Endocrinolog 3 6 4 13 0 ICU Intensive Ca 17 22 15 54 5 NEU Neurology 6 15 8 29 1 OBS Obstetrics 9 17 17 43 3 ONC Oncology 5 3 9 17 8 RAD Radiology 2 8 27 37 4 Total 59 106 128 293 46 Last update 5/13/2024 Today's date 3/27/2024 Abbreviati on

Tallridge Regional Medical Center Budget Department 2021 2022 2023 2024 Coronary Car $ 4,590,049 $ 5,108,725 $ 4,991,224 $ 5,250,768 Emergency 14,003,329 16,089,825 17,183,933 18,782,039 Endocrinolog 2,775,520 2,850,459 2,841,908 2,847,592 Intensive Ca 8,755,483 8,895,571 9,073,482 9,282,172 Neurology 5,095,866 5,289,509 5,363,562 5,283,109 Obstetrics 10,258,041 10,093,912 10,659,171 11,010,924 Oncology 10,617,497 10,734,289 11,732,578 12,448,265 Radiology 10,670,979 10,606,953 10,797,878 10,927,453 Total $ 66,766,764 $ 69,669,243 $ 72,643,736 $ 75,832,322 2021 2022 2023 2024 2025 $0 $5,000,000 $10,000,000 $15,000,000 $20,000,000 $25,000,000 Departmental Budget Comparison 2021-202 Department Coronary Care Emergency Endocrinology Neurology Obstetrics Oncology Radiology Department Budgets

r 2025 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 $40,000,000 $45,000,000 $50,000,000 $55,000,000 $60,000,000 $65,000,000 $70,000,000 $75,000,000 $80,000,000 $85,000,000 25 Intensive Care Total Total Budget [CELL- RANGE] 45900490 0% [CELL- RANGE] 14003329 00% [CELL- RANGE] 27755200 0% [CELL- RANGE] 87554830 0% [CELL- RANGE] 50958660 0% [CELL- RANGE] 10258041 00% [CELL- RANGE] 10617497 00% [CELL- RANGE] 10670979 00% 2021 Budget by Department Coronar Emerge Endocrin Intensiv Neurolog Obstetri Oncolog Radiolog 6% 25% 4% 12% 7% 14% 17% 15% 2025 Budget by Department Coron Emerg Endoc Intens Neuro Obste Oncol Radio