Question 1:

You’re a junior analyst at a marketing agency responsible for tracking and analysing social media campaign performance. Your team relies on a shared spreadsheet to record engagement metrics across various platforms, such as likes, shares, and comments. However, you’ve noticed inconsistencies in data entry and a lack of standardised formatting, making it challenging to generate accurate reports. Below is the table containing recorded social media engagement metrics. Answer the following questions.

1/1/2023  Facebook                150               25                      10

1/15/2023  Twitter                       75               10                        5

2/10/2023  Instagram                200               30                      15

3/20/2023  LinkedIn                  100               20                        8

4/5/2023  Facebook                180               35                      12

5/12/2023  Twitter                       90               15                        7

6/30/2023  Instagram                220               40                      20

7/15/2023  LinkedIn                  120               25                      10

1.1 Create a MicrosoftExcelspreadsheet of the table provided, which the company can use to record the correct data entry values. (2 marks)

1.2 Add an Engagement column and calculate the total engagement for each platform on each given date. (3 marks)

1.3 Format the Date column to display in the order yyyy-mm-dd. (1 mark)

1.4 Add data validation to limit date entries between 2023/01/01 and 2023/12/31 and include an input message in the Date column to remind users to input dates only in this range. (2 marks)

1.5 Add an alert message in the date column to inform users that only date entries are permitted if they attempt to input text. (2 marks)

Question 2:

TechReady Solutions is a leading technology retailer specialising in consumer electronics. They have a team of sales representatives responsible for promoting and selling various products to customers. Each sales representative is assigned monthly sales goals, and their performance is evaluated based on their ability to meet or exceed these goals. The company offers different commission rates based on the performance category of each sales representative. In February, TechTrend Solutions assessed the performance of their sales team. Answer the following questions.

Table 1:

Table 2:

Required:

2.1 Create a Microsoft Excel spreadsheet for the provided data. (2 marks)

2.2 Using the provided sales data table and commission category table, determine the commission percentage for all the sales representative based on their commission category (Use VLOOKUP function). (4 marks)

2.3 Calculate the commission from each sales representative, and where the sales are below the sales goal, add the message ” No commission.” (4 marks)

2.4 Calculate the total salary paid to all the sales representatives who made the commission. (2 marks)

Question 3 [22 marks]

Having explored pivot tables for data summarisation and analysis in one of your recent course modules, you’ve found the topic captivating. Now, you’ve come across Makro’s store data and are eager to leverage pivot charts and graphs to illustrate how Makro is faring across its various operating regions.

Note: Complete the following questions using the provided data sheet named HCLT108-

1-July-Dec2024-FA2-Macro Data file available on Col Campus under the FA2 tab.

Required:

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

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

3.3 Create a pivot table to show the average expenses per store location and region.(3 marks)

3.4 Create a pivot table showing expenses compared between Makro stores in different regions. (3 marks)

3.5 Create a pivot table showing each store location’s average sales, expenses, and revenue. (3 marks)

3.6 Create a Line with Markers chart from the pivot table created in Q3.3. (3 marks)

3.7 Create a Bar chart from the pivot table created in Q3.4 and provide a title for the chart. (4 marks)

Question 4 [6 marks]

You opened a restaurant during an intense recession. You have been facing several challenges as the business is not growing. Answer the following questions.

You have acquired a loan to boost your business. You decided to create combos that will draw in customers. You cannot make any investment from your daily sales after paying the monthly loan repayment. After some calculations, you determine you will need to make R20 000 a day to save money for investment. The unit cost of a combo is R40, and you are selling 100 combos a day.

4.1Use Goal Seek to determine how many combos you must sell to make R20,000 daily. (3 marks)

You want to determine the profitability of your business. You are currently selling 100 combos a day at R40 each. The cost of making a combo is R15.

4.2 How many combos must you sell daily to make a profit of R20 000? (Use the Solver function) (3 marks)

Answers to Above Questions on Computer Literacy

Answer 1:  The Microsoft Excel spreadsheet that the company can use to record the correct data entry values is presented below:

Hire the best computer literacy experts from the team of writers of Student Life Saviour in South Africa for best writing assistance to above questions.