Please help with the following question

Ill Week 12 - Case Problem L3 - P.6( x Homework Help - Q&LA from Onl x 5Succeeding in Business with Mi x > C cloudflare-ipfs.com/ipfs/bafykbzacedw2jwgmi3skyrpuoksxefcyybbdykegbyy6lyoobnf5ybivoy46m?filename=%28New%20Perspectives%29%20Debra%20Gross%20%20Frank%20Akaiwa%20%20Karleen%20Nordquist%20-%20Succeeding%20in%... G 668 date screwed custers does not materially affect the overall learning experien Editorial review has deemed that may suppressed content do overall learning experience. Cengage Learning beerin the right to reframe saudifusal content at my time if subsequent rights reveidioms require it Troubleshooting Workbooks and Automating Excel Applications Chapter 10 9. Add the following sales using the entry form, each time running the macro to copy the information to the sales list: Brady Anderson 7/25/2016 $4500 Richard Taylor 7/25/2016 $750 Richard Taylor 7/27/2016 $1825 Tasha Johnson 7/28/2016 $1750 10. Save and close the July Sales.xism workbook 10 Chapter Exercises e Income Template.xIsx Show all X Type here to search O ENG 10:13 PM US 2020-12-07Ill Week 12 - Case Problem L3 - P.6( x * Homework Help - Q&A from Onl x Succeeding in Business with Mi x C cloudflare-ipfs.com/ipfs/bafykbzacedw2jwgmi3skyrpuoksxefcyybbdykegbyy6lyoobnf5ybivoy46m?filename=%28New%20Perspectives%29%20Debra%20Gross%20%20Frank%20Akaiwa%20%20Karleen%20Nordquist%20-%20Succeeding%20in%.. G Succeeding in Business" with Microsoft@ Excel@ 2013: A Problem-Solving Approach 689 / 723 Editorial review has decried that my suppressa agage Learning reserves the right to rename additional content at mary time if subsequent rights restrictions require it. Troubleshooting Workbooks and Automating Excel Applications Chapter 10 Level 3 - Projected Sales and Commissions for CKG Auto You are working in CKG Auto's 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. CKG Auto pays cach 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 Complete the following: 1. Open the workbook named Sales.xlex located in the Chapter 10 folder, and then save the file as the macro-enabled workbook July Sales.xIsm. 2. Create a new worksheet named SalesRepSummary. On this new worksheet, list the name of each salesperson and summarize each salesperson's total sales and total commissions based on the data listed on the SalesList worksheet. (Hint: Use a PivotTable or the Remove Duplicates button in the Data Tools group on the DATA tab to remove the duplicate names quickly.) 3. Examine the SalesEntryForm worksheet and correct its formula error. Create validation rules so sales rep names appear in a drop-down list using the data on the SalesRepSummary worksheet, sales dates are limited to valid dates in 2016, and sales 10 amounts must be positive values. Annotate the validation rules so users entering the data will know what data to enter and what is allowed. 4. Create a macro named SubmitSales with shortcut key Curl+Shift+S, 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 Chapter Exercises the sales entry form. Create a custom button on the Sales EntryForm 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: SalesEntryForm!85 Sales Rep Name SalesEntryForm!B6 Sales_Date SalesEntryForm!B7 Sales_Amount SalesEntryForm!B8 Commission SalesList!A5 top + . Apply the Use Relative References option to make sure that when recording your macro, the insertion point is relative to the active cell. (Hint. The Use Relative References button in the Code group on the DEVELOPER tab toggles this feature on and off. Be sure the button is highlighted in green to indicate that the feature is on before you begin recording the macro.) e Income Template.xisx Show all X Type here to search O CABO 9 ENG 10:12 PM US 2020-12-07 EIll Week 12 - Case Problem L3 - P.6( x * Homework Help - Q&LA from Onl x Succeeding in Business with Mi x cloudflare-ipfs.com/ipfs/bafykbzacedw2jwgmi3skyrpuoksxefcyybbdykegbyy6lyoobnf5ybivoy46m?filename=%28New%20Perspectives%29%20Debra%20Gross%20%20Frank%20Akaiwa%20%20Karleen%20Nordquist%20-%20Succeeding%20in%.. G Succeeding in Business" with Microsoft@ Excel@ 2013: A Problem-Solving Approach 690 / 723 Chapter 10 Troubleshooting Workbooks and Automating Excel Applications . Enter some sample data into the sales entry form. . Start the macro, assigning the appropriate name and macro shortcut key. Enter a brief description. . Open the Go To box by pressing the F5 function key, and then select top in the list of range names to move the starting point on the SalesList worksheet to cell A5. . Press Ctri+ 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 past 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. . Create a custom macro button called Record Sale on the SalesEntryForm work- sheet that automatically launches this macro. . Remove the sample data from the SalesList worksheet that you used when writing the macro. 5. Create a column chart on a separate worksheet named SalesChart that shows the sales by sales rep, but do not show the sales rep name. Excel will assign numbers to cach column. 6. 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 100 entries. . Place a comment in cell SalesList!A102 warning that to include this entry you will need to update formulas on the SalesRepSummary worksheet. 7. Insert a worksheet named Documentation. 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. B. 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. e Income Template.xIsx Show all X Type here to search O CABO 9 ENG 10:13 PM US 2020-12-07