In column 6, Benicio wants to indicate whether DIG Technology should send the customer a promotional offer. Customers are eligible for the offer if they purchased a Mini 2 in the U.S. Provide the promotional offer information for Benicio as follows: In cell G5, start to enter a formula using the AND function and structured references b. The first condition tests whether the value in the Product column ([@Product]) equals "Mini 2", the product eligible for the promotional offer. C The second condition tests whether the value in the Location column ([@Location]) equals "U.S.", the location eligible for the promotional offer. d. If Excel does not fill the column, fill the range G6:440 with the formula in cell G5. 10. The Sales table is currently sorted by the values in the Sale ID column, but Benicio wants to sort the table by date, then amount to make it easier to track the data. Apply a custom sort to the Sales table to sort it in ascending order first by the values in the Date column, and then by the values in the Amount column. 11. Benicio wants to make sure that the sales table does not contain any duplicate records, which would make any sales analysis incorrect. Identify and remove duplicate records in the Sales table as follows: a. In the range A5:A40, create a conditional formatting Highlight Cells Rule that formats Duplicate Values in Light Red Fill with Dark Red Text. b. Delete the second duplicate record from the table. (Hint: Do not delete the row from the worksheet.) 12. Benicio might want to filter the Product Details data in the range 14:M16, so he asks you to format it as a table. Format the range 14:M16 as a table using Light Gray, Table Style Medium 14 to match the Sales table. 13. Benicio wants to examine the January, 2022 sales by country and channel. Create a Pivot Table based on the Sales table as follows: a. On a new worksheet, insert a PivotTable based on the data in the Sales table, and use January Pivot as the name of the worksheet. b. Display the Channel Type values as column headings. C. Display the Location values as row headings. d. Sum the Amount values 14. Format the PivotTable as follows to make it easier for Benicio and others to interpret: Apply the Currency number format with o decimal places and the $ symbol to the Sum of Amount values. a. b. C. d. Use Sales (000s) as the custom name of the Sum of Amount field. In cell A4, use Country to identify the row headings. In cell B3, use Channel to identify the column headings. Change the Pivottable style to White, Pivot Style Medium 14 to coordinate with the tables on the Current Sales worksheet. e. New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project la a 5. Benicio wants to compare the sales of each product in 2021 with the sales in 2020. He has the 2020 sales data stored in a separate workbook. Add the 2020 sales data to the All Locations worksheet as follows: Open the workbook Support_EX19_CS5-8a_2020.xlsx b Return to the All Locations worksheet in the original workbook. C. In cell G5, enter a formula using an external reference to display the total sales of Mini products in 2020 (cell F5). d. In the formula in cell G5, change the absolute reference to a mixed reference, with a relative reference to the row number. e Fill the range G6:G8 with the formula in cell G5, filling without formatting. f. Close the workbook Support_EX19_CS5-8a_2020.xlsx. In the range B12:E12, Benicio wants to display a rating depending on the total sales for each quarter. He listed the rating criteria in the range A14:F15. For example, if total sales in Quarter 1 are between $5900 and $5999, the Performance rating is Good. Enter the performance ratings as follows: In cell B12, start to enter a formula using the HLOOKUP function. b. Use the Total Q1 sales (cell B8) as the value to look up. C. Use the Revenue Amts and Rating information (range $B$14:$F$15) as the table containing the lookup data, using absolute references to specify the range. d. Specify that row 2 contains the value you want to return, which is the performance rating Specify an approximate match (TRUE) because the Revenue Amts represent ranges of values. f. Fill the range C12:E12 with the formula in cell B12 to enter ratings for Quarters 2- 4. a. e. 6. In the range 13:17, Benicio listed information about the managers of the DIG Technology main offices in the U.S., Canada, and Mexico. He needs to add a link to the email address of the U.S. manager. In cell L5, create a link to the tfenton@example.com email address without changing the display text. 7. Benicio also wants to make it easy to access more detailed information about the managers, which he has stored in a Word document. Create a link to a file as follows: a. In cell 19, create a link to the Word document Support_EX19_CS5- 8a_Management.docx. b. Use Management Details as the text to display. C. Use Access manager details as the Screen Tip text. 8. Go to the Current Sales worksheet, which contains a table listing sales data for January, 2022. Benicio wants to use the table data in formulas. Assign the table name Sales to the table of sales data in the range A5:640 to make it easy to refer to the data. In column 6, Benicio wants to indicate whether DIG Technology should send the customer a promotional offer. Customers are eligible for the offer if they purchased a Mini 2 in the U.S. Provide the promotional offer information for Benicio as follows: In cell G5, start to enter a formula using the AND function and structured references b. The first condition tests whether the value in the Product column ([@Product]) equals "Mini 2", the product eligible for the promotional offer. C The second condition tests whether the value in the Location column ([@Location]) equals "U.S.", the location eligible for the promotional offer. d. If Excel does not fill the column, fill the range G6:440 with the formula in cell G5. 10. The Sales table is currently sorted by the values in the Sale ID column, but Benicio wants to sort the table by date, then amount to make it easier to track the data. Apply a custom sort to the Sales table to sort it in ascending order first by the values in the Date column, and then by the values in the Amount column. 11. Benicio wants to make sure that the sales table does not contain any duplicate records, which would make any sales analysis incorrect. Identify and remove duplicate records in the Sales table as follows: a. In the range A5:A40, create a conditional formatting Highlight Cells Rule that formats Duplicate Values in Light Red Fill with Dark Red Text. b. Delete the second duplicate record from the table. (Hint: Do not delete the row from the worksheet.) 12. Benicio might want to filter the Product Details data in the range 14:M16, so he asks you to format it as a table. Format the range 14:M16 as a table using Light Gray, Table Style Medium 14 to match the Sales table. 13. Benicio wants to examine the January, 2022 sales by country and channel. Create a Pivot Table based on the Sales table as follows: a. On a new worksheet, insert a PivotTable based on the data in the Sales table, and use January Pivot as the name of the worksheet. b. Display the Channel Type values as column headings. C. Display the Location values as row headings. d. Sum the Amount values 14. Format the PivotTable as follows to make it easier for Benicio and others to interpret: Apply the Currency number format with o decimal places and the $ symbol to the Sum of Amount values. a. b. C. d. Use Sales (000s) as the custom name of the Sum of Amount field. In cell A4, use Country to identify the row headings. In cell B3, use Channel to identify the column headings. Change the Pivottable style to White, Pivot Style Medium 14 to coordinate with the tables on the Current Sales worksheet. e. New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project la a 5. Benicio wants to compare the sales of each product in 2021 with the sales in 2020. He has the 2020 sales data stored in a separate workbook. Add the 2020 sales data to the All Locations worksheet as follows: Open the workbook Support_EX19_CS5-8a_2020.xlsx b Return to the All Locations worksheet in the original workbook. C. In cell G5, enter a formula using an external reference to display the total sales of Mini products in 2020 (cell F5). d. In the formula in cell G5, change the absolute reference to a mixed reference, with a relative reference to the row number. e Fill the range G6:G8 with the formula in cell G5, filling without formatting. f. Close the workbook Support_EX19_CS5-8a_2020.xlsx. In the range B12:E12, Benicio wants to display a rating depending on the total sales for each quarter. He listed the rating criteria in the range A14:F15. For example, if total sales in Quarter 1 are between $5900 and $5999, the Performance rating is Good. Enter the performance ratings as follows: In cell B12, start to enter a formula using the HLOOKUP function. b. Use the Total Q1 sales (cell B8) as the value to look up. C. Use the Revenue Amts and Rating information (range $B$14:$F$15) as the table containing the lookup data, using absolute references to specify the range. d. Specify that row 2 contains the value you want to return, which is the performance rating Specify an approximate match (TRUE) because the Revenue Amts represent ranges of values. f. Fill the range C12:E12 with the formula in cell B12 to enter ratings for Quarters 2- 4. a. e. 6. In the range 13:17, Benicio listed information about the managers of the DIG Technology main offices in the U.S., Canada, and Mexico. He needs to add a link to the email address of the U.S. manager. In cell L5, create a link to the tfenton@example.com email address without changing the display text. 7. Benicio also wants to make it easy to access more detailed information about the managers, which he has stored in a Word document. Create a link to a file as follows: a. In cell 19, create a link to the Word document Support_EX19_CS5- 8a_Management.docx. b. Use Management Details as the text to display. C. Use Access manager details as the Screen Tip text. 8. Go to the Current Sales worksheet, which contains a table listing sales data for January, 2022. Benicio wants to use the table data in formulas. Assign the table name Sales to the table of sales data in the range A5:640 to make it easy to refer to the data