EXCEL CLASS TEST 3 (20th August 2021) _ BCA1 (BCP150S & X_FT-PT-FW-ECP)

Instructions:
• Download and Open the workbook/Data File called “Income” from Blackboard (Excel Class Test 3).
• Save your Class Test as: ECT3_Your Surname_Your Group_Your Student Number.
• Refer to Annexure A & B for Possible Solution (s) (Page 3 & 4) and Complete the following:

SHEET 1:
1. Rename Sheet1 to 1st Quarter. (1 Mark)
2. Complete the Months: Feb (C3) and Mar (D3), By Utilizing the AutoFill Function. (2 Marks)
3. Key in the Following: Highest in Cell F3; Lowest in Cell G3 and; Average in Cell H3. (2 Marks)
4. Format the Heading as follows:
4.1 Merge and Centre the heading over Columns A through H. (2 Marks)
4.2 Font: Century Gothic; Size 12 and Bold. (2 Marks)
4.3 Fill in with Colour: Green and apply a Thick Box Boarder. (2 Marks)
5. Center, and Italics the Sub-Heading in Row 3 (Cell A3 to Cell H3). (2 Marks)
6. Insert a new row above (Pieter) and; Key in the following below: (2 Marks)

7. Use Format Painter Function to copy Cell A8 and; apply the formatting to Cell A15 and Cell A17. (2 Marks)
8. Delete Column E and; Delete Row 2 and Row 8. (2 Marks)
9. Apply All Borders from Cell A to Cell G13. (2 Marks)
10. Change the numeric format of all cells showing figures to Currency and; Replace
The Symbol to “R English (South Africa)”. (2 Marks)
11. Apply the AutoSum Function to Add the following:
11.1.1 Total Income (Cell B7 to D7) (1 Mark)
11.1.2 Total Expenses (Cell B9 to D13) (1 Mark)
12. Create or Utilize a Formula to Determine the Profit For Jan. (Cell B15). (2 Marks)
Apply the AutoFill Function to determine the profit for Feb. (Cell B16) and; Mar. (B17) (1 Mark)
13. Insert a “Top and Double Bottom Border” to Cell B15 to B17. (1 Mark)
14. Utilize a Function to Determine Mary’s Highest Sales Achieved for the 3 Months in Cell E3. (2 Marks)
Apply the AutoFill Function to the other 3 Salesman’s Highest Sales Achieved (Cell E4 to Cell E6). (1 Mark)
15. Utilize a Function to Determine Mary’s Lowest Sales Achieved for the 3 Months in Cell F3. (2 Marks)
Apply the AutoFill Function to the other 3 Salesman’s Lowest Sales Achieved (Cell F4 to Cell F6). . (1 Mark)
16. Utilize a Function to Determine Mary’s Average Sales Achieved for the 3 Months in Cell G3. (2 Marks)
Apply the AutoFill Function to the other 3 Salesman’s Average Sales Achieved (Cell G4 to Cell G6). (1 Mark)
17. AutoFit all the Columns. (1 Mark)
18. Insert a Header: on the left side of the header, type your Surname-Student Number. In the centre
Of the header, insert your Excel Class Test and; on the right side of the header, insert the Current Date. (3 Marks)
19. Centre the Margins of your test to “vertically and horizontally”. SAVE SHEET 1 (2 Marks)
SHEET 1 TOTAL: 44 MARKS
SHEET 2:
1. Rename Sheet2 to Formulas and Apply Tab Standard Colour: Green (2 Marks)
2. In Cell C:3 (Salesperson Name & Surname Column), use a Formula to “Combine” the Salesperson Name (A:3)
and Salesperson Surname (B:3). Make sure that there is a space between the Name and Surname. (3 Marks)
AutoFill the Formula Down (C:3 to C:6) (1 Mark)
3. In Cell i:3 (Award/No Award Column), use an “IF” Formula to Determine which Salesperson would receive an
“Award” based on Cell D:3 (Most Products Sold Column) if that salesperson sold More Than 65 Products.
Salesperson’s that sold Less Than 65 Products, would Receive “No Award”. (3 Marks)
AutoFill the Formula Down (i:3 to i:6) (1 Mark)
4. Change Cell J:3 to J:6 to Percentage (% of Totals Column). In Cell J:3 (% of Totals Column), Calculate the
Percent of the Value in Cell H:3 From H:7, using a Special Formula. (3 Marks)
AutoFill the Formula Down (J:3 to J:6) (1 Mark)
5. In Cell B:9 (Highlighted in Green), Use a Count Formula to Determine how many Salesperson’s Sold Products
Less Than 74, based on Cell D:3 to D:6 (Most Products Sold Column) (3 Marks)
6. In Cell J:3 to J:6 (% of Totals Column), utilize the “Styles-Conditional Formatting” Function to Highlight the
Percentages Between 20% and 25%. Format the Cells further by using the Colours: Green Fill
with Dark Green Text. SAVE SHEET 2 (3 Marks)
7. Upload and Submit on Blackboard: Class Test / Excel Class Test 3. (1 Mark)

SHEET 2 TOTAL: 21 MARKS

GRAND TOTAL OF EXCEL CLASSTEST: 65 MARKS