HIGHER EDUCATION PROGRAMMES

Academic Year 2025: January - June

Formative Assessment 2: Computer Literacy Advanced
(HCLT108-1)
NQF Level, Credits: 5, 10
Weighting: 25%
Assessment Type: Practical Questions
Educator: L. Saunders
Examiner: L. Saunders
Due Date: 2 May 2025
Total: 50 Marks

Instructions:
• This paper consists of four (4) questions.
• It is based on Units 6 - 8 of your Boston City Campus Study Guide.
• All questions are compulsory.
• Download the Answer Sheet (Word format) from Col Campus under the FA
2 assessment tab
• Submit
o The Answer Sheet in PDF format
o Excel Spreadsheet

Learning Outcomes
• Use Data validation
• Add a named range
• Use a named range in a formula
• Use Lookup functions
• Combine functions
• Use Pivot Tables
• Create Pivot Charts
• Utilise Goal Seek and Solver
1 HCLT108-1-Jan-Jun2025-FA2-LS-V.3-28012025
Note: Complete the following questions using the provided data sheet named HCLT108- 1-
Jan-June2025-FA2-Data file available on Col Campus under the FA2 tab.

Question 1 [13 marks]

You are a junior analyst for a restaurant responsible for tracking and analysing their
finances; you are responsible for calculating weekly totals, subtotals, and taxes for
different menu items and then generating a report to understand the sales performance.
Answer the following questions.

Required:

1.1 Add a data validation message to restrict price entries in the price column to
whole numbers. (2 marks)

1.2 Restrict the Subtotal column to accept currency without decimals and add a
message that reads, "Only enter currency values without decimals." (2 marks)

1.3 Add an error alert to the Subtotal column that notifies the user that the column only
accepts currency values without decimal entries when a text is entered. (2 marks)

1.4 Calculate the subtotal recorded for each item. (2 marks)

1.5 Rename the cell at I1 to a named range called Tax_Rate. (2 marks)

1.6 Calculate the tax for each subtotal using the named range (cell I1). (3 marks)

2 HCLT108-1-Jan-Jun2025-FA2-LS-V.3-28012025
Question 2 [10 marks]
After analysing Joe's Pizza Ledger's finances, you must further evaluate its commission
system. They have a team of waiters responsible for serving customers and ensuring a
high-quality dining experience. Each waiter is assigned monthly sales goals, and their
performance is evaluated based on their ability to meet or exceed these goals. The
restaurant offers different commission rates based on the service rating of each waiter.
Answer the following questions.

Table 1
Waiters Salary Sales Goal Service Rating
Shannon R10,000.00 R25,000.00 R24,000.00 
Jade R8,500.00 R15,000.00 R14,000.00 
Elton R6,500.00 R9,756.00 R10,000.00 
James R11,000.00 R31,025.00 R30,000.00 
Alex R9,000.00 R15,648.00 R12,567.00 
Donny R9,500.00 R6,475.00 R11,253.00 
David R5,000.00 R3,596.00 R8,967.00 
Jason R7,500.00 R10,457.00 R9,645.00 

Table 2
Service Rating Commision
 20%
 15%
 10%

Required:

2.1 Using the provided sales data table and commission category table, add the
Commission Percentage for each waiter to the sales data table based on their
service rating (Use VLOOKUP function). (4 marks)

2.2 Calculate the Sales Commission from each waiter, and where the sales are below
the sales goal, add the message " Zero commission." (Use IF function) (4 marks)

2.3 Calculate the total salary paid to all the waiters who earned a commission.
(2 marks)

3 HCLT108-1-Jan-Jun2025-FA2-LS-V.3-28012025
Question 3 [21 marks]

The Joe's Pizza Ledge manager has been thoroughly impressed with your exceptional
analytical skills and has asked you to take on a new task: summarising all the store's
data to provide a clear and comprehensive view of its performance across its various
operating regions. Recognising the value of data-driven insights, the manager visualizes
this information through pivot charts and graphs, allowing an in-depth understanding of
the store's overall performance. After exploring the capabilities of pivot tables for data
summarisation and analysis, you confidently agree to take on the challenge and prepare
a detailed presentation.

Required:

3.1 Create a pivot table to show the sales revenue of each store location. (3 marks)

3.2 Create a pivot table to show the average profit per region. (3 marks)

3.3 Create a pivot table comparing sales revenue and expenses across regions.
(3 marks)

3.4 Create a pivot table showing expenses between regions and their different store
locations. (3 marks)

3.5 Create a 3-D Stacked Bar chart from the pivot table created in Q3.1 (3 marks)

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

3.7 Create a Clustered Bar chart from the pivot table created in Q3.4. (3 marks)

4 HCLT108-1-Jan-Jun2025-FA2-LS-V.3-28012025
Question 4 [6 marks]

You recently opened a café during an economic downturn, and you've been struggling to
attract enough customers to grow your business. To give your café a competitive edge, you
decided to take out a loan and introduce a new line of specialty coffee bundles designed to
attract more customers. However, after covering your monthly loan repayments, you find
that you have no remaining funds from your daily sales to reinvest into the business.

After analysing your finances, you determine that you need to generate R15,000 in sales
each day to start saving money for future investments. Currently, the cost of producing
each coffee bundle is R50, and you're selling 80 bundles daily.

4.1 Using Goal Seek, determine how many coffee bundles you must sell daily to
achieve daily sales of R15,000. (3 marks)

You want to determine the profitability of your business after increasing the coffee bundle
price to R55. You are currently selling 120 coffee bundles a day. The cost of making
a combo is R25.

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

5 HCLT108-1-Jan-Jun2025-FA2-LS-V.3-28012025
The below only applies to students enrolled in the Bachelor of Accounting program.

Aligns to SAICA competencies:

W7 Accounting information systems
b) Use presentation software in an accounting/ business context.
Use spreadsheet software in an accounting/ business context (e.g.,
working papers; knowledge of effective utilisation (including shortcut
c) keys, macros, pivot tables, and other advanced excel functions).
d) Use accounting software to create and view financial transactions.

W3 Data Analytics
Identify the practical challenges of data analytics (e.g., data volume
a) and quality, privacy, regulatory, and ethical issues).
Use processes of inspection, extraction, transformation, loading
(ETL), and modelling data (as discussed below) (see (i) to (iv)) to
discover information able to enhance problem-solving and decision-
b) making.
Use data analytic software tools to analyse data (e.g., ACL, IDEA,
c) advanced Excel™ functions)
Data inspection:
(i) Describe the elements of a specific business process by
documenting the workflow
(ii) Define the problem to be solved and determine clear measurement
priorities
(iii) Identify data sources appropriate to solving the defined problem
(iv)Evaluate the input controls responsible for ensuring that the data
captured and used is valid, accurate, and complete
(v)Explain the nature, distribution, and limitations of the identified data
f) and the population to be tested.
Data transformation:
(i) Perform data normalisation (clean-up) (e.g., standardisation of
fields and records, removal of duplicates, verification of anomalies,
sorting of data)
h) (ii) Identify anomalies in the data by applying professional skepticism
Data modelling
(i) Examine the key fields using descriptive statistics to determine their
i) characteristics and statistical parameters.

6 HCLT108-1-Jan-Jun2025-FA2-LS-V.3-28012025