Question 1 [44 marks] Cape Produce is a fruit and vegetable retail shop based in the Western Cape province. With 15 branches in the region, their main branch is in the Cape Town city center. The branch has experienced a decline in demand for most of its products. The business has neglected marketing efforts in the past three years due to the COVID-19 pandemic, which impacted sales and profitability. You have been employed to lead the Sales department and implement marketing promotions. Before you strategise any marketing campaign, you must understand how the business performs.

Background

You have been provided with the attached Cape Produce Sales data in an Excel sheet. Answering these questions will provide a marketing promotion planning basis.

Required:

Note: add columns to the provided data table to calculate discounts and sales. Complete all calculations using formulas in Excel. Use the provided Answer Sheet.

1.1 Arrange the fruit names in descending order using the Sort & Filter function. (2 marks)

1.2 Calculate the sales obtained from each fruit. (3 marks)

Show the formula visible in the formula bar.

1.3 Calculate the total sales obtained by Cape Produce from the provided data in the Rand currency. Show the formula visible in the formula bar. (2 marks)

1.4 Calculate the total quantities of fruits sold in the period. (2 marks) Show the formula visible in the formula bar.

1.5 Using an Excel function, determine which fruit had the highest quantities sold. (2 marks)

Show the formula visible in the formula bar.

1.6 What is the sales value of the fruit in Q1.5? (1 mark)

1.7 Using an Excel function, determine which fruit had the lowest sales and identify its sales value. Show the formula visible in the formula bar. (2 marks)

You intend to offer promotional discounts to customers to improve customer traffic and sales. Show how discounts will affect company sales by answering the questions below. (Note: Sales referenced in Q1.8 and 1.15 are sales you calculated in Q.1.2)

1.8 If a 10 % discount is applied to the sales value of each fruit item, calculate the discount value per fruit item and the total discount on the sales. (4 marks)

1.9 After the 10% discount is applied in Q.1.8, calculate the discounted sales per fruit item and the total sales after the discount is applied. (4 marks)

Note: First calculate the discounted sales for each fruit then SUM the discounted sales.

If Cape Produce had implemented a 10% discount policy on only sales invoices above R500 000, answer the following.

1.10 What is the total discount value for sales above R500 000 obtained in Q1.2?

Note: Use the IF logical function. Add a message “No discount” for sales below R500 000. Add a screenshot of the calculated column showing the formula used in the formula bar. First calculate the discount for each fruit then SUM the discounts. (5 marks)

1.11 Determine the total sales after the 10% discount is applied in Q.1.10. (4 marks) Note: Calculate the sales after discount for each fruit then SUM the sales..

1.12 Create a column chart for the fruit quantities sold. Include quantities sold as data labels. (3

1.13 Create a line chart to show the total quantities sold for Orange, Guava, Dates, and Banana. (4 marks)

1.14 Create a 2-D bar chart to show the total sales of Apples, Cashew nuts, Dry apricots, Mango, and Papaya calculated in Q.1.2. Include data labels. (3 marks)

1.15 Create a 3-D pie chart to show the total sales of Plums, Papaya, and Oranges as calculated in Q.1.2. Include data labels. (3 marks)
Question 2 [6 marks] The impact of COVID-19 on the lives of many was a wake-up call to the need for savings. Many people lost jobs and sources of income. Saving for the unforeseen future is a measure that will likely curb the impact of such occurrences.

2.1. You have started saving for such challenging times by investing R3 000 at the end of each month for five (5) years. If the interest on your investment is 9% per annum, how much should you have at the end of 5 years? (3 marks)

2.2. You have cut back on your spending since the COVID-19 lockdown period. You acquired a home loan valued at R550 000 to buy a property and start a rental business. The interest on the loan is 12% per annum, and the repayment period is 20 years. Calculate your monthly repayment amount. (3 marks)

Answers to Above Questions on Computer Literacy

Answer 1: The arrangement of fruits in descending order using the Sort & Filter function is performed as follows:

Get answers to above questions on computer literacy as offered by the best do my assignment South Africa experts of Student Life Saviour.