Important: Credit will only be awarded when the appropriate formula is used. No credit is awarded to manual calculations.

John Smith, the Human Resource Manager at TMU, is interested in looking at how employees are utilizing the extended health benefits offered to employees.

$1-$ Open the Health Expenses sheet. $[20$ pts$]$

a$)$ Insert a new column between columns C and D$.$ The new column should be titled "Coverage Balance". For each row, the cells in that column should display the value of "Expense Limit $-$ Expense Amount". $[5$ pts$]$

b$)$ Use conditional formatting to highlight in red all the values in the new "Coverage Balance" column that have a negative

balance amount. These represent all employees that have exceeded their coverage $($i$.$e$.,$ expensed more than the allowed amount$).[5$ pts$]$

c$)$ Use the Freeze Pane to freeze the top row so all column headings are still visible when scrolling down to the end of the sheet.

Change the color of the Tab for the sheet to "purple". $[5$ pts$]$

d$)$ Convert the data in the sheet to a table. Use "Green, Table Style Dark $11"$ style. Sort the data by "City of Residence" in decending order. After sorting the Table, filter the table so it filters out $($does not show$)$ the data for "Barrie". $[5$ pts$]$

$2-$ Using the data in the Health Expenses sheet, $[20$ pts$]$

a$)$ Create a pivot table on a new worksheet that shows the average of "Coverage Balance", and which uses "Household Size" in ROWS; "City of Residence" in COLUMNS; and "Disability" as FILTER. Change all the values in the table to currency with one digit after the decimal. Rename the sheet to "Pivot Table". $[10$ pts$]$

b$)$ Create a slicer for Household Size. Choose household sizes $1$ to $3.[5$ pts$]$

c$)$ Create a pivot chart of type Stacked Bar and move it to a new sheet. Add "Insurance by Household Size" as the title for the chart. Rename the sheet "Stacked Chart". Filter the chart so it only shows "Disability $=$ Yes". $[5$ pts$]$

$3-$ On the Analysis sheet, $[20$ pts$]$

a$)$ Use the SUM function to complete the calculations for the "Total" column $($column I$)$ and the "Total" row $($row $7).$ Format the numbers in the Table as currency with one digits after the decimal point. $[5$ pts$]$

b$)$ In the "Trend" column $($cells J$3$:J$7),$ create sparklines that represent the revenues across the different size households.$($Hint: only include the data in columns C to H$).$ Change the color of the lines to red. $[5$ pts$]$

c$)$ Create a $3-$D clustered column chart that displays the amounts for each city and each household size $($data in C$3$:H$6),$ such as the City of Residence should be on the x$-$axis; for each city there should be six columns each representing a household size. Each column should be rotated at $40$ degree on the x$-$axis. The title of your chart should be "Health Expenses". You should show data labels, and the chart legend should be placed at the Bottom of the chart. Move the chart to a new sheet and using your first name, name the sheet "your first name Chart" as in$,$ for example, "Roger Chart". $[10$ pts$]$

$4-$ On the Analysis sheet, $[40$ pts$]$

a$)$ In cells B$12$ and B$15,$ use the LEFT and RIGHT functions to display the first four and the last four letters of the word in cell B$5($that word is "Oakville", and hence the letters in B$12$ should be "Oakv" and the letter in B$15$ should be "ille"$).$ Use the CONCAT function to merge the text in B$12$ and B$15$ in B$18$ so the complete word is displayed in cell B$18.[10$ pts$]$

b$)$ In cell D$12,$ create an IF function that checks whether the Total for household size $3$ is less than that of for household size $4($in top Table$).$ If true, it should return $"$OK$"$; else it should return "Inspect". $[10$ pts$]$

c$)$ In cell D$15,$ create a COUNTIF function that looks for the total number of disabilities: Disability $=$"Yes" in the "Health Expenses" sheet. $[5$ pts$]$

d$)$ In cell D$18,$ use the AVERAGEIF function to calculate the average "Expensed Amount" $($column C in sheet "Health Expenses"$)$ that exceed $3,500($i$.$e$.,$ you only want to average the amounts that exceed $3,500).$ Display the result with two digits after the decimal point. $[10$ pts$]$

e$)$ In cells G$11$ to G$14,$ calculate the taxable amount by multiplying the total amount from cell I$3$ to I$5$ by the fixed tax rate in cell J$10.$ To do so$,$ you must use absolute referencing $($which uses $ signs$)$ when referring to the Tax Rate $.$ Round up the values to the nearest number. $[5$ pts$]$