COMPUTER APPLICATION TECHNOLOGY PAPER 1
GRADE 12
NATIONAL SENIOR CERTIFICATE EXAMINATIONS
MAY/JUNE 2021

INSTRUCTIONS AND INFORMATION

1. Owing to the nature of this practical examination, it is important to note that, even if you complete the examination early, you will NOT be permitted to leave the examination room until all the administrative functions associated with the examination have been finalised. During the examination, the normal rules regarding leaving the examination room apply.
2. If you are working on the network, or the data files have been preloaded, you must follow the instructions provided by the invigilator/educator. Alternatively, the invigilator will give you a CD/DVD/flash drive containing all the files needed for the examination. If a CD/DVD/flash drive has been issued to you, you must write your examination number and centre number on the CD/DVD/flash drive.
3. At the end of the examination, you must hand in the CD/DVD/flash drive given to you by the invigilator with ALL your answer files saved onto the CD/DVD/flash drive, OR you should make sure that ALL the answer files are saved on the network/computer as explained to you by the invigilator/educator.
4. Make absolutely sure that all files can be read. Do NOT save unnecessary files/folders and do NOT hand in duplicate answer files/folders. Do NOT delete any original files that you did not work on.
5. The information sheet that has been provided with the question paper MUST BE COMPLETED AFTER THE THREE-HOUR EXAMINATION SESSION. Hand it to the invigilator at the end of the examination.
6. A copy of the master files will be available from the invigilator. Should there be any problems with a file, you may request another copy from the invigilator.
7. This question paper consists of SEVEN questions. Answer ALL the questions.
8. Read through each question before answering or solving the problem. Do NOT do more than is required by the question.
9. Read the question carefully as accuracy will be taken into account.
10. Ensure that you save each document using the file name given in the question paper. Save your work at regular intervals as a precaution against possible power failures.
11. You may NOT use any resource material.
12. Ensure that the regional settings are set to South Africa and that date and time settings, number settings and currency settings are correctly set.
13. In all questions involving word processing, you should set the language to English (South Africa). The paper size is assumed to be A4 Portrait, unless instructed otherwise. Use centimetres as the unit of measurement.
14. Formulae and/or functions must be used for ALL calculations in questions involving spreadsheets. Use absolute cell references only where necessary to ensure that formulae are correct when you copy them to other cells in a spreadsheet.
    NOTE: All formulae and/or functions should be inserted in such a manner that the correct results will still be obtained even if changes are made to the existing data.
15. You may NOT use a word processing program such as Word to answer the HTML question.
16. The examination folder/CD/DVD/flash drive that you receive with this question paper contains the files listed below. Ensure that you have all the files before you begin with this examination.
    

    1Review Word processing file 2History Word processing file 3Countries Spreadsheet 3Open Compressed folder 4Donate Spreadsheet 5Rescue Image 5Support Database 6_1Picture Image 6_1Sierra HTML file 6_2Progress Image 6_2Wash HTML file 7Distribute Word processing file 7Prospects Spreadsheet 7Reorder Spreadsheet

    
    
    

    SCENARIO Médecins Sans Frontières (MSF), translated as Doctors Without Borders, is an independent international organisation that provides medical and humanitarian assistance to people who face extreme hardships.

QUESTION 1: WORD PROCESSING

Open the 1Review word processing document, which contains information about the operations of the Médecins Sans Frontières (MSF) organization, and enter your examination number in the header or the footer.

1.1 Change the heading 'Year in Review' as follows:
1.1.1 Modify the text to a WordArt object with a:

• Green fill
• 'Wave 2' transformation (3)

1.1.2 Modify the WordArt to appear as in the example below:

(2)
1.2 Change the endnote that appears after the text 'Directors of Operations' to appear at the bottom of the page on which the reference appears. (1)

1.3 Use the ruler settings to display the text as shown below. (3)

1.4 Place the cursor on the text 'Here' at the bottom of the page. Create a new style called 'Display', formatted to expanded character spacing. (3)

1.5 Replace all occurrences of the word 'care' with the ambulance symbol, , (Webdings character code 104). Do NOT change parts of words that contain the text 'care'. (4)

