$1.$ You are a statistical analyst intern for the PR Department at the Twenty Four Frames Headquarters Cape Town. You have just received the data sheet for $2020.$ You are required to present the data at the annual year end Twenty Four Frames review meeting. You have decided to use an Excel workbook, including charts to easily present the information. Go to the Help Centre worksheet and format the color scheme of the excel document to match the Twenty Four Frames branding as follows:

a$.$ Change the theme of the workbook to Office.

b$.$ Change the theme color to Red Orange.

$2.$ Delete column B from the worksheet.

$3.$ Move the data in the range D$9$:E$14$ to the range B$18$:C$23$ and keep source formatting.

$4.$ Enter the values shown in Table $1$ into the corresponding cells in the range D$4$:F$7.$

Table $1$: Data for the Range D$4$:F$7$

D E F

$4$ Speed Quality Resolution

$53$ Mbps SD $480$p

$65$ Mbps HD $720$p

$725$ Mbps Ultra HD $1080$p

$5.$ In cell C$11,$ enter the following text $"$No$"$ before the text "Live Streaming".

$6.$ Use AutoFit to resize columns B$-$F to fit their contents.

$7.$ Merge the cell range B$1$:F$2.$

$8.$ Make the following changes to the merged cells in the range B$1$:F$2$:

a$.$ Apply a Dark Red, Accent $6$ fill color to the merged range $(10$th column, $1$st row of the Theme Colors palette$)$ to make the heading easier to read.

b$.$ Middle$-$align and center the text in the cell.

c$.$ Increase the font size to $14$ point and bold the text.

$9.$ Use the Format Painter tool to apply the format from the subheading titled "Getting Started" to the "Features" subheading in cell B$9$ to create consistent shadings in the worksheet.

$10.$ Format the range H$12$:K$34$ as follows:

a$.$ Apply a Dotted Outside Borders border to the range.

b$.$ Format the border color to Dark Red, Accent $6,$ Darker $50\%(10$th column, $6$th row of the Theme Colors palette$).$

$11.$ Correct the formatting error by applying a Short Date format to cell L$12.$

$12.$ Begin completing the Movie Genre Schedule as follows:

a$.$ Insert a formula without using a function in cell L$4$ that adds the value in cell I$4$ to the value in cell J$4$ and subtracts the value in K$4$ to determine the total movies aired for $2020.$

b$.$ Copy the formula you created in cell L$4$ to the range L$5$:L$9$ without copying any cell formatting. $($Hint: Use the Paste Gallery.$)$

$13.$ In cell I$10,$ create a formula using the SUM function to determine the total TV shows and movies featured in $2020$ by totaling the values in the range I$4$:I$9.$ Copy the formula you created in cell I$10$ to the range J$10$:L$10.$

$14.$ Adjust the Zoom level of the Help Centre worksheet to $110\%.$

$15.$ Go to the Giveaways worksheet, which compiles data about the top $50$ Twenty Four Frames viewers worldwide. Twenty Four Frames summed up a table for the promotional offers and delivery options in a Word file. Import the data as follows:

a$.$ Open the Support$\_$SAM$\_$EX$365\_2021\_$RSA$\_$CS$1-4$a$\_$Twenty Four Frames Headquarters$\_$Giveaways.docx Word document.

b$.$ Select the table, copy the data to the Office Clipboard, paste the Word data into cell J$3$ of the worksheet, keeping the source formatting, and then close the Word file.

$16.$ In keeping with the focus on subscriber number, promotional gifts are awarded to members with the most views. In cell G$3,$ enter a formula using the IF function to automatically determine the type of promotional gift that will be awarded to the top $50$ viewers as follows.

a$.$ If the average hours per week $($in cell C$3)$ is greater than J$4,$ the IF function should return the text in cell K$4.$

b$.$ If the Event Type $($in cell C$3)$ is not greater then J$4,$ the IF function should return the text in cell K$5.$

c$.$ Use absolute references to cells J$4,$ K$4,$ and K$5$ in your formula.

d$.$ Copy the formula in cell G$3$ into the range G$4$:G$52,$ pasting only the formula.

$17.$ The promotional gifts are delivered using various delivery options offered by Twenty Four Frames as shown in the range K$7$:K$9.$ In cell H$3,$ enter a formula using a nested IF function to determine the delivery option associated with the location. The location of the user selected for the promotional offer is shown in cell B$3.$

a$.$ If the location is the US$,$ the function should return the value in cell K$7.$

b$.$ If the location is not the US$,$ a second IF function should check if the location is Canada. If this condition is true, the function should return the value in cell K$8.$

c$.$ If the location is not Canada, a third IF function should check if the location is Australia. If this condition is true, the function should return the value in cell K$9.$

d$.$ If the third IF function condition is false, the function should return the text Cargo Ship.

e$.$ Copy the formula in cell H$3$ into the range H$4$:H$52,$ pasting only the formula.

$18.$ Twenty Four Frames plans to find out whether the more hours viewed, is due to the user's data package and Twenty Four Frames premium plan. Determine whether these packages are related to the number of hours viewed per week as follows:

a$.$ In cell I$3,$ enter a formula using the AND function that tests