You are working in MTSU Autos Sales Department as an analyst. The sales manager, Leroy Mason, wants to automate the worksheet he uses to calculate monthly sales commissions so that others can use it. MTSU Auto pays each salesperson a base salary plus a commission. This commission value has changed so this value for all new entries should be 7% of the sales amount. Leroy asks for your help in ensuring users enter valid data and do not change or delete the sales data the workbook already contains. He also wants to include a chart comparing the total sales of each salesperson without revealing their names.

Please show formulas in excel

Complete the following:

Open the workbook named MTSUCarSales(your last name).xlsx in the Module 4 section of the D2L course, and then save the file as the macro-enabled workbook MTSUCarJulySales.xlsm.

Create a new worksheet named SalesRepSummary. On this new worksheet, use a table to list the name of each salesperson and summarize each salespersons total sales and total commissions based on the data listed on the SalesList worksheet. Make sure that the table will auto-update when new data is added to the SalesList through row 255.

Examine the SalesEntryForm worksheet and correct its formula error. Create validation rules so that unique sales rep names appear in a drop-down list using the data on the SalesRepSummary worksheet, sales dates are limited to valid dates in 2022, and sales amounts must be positive values. Annotate the validation rules so users entering the data will know what data to enter and what is allowed.

Create a macro named UpdateSalesList with shortcut key Ctrl+Shift+C, assigned to this workbook. The purpose of the macro is to automate the moving of the data entered in the sales entry form to the SalesList worksheet and then to clear the data in the sales entry form. Create a custom button on the SalesEntryForm worksheet with the label Record Sale that automatically launches this macro. For best results, use the following procedure:

Before recording the macro, assign range names to the following cells:

Click the Use Relative References button in the code section of the Developer tab to make sure that when recording your macro, the insertion point is relative to the active cell. Be sure the button is highlighted in dark gray to indicate that the feature is on before you begin recording the macro.

Enter some sample data into the sales entry form

Click Record Macro, then assign the appropriate name and shortcut key. Store the macro in this workbook and enter a brief description

Open the Go To box by pressing the F5 function key, then select Top in the list of range names to move the starting point on the SalesList worksheet to cell A5.

Press Ctrl+ to move the active cell to the bottom of the list. Then, using the arrow keys, move the active cell down one row to the first empty cell. Type =Sales_Rep_Name. Move to the next column where the user will enter the sales date, and then type =Sales_Date. Repeat this procedure to enter the sales amount and commission using their corresponding range names.

Copy the cells with the formulas you just entered, and then use the Paste Special Values option to paste the cells in the same location. This paste action removes the formulas but keeps the values that appear in the data entry form.

Go to the Sales_Rep_Name range and delete the entry. Move down one cell and delete the sales date and sales amount. You must clear the input data to leave the cells ready for the next user to enter new data. Press the Esc key.

Stop recording the macro.

Create a custom macro button called Record Sale Info on the SalesEntryForm worksheet that automatically launches this macro.

Remove the sample data from the SalesList worksheet that you used while recording the macro.

SalesEntryForm!B5	Sales_Rep_Name
SalesEntryForm!B6	Sales_Date
SalesEntryForm!B7	Sales_Amount
SalesEntryForm!B8	Commission
SalesList!A5	Top

NOTE: It is common for this macro to crash with an error. If that happens, the easiest thing is to delete your macro and re-record it. Make sure that you are following the directions above closely, such as using the keyboard specifically when specified instead of your mouse.

Create a column chart on a separate worksheet named SalesChart that shows the sales by sales rep from the table created in step 2, but do not show the sales rep name in the chart. Excel should assign numbers to each column.

Test the workbook formulas, macro, and chart to ensure they all work correctly. Be sure to try invalid values to be sure data validation is working correctly. Return the SalesList to its original state when testing is complete. Note the following:

Does the data on the SalesRepSummary worksheet update correctly?

Modify the formula ranges to accommodate up to 250 entries.

Place a comment in cell SalesList!A256 warning that to include this entry, you will need to update formulas on the SalesRepSummary worksheet.

Insert a worksheet named Documentation. Move it to be the first sheet in the workbook. Enter your name and date as the user who created the workbook and the one who last updated it. Identify the purpose of the workbook, explain how the workbook should be used, identify and explain worksheets, and identify and explain the workbook macro.

Protect the workbook so that users cannot add or delete worksheets. Protect the SalesEntryForm worksheet, allowing data entry for only the sales rep name, sales date, and sales amount. Do not use passwords.

Add the following sales using the entry form, each time running the macro to copy the information to the sales list:

Marsha Littlefield, 7/25/2022, $5500

Sam Smith, 7/25/2022, $900

Sam Smith, 7/27/2022, $1175

Emily Newhouse, 7/28/2022, $2100

Save and Close the MTSUCarJulySales workbook.

FileHomeInsertPageLayoutFormulasDataReviewViewHelpHownts Share Workbook (Legacy) B12 :x \begin{tabular}{|l|c|} \hline Sales Rep Name & \\ \hline Sales Date & \\ \hline Sales Amount & \\ \hline Commission & \#NAME? \\ \hline \end{tabular} Commission Percentage: 7.75% Share Workbook (Legacy) > FileHomeInsertPageLayoutFormulasDataReviewViewHelpHownts Share Workbook (Legacy) B12 :x \begin{tabular}{|l|c|} \hline Sales Rep Name & \\ \hline Sales Date & \\ \hline Sales Amount & \\ \hline Commission & \#NAME? \\ \hline \end{tabular} Commission Percentage: 7.75% Share Workbook (Legacy) >