1.6 Use a word processing feature to replace the word 'intercepted' in the paragraph starting with 'In the Mediterranean, …' with a synonym suggested by the computer. (1)

1.7 Find the text starting with 'PEOPLE OUT OF …' and ending with '… missing to date.' and edit it as follows:

• Display the text in TWO columns with a vertical line between the columns.
• Ensure that the heading 'MEDICAL ACHIEVEMENTS …' will always appear at the top of the second column. (3)

1.8 Modify the text and the picture found on the last page of the document to display as shown below.

NOTE:

• The display of the clipboard image
• The Tab settings
• The Form Controls
• The user must be able to select from the options:
  • Doctor
  • Nurse
  • Surgeon
• The line surrounding the text and image (10)

Save and close the 1Review document.
[30]

QUESTION 2: WORD PROCESSING
Open the 2History word processing document which contains the interesting history of Doctors Without Borders. Enter your examination number in the header or the footer.

2.1 Find the text 'Doctors Without Borders' below the heading 'Introduction' and create a hyperlink on the text to the 'Legends' bookmark. (2)
2.2 Create a new source to reference the report titled 'Year in Review', which was written by the corporate authors of MSF. (3)
2.3 Find the table below the heading '1970s - Events that took place' and do the following:

• Use a table feature to separate the last three rows from the main table, so that the 1980s data appears in a separate table below the 1970s table.
• Insert a new row above the first row in the new table.
• A sponsor will donate €15,00 for every patient treated by MSF. Modify the formula in the table (last row, second column) to calculate the total funding that the organisation will receive for the 1980s.
• Format the total funding amount as currency. Change the currency symbol to € (euro), shortcut key: Alt+Ctrl+E.
  (6)

2.4 Modify the automatic page numbering in the whole document as follows:

• Page numbering must NOT appear on the first page.
• Change the page numbering on the second page to start at 1.
  NOTE: The page numbering will not appear on the last page. (3)

2.5 The table of figures does not show the 10 images found in the document.

Make the necessary changes to solve the problem. (4)

2.6 Insert any page border of your choice to only the last page. (2)

Save and close the 2History document.
[20]

QUESTION 3: SPREADSHEET
NOTE:

• Use formulae and/or functions for ALL calculations in the spreadsheet.
• Use absolute cell references ONLY where necessary to ensure that formulae are correct when you copy them to other cells in the same column (copy down).
• Insert formulae and/or functions in such a manner that the correct results will still be obtained even if changes are made to the existing data.
• Should you need to use building blocks, use the allocated space in the spreadsheet.

Open the 3Countries spreadsheet, which contains information about the services provided to people in the different countries, and work in the Services worksheet.

3.1 Change the border around cell C2 to a thick red border. (2)
3.2 Insert a function in cell P9 to display the average number of outpatients (column C) treated. Format the cell to display the result with NO decimals. (3)
3.3 The services offered by MSF are listed from column C to column L. If no value appears in a cell, then that service is not available in that country.

Insert a function in cell P11 to determine how many different kinds of services are available for use by the people in the DRC (row 20). (2)

3.4 Insert a function/formula in cell P13 to determine how many countries were first helped in the 1980s (column B). (6)
3.5 Apply data validation to cell range M5:M75 so that only the values: Disastrous, Critical or Moderate (cell range O5:O7) can be entered. (3)
3.6 The treatment of DR-TB is indicated in column H.

Insert a formula in cell P15 to calculate which percentage of people with DR-TB were treated by MSF in India (row 32). (3)

Do NOT close the 3Countries spreadsheet.
3.7 Open the password-protected folder 3Open with the password: 2021.

How many spreadsheets are found in the 3Open folder? Insert your answer in cell P17. (1)

3.8 Find the chart/graph at the bottom of the Services worksheet. The chart displays the total number of people (row 76) assisted by some of the services provided.

Carry out the following instructions to change the chart/graph to display as shown below:

• Display only the services indicated in the example.
• Change the data labels to display the names of the services and the associated percentages.
• Set the doughnut explosion to 5%.
• Move the chart to the worksheet called ServiceChart.
  
  (5)

Save and close the 3Countries spreadsheet.
[25]

QUESTION 4: SPREADSHEET
NOTE:

