$1$ Start Excel. Download and open the file named Student$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Manage.xlsx$.$ Save the file as Last$\_$First$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Manage. $0$

$2$ Create a connection to the Friday worksheet in the MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Friday.xlsx file. Load the data as a new worksheet and rename it as Friday. Move the worksheet after Thurs in the workbook. Close the Queries & Connections pane. Save the file.

$10$

$3$ Use Excel Options to show the Developer tab on the ribbon. Enable all macros in the workbook. Record a macro named Heading. Follow these steps in the Friday worksheet:

$*$ Insert three blank rows $1$:$3.$

$*$ Copy A$1$:A$2$ from the Wed worksheet and use Paste Using Source column widths to A$1$ in the Friday worksheet.

$*$ Replace A$2$ text with Friday, and then stop recording.

Save the file as a macro enabled workbook using the same filename. Open the Last$\_$First$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Manage.xlsx file.

Copy the Module$1$ macro from the Last$\_$First$\_$MOS$19\_\_$XL$\_$Expert$\_$PROJ$1\_$Manage.xlsm file to the Visual Basic editor in the Last$\_$First$\_$MOS$19\_$XL$\_$ Expert$\_$PROJ$1\_$Manage.xlsx$.$

Run the Heading macro in the Excel workbook.

In VBE, delete any empty lines or lines $()$ in the Module$1$ code. Copy the code and paste the code in cell A$1$ of the Macro sheet in the Last$\_$First$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Manage.xlsx workbook.

Save the workbook as a macro free workbook. Close the Visual Basic window and close the macro enabled workbook.

$20$

$4$ Apply the password Protected! to the Macro worksheet to protect the worksheet and locked cells. $5$

$5$ On the Wed worksheet, replace the sales for Gayle Youngest with $4150\mathrm{.}00.$ Update the Calculation Options to automatically update. Show the comment in F$4$ and then reply to the comment These have been reviewed and are correct. $($include the period$).15$

$6$ On the Summary worksheet, create a formula in cell B$5$ that provides the total number of Bookings for Thursday and includes the structured reference for the Thursday table.

Repeat this technique to create the structured reference for total sales in cell C$5.$

Create a formula in B$6$ that refers to cell C$58$ in the MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Friday.xlsx workbook.

Create a formula in C$6$ that refers to cell D$58$ using the file in the previous step. Save the file. $10$

$7$ Using the Task Manager, use End Task to close Excel. Start Excel and show recovered files. Use the Snipping Tool to create a rectangular screenshot of the Document Recovery Task pane and the recovered Last$\_$First$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Manage.xlsx file.

Open the recovered file. Copy and paste the snip to cell A$1$ in the Recovery worksheet. Close the Document Recovery task pane. Save the file. $10$

$8$ Save the file as an Excel Template with the name Last$\_$First$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Template.xltx$,$ accept to refresh the external data.

Open the Last$\_$First$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Manage.xlsx file. Create a workbook connection to the template file. Close the Query Editor and keep the changes.

Rename Sheet$1$ as Template and ensure it's located as the last sheet in the workbook. Close Queries & Connections pane. Save the file. Close the template file.

$10$

$9$ On the Summary worksheet, restrict editing of the formulas in B$4$:C$6.$ Enter the Title Protect Range and use the password Protected! Paste the permission information into a new workbook.

Copy the range A$1$:D$4$ and then paste to cell A$8$ in the Summary worksheet. Close Book$1.5$

$10$ Encrypt the workbook with the password Protected! Use the Snipping Tool to take a rectangular snip of the Protect Workbook yellow highlighted area. Copy the snip and then remove the workbook password encryption. Paste the snip to the Encrypt worksheet in cell A$1.10$

$11$ Protect the structure of the workbook without entering the optional password. Close the file. $5$

$12$ Ensure the worksheets are in the following order: Summary, Wed, Thurs, Friday, Macro, Recovery, Encrypt, and Template. Save and close Last$\_$First$\_$MOS$19\_$XL$\_$Expert$\_$PROJ$1\_$Manage.xlsx$.$ Exit Excel. Submit the file as directed. $0$

Total Points $100$