Question 1 [10] You are an intern in a retail store's laptop and tablet sales department. The sales manager has complained of incorrect data recordings. The date data are recorded in different formats with poorly documented sales figures. It is difficult to read the recorded sales figures with no currency symbol. You used your Excel skills to enforce data validation in the spreadsheet to prevent such challenges in the future. Below is the table containing the recorded sales. Answer the questions below. Date of purchase Product name Unit cost in Rands Quantities sold 2022/09/08 Galaxy Book Odyssey 85000 3 2022/09/16 Galaxy Book 2 49000 4 2022/10/17 Galaxy Book 2 Pro 65000 2 2022/10/21 Galaxy Book 3 72000 5 2022/11/12 Galaxy Book3 Pro 80000 7 2022/11/22 55" Odyssey Ark 30000 3 2022/11/24 Galaxy Tab S8 6000 6 2022/11/26 Galaxy Tab A7 4000 7 Required: 1.1 Create a sales spreadsheet the company can use to record the sales in the correct currency values. (2 marks) 1.2 Calculate the total sales recorded. (2 marks) 1.3 Add data validation to restrict date entries and input message to the Date column to notify users to enter dates only between 2022/01/01 and 2022/12/31. (2 marks) 1.4 Add an alert message to the date column that notifies the user that the column only accepts dates if a text is entered. (2 marks) 1.5 Restrict the total sales column to accept currency without decimals and add a message that reads, "Only enter currency values without decimals." (2 marks) Question 2 [13] 3 HCLT108-1-July-Dec2023-FA2-IM-V.2-29052023 Cape Electronics sells office furniture with five sales representatives working in their wholesale branch. Varying commission percentages are paid based on product sales and commission categories. No commission is paid for monthly product sales below R200 000. The December 2022 product sales for Tim, one of the sales representatives, are given in Table 1, and commission percentages are shown in Table 2 below. Answer the following questions. Table 1 Product Quantities Sold Unit Cost in Rands Commission Category Printers 15 85000 A Desktop computers 40 9000 C Laptops 120 8000 B Smartphones 107 6500 C USB cables 520 160 A Digital tablets 49 2500 B Hard drives 180 1600 B Digital mouse 290 180 A Table 2 Commission Category Commission % A 15% B 13% C 11% Required: 2.1 Create a spreadsheet for the provided data. (2 marks) 2.2 Calculate Tim's total sales. (2 marks) 2.3 Use the VLOOKUP function to add the commission percentage to the created table in the spreadsheet. Add the VLOOKUP function used as your answer. (3 marks) 2.4 Calculate the commission from each product's total sales, and where the sale is below R200 000, add the message "No commission." Add a screenshot of your table in Excel showing the formula in the formula bar. (3 marks) 2.5 Calculate the total commission paid to Tim. (3 marks) 4 HCLT108-1-July-Dec2023-FA2-IM-V.2-29052023 Question 3 [18] You have been learning to use pivot tables for summarising and analysing data in one of this module's study units. You found the topic exciting and have come across the attached Walmart stores data. Create pivot charts and graphs to show how Walmart performs in its operating regions. Note: The file attachment is available on Col Campus under the Assignments tab. Click on Formative Assessment 2 (FA2); the file is attached with the assessment question paper. Required: 3.1 Create a pivot table to show the regional shipping costs. (3 marks) 3.2 Create a pivot table to show the total regional sales. (3 marks) 3.3 Create a pivot table to show the total sales per region and customer segment. (3 marks) 3.4 Create a pivot table showing each region's total order priority count. (3 marks) 3.5 Create a pivot chart to show the total regional sales, shipping cost, and order of priority per customer segment (3 marks) 3.6 Create a pivot chart showing the shipping mode count used in each region. (3 marks)