• Use formulae and/or functions for ALL calculations in the spreadsheet.
• Use absolute cell references ONLY where necessary to ensure that formulae are correct when you copy them to other cells in the same column (copy down).
• Insert formulae and/or functions in such a manner that the correct results will still be obtained even if changes are made to the existing data.
• Should you need to use building blocks, use the allocated space in the spreadsheet.

The details of sponsors and their donations are stored in a spreadsheet. As sponsors may make a single or monthly donation, the card numbers from which payments are made, is recorded.

Open the 4Donate spreadsheet and work in the Details worksheet.

4.1 Insert a function in cell F3 to determine the value that appears most often in column D. (2)
4.2 Several sponsors donated the same highest amount (column D).

Insert a function/formula in cell F4 to determine the total value of the highest amount donated. (4)

4.3 Change the SUMIFS function in cell F5 to determine the total amount of the single donations (column C) that were made in 2019 (column E). (4)

4.4 MSF will use the data in column C to determine which type of message should be sent to the sponsors.

NOTE: A bank card has expired if the expiry date (column G) is before the date in cell H4.

Use the donation type (column C) and the expiry date (column G).

Insert a function/formula in cell H8 to display the following:

• The word 'Expired' if the bank card of a monthly sponsor has expired OR
• The word 'Remind' if the bank card of a monthly sponsor is still valid and a transaction will take place OR
• A '#' character if the card was used for a single donation as he/she will not receive a message

Ensure that the correct results will display if the function were to be copied to the rest of the column. (7)

Work in the Code worksheet.

4.5  MSF allocates a code to each country to keep track of how many donations come from each country.

The code is created by moving the first two characters of the name of the country to appear as the last two characters of the name.
Example: China will be displayed as 'inaCh'.

Insert a combination of functions in cell C3 to display the code for Argentina using this coding system. (8)

Save and close the 4Donate spreadsheet.
[25]

QUESTION 5: DATABASE

A database containing the details of individuals that volunteer their services was created. The database also contains information about the countries in which these volunteers work.

Open the 5Support database.

5.1 Open the tbl5_1 table in Design View and change it as follows:
5.1.1 Insert a new field, RegionId, as follows:

• Place the new field RegionId as the first field.
• Set a primary key on the new field.
• Set the data type of this field to assign an automatic number to the field.
• Change the 'New Values' property of this field to assign random numbers to the field. (5)

5.1.2 Study the data in the RegionCode field in the Datasheet View to determine the format of the data in the field.

In the Design View, change the RegionCode field as follows:

• Set the field size to match the data.
• Create an input mask to ensure that the user must insert a code in the required format. (5)

5.1.3 Change the StaffAvailable field to automatically display the value 10 in all new records. (1)

Save and close the tbl5_1 table.

5.2 Create a form called frm5_2 based on the tbIVolunteers table to display in Form View as shown below.

• Insert an appropriate control to the form header and enter your examination number in this control.
• Change a property setting of the control containing your examination number, so that the examination number will not display in Form View. (The examination number must remain visible in the Design View.)
• Change the CountryToVolunteer field text box to a combo box linked to the tblCountry table.

Save and close the frm5_2 form. (5)

5.3 Open the qry5_3 query based on the tblVolunteers table in Design View and do the following:

• Display only the records where the volunteers are return volunteers and are willing to help for 20 weeks or more.
• Display the records alphabetically according to the surnames.

Save and close the qry5_3 query. (4)

5.4 Open the qry5_4 query based on the tblVolunteers table in Design View and do the following:

• Display only the Name, Surname, CountryToVolunteer and MedicalService fields.
• Display only the records of volunteers who volunteer for any treatment, excluding HIV/Aids, AND only volunteer for Zimbabwe or Angola.

Save and close the qry5_4 query. (6)
Computer Applications Technology/P1 14 DBE/2021
SC/NSC
Copyright reserved Please turn over
5.5
Open the qry5_5 query based on the tblVolunteers table in Design View.
Modify the query so that it displays 17 records as shown below:

Save and close the qry5_5 query. (5)

5.6 Open the rpt5_6 report based on the tblVolunteers table in Design View and edit as follows:

• Insert the 5Rescue image found in your examination folder, in the report header. Resize the image box to 1.6 cm high and 2 cm wide. The whole image must fill the image box.
• Add another grouping to the report on the ReturnVolunteer field.
• Insert a function in the MedicalService group footer to determine the number of volunteers per medical service.
• Insert a suitable label for the function.

Save and close the rpt5_6 report. (9)

Save and close the 5Support database. [40]

QUESTION 6: WEB DESIGN (HTML)
You may NOT use a word processing program such as Word to answer the HTML question.

MSF does humanitarian work in Sierra Leone, Africa.

NOTE:

• An HTML tag sheet is attached for reference.
• All files needed to complete this question are found in the examination folder.

ONE mark will be allocated for the correct closing of all tags and the correct nesting for both the web pages. (1)

6.1 Open the incomplete 6_1Sierra web page in a web browser and also in a text/HTML editor (NOT a word processing program such as Word).
NOTE: Question numbers appear as comments in the coding to guide you where to insert the answer(s). DO NOT delete these comments.


NOTE: Use the example on the previous page as a guide for doing this question.
6.1.1 Change the background colour of the web page to 'linen'. (1)
6.1.2 Display only the word 'Ebola' found in the first paragraph below the heading '2018 Status' in a red colour. (1)
6.1.3 Create a link on the heading '2020 Status' to link to a target location that has been created in the document. (3)
6.1.4 Format the table as shown in the example on the previous page:

• Insert a last row with the text '2020 Status Pending!'
• Centre the text in the last row. (4)

Save and close the 6_1Sierra document.

6.2 Open the incomplete 6_2Wash web page in a web browser and also in a text/HTML editor (NOT a word processing program such as Word).

Your final web page should appear as shown in the example below.

NOTE:

• The line below the heading is set to 50% of the width of the screen.
• The image 6_2Progress can be found in your examination folder.
• The height of the image is 500.
• The image has a thick border.
  (10)

Save and close the 6_2Wash document. [20]

QUESTION 7: GENERAL
Various lists and statistics are required to make informed decisions.

7.1 The managing director requested a directory table that contains the names of some prestige sponsors.

Open the 7Distribute word processing document and enter your examination number in the footer.

7.1.1 Create a mail merge as follows:

NOTE: Select the Directory option when you start the mail merge.

• Link the 7Prospects spreadsheet as a data source for the 7Distribute mail merge document.
• Edit the data source recipient list to include ONLY the data of those members who have a 'Yes' Prestige_Aansien status AND contributed an amount greater than or equal to 800.
• Enter the merged field Surname_Van in the first cell of the table.
• Enter the merged field Name_Naam in the second cell of the table.

Save the 7Distribute document, but do NOT close it. (5)

7.1.2 Complete the merge and save the document as 7Directory. Save and close all documents. (3)

7.2 Open the 7Reorder spreadsheet.

Work in the Subtotal worksheet.

The SUBTOTAL feature displays the total amount donated per category.

Change the SUBTOTAL feature to determine the number of countries in each category. (3)

7.3 Continue working in the 7Reorder spreadsheet.

7.3.1 Work in the Stock worksheet.

Use an appropriate lookup function in cell D4 to determine the minimum number of items that should be in stock using the data in the Order worksheet.

Ensure that the correct results will display if the function were to be copied to the other cells. (4)

7.3.2 Work in Order worksheet.

Use an IF function in cell C5 to display the quantity of the stock item that must be reordered, e.g. 74 764 Drips.
NOTE:

• The reorder quantity is the number of items that must be ordered if the number of items in stock (Stock worksheet) is less than the minimum of items required.
• Display the result of the function with the quantity required, followed by a space, followed by the name of the item.(5)

Save and close the 7Reorder spreasheet.
[20]
TOTAL: 180

INPUT MASK CHARACTER SHEET

COMPUTER APPLICATIONS TECHNOLOGY P1 – 2021
INFORMATION SHEET (to be completed by the candidate AFTER the 3-hour session)
CENTRE NUMBER __________________________________________________________
EXAMINATION NUMBER _____________________________________________________
WORK STATION NUMBER ____________________________________________________

FOLDER NAME ______________________________________________________________
File names have been entered for each answer. Tick if saved and/or attempted.

Comment (for office/marker use only)
__________________________________________________________________________
__________________________________________________________________________