$$ Open the file NP$\_$EX$19\_$CS$1-4$a$\_$FirstLastName$\_1.$xlsx$,$ available for download from the SAM website.

$$ Save the file as NP$\_$EX$19\_$CS$1-4$a$\_$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$\_$EX$19\_$CS$1-4$a$\_$FirstLastName$\_2.$xlsx still open, ensure that your first and last name is displayed in cell B$6$ of the Documentation sheet.

o If cell B$6$ 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\mathrm{.}00$ characters.

$3.$ Change the height of row $1$ to $21$ points.

$4.$ Merge and center the contents of the range A$2$:J$2.$

$5.$ Format the merged range A$2$:J$2$ as described below:

a$.$ Apply the $40\%-$ Accent $5$ cell style.

b$.$ Apply bold formatting.

c$.$ Change the font size to $14.$

$6.$ Enter the values shown in Table $1$ into the corresponding cells in the range A$5$:E$5.$

Table $1$: Data for the Range A$5$:E$5$

A B C D E

$5$ Abbreviation Department MD RN Support

$7.$ Format the range A$5$:J$5$ 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\%(9$th column, $4$th row of the Theme Colors palette$).$

d$.$ Apply Wrap Text to the text in each cell.

$8.$ Select the range A$6$:J$14$ and then add a White, Background $1$ border to all sides of each cell.

$9.$ Select the range A$14$:J$14$ and then add a thin top border to each cell using the Automatic color.

$10.$ Select the range H$6$:H$13$ 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\mathrm{.}24$ as light red fill with dark red text.

$11.$ Select the range I$6$:I$13$ and then use conditional formatting to add solid orange data bars.

$12.$ Select the range J$6$:J$13$ 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 M$7$ using the VLOOKUP function to find an exact match for the department abbreviation. Look up the department abbreviation $($cell M$6)$ using an absolute reference, search the staff table data $($the range A$6$:J$13)$ using an absolute reference, and return the department name $($the $2$nd column$).$

$14.$ Copy the formula in cell M$7$ to the range M$8$:M$15,$ and edit the copied formulas to return the value from the column indicated by the label in column L$.$

$15.$ In cell C$17,$ 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 A$5$:A$12$ and F$5$:F$12.$ Modify the chart as described below:

a$.$ Resize and reposition the chart so that the upper$-$left corner is located within cell H$4$ and the lower$-$right corner is located within cell O$22.$

b$.$ Apply Chart Style $3$ to the chart.

c$.$ Enter $2022$ Budget by Department as the chart title.

$19.$ In the $2018$ Budget by Department $2-$D pie chart $($located in the range H$23$:O$43),$ make the following changes:

a$.$ Change the data labels to display only the percentage and a label position of Center. $($Hint: Select Reset Label Text to remove category names$)$

b$.$ Reposition the legend on the right side of the chart.