NATIONAL SENIOR CERTIFICATE EXAMINATION

NOVEMBER 2021

COMPUTER APPLICATIONS TECHNOLOGY: PAPER I

Time: 3 hours 180 marks

PLEASE READ THE FOLLOWING INSTRUCTIONS CAREFULLY

1. This question paper consists of 14 pages and an insert (HTML Tag List and Input
Mask Character Sheet) of 2 pages (i–ii). Please check that your question paper is
complete.
2. The exam consists of 5 sections and 7 questions. All questions in all sections must
be answered.
3. You must use a text editor for the HTML section. Acceptable applications are
Notepad, Notepad++ or WordPad.
4. Please read the entire question before attempting a solution.
5. Do not do more than is required by the question.
6. Use functions and/or formulas to calculate answers, unless otherwise instructed.
7. Take note of the mark allocation to ascertain the complexity of the solution required
and the amount of time you should spend on each question.
8. Save your work regularly.
9. If you are unable to get a formula/function to work correctly: open the
Student_Screenshots document; indicate the question number; paste a
screenshot showing the issue and provide a brief explanation to obtain part marks;
you may then leave it out of the spreadsheet or database solution.
10. Unless other instructions are given, all word-processing documents should be set
up as follows:
• The default for measuring units – centimetres (cm)
• Language – English (South Africa)
• Page size – A4
• Margins – 2,54 cm
11. You may use the offline help function, if available, in each application.
12. Figures in the question paper have a border. When a figure is provided as part of a
question, you are not expected to add the border unless specifically instructed to do so.
13. Before you begin, rename your data folder to your examination number.
14. The folder named Backup_Data_Files contains copies of all the data files. Use this
folder to get a clean copy of an original file if you need to re-do a particular question.
DO NOT save your work inside this folder.
IEB Copyright © 2021 PLEASE TURN OVER
NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 2 of 14

The contents of your exam Data Files folder and sub-folders are as follows:

Data Files Question1 Question2

Question3 Question4 Question5

Question7
Question6 Question7 (images subfolder)

Backup_Data_Files

Make sure to check each folder and ensure that every file/document/folder is accessible.

Contextual Scenario

2Mars is a project inviting people, who want to be among the first humans to live on Mars,
to take part in a special preparation and training programme. An advert was placed for
applicants to apply to be part of the programme, called Mars2030.

The response was overwhelming. In the first week, 500 people signed up. The
programme involves developing applicants' knowledge of Mars, ascertaining their
physical eligibility and ensuring that all their important details have been correctly
captured for processing.

IEB Copyright © 2021

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 3 of 14

SECTION A FILE AND FOLDER MANAGEMENT

QUESTION 1

Open and work only in the folder named Question1.

1.1 Enable file name extensions, if not already enabled.

1.1.1 Take a screenshot showing the File Explorer ribbon, indicating that
file name extensions have been enabled. (1)

1.1.2 Insert the screenshot in the document Q1_Answers. (1)

1.1.3 Crop the image, showing only the relevant information in the ribbon. (1)

1.2 Sort all items in the Question1 folder according to file size, in ascending
order. Insert a screenshot in the Q1_Answers document showing all
column headings and all sorted files. (2)

1.3 Who is the album artist of the audio file NeverBeenToMars.mp3? Type
your answer in the Q1_Answers document. (1)

1.4 What is the file size of the Q1MIP.docx file, in MB? Type your answer in the
Q1_Answers document. (1)

1.5 In the Q1_Answers document, write or paste the full computer path to the
Question1 folder. You may then close the Q1_Answers document. (1)

1.6 Create a subfolder called Images in the Question1 folder. (1)

1.7 Move all the image files into the newly created Images subfolder. (2)

1.8 Extract/Unzip the file Q1Articles.zip into its own default folder. Ensure the
unzipped folder is in the Question1 folder. (1)

1.9 Delete the Q1Articles.zip file after extracting the files from it. (1)

1.10 Zip/Compress the file Q1MIP.docx to Q1MIP.zip. (1)

1.11 Create a shortcut, appearing in the Question1 folder, to the Q1_Answers

file. (1)

1.12 Rename the shortcut (created in Question 1.11) to Answers_Doc. (1)

1.13 Ensure that the PowerPoint file is set to Read-only so that no changes can
be saved to the file. (1)

17 marks

IEB Copyright © 2021 PLEASE TURN OVER

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 4 of 14

SECTION B WORD PROCESSING

QUESTION 2

Open and work only in the folder named Question2.

Open the document called Q2_Colonization of Mars.docx.

Note: In this document, page numbers and/or the location of paragraphs given in
the questions could change due to formatting or editing of content in other parts of
the document. For example, a paragraph at the bottom of page 3 could shift to the
top of page 4.

Page 1

2.1 Set the page margins of the document to Normal. (1)

2.2 Apply the style MainArticleHeader to the text 'Living on Mars'. (1)

2.3 Format the image with the style Rounded Diagonal Corner, White. See the
figure below with correct styling applied. (1)

Page 4

2.4 Create a dropped-cap effect on the letter 'T' in the first paragraph after the
heading. Ensure that it drops only two lines. (2)

2.5 Under the image insert a caption titled 'Figure 1 Colonisers on Mars' (no
quotation marks). (1)

2.6 Create a new style named MySubTitle, based on the Normal style, with font
type Times New Roman, size 14, bold and underlined. (2)

2.7 Change the style of all text formatted with the Temp style (highlighted in
yellow) to the MySubTitle style. (1)

IEB Copyright © 2021

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 5 of 14

Page 5 (depending on previous questions' formatting)

2.8 Insert a bookmark called 'DFE' in front of the heading 'Differences between
Mars and Earth' (no quotation marks). (2)

2.9 Locate the text under the heading 'Relative similarity to Earth' (highlighted
in green).

2.9.1 Convert this text to a hyperlink linking to the 'Differences between

Mars and Earth' heading. (1)

2.9.2 Create a screen tip that reads 'What is different about Mars?' when
the mouse hovers over the hyperlink. (1)

2.10 Place a footnote at the end of the first bullet point (highlighted in blue). The
footnote text can be obtained from the text file Sources.txt in the
Question1 folder. (2)

Page 7 (depending on previous questions' formatting)

Locate the table on the page.

2.11 Place a table caption that reads 'Table 1 Atmospheric pressure comparison'
above the table (no quotation marks). (2)

Page 8 (depending on previous questions' formatting)

2.12 Ensure the heading 'Transportation' will always appear at the top of a new
page. You may not use a manual page break, spaces or the enter key. (2)

Page 11 (depending on previous questions' formatting)

In the 'Equipment needed for colonization' paragraphs:

2.13 Convert the text after the first paragraph (starting with 'Basic' and ending
with 'Mars aircraft') to a bulleted list using the image rocketship.png as the
bullet instead of a symbol. (2)

2.14 Adjust the tab alignment settings of the bulleted list as follows:

2.14.1 First line indent at 1 cm. (1)

2.14.2 Hanging indent at 2 cm. (1)

Example of indents using the ruler.

IEB Copyright © 2021 PLEASE TURN OVER

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 6 of 14

Page 13 (depending on previous questions' formatting)

2.15 Insert the image Rover.jpg at the beginning of the paragraph starting with
'The path to a human colony' (after the heading 'Robotic precursors'). (1)

2.16 Format the image Rover.jpg as follows:

2.16.1 Width: 5 cm (1)

2.16.2 Height: 4 cm (1)

2.16.3 Text wrap set to Square. (1)

Page 2

2.17 Create an automatic table of contents using only Heading 1 and MySubTitle
styles as level 1 and level 2, respectively. (2)

Page 3

2.18 Update the Table of Tables section to automatically show the table caption
of the table referred to in Question 2.11. (1)

Last page

2.19 Convert the last line of text, starting with 'Do you' to WordArt. Use any form
and style. (1)

2.20 Number all pages, except the first page, automatically. Odd numbers on the
left, even numbers on the right. (4)

2.21 Protect the document by marking it as final. (1)

Save and close the document.

[36]

IEB Copyright © 2021

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 7 of 14

QUESTION 3

Open and work only in the folder named Question3.

Open the document called Q3_Mars Information Pack.docx.

Page 1

3.1 Place a page border around page 1 only. (2)

Page 2

3.2 Change the orientation of page 2 to landscape. All other pages must remain
portrait. (3)

Page 3

3.3 Format the heading 'Some Mars Facts' as follows:

3.3.1 Centre the heading using a centre tab at 8 cm. (2)

3.3.2 Place a double-lined paragraph border around the heading. (2)

3.4 Insert the contents of the MarsFacts.txt file just below the heading 'Some
Mars Facts' and convert it to a table of three columns and eleven rows. (3)

3.5 Correct the incorrectly spelt word in the first paragraph under the heading
'The Martian Moons'. (1)

3.6 Format the two paragraphs under the heading 'The Martian Moons' as
follows:

3.6.1 Place the text in two columns without the use of tables. (1)

3.6.2 Ensure that the sentence beginning with 'And people may' begins
at the top of the second column. (1)

3.7 Shade the paragraph under 'How the Mars Moons Got their Names' any
colour. (1)

Page 4

3.8 Locate the heading 'How much do you know about the Red Planet?'.

Ensure that the words 'solar system', located in the first point, will always be
together and not separated over two lines. (1)

Save and close the document.

[17]

53 marks

IEB Copyright © 2021 PLEASE TURN OVER

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 8 of 14

SECTION C SPREADSHEETS

QUESTION 4

Open and work only in the folder named Question4.

Open the file Q4_Applicants.xlsx.

Worksheet: Countries

4.1 Use a function in B1 to display only the first two letters of the country code
from the text in A1. Copy the function down for all cells.

For example, AF/AFG in A1 becomes AF in B1.

(3)

Worksheet: Applicants

4.2 Adjust the view of the worksheet so that Row 1 does not scroll when
scrolling in the worksheet occurs. (1)

4.3 Each applicant requires a special access code to log into their Personalised
Online Training Portfolio System. The code is made up as a combination of
their id and their LastName, e.g.

Correctly generate the access codes in Column B for each applicant. (3)

4.4 Use a function in Column I to display the full country name for each
applicant, based on the country code in Column H and the information in
the Countries worksheet. (4)

4.5 Correct the issue causing the figures in Column M to display incorrectly. (1)

4.6 Applicants are encouraged to pay a deposit of at least 50% of the R100 000
programme fee in order to receive a 10% discount on the deposit. Calculate
the discount amount for the first applicant in N2. Copy the calculation down
for all applicants. (3)

4.7 Calculate in Column O what each applicant still owes. The programme fee
is in Q1. Format the column as Currency. (4)

4.8 For randomisation of groups, each applicant is given a random score

between 1 and 10. Using a function in Column P, generate a random
number between 1 and 10 for each applicant. (2)

IEB Copyright © 2021

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 9 of 14

4.9 Use Excel functions to calculate the following:

4.9.1 In Q503: The total amount of fees owed. (2)

4.9.2 In Q504: The number of applicants that have not made any payment
yet. (2)

4.9.3 In Q505: The total fees owing that do not qualify for a discount. (4)

Worksheet: Fitness

4.10 Centre the text in A1:G1 horizontally and vertically. (1)

4.11 Every applicant's body mass index (BMI) must be calculated by dividing his
or her weight by the square of his or her height. Using an appropriate
formula, calculate the BMI for each applicant in Column F. (2)

4.12 In Column G, use an appropriate formula/function to display the health

condition of each applicant based on their BMI. Use the information from
I1:K3 to assist you. (4)

4.13 Use an appropriate formula/function to show the number of applicants for

each health condition, i.e. Unhealthy; Healthy; Overweight in J5:J7. (3)

4.14 Use conditional formatting to highlight any cells in Column G that contain
the word 'Overweight'. You may choose your own formatting style(s). (2)
5.
Save and close the workbook.
[41]

IEB Copyright © 2021 PLEASE TURN OVER

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 10 of 14

QUESTION 5

Open and work only in the folder named Question5.

Open the file Q5_ApplicantInterests.xlsx.

Worksheet: Ages

5.1 Determine the present age of each applicant in Column E without decimals. (2)

Worksheet: Mining

5.2 Filter the data to display only those applicants who have an interest in
'mining'. (2)

Worksheet: CountryInterests

5.3 Create a pivot table showing the various interest categories and the number
of applicants from each country. See Figure 1 below as the example.

Figure 1: Example of Pivot Table data (4)

Save and close the workbook.

IEB Copyright © 2021

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 11 of 14

Open the file Q5_Interviews.xlsm. Enable macros – Click 'Enable Content' – if

prompted to do so.

5.4 Create a macro that will run when the keyboard shortcut CTRL + I (capital
letter I, not number 1) is pressed. The macro must write the word 'Interview'
– no quotation marks – in any selected cell. Name the macro Interview. (3)

5.5 Create a button in F1 with the word 'Interview' on it. Assign the Interview
macro to the button so that it runs the macro when the button is clicked. (2)

Save and close the workbook.

[13]

54 marks

IEB Copyright © 2021 PLEASE TURN OVER

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 12 of 14

SECTION D DATABASE

QUESTION 6

2Mars has created a database to keep track of all the applicants. Follow the
instructions below to create the various queries, forms and reports needed by the
company to manage their data.

Open and work only in the folder named Question6.

Open the Microsoft Access Database called Q6_MarsEntries.accdb.

Tables: tblApplicants
6.
6.1 The table is missing a primary key. Select and enable the most appropriate
field to be a primary key. (2)

6.2 Import the applicants from the ExtraApplicants.xlsx file into the
tblApplicants table. (2)

6.3 The field AccessCode must follow a specific format. Use the example format
given below, showing letters, numbers and case. Ensure that data entered
into the AccessCode field follows this format exactly.

Example format: Abcde1234FG (4)

6.4 Change the field size of the LastName and FirstName fields to 50
characters. (1)

6.5 No birth dates before the 1st of January 1980 are allowed to be entered into
the Birthdate field. Create a rule to enforce this. Supply a meaningful text
message to the user, should they enter incorrect data. (3)

6.6 Change the data type of the Email field so that one can simply click on the
email address to send an email. (1)

6.7 Change the Interest field to display a combo box when selected. The values
must come from the tblInterest table. (2)

6.8 Use a feature to automatically calculate and display the total amount of fees
paid when the table is viewed. (1)

Queries

6.9 The 2Mars director wants to know how many applicants have not yet paid
for the programme at all. Create a query called qryNotPaid showing all
applicants who have not yet paid any fees. The only visible fields required
are LastName, FirstName and Email. (3)

6.10 There are some applicants who have expressed an interest in survival skills.
2Mars wants to see a list of those interested applicants who also scored
between 8 and 10. Create a query called qrySurvival showing their
LastName, FirstName, Interest and Score. Sort in descending order
according to Score. (4)
IEB Copyright © 2021
NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 13 of 14

6.11 Create a query, qryProfessionsCount, showing all JobTitles and the total
number of applicants per title. (3)

Forms

6.12 Create a form called frmApplicants, containing all fields from the
tblApplicants table. (2)

6.13 Create a form called frmApplicantsTrainingLevels.

6.13.1 It must contain the LastName and FirstName of applicants. (1)

6.13.2 It must contain a sub-form with data obtained from the TrainingLevel
field in the tblTrainingLevel table. Only the TrainingLevel field needs
to be visible. (3)

Reports

6.14 A report has been requested that must be grouped according to Country
and show each applicant's Title, FirstName and LastName. Create the
report and also calculate the number of applicants in each country beneath
each group. Save the report as rptCountryParticipation. (4)

6.15 Open the report titled rptPaidLessThan50% and format it as follows:

6.15.1 Add the FeesPaid field to the report. (2)

6.15.2 Use conditional formatting to automatically highlight amounts that

are less than R50 000. (2)

Save and close the database and all its objects. [40]

40 marks

IEB Copyright © 2021 PLEASE TURN OVER

NATIONAL SENIOR CERTIFICATE: COMPUTER APPLICATIONS TECHNOLOGY: PAPER I Page 14 of 14

SECTION E WEB DEVELOPMENT (HTML DESIGN)

QUESTION 7

Open and work only in the folder named Question7.

Open the document index.html using a text-only HTML editor.

7.
7.1 Set the HTML document title to The Red Planet. (1)

7.2 Set the web page background colour to #BB1B1B. (1)

7.3 Set the text 'Living on Mars' at the top of the page to be coloured white. (1)

7.4 Format the TABLE tag as follows:

7.4.1 No table border (1)

7.4.2 Table width: 75% (1)

7.5 Insert the image rocketship.png in the first cell of the table. Width must be
200 px. (2)

7.6 Ensure that the image, mars.gif, links to https://youtu.be/123mars when

clicked on. (2)

7.7 Enable text to appear when the mouse hovers over the image, mars.gif.
The text 'Mars in 4k' must display as the mouse hovers over the
image. (1)

7.8 Format the heading Want to live on Mars? with a Heading 2 tag and centre
it. (2)

7.9 Convert the text under the heading 'Want to live on Mars?' to a bulleted
list. (2)

7.10 Place the text at the end of the document, starting with 'Website updated
by', in comment tags so it does not display on the actual web page. Replace
the text 'Student candidate no. here' with your own exam number. (2)

Save and close the file.

16 marks

Total: 180 marks

IEB Copyright © 2021