Question 1 [19 marks]

Read the following background information before attempting any questions. Cape Machinery sells various types of machinery and equipment. You have been provided with the attached sales data records. You noted different date formats are used in the document. The records are for the February to May 2023 period. From the knowledge you have acquired from the Computer Advanced module, create a
sales report for the company’s management by answering the following questions.

Required:

1.1Correct the date format in the sales record to use the YYYYMMDD date format (4 marks)

1.2 Calculate the total sales recorded for each item and the total sales for the period. (3 marks)
1.3 Add a data validation message to restrict date entries in the date column. (2 marks)

1.4 Add a message to the date column that notifies the user that the column only accepts date entries and an alert message when 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)

You have decided to show how the different machinery categories have been performing using a Pivot Table. Answer the following questions.

1.6 Use a Pivot Table to show the product items and quantities sold in each month.(3 marks)

Note: NO marks if the table was not created using a Pivot Table.

1.7 Show the total sales from each product item in a Pivot Table. (3 marks)

Note: NO marks if the table was not created using a Pivot Table.

Mobile Sales sells various cell phone brands online. They have a promotion, and all brands are discounted. See the Q.2 sheet in the attached Excel file. The cell phones are discounted in percentages ranging from 10 to 15%. Answer the following questions.

Required:

2.1 Use the VLOOKUP function to add the discount percentages to the table in the spreadsheet. Copy and add the VLOOKUP function used as your answer. NO marks if the VLOOKUP function is not used. (4 marks)

2.2 Calculate the total quantities of cell phones sold. (2 marks)

2.3 Calculate each brand’s total sales and the total sales the company realised from all brands. (4 marks) Note: First, calculate the sales for each brand, and then SUM these sales.

2.4 Calculate the discount for each brand and the total discount given by the company. (4 marks) Note: First, calculate the discount for each brand, and then SUM these discounts.

2.5 Calculate each brand’s sales after the discount has been applied and the total sales the company realised after the discounts applied on each brand (4 marks)
Note: First, Calculate the sales after the discount is applied for each brand, then SUM these sales.

2.6 Create a pivot table to show the total quantities sold for each brand. (2 marks) Note: NO marks if table was not created using a Pivot Table.

2.7 Create a pivot table to show the total sales per brand after discount. (2 marks)

Note: NO marks if table was not created using a Pivot Table.

2.8 Create a pivot chart (column chart) using the information from the pivot table in 2.7 (3 marks)

You have decided to purchase a house and start a rental business. You qualify for a loan of R300 000 and during your search, you found a house selling for the same value. Upon application for a loan, Citi Bank offers you the loan at 15% per annum, paid over ten (10) years. Calculate the repayment amount for a 15-year loan. (Use the PMT function and a table to capture the values).

3.1 15 years (3 marks)

You have identified a potential rental property priced at R300,000. Citi Bank proposes a 20-year loan with a 15% annual interest rate, resulting in a monthly payment of R3950. Determine the total payment over the loan term of 20 years. (Use the FV function and a table to capture the values).

3.2 20 years (3 marks)

Answers on Above Questions on Computer Literacy

Answer 1: Based on the sales data record, they are converted to the given YYYYMMDD date format in the attached excel file as follows:

Get completed answers on the above questions on computer literacy from the experts of Student Life Saviour in South Africa at affordable prices.