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.

Required:
Create a sales spreadsheet the company can use to record the sales in the correct currency values. (2 marks)

Calculate the total sales recorded. (2 marks)

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)

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)

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]
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

Table 2

Required:
Create a spreadsheet for the provided data. (2 marks)

Calculate Tim’s total sales. (2 marks)

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)
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)

Calculate the total commission paid to Tim. (3 marks)
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:

Create a pivot table to show the regional shipping costs. (3 marks)

Create a pivot table to show the total regional sales. (3 marks)

Create a pivot table to show the total sales per region and customer segment.(3 marks)

Create a pivot table showing each region’s total order priority count. (3 marks)

Create a pivot chart to show the total regional sales, shipping cost, and order of priority per customer segment (3 marks)
Create a pivot chart showing the shipping mode count used in each region.(3 marks)

Question 4 (9 marks)

You opened a baby toy shop during the intense COVID-19 movement restriction period. You have been facing several challenges as the business is not growing. Answer the following questions.
You have acquired a loan to boost your toy business. You create toy combos for children below five (5) years old. The combo is composed of three (3) different toys. You cannot make any investment from your daily sales after paying the monthly loan repayment. After making some calculations, you determine you will need to make R18 000 a day to save money for investment. The unit cost of a combo is R40, and you are selling 300 combos a day. Use Goal Seek to determine how many combos you must sell to make R18 000 daily. (3 marks)
You decide to include the cost of making the combo as you purchase from different suppliers. The packaging costs vary between R25 and R15. Determine how many units you need to sell daily to still get a profit of R18 000 at each varying packaging cost when selling the combo at R55 each. Use the Solver function. (6 marks)

Answers to Above Questions on Computer Literacy

Answer 1: The creation of a sales spreadsheet the company can use to record the sales in the correct currency values is performed as follows:

Get completed answers on the questions above on computer literacy from the best IT experts of Student Life Saviour South Africa.