Edexcel IGCSE · Thinka-original Practice Paper

2023 Edexcel IGCSE Information and Communication (ICT) Practice Paper with Answers

Thinka Jun 2023 Cambridge International A Level-Style Mock — Information and Communication (ICT)

200 marks270 mins2023
AnalysisSample paper
An original Thinka practice paper modelled on the structure and difficulty of the Jun 2023 Cambridge International A Level Information and Communication (ICT) paper. Not affiliated with or reproduced from Cambridge.

Paper 1 (Written)

Answer all questions. Show your understanding of ICT systems, hardware, networks, online safety, and the societal impacts of digital technologies.
57 Question · 100 marks
Question 1 · MCQ
1 marks
Which of the following network protocols is responsible for securely transferring files over the internet by encrypting both commands and data?
  1. A.FTP
  2. B.SFTP
  3. C.HTTP
  4. D.SMTP Justin Setup (SMTP)
Show answer & marking scheme

Worked solution

SFTP (SSH File Transfer Protocol) encrypts both commands and data, preventing passwords and sensitive information from being transmitted in cleartext over the network. FTP does not encrypt. HTTP is for web pages. SMTP is for email.

Marking scheme

1 mark for the correct answer (B).
Question 2 · MCQ
1 marks
A graphic designer wants to create a company logo that can be scaled up to the size of a billboard without losing quality or becoming pixelated. Which type of graphic format should they use?
  1. A.Bitmap
  2. B.JPEG
  3. C.Vector
  4. D.PNG
Show answer & marking scheme

Worked solution

Vector graphics are based on mathematical formulas (points, lines, curves) rather than pixels. This allows them to be scaled infinitely without losing quality. Bitmap formats like JPEG and PNG lose quality and become pixelated when scaled up.

Marking scheme

1 mark for the correct answer (C).
Question 3 · MCQ
1 marks
Which of the following is a primary health risk associated with repetitive strain injury (RSI) due to prolonged, incorrect keyboard usage?
  1. A.Eyestrain
  2. B.Deep Vein Thrombosis (DVT)
  3. C.Carpal Tunnel Syndrome
  4. D.Migraines
Show answer & marking scheme

Worked solution

Carpal Tunnel Syndrome is a specific type of Repetitive Strain Injury (RSI) that affects the wrists and hands due to repeated movements, such as typing on a keyboard without proper ergonomic support.

Marking scheme

1 mark for the correct answer (C).
Question 4 · MCQ
1 marks
In a spreadsheet, the formula =VLOOKUP(105, A2:D20, 3, FALSE) is used. What does the parameter 3 represent in this formula?
  1. A.The row index from which to retrieve the data.
  2. B.The column index from which to retrieve the data.
  3. C.The number of decimal places to format the output.
  4. D.The exact match criterion.
Show answer & marking scheme

Worked solution

In a VLOOKUP function, the third parameter represents the column index number within the lookup range (A2:D20) from which the matching value should be returned.

Marking scheme

1 mark for the correct answer (B).
Question 5 · MCQ
1 marks
In a relational database, what is the main purpose of a foreign key?
  1. A.To uniquely identify each record in its own table.
  2. B.To prevent any duplicate records from being entered into the database.
  3. C.To link two tables together by referencing the primary key of another table.
  4. D.To automatically index fields for faster search queries.
Show answer & marking scheme

Worked solution

A foreign key is a field in one table that uniquely identifies a row of another table (the primary key in that other table), thereby establishing a relationship between the two tables.

Marking scheme

1 mark for the correct answer (C).
Question 6 · MCQ
1 marks
Which type of secondary storage device uses non-volatile flash memory and has no moving parts, making it highly shock-resistant and suitable for portable devices?
  1. A.Solid-State Drive (SSD)
  2. B.Hard Disk Drive (HDD)
  3. C.Magnetic Tape Drive
  4. D.Optical Disc Drive
Show answer & marking scheme

Worked solution

Solid-State Drives (SSDs) use flash memory and contain no moving mechanical parts, which makes them highly resistant to physical shocks, faster, and more reliable than traditional HDDs.

Marking scheme

1 mark for the correct answer (A).
Question 7 · MCQ
1 marks
Which of the following is an example of an integrity check used to verify that data has not been altered or corrupted during transmission?
  1. A.Biometric authentication
  2. B.Firewall filtering
  3. C.Checksum calculation
  4. D.Virtual Private Network (VPN)
Show answer & marking scheme

Worked solution

A checksum is a mathematical value calculated from a block of digital data to detect errors or tampering during transmission. Biometrics is for authentication, firewalls for access control, and VPNs for secure communication channels.

Marking scheme

1 mark for the correct answer (C).
Question 8 · MCQ
1 marks
A remote worker uses a Virtual Private Network (VPN) to access their company's internal server. How does a VPN ensure the confidentiality of data transmitted over the public internet?
  1. A.By physically isolating the remote computer from external networks.
  2. B.By encrypting the data packets before they travel over the public network.
  3. C.By storing all data in a local, secure cache before synchronization.
  4. D.By scanning all transmitted packets for malware and viruses.
Show answer & marking scheme

Worked solution

A VPN secures a connection by creating an encrypted tunnel through the public internet, ensuring that any intercepted data remains unreadable to unauthorised parties.

Marking scheme

1 mark for the correct answer (B).
Question 9 · MCQ
1 marks
An embedded system in a smart washing machine needs to store its control program (firmware) so that it is not lost when the appliance is switched off. Which type of memory is used for this purpose?
  1. A.Random Access Memory (RAM)
  2. B.Read-Only Memory (ROM)
  3. C.Cache Memory
  4. D.Virtual Memory
Show answer & marking scheme

Worked solution

ROM (Read-Only Memory) is non-volatile memory, meaning it retains its contents even when the power is turned off. This makes it ideal for storing the start-up instructions and firmware of embedded systems.

Marking scheme

1 mark for selecting Read-Only Memory (ROM) as the correct non-volatile storage type for firmware.
Question 10 · MCQ
1 marks
A school network uses a device to connect multiple local area networks (LANs) together and direct data packets across the internet to their correct destinations using IP addresses. Which network device is designed for this purpose?
  1. A.Network Interface Card (NIC)
  2. B.Hub
  3. C.Router
  4. D.Switch
Show answer & marking scheme

Worked solution

A router is a network device that forwards data packets between computer networks. It connects different networks (such as a school LAN to the Internet) and uses IP addresses to determine the best path for routing data.

Marking scheme

1 mark for identifying Router as the device that connects different networks and routes packets.
Question 11 · MCQ
1 marks
A user is registering for an online account and is asked to enter their new password twice to ensure they have not made a typo. Which data entry check is this an example of?
  1. A.Format check (Validation)
  2. B.Double entry (Verification)
  3. C.Range check (Validation)
  4. D.Check digit (Verification)
Show answer & marking scheme

Worked solution

Double entry is a verification method where the same data is entered twice by the user. The system compares both entries to ensure they match, which helps prevent transcription errors.

Marking scheme

1 mark for identifying double entry verification as the correct data check method.
Question 12 · Short Answer
1 marks
State one primary function of the Control Unit (CU) within a computer's Central Processing Unit (CPU).
Show answer & marking scheme

Worked solution

The Control Unit (CU) is a component of the CPU that directs the flow of data, decodes instructions, and controls the operation of other parts of the processor during the fetch-decode-execute cycle.

Marking scheme

Award 1 mark for a correct function of the Control Unit, such as: decoding instructions, managing the fetch-decode-execute cycle, or controlling the flow of data within the CPU. Reject: executing arithmetic operations.
Question 13 · Short Answer
1 marks
Identify the network device that connects a local area network (LAN) to a wide area network (WAN) such as the internet by forwarding data packets between them.
Show answer & marking scheme

Worked solution

A router is a network hardware device that routes data packets between different networks, commonly connecting a local home or office network (LAN) to the internet (WAN).

Marking scheme

Award 1 mark for router. Accept: Router. Reject: switch, hub, modem.
Question 14 · Short Answer
1 marks
State the type of application software that is specifically designed to allow users to view and navigate web pages.
Show answer & marking scheme

Worked solution

A web browser is the specific class of application software used to fetch, render, and display web pages from the internet.

Marking scheme

Award 1 mark for web browser. Accept: Browser. Reject: search engine, operating system, website.
Question 15 · Short Answer
1 marks
State one physical health hazard associated with the prolonged use of traditional computer keyboards.
Show answer & marking scheme

Worked solution

Prolonged and continuous typing on a standard keyboard without proper ergonomic support can lead to Repetitive Strain Injury (RSI) or Carpal Tunnel Syndrome.

Marking scheme

Award 1 mark for any valid physical health hazard associated with keyboard use, such as: Repetitive Strain Injury (RSI), Carpal Tunnel Syndrome, wrist strain, or tendonitis. Reject: eye strain, backache (not specific to keyboards).
Question 16 · Short Answer
1 marks
State the term used to describe a cell reference in a spreadsheet formula that does not change when the formula is copied to another cell.
Show answer & marking scheme

Worked solution

An absolute cell reference is fixed using dollar signs (e.g., $A$1) so that the reference to the cell remains constant regardless of where the formula is copied or moved.

Marking scheme

Award 1 mark for absolute cell reference. Accept: Absolute reference, absolute. Reject: relative cell reference, fixed cell.
Question 17 · Short Answer
1 marks
State the term used to describe a unique identifier field in a database table that ensures every record is distinct.
Show answer & marking scheme

Worked solution

A primary key is a field, or set of fields, in a database table that uniquely identifies each record and prevents duplicate entries.

Marking scheme

Award 1 mark for primary key. Accept: Primary key, key field. Reject: foreign key, unique key.
Question 18 · Short Answer
1 marks
State the term for a type of malware that disguises itself as legitimate software but performs malicious actions once installed on a system.
Show answer & marking scheme

Worked solution

A Trojan horse (or Trojan) is a type of malicious software that tricks users by pretending to be a useful or safe utility, but executes harmful commands in the background once run.

Marking scheme

Award 1 mark for Trojan horse. Accept: Trojan, Trojan horse. Reject: virus, worm, spyware, ransomware.
Question 19 · Short Answer
1 marks
State one advantage of using Bluetooth rather than Wi-Fi for connecting a wireless mouse to a laptop.
Show answer & marking scheme

Worked solution

Bluetooth is designed for short-range ad-hoc communication with minimal power requirements, making it ideal for extending the battery life of peripheral devices like mice compared to the higher energy needs of Wi-Fi.

Marking scheme

Award 1 mark for a valid advantage of Bluetooth over Wi-Fi for peripheral connection, such as: lower power consumption (longer battery life for the mouse), easier/automatic pairing process, or not requiring a wireless router/access point. Reject: higher data transfer rate, longer range.
Question 20 · Short Answer
1 marks
State one hardware output device that can be used to present text in a tactile format for a visually impaired user.
Show answer & marking scheme

Worked solution

A refreshable Braille display is an electro-mechanical device used to display Braille characters by raising round-tipped pins through holes in a flat surface, allowing visually impaired users to read text through touch.

Marking scheme

Award 1 mark for any of the following: Refreshable Braille display, Braille printer, Braille embosser.
Question 21 · Short Answer
1 marks
State the network protocol used to securely transfer web pages and data over the internet.
Show answer & marking scheme

Worked solution

HTTPS (Hypertext Transfer Protocol Secure) encrypts the communication channel between a client browser and a web server using TLS/SSL to protect data integrity and privacy.

Marking scheme

Award 1 mark for HTTPS (accept Hypertext Transfer Protocol Secure). Reject: HTTP.
Question 22 · Short Answer
1 marks
State one physical health risk associated with the prolonged, repetitive use of a keyboard and mouse without taking regular breaks.
Show answer & marking scheme

Worked solution

Repetitive Strain Injury (RSI) is a general term used to describe pain and damage in muscles, nerves, and tendons caused by repetitive movement and overuse.

Marking scheme

Award 1 mark for: Repetitive Strain Injury (RSI) or Carpal Tunnel Syndrome. Reject: Eye strain or backache (not directly caused by keyboard/mouse repetitive use).
Question 23 · Short Answer
1 marks
Identify the type of malware that encrypts a user's files and demands a fee to restore access.
Show answer & marking scheme

Worked solution

Ransomware is malicious software that encrypts files on a device, making them unusable, and then demands a payment (often in cryptocurrency) in exchange for the decryption key.

Marking scheme

Award 1 mark for Ransomware. Reject: Malware, Virus, Trojan.
Question 24 · Short Answer
1 marks
State the term used to describe a database field that uniquely identifies each record in a table.
Show answer & marking scheme

Worked solution

A primary key is a field (or combination of fields) in a database table that uniquely identifies each individual row/record in that table.

Marking scheme

Award 1 mark for Primary key. Reject: Foreign key.
Question 25 · Short Answer
1 marks
State the spreadsheet feature that automatically changes the appearance of a cell based on specific rules or criteria.
Show answer & marking scheme

Worked solution

Conditional formatting dynamically alters cell styles (such as background color or font style) based on user-defined rules applied to the cell values.

Marking scheme

Award 1 mark for Conditional formatting. Reject: Formatting.
Question 26 · Short Answer
1 marks
State the category of system software used to perform maintenance tasks, such as disk defragmentation or file compression.
Show answer & marking scheme

Worked solution

Utility software comprises system tools designed to analyze, configure, optimize, or maintain a computer system.

Marking scheme

Award 1 mark for Utility software (or utility program). Reject: Operating system, application software.
Question 27 · Structured Explanation
2 marks
Explain one advantage of using a peer-to-peer network instead of a client-server network in a small home office.
Show answer & marking scheme

Worked solution

A peer-to-peer (P2P) network is cheaper and less complex to set up because each computer on the network can communicate and share resources directly with others without needing a dedicated central server. This also removes the need to buy expensive server operating systems and hire a specialist network administrator to maintain it.

Marking scheme

Award 1 mark for identifying a valid advantage (e.g. lower setup cost, easier to configure, no single point of failure). Award 1 mark for the explanation of how or why this advantage occurs (e.g. no need to buy an expensive central server, does not require specialist ICT staff to maintain).
Question 28 · Structured Explanation
2 marks
Explain why vector graphics are preferred over bitmap graphics for creating company logos.
Show answer & marking scheme

Worked solution

Vector graphics are stored as mathematical formulas and instructions rather than as a grid of individual pixels. Because of this, they can be scaled up or down infinitely without any loss of quality or pixelation, making them ideal for logos which must appear crisp on everything from a small business card to a massive promotional billboard.

Marking scheme

Award 1 mark for identifying that vector graphics are scalable / do not lose quality / do not become pixelated when resized. Award 1 mark for explaining why this is important for a logo (e.g. needs to be reproduced on different sized media from small business cards to large billboards while maintaining a professional appearance).
Question 29 · Structured Explanation
2 marks
Describe one way the digital divide can impact a student's educational opportunities.
Show answer & marking scheme

Worked solution

The digital divide refers to the gap between individuals who have access to modern digital technologies and those who do not. A student living without high-speed internet or access to a computer at home is severely disadvantaged because they cannot easily access virtual learning environments, conduct research for assignments, or complete online homework, which can negatively affect their overall academic achievement.

Marking scheme

Award 1 mark for describing the barrier/limitation (e.g. lack of computer or high-speed internet at home). Award 1 mark for explaining the impact on education (e.g. cannot access online research databases, cannot complete online homework assignments, falling behind peers).
Question 30 · Structured Explanation
2 marks
Explain the difference between absolute and relative cell referencing when copying formulas in a spreadsheet.
Show answer & marking scheme

Worked solution

A relative cell reference (such as A1) changes automatically when a formula is copied to another cell because it is relative to the position of that cell. In contrast, an absolute cell reference (such as $A$1) uses dollar signs to lock the reference, meaning it will always point to the exact same cell regardless of where the formula is copied.

Marking scheme

Award 1 mark for explaining relative referencing (changes/adjusts dynamically when copied). Award 1 mark for explaining absolute referencing (stays fixed/locked to a specific row and/or column when copied).
Question 31 · Structured Explanation
2 marks
Explain why a database designer would use a primary key in a table.
Show answer & marking scheme

Worked solution

A primary key is a field in a database table that must contain a unique value for every single record (such as an ID number). This ensures that every row can be uniquely identified, prevents duplicate records from being created, and allows the table to be linked to other tables to create relational databases.

Marking scheme

Award 1 mark for explaining that it uniquely identifies each record/row. Award 1 mark for explaining the purpose or benefit (e.g. prevents duplicate entries, allows relationships to be established between tables to maintain data integrity).
Question 32 · Structured Explanation
2 marks
Describe the purpose of RAM (Random Access Memory) in a computer system.
Show answer & marking scheme

Worked solution

RAM is the computer's volatile main memory. It acts as temporary storage that holds the operating system, running applications, and open files currently being processed by the CPU, allowing the processor to access this data extremely quickly compared to retrieving it from secondary storage.

Marking scheme

Award 1 mark for identifying that it is temporary/volatile memory that stores data/programs currently in use. Award 1 mark for explaining its purpose (e.g. allows fast access by the CPU, prevents the computer from slowing down by reducing access to secondary storage).
Question 33 · Structured Explanation
2 marks
Explain how a firewall protects a local area network (LAN) from unauthorized external access.
Show answer & marking scheme

Worked solution

A firewall acts as a security barrier between a private local network and public external networks like the internet. It inspects all incoming and outgoing data packets and compares them against a set of security rules, blocking traffic that is unauthorized or suspected of being malicious (such as hacking attempts or malware).

Marking scheme

Award 1 mark for stating that it monitors or filters incoming and outgoing network traffic/data packets. Award 1 mark for explaining that it blocks unauthorized/suspicious traffic based on pre-defined security rules.
Question 34 · Structured Explanation
2 marks
Explain how a virtual private network (VPN) provides secure communication over the internet.
Show answer & marking scheme

Worked solution

A VPN establishes a secure, encrypted connection (often referred to as a tunnel) between a user's digital device and a private network over the public internet. This ensures that any data packets transmitted are encrypted, making them unreadable to unauthorized external parties, hackers, or internet service providers who might intercept them.

Marking scheme

Award 1 mark for identifying that it encrypts data packets / makes data unreadable to interceptors. Award 1 mark for explaining that it creates a secure/private connection tunnel over a public network (the internet).
Question 35 · Structured Explanation (Describe/Explain)
2 marks
Explain one reason why an action camera, designed to be mounted on a bicycle, uses solid-state storage rather than a magnetic hard disk drive.
Show answer & marking scheme

Worked solution

1. State that solid-state storage has no moving parts or is highly durable. (1 mark)
2. Explain how this benefits a bicycle-mounted camera (prevents data corruption or physical drive damage caused by bumps and vibrations). (1 mark)

Marking scheme

Award 1 mark for a characteristic of solid-state storage (e.g., no moving parts, light weight).
Award 1 mark for linking it to the movement/vibrations of a bicycle camera (e.g., prevents disk head crashes, skipping, or physical damage).
- Accept: shock resistance (1) + so the drive does not break when the bike goes over rough terrain (1).
Question 36 · Structured Explanation (Describe/Explain)
2 marks
Describe how high latency can affect a gamer's experience during a fast-paced multiplayer online match, even if they have a very high-bandwidth internet connection.
Show answer & marking scheme

Worked solution

1. Describe latency as the time delay for data packets to travel between the client and server. (1 mark)
2. Explain the impact of this delay on gameplay (character actions appear sluggish or out of sync, giving an unfair disadvantage). (1 mark)

Marking scheme

Award 1 mark for defining latency as a delay in data packet transfer.
Award 1 mark for explaining the impact on gameplay (e.g., lag, delayed character reactions, lack of real-time synchronisation).
- Accept: delay in sending/receiving signals (1) + meaning the character actions are registered late on screen (1).
Question 37 · Structured Explanation (Describe/Explain)
2 marks
Explain one benefit of using vector graphics instead of bitmap graphics when designing a corporate logo that will be printed on both small business cards and large vehicles.
Show answer & marking scheme

Worked solution

1. State that vector graphics are based on mathematical formulas/paths (making them resolution-independent). (1 mark)
2. Explain the benefit in this scenario (the logo remains sharp and clear on both small business cards and large vehicle wraps). (1 mark)

Marking scheme

Award 1 mark for identifying that vector graphics are resolution-independent / scalable.
Award 1 mark for explaining the outcome (no loss of quality / no pixelation when enlarged to fit a vehicle).
- Reject: 'vectors have smaller file sizes' unless linked directly to why scaling is beneficial in this printing scenario.
Question 38 · Structured Explanation (Describe/Explain)
2 marks
Explain how a relational database, using linked tables, reduces the risk of data inconsistency compared to a flat-file database.
Show answer & marking scheme

Worked solution

1. Explain that relational databases use relationships/linked tables to avoid duplicate data. (1 mark)
2. Explain how this prevents inconsistency (changes are made in one place and apply everywhere, preventing mismatched records). (1 mark)

Marking scheme

Award 1 mark for referencing linked tables / reduction of data duplication / use of primary and foreign keys.
Award 1 mark for explaining that updates only need to be made once, preventing conflicting records.
- Accept: referential integrity rules (1) + prevent orphan records or mismatched data across the system (1).
Question 39 · Structured Explanation (Describe/Explain)
2 marks
Explain one way that a lack of reliable high-speed broadband access contributes to the digital divide for students in rural areas.
Show answer & marking scheme

Worked solution

1. Identify an educational barrier (e.g., inability to download large learning resources or join live online lessons). (1 mark)
2. Link this to the digital divide (e.g., students fall behind academically, leading to unequal opportunities). (1 mark)

Marking scheme

Award 1 mark for a specific limitation (e.g., inability to participate in online homework, access research websites, or join virtual classrooms).
Award 1 mark for explaining the societal/divide impact (e.g., lower grades, unequal educational opportunities compared to urban students).
Question 40 · Structured Explanation (Describe/Explain)
2 marks
Explain the difference between phishing and pharming in relation to how a user is directed to a malicious website.
Show answer & marking scheme

Worked solution

1. Explain that phishing relies on social engineering / tricking the user into clicking a link. (1 mark)
2. Explain that pharming redirects the user automatically by corrupting DNS settings or using malware. (1 mark)

Marking scheme

Award 1 mark for explaining phishing (social engineering, email links, user action required).
Award 1 mark for explaining pharming (DNS server corruption/redirection, malware, happens automatically without clicking a suspicious link).
- Both points must clearly distinguish the mechanism of direction to receive full marks.
Question 41 · Structured Explanation (Describe/Explain)
2 marks
Explain why a spreadsheet designer must use an absolute cell reference (such as $B$2) when referencing a sales tax rate in a formula that will be copied down a column of sales figures.
Show answer & marking scheme

Worked solution

1. Explain that the absolute reference locks/freezes the cell position when the formula is copied. (1 mark)
2. Explain the consequence if relative references were used (the reference would shift to empty cells, causing calculation errors). (1 mark)

Marking scheme

Award 1 mark for explaining that absolute referencing keeps the cell reference fixed/constant during copying.
Award 1 mark for explaining that a relative reference would shift down and point to incorrect/empty cells, causing errors.
- Accept: explanation of the '$' symbol (1) + to maintain reference to the single static tax rate cell (1).
Question 42 · Structured Explanation (Describe/Explain)
2 marks
Explain one advantage of using a peer-to-peer (P2P) network structure rather than a client-server network structure for a small office with only four computers.
Show answer & marking scheme

Worked solution

1. State an advantage of P2P (e.g., lower cost, easier setup, no need for specialist IT staff). (1 mark)
2. Explain why this is suitable for a small office (e.g., a server is an unnecessary expense for only four computers). (1 mark)

Marking scheme

Award 1 mark for identifying a valid advantage of P2P (e.g., cheaper setup costs, no server hardware required, easier to configure).
Award 1 mark for linking this to the small office scenario (e.g., saves money and management effort since four computers do not require a central server's resources).
- Reject: security advantages (since client-server is more secure).
Question 43 · Structured Explanation (Describe/Explain)
2 marks
Explain the purpose of a gateway in a network.
Show answer & marking scheme

Worked solution

A gateway is a network node that connects two networks with different transmission protocols. It acts as a converter, translating data from one protocol format to another so that devices on different networks can communicate.

Marking scheme

Award 1 mark for identifying that it connects different or incompatible networks (1). Award 1 mark for explaining that it translates or converts protocols between these networks (1).
Question 44 · Structured Explanation (Describe/Explain)
2 marks
Explain how the 'digital divide' can negatively affect a person's employment opportunities.
Show answer & marking scheme

Worked solution

The digital divide refers to the gap between those who have access to modern ICT and those who do not. This negatively affects employment because many job openings are only advertised online, making them inaccessible to those without internet access. Furthermore, many modern jobs require basic digital literacy, which individuals on the wrong side of the divide cannot develop.

Marking scheme

Award 1 mark for describing an aspect of the divide, such as lack of internet access or digital skills (1). Award 1 mark for linking this directly to employment limitations, such as being unable to apply online or lacking the skills required by employers (1).
Question 45 · Structured Explanation (Describe/Explain)
2 marks
Describe how an optical mark recognition (OMR) system processes a completed multiple-choice exam paper.
Show answer & marking scheme

Worked solution

An OMR reader works by shining a light onto the exam sheet. The areas where the candidate has made dark pencil or pen marks will reflect less light back to the sensors compared to the white paper. The system detects these differences in reflected light to determine which options were selected and matches them against an answer key.

Marking scheme

Award 1 mark for stating that light is shone onto the paper or sensors detect reflected light (1). Award 1 mark for explaining that dark marks reflect less light/no light to identify selected answers (1).
Question 46 · Structured Explanation (Describe/Explain)
2 marks
Explain how a firewall protects a local area network (LAN) from unauthorized external access.
Show answer & marking scheme

Worked solution

A firewall acts as a barrier between a trusted internal network (LAN) and an untrusted external network (such as the Internet). It inspects all incoming and outgoing data packets and compares them against a set of pre-defined security rules. It then blocks any packets that violate these rules, preventing unauthorized access or malware transmission.

Marking scheme

Award 1 mark for mentioning the inspection or monitoring of incoming and outgoing traffic (1). Award 1 mark for explaining that it blocks packets that do not meet security criteria or rules (1).
Question 47 · Structured Explanation (Describe/Explain)
2 marks
Explain the difference between a relative cell reference and an absolute cell reference when a formula is copied to another cell.
Show answer & marking scheme

Worked solution

When a formula is copied in a spreadsheet, a relative cell reference (e.g., A1) adjusts automatically based on the relative position of the row and column where the formula is pasted. In contrast, an absolute cell reference (e.g., $A$1) remains locked to that specific cell and does not change regardless of where the formula is copied.

Marking scheme

Award 1 mark for explaining that relative cell references adjust or change based on the new position (1). Award 1 mark for explaining that absolute cell references remain unchanged or locked (1).
Question 48 · Structured Explanation (Describe/Explain)
2 marks
Explain why a business might require its remote workers to use a Virtual Private Network (VPN) when accessing company files.
Show answer & marking scheme

Worked solution

A Virtual Private Network (VPN) creates an encrypted connection (tunnel) over a public network like the Internet. By using a VPN, any data transmitted between the remote worker's home and the company network is encrypted. This prevents unauthorized interception, eavesdropping, or data theft by hackers, ensuring that confidential company files remain secure.

Marking scheme

Award 1 mark for mentioning that it encrypts data or creates a secure tunnel over public networks (1). Award 1 mark for explaining that this prevents data interception or unauthorized access to confidential company files (1).
Question 49 · Structured Explanation (Describe/Explain)
2 marks
Describe how using Cascading Style Sheets (CSS) improves the process of developing and maintaining a website.
Show answer & marking scheme

Worked solution

CSS allows the design and layout (presentation) of a website to be kept separate from the content (HTML). By using an external style sheet, a developer can change the visual appearance (fonts, colors, alignment) of hundreds of web pages simultaneously just by editing a single CSS file, which saves time and ensures design consistency across the entire site.

Marking scheme

Award 1 mark for mentioning the separation of design or presentation from content (1). Award 1 mark for explaining that editing a single file updates the style of multiple web pages, which saves time or ensures consistency (1).
Question 50 · Structured Explanation (Describe/Explain)
2 marks
Explain one environmental concern associated with the disposal of old digital devices in landfills.
Show answer & marking scheme

Worked solution

Digital devices contain hazardous materials such as lead, mercury, and cadmium. If these devices are disposed of in landfills rather than being properly recycled, these toxic heavy metals can leach into the surrounding soil and groundwater. This contamination harms local ecosystems, wildlife, and can eventually contaminate human water supplies.

Marking scheme

Award 1 mark for identifying that devices contain toxic or hazardous materials, such as lead or mercury (1). Award 1 mark for explaining that these leach or leak into soil or water and damage the environment or ecosystem (1).
Question 51 · Structured Explanation
2 marks
Explain one benefit of using an embedded system in a smart washing machine compared to using a general-purpose computer.
Show answer & marking scheme

Worked solution

An embedded system is designed to perform a specific dedicated function. This means it only requires minimal hardware components and simple processing power. As a result, it is much cheaper to manufacture and consumes significantly less electricity compared to a general-purpose computer.

Marking scheme

Award 1 mark for identifying a valid benefit (e.g., lower manufacturing cost, smaller physical size, lower power consumption, higher reliability) and 1 mark for explaining it in context (e.g., because it only runs a single program / doesn't require complex hardware components / operates on minimal power).
Question 52 · Structured Explanation
2 marks
Explain why an online banking website must use HTTPS rather than HTTP.
Show answer & marking scheme

Worked solution

HTTPS uses encryption protocols (SSL/TLS) to secure the communication channel between the user's web browser and the bank's server. This prevents unauthorized interception (eavesdropping) because any intercepted data will appear as scrambled, unreadable ciphertext.

Marking scheme

Award 1 mark for identifying that HTTPS encrypts data / creates a secure tunnel, and 1 mark for explaining the purpose (e.g., preventing unauthorized parties from reading sensitive login credentials or transaction details if intercepted).
Question 53 · Structured Explanation
2 marks
Describe how a check digit is used to verify that a numerical code has been entered correctly.
Show answer & marking scheme

Worked solution

A check digit is a single digit calculated from the other digits in a data input using an algorithm. When the data is entered, the computer applies the same algorithm to recalculate the check digit. If the recalculated digit matches the entered check digit, the data is accepted as correct; otherwise, an error is reported.

Marking scheme

Award 1 mark for stating that an algorithm/calculation is applied to the digits of the input data, and 1 mark for explaining that the calculated result is compared with the check digit to detect transcription errors.
Question 54 · Structured Explanation
2 marks
Explain one way in which geographical location can contribute to the digital divide.
Show answer & marking scheme

Worked solution

Telecommunication providers often focus their infrastructure investments (such as fiber-optic cabling and 5G cellular masts) in densely populated urban areas to maximize profit. Consequently, individuals living in remote or rural locations are left with slow or unreliable connections, limiting their ability to access modern digital services.

Marking scheme

Award 1 mark for identifying a geographical barrier (e.g., lack of high-speed broadband/cellular infrastructure in rural areas) and 1 mark for explaining how this creates a divide (e.g., leaving rural residents with slower, less reliable access to online resources, education, or jobs compared to urban areas).
Question 55 · Structured Explanation
2 marks
Explain the primary difference between application software and utility software.
Show answer & marking scheme

Worked solution

Application software is designed to enable the user to perform specific real-world tasks, such as writing a document or browsing the web. On the other hand, utility software performs background system maintenance and optimization tasks, such as disk defragmentation or virus scanning, to keep the computer running efficiently.

Marking scheme

Award 1 mark for describing the purpose of application software (designed for user-specific tasks/output) and 1 mark for describing the purpose of utility software (designed to manage, maintain, or optimize the computer system).
Question 56 · Extended Essay
8 marks
Many organisations now offer employees the option of teleworking (working from home) using digital technologies rather than commuting to a central office. Discuss the impact of the widespread adoption of teleworking on employees and the environment.
Show answer & marking scheme

Worked solution

Impact on Employees:
- Advantages: Offers greater flexibility and a better work-life balance, allowing employees to manage personal commitments alongside work. Employees save time and money on daily commuting. Increased autonomy and less direct micro-management can lead to higher job satisfaction.
- Disadvantages: Can lead to social isolation and feelings of loneliness due to lack of face-to-face interaction with colleagues. The boundary between home and work life can become blurred, leading to longer working hours and burnout. Increased personal utility bills (heating, electricity, broadband).

Impact on the Environment:
- Positive effects: Significant reduction in carbon emissions and air pollution due to fewer daily commutes and reduced traffic congestion. Decreased energy consumption in large corporate offices (heating, lighting, cooling).
- Negative effects: Increased residential energy consumption as homes are heated and powered throughout the day. Increased demand for and manufacturing of home-office digital devices, leading to higher resource consumption and electronic waste (e-waste).

Conclusion:
Teleworking offers significant environmental benefits in terms of emissions and gives employees greater flexibility. However, it requires careful management to prevent employee isolation and to mitigate the shift in energy usage to residential homes.

Marking scheme

Level 1 (1-3 marks):
- Response is basic and descriptive, identifying a few isolated points (e.g., 'teleworking is good because you do not have to travel').
- Lacks depth, balance, or clear structure.
- Focuses almost entirely on either employees or the environment, with little to no mention of the other.

Level 2 (4-6 marks):
- Response discusses both advantages and disadvantages, but may be unbalanced (e.g., detailed discussion of employees but very brief mention of the environment).
- Demonstrates a clear understanding of the impacts of teleworking, but explanations may lack detail or technical depth.
- A conclusion may be present but is not fully developed.

Level 3 (7-8 marks):
- Response is a balanced and comprehensive discussion covering both employees and the environment in detail.
- Clearly analyses both positive and negative consequences for both targets.
- Well-structured, using appropriate ICT terminology throughout.
- Concludes with a well-reasoned, logical summary based on the points discussed.
Question 57 · Extended Essay
8 marks
An international retail organisation is considering moving all its operational and customer data from local on-premise servers to public cloud storage. Discuss the security and accessibility implications of this decision.
Show answer & marking scheme

Worked solution

Security Implications:
- Local Storage: Provides direct, physical control over servers and data security protocols. However, local storage is highly vulnerable to local disasters (fire, flood, theft) and depends entirely on the expertise of in-house IT staff for patching and maintaining security.
- Cloud Storage: Cloud providers invest heavily in state-of-the-art physical security, automated backups across multiple data centres, and advanced encryption at rest and in transit. However, public cloud platforms are primary targets for cyberattacks, and poor configuration of permissions can lead to massive data leaks.

Accessibility Implications:
- Local Storage: Fast, low-latency access for users inside the main office. However, remote access is complex, typically requiring Virtual Private Networks (VPNs) which can be slow and have limited bandwidth.
- Cloud Storage: Enables high accessibility from any location with an internet connection, supporting remote work and real-time collaboration. It is also highly scalable. However, the organisation becomes entirely dependent on internet connectivity; any downtime or broadband failure will halt access to operations and customer data.

Conclusion:
While cloud storage offers superior remote accessibility and robust disaster recovery, it introduces absolute dependency on internet connectivity and transfers direct control of security to a third-party provider. A careful service level agreement (SLA) is vital.

Marking scheme

Level 1 (1-3 marks):
- Identifies basic, isolated facts about cloud storage or local storage (e.g., 'anyone can access cloud storage anywhere', 'hackers can get into the cloud').
- No real comparison or balance between security and accessibility.
- Use of technical terminology is weak or absent.

Level 2 (4-6 marks):
- Discusses both security and accessibility implications, showing a good understanding of both cloud and local options.
- Explanations are clear but may be unbalanced (e.g., detailed analysis of accessibility but very brief mention of security).
- Response is structured but may lack a well-reasoned conclusion.

Level 3 (7-8 marks):
- A comprehensive, balanced, and well-structured discussion of both security and accessibility implications.
- Clearly contrasts public cloud storage with local on-premise storage.
- Uses appropriate technical terminology (e.g., encryption, latency, SLA, redundancy, single point of failure).
- Culminates in a logical, reasoned conclusion based on the preceding arguments.

Paper 2 (Practical)

Complete practical tasks in Graphics, Databases, Presentations, Spreadsheets, and Word Processing using the provided source data folders.
5 Question · 100 marks
Question 1 · practical
9 marks
EcoRide is a new environmentally-friendly bicycle rental service launching in your city. They require a web banner for the top of their homepage. Create a new image banner using suitable graphics software to meet the following design brief:

1. Set the image canvas size to a width of 800 pixels and a height of 200 pixels.
2. Apply a solid background color with the hex code #E2F0D9 (light green).
3. Insert the image 'BICYCLE.PNG' from your source folder. Crop the image to show only the main frame and wheels, resize it proportionally to fit the height of the banner, and place it on the far left-hand side.
4. Add the title text 'EcoRide' in a bold, sans-serif font (e.g., Arial), size 48pt, with the font color #385723 (dark green). Center-align this text horizontally in the remaining banner area.
5. Below the title, add the slogan 'Ride Green, Live Clean' in an italic, sans-serif font, size 20pt, with the font color #595959 (dark grey). Center-align this slogan relative to the title.
6. Save the completed banner as a PNG image with the filename 'ECORIDE_BANNER' in your work folder.
Show answer & marking scheme

Worked solution

Step-by-step implementation in a graphics editor (e.g., GIMP, Adobe Photoshop, or Paint.NET):

1. Open the graphics software and select 'File' -> 'New'. Set Width to 800 pixels and Height to 200 pixels. Click OK.
2. Select the paint bucket tool. Set the foreground color to hex code #E2F0D9. Click on the canvas to fill the background with light green.
3. Go to 'File' -> 'Open as Layers' (or 'Place') and select 'BICYCLE.PNG' from the source folder. Use the crop tool to remove any excess background space around the bicycle frame and wheels. Use the scale tool to resize the bicycle proportionally so its height fits comfortably within the 200px limit (e.g., 180px height). Move the resized image to the far left edge of the canvas.
4. Select the Text tool. Click in the center-right area. Set font to Arial (or another sans-serif font), size to 48pt, style to Bold, and color to #385723. Type 'EcoRide'. Align the text box to the center of the remaining space.
5. Select the Text tool again. Click below the 'EcoRide' text. Set font to Arial, size to 20pt, style to Italic, and color to #595959. Type 'Ride Green, Live Clean'. Align it horizontally with the title above.
6. Go to 'File' -> 'Export As' or 'Save As'. Choose PNG format. Name the file 'ECORIDE_BANNER' and save it to the designated work folder.

Marking scheme

Marking criteria (Total: 9 marks):
- Canvas size: 1 mark for setting correct dimensions of 800 x 200 pixels.
- Background color: 1 mark for correct hex code #E2F0D9 (light green) applied as background.
- Image insertion: 1 mark for successfully inserting 'BICYCLE.PNG' and positioning it on the left.
- Image editing: 1 mark for cropping the bicycle image to show only the frame and wheels and maintaining proportional scaling.
- Title text: 1 mark for correct text 'EcoRide' with correct font style (bold, sans-serif) and correct color (#385723).
- Title size and alignment: 1 mark for 48pt font size and horizontal centering in the remaining area.
- Slogan text: 1 mark for correct text 'Ride Green, Live Clean' with correct style (italic, sans-serif) and color (#595959).
- Slogan size and alignment: 1 mark for 20pt font size and position centered directly beneath the main title.
- File saving: 1 mark for saving in PNG format with the exact name 'ECORIDE_BANNER'.
Question 2 · Database Construction and Management
25 marks
You are going to use a database to manage information about members and bookings at the Oakwood Activity Centre.

The database contains two tables: MEMBERS and BOOKINGS.

The structure of the MEMBERS table is:
- MemberID (Text - e.g. MEM101)
- FirstName (Text)
- LastName (Text)
- Gender (Text)
- DateOfBirth (Date/Time)
- MembershipType (Text - Gold, Silver, Bronze)

The structure of the BOOKINGS table is:
- BookingID (Numeric/AutoNumber)
- MemberID (Text)
- Activity (Text - e.g. Kayaking, Climbing, Archery)
- BookingDate (Date/Time)
- Cost (Currency)

Answer the following questions:

TASK DB1
(a) State the field that would be the most suitable primary key for the MEMBERS table. [1]
(b) Explain why MemberID is more suitable as a primary key than LastName. [2]
(c) Describe how a one-to-many relationship is created between the MEMBERS table and the BOOKINGS table, identifying the primary and foreign keys. [3]

TASK DB2
(a) The database administrator wants to ensure that only 'Gold', 'Silver', or 'Bronze' can be entered into the MembershipType field. State the type of validation check that should be used and explain how it would operate. [2]
(b) State one validation check that could be applied to the BookingDate field to ensure bookings are not made for past dates. Explain how it works. [2]

TASK DB3
(a) A query is required to find all members who have a 'Gold' membership and were born after 31/12/1995. Write down the search criteria (expression) that would be used in the query design grid for:
(i) MembershipType [1]
(ii) DateOfBirth [2]
(b) The activity manager wants to find all bookings for 'Climbing' or 'Kayaking' where the cost is more than £25.00. The results must show only the fields BookingID, MemberID, Activity, and Cost in ascending order of Cost. Write an SQL statement to retrieve this information. [5]

TASK DB4
The manager wants a database report based on the query in Task DB3(b).
(a) Describe three formatting features that should be applied to make the report look professional and suitable for a business audience. [3]
(b) The manager wants to show the average cost of these selected bookings at the bottom of the report.
(i) State the function that would be used to calculate this. [1]
(ii) Explain where this calculation must be placed in the report design view to ensure it appears once at the very end of the report. [1]
(iii) Write the exact formula/expression to calculate this average based on the Cost field. [2]
Show answer & marking scheme

Worked solution

TASK DB1
(a) The primary key is MemberID. It is the only field guaranteed to contain unique values for every member.
(b) LastName cannot be a primary key because multiple members can have the same last name (e.g., Smith). A primary key must be unique for each record to prevent ambiguity.
(c) To create the relationship:
1. Open the relationships tool and add both tables.
2. Drag the MemberID field (Primary Key) from the MEMBERS table and drop it onto the MemberID field (Foreign Key) in the BOOKINGS table.
3. Enforce referential integrity to complete the one-to-many link.

TASK DB2
(a) Validation Check: List Check / Lookup List / Validation Rule.
Operation: It compares the entered text against a restricted set of allowed values (Gold, Silver, Bronze). If the input does not match any items in the list, an error message is displayed and the entry is rejected.
(b) Validation Check: Range Check / Limit Check / Date Validation.
Operation: A validation rule like ">=Date()" or ">=DateOfToday()" is applied. When a user enters a booking date, the system checks whether it is greater than or equal to the current system date. If it is in the past, it is rejected.

TASK DB3
(a) (i) Criteria for MembershipType: "Gold"
(ii) Criteria for DateOfBirth: > #31/12/1995# (or > #12/31/1995# depending on region/database system)
(b) SQL Statement:
SELECT BookingID, MemberID, Activity, Cost
FROM BOOKINGS
WHERE (Activity = 'Climbing' OR Activity = 'Kayaking') AND Cost > 25
ORDER BY Cost ASC;

TASK DB4
(a) Formatting features:
1. Title: Add a customized, descriptive title (e.g., 'Selected Bookings Report') instead of the default query name, in a large, bold font.
2. Layout/Alignment: Ensure all columns are wide enough so no data or headers are clipped/truncated. Align text fields to the left and numerical/currency fields to the right.
3. Professional Styles: Apply appropriate currency formatting (e.g. £ or $) with 2 decimal places to the Cost field, and use a consistent, clean professional color scheme and sans-serif font family (e.g., Arial or Calibri).
(b) (i) Function: AVG (or AVERAGE)
(ii) Location: Report Footer section. Placing it here ensures it is calculated once and displayed at the very end of the entire report, rather than on every page.
(iii) Formula: =AVG([Cost])

Marking scheme

TASK DB1 (Total: 6 marks)
- (a) [1 Mark] MemberID identified as the primary key.
- (b) [2 Marks] 1 mark for stating that multiple members can share the same last name; 1 mark for stating that a primary key must uniquely identify each record.
- (c) [3 Marks] 1 mark for identifying MemberID in MEMBERS as the primary key; 1 mark for identifying MemberID in BOOKINGS as the foreign key; 1 mark for explaining that they are linked/dragged to establish the one-to-many relationship (or enforcing referential integrity).

TASK DB2 (Total: 4 marks)
- (a) [2 Marks] 1 mark for identifying validation type (e.g., lookup list, drop-down, or validation rule); 1 mark for explaining that it restricts values to only 'Gold', 'Silver', or 'Bronze'.
- (b) [2 Marks] 1 mark for identifying validation type (range check, limit check); 1 mark for explaining that the expression compares input with today's date (e.g., >=Date()) and rejects historical dates.

TASK DB3 (Total: 8 marks)
- (a)(i) [1 Mark] "Gold" or equivalent syntax.
- (a)(ii) [2 Marks] 1 mark for the correct comparison operator (>); 1 mark for correct date representation (e.g., #31/12/1995# or similar standard format).
- (b) [5 Marks]
- 1 mark for: SELECT BookingID, MemberID, Activity, Cost
- 1 mark for: FROM BOOKINGS
- 1 mark for: WHERE Cost > 25
- 1 mark for: AND (Activity = 'Climbing' OR Activity = 'Kayaking') (with correct logical grouping)
- 1 mark for: ORDER BY Cost [ASC]

TASK DB4 (Total: 7 marks)
- (a) [3 Marks] 1 mark for each valid, distinct formatting feature described up to 3 (e.g., title enhancement, text-wrapping check/column width adjustment, consistent professional fonts, currency formatting for Cost field).
- (b)(i) [1 Mark] AVG or AVERAGE function specified.
- (b)(ii) [1 Mark] Report Footer section specified (reject Page Footer).
- (b)(iii) [2 Marks] 1 mark for the formula beginning with an equals sign (=) and correct function name; 1 mark for correct syntax and field reference in brackets ([Cost]).
Question 3 · Interactive Presentation Design
16 marks
Scenario: GreenCycle Hub is a local non-profit promoting community recycling. You must create a short, interactive three-slide presentation to pitch their new initiatives. Task instructions: 1. Create a Master Slide to ensure consistency across all slides. The master slide must have a light grey background, a dark green horizontal banner at the top, a logo placeholder in the top-right corner, and a footer containing the automated date and automated slide number. Font style for titles must be sans-serif (e.g., Arial), bold, and dark green. 2. Slide 1 (Title Slide): Add the title 'GreenCycle Community Action' and subtitle 'Our Path to Zero Waste'. Add an interactive button labeled 'Start' that links to Slide 2. 3. Slide 2 (Key Initiatives): Add the title 'Key Initiatives'. Insert a table with 3 columns and 3 rows containing the following data: Column headers: Initiative, Target, Start Date. Row 2: Kerbside Food Waste, 500 households, October. Row 3: Plastic Free Schools, 12 schools, November. Format the table with a clean border and bold headers. Add two navigation buttons: 'Home' (links to Slide 1) and 'Next' (links to Slide 3). 4. Slide 3 (Get Involved): Add the title 'Get Involved'. Insert an image placeholder of an eco-friendly icon, adding alternative text 'GreenCycle Community'. Add two interactive buttons: 'Back' (links to Slide 2) and 'Sign Up' (links to an email address info@greencyclehub.org). Save your presentation as GREEN_PRES. Ensure all text is clearly legible with high contrast, and elements do not overlap.
Show answer & marking scheme

Worked solution

Step 1: Open presentation software and access Master Slide view. Format the master layout with a light grey background. Insert a dark green rectangle at the top to act as a banner. Insert a generic logo image in the top-right corner. Insert slide numbers and current date placeholders in the footer. Change the master title font to Arial, Bold, Dark Green. Close Slide Master view. Step 2: On Slide 1, enter title 'GreenCycle Community Action' and subtitle 'Our Path to Zero Waste'. Insert a shape (e.g., rounded rectangle), add text 'Start', right-click the shape, select Hyperlink, and link it to Slide 2. Step 3: Insert Slide 2. Change layout to title and content. Type title 'Key Initiatives'. Insert a 3x3 table. Type headers: Initiative, Target, Start Date. Enter data: Kerbside Food Waste / 500 households / October in Row 2, and Plastic Free Schools / 12 schools / November in Row 3. Apply standard styling to headers. Add two navigation shapes: 'Home' (hyperlink to Slide 1) and 'Next' (hyperlink to Slide 3). Step 4: Insert Slide 3. Type title 'Get Involved'. Insert an image, right-click, select 'Edit Alt Text', and enter 'GreenCycle Community'. Add a shape labeled 'Back' (hyperlink to Slide 2) and another shape labeled 'Sign Up' (hyperlink to email address 'mailto:info@greencyclehub.org'). Save file as GREEN_PRES.

Marking scheme

Master Slide [5 marks]: - Consistent light grey background and dark green top banner [1 mark] - Logo placeholder in correct top-right position [1 mark] - Automated footer date added [1 mark] - Automated footer slide number added [1 mark] - Consistent title font (sans-serif, bold, dark green) [1 mark]. Slide 1 [3 marks]: - Accurate title and subtitle text [1 mark] - High contrast and professional layout without overlapping [1 mark] - Interactive 'Start' button successfully linked to Slide 2 [1 mark]. Slide 2 [4 marks]: - Correct slide title 'Key Initiatives' [1 mark] - Correct table data entered with 3 columns and headers [1 mark] - Table styled professionally (bold headers and borders) [1 mark] - 'Home' and 'Next' navigation buttons working and linking to Slide 1 and Slide 3 [1 mark]. Slide 3 [4 marks]: - Correct slide title 'Get Involved' [1 mark] - Image placeholder with alternative text 'GreenCycle Community' [1 mark] - Navigation button 'Back' linking to Slide 2 and 'Sign Up' linking to email mailto:info@greencyclehub.org [1 mark] - Saved with filename GREEN_PRES, fit for purpose, professional aesthetics [1 mark].
Question 4 · Spreadsheet Modeling and Analysis
33 marks
The Adventure Sports Centre (ASC) organizes outdoor adventure sessions. You have been asked to help them analyze their bookings and instructor costs for the first quarter. Open the spreadsheet BOOKINGS. It contains two worksheets: 'Booking_Data' and 'Instructor_Rates'.

**Booking_Data** has the following structure starting in row 2:
* Column A: Booking ID (B001 to B010)
* Column B: Activity (Kayaking, Climbing, Archery, etc.)
* Column C: Group Size
* Column D: Hours Worked
* Column E: Instructor Code
* Column F: Hourly Rate
* Column G: Total Cost
* Column H: Discounted Cost

**Instructor_Rates** contains:
* Column A: Instructor Code (INS1, INS2, INS3)
* Column B: Hourly Rate ($25.00, $30.00, $20.00)

Complete the following tasks:

### Task SS1: Formatting and Presentation (6 marks)
1. Insert a new row at the very top of the 'Booking_Data' sheet.
2. Merge the cells from A1 to H1, and enter the title: 'Adventure Sports Centre - Q1 Bookings Analysis'. Format the title to be bold, 16pt, with a light blue fill color.
3. Format the column headings in row 2 to be bold, horizontally centered, and with text wrapping enabled.
4. Format all monetary values in columns F, G, and H to show US Dollar currency ($) with 2 decimal places.

### Task SS2: Formulas and Functions (15 marks)
1. In cell F3, enter a formula using a lookup function and the 'Instructor_Rates' sheet to retrieve the Hourly Rate based on the Instructor Code in cell E3. Ensure the formula can be replicated downwards for all bookings. (4 marks)
2. In cell G3, enter a formula to calculate the Total Cost by multiplying the Hours Worked by the Hourly Rate. Replicate this for all bookings. (2 marks)
3. In cell H3, enter a formula using an IF function to calculate the Discounted Cost. If the Group Size (Column C) is greater than 10, apply a 10% discount to the Total Cost; otherwise, the Discounted Cost is equal to the Total Cost. Replicate this for all bookings. (4 marks)
4. In cell H15, calculate the Total Revenue (sum of all Discounted Costs). (1 mark)
5. In cell C15, calculate the Average Group Size. (1 mark)
6. In cell D15, calculate the Maximum Hours Worked. (1 mark)
7. In cell H17, enter a formula using a function to count the number of bookings where the Activity is 'Kayaking'. (2 marks)

### Task SS3: Sorting and Filtering (4 marks)
1. Sort the bookings in alphabetical order of Activity, and then as a secondary sort, in descending order of Discounted Cost. (2 marks)
2. Apply a filter to display only the bookings where a discount was applied (i.e., Group Size is greater than 10). (2 marks)

### Task SS4: Chart Creation (8 marks)
1. Create a 2D column chart to display the Total Cost for each Booking ID (using data from the unfiltered table). Ensure only Booking ID and Total Cost are represented on the chart.
2. Set the chart title to 'Total Cost per Booking ID'.
3. Set the X-axis title to 'Booking ID' and the Y-axis title to 'Cost ($)'.
4. Remove the legend.
5. Move the chart to a new sheet named 'Cost_Chart'.
Show answer & marking scheme

Worked solution

### Detailed Step-by-Step Solution:

#### Task SS1:
1. Right-click row 1 and select 'Insert' to add a row. In the new cell A1, type 'Adventure Sports Centre - Q1 Bookings Analysis'. Select A1:H1, click 'Merge & Center', format text as bold, size 16pt, and apply a light blue fill.
2. Select A2:H2, click the 'Bold' icon, set alignment to 'Center', and click 'Wrap Text'.
3. Select columns F, G, and H, and format them as Currency with the symbol '$' and 2 decimal places.

#### Task SS2:
1. In cell F3, enter: `=VLOOKUP(E3, Instructor_Rates!$A$2:$B$4, 2, FALSE)`. Drag this formula down to cell F12.
2. In cell G3, enter: `=D3*F3`. Drag this formula down to cell G12.
3. In cell H3, enter: `=IF(C3>10, G3*0.9, G3)` or `=IF(C3>10, G3-(G3*10%), G3)`. Drag this formula down to cell H12.
4. In cell H15, enter: `=SUM(H3:H12)`. The sum evaluates to $801.00.
5. In cell C15, enter: `=AVERAGE(C3:C12)`. The average group size is 9.9.
6. In cell D15, enter: `=MAX(D3:D12)`. The maximum hours worked is 5.
7. In cell H17, enter: `=COUNTIF(B3:B12, "Kayaking")`. The count is 4.

#### Task SS3:
1. Select range A2:H12. Go to Data > Sort. Set Column B (Activity) as Sort by (A to Z), click 'Add Level', and set Column H (Discounted Cost) as Then by (Largest to Smallest).
2. Select row 2, click 'Filter'. Click the filter arrow on Column C (Group Size), choose Number Filters > Greater Than, and enter 10.

#### Task SS4:
1. Highlight range A2:A12, hold Ctrl, and highlight G2:G12. Click Insert > Recommended Charts > 2D Column Chart.
2. Click on the Chart Title and change it to 'Total Cost per Booking ID'.
3. Add Axis Titles: set horizontal axis to 'Booking ID' and vertical axis to 'Cost ($)'.
4. Select the Legend and delete it.
5. Right-click the chart, choose Move Chart, select 'New sheet', and name it 'Cost_Chart'.

Marking scheme

**Task SS1: Formatting (Max 6 marks)**
* 1 mark for inserting a row and merging A1:H1.
* 1 mark for correct formatting of the title (bold, 16pt, light blue fill).
* 2 marks for row 2 headers (1 mark for bold/centered, 1 mark for wrap text).
* 2 marks for formatting columns F, G, and H to currency with 2 decimal places ($) (1 mark for currency symbol, 1 mark for 2 decimal places).

**Task SS2: Formulas and Functions (Max 15 marks)**
* 4 marks for the lookup formula in F3:
* 1 mark for using VLOOKUP or XLOOKUP.
* 1 mark for looking up E3.
* 1 mark for referencing the correct sheet and absolute range `Instructor_Rates!$A$2:$B$4`.
* 1 mark for correctly retrieving column 2 and exact match parameter.
* 2 marks for total cost formula `=D3*F3` in G3 (1 mark for correct arithmetic, 1 mark for replicating down).
* 4 marks for IF formula in H3:
* 1 mark for correct IF syntax.
* 1 mark for logical test `C3>10`.
* 1 mark for applying 10% discount `G3*0.9` or `G3-G3*0.1`.
* 1 mark for returning unmodified value `G3` if false.
* 1 mark for `=SUM(H3:H12)` in H15.
* 1 mark for `=AVERAGE(C3:C12)` in C15.
* 1 mark for `=MAX(D3:D12)` in D15.
* 2 marks for `=COUNTIF(B3:B12, "Kayaking")` in H17 (1 mark for COUNTIF, 1 mark for correct criteria and range).

**Task SS3: Sorting and Filtering (Max 4 marks)**
* 2 marks for sorting (1 mark for correct primary sort by Activity, 1 mark for correct secondary sort by Discounted Cost descending).
* 2 marks for filtering (1 mark for enabling filters, 1 mark for correctly displaying only rows where Group Size > 10).

**Task SS4: Charting (Max 8 marks)**
* 2 marks for selecting the correct ranges (Booking ID and Total Cost columns).
* 2 marks for selecting a 2D Column Chart type.
* 2 marks for labels (1 mark for Title: 'Total Cost per Booking ID', 1 mark for correct X and Y axis titles).
* 1 mark for removing the legend.
* 1 mark for moving chart to a new sheet named 'Cost_Chart'.
Question 5 · DTP Leaflet and Word Processing
17 marks
**Activity 1: Word Processing / Desktop Publishing (DTP)**

Greenway Eco-Hub is holding an upcoming 'Eco-Fair 2024' event to promote sustainable living. You must produce a two-page newsletter flyer to be distributed to local residents.

**Task WP1**
Open a new document using word processing software.
  1. Set the page size to A4 and orientation to Landscape. Set all margins (top, bottom, left, right) to 2 cm. [2 marks]
  2. Format the entire document so that it has two columns of equal width with a 1 cm spacing (gap) between them. [2 marks]
  3. Insert a header that has:
    - The text 'Greenway Eco-Hub News' left-aligned.
    - Your Name, Candidate Number, and Centre Number right-aligned.
    Insert a footer with automated page numbering centred. [3 marks]
  4. At the top of the first column, enter the heading: 'Eco-Fair 2024: Sustainable Living for Everyone!'
    Format this heading as:
    - Font style: Sans-serif (e.g., Arial)
    - Font size: 18 pt
    - Attributes: Bold, Centred
    - Shading: Light grey background fill (approx. 15-20% tint). [3 marks]
  5. Copy the text from the source file 'FAIR_TEXT.txt' and paste it below the main heading. Format all the body text as:
    - Font style: Serif (e.g., Times New Roman)
    - Font size: 11 pt
    - Alignment: Justified
    - Line spacing: 1.15 lines. [2 marks]
  6. Insert the image 'green_leaf.png' from your source folder into the second column. Position the image at the bottom of the second column, wrapped tightly with text. Format the image so that it is exactly 5 cm wide and retains its original aspect ratio. [3 marks]
  7. Save the active document with the filename 'ECO_FAIR_NEWS' as a word processing format. Export/Save a copy of this completed document as a PDF file named 'ECO_FAIR_NEWS_FINAL.pdf'. [2 marks]
Show answer & marking scheme

Worked solution

To complete the practical task:

  1. Open a blank document in MS Word or another word processor. Go to Layout > Size and select A4. Go to Layout > Orientation and select Landscape. Go to Layout > Margins > Custom Margins and set Top, Bottom, Left, and Right margins to 2.0 cm.
  2. In the Layout tab, click Columns > More Columns. Select 'Two' columns, ensure 'Equal column width' is checked, and set the Spacing to 1.0 cm. Click OK.
  3. Go to Insert > Header > Blank (Three Columns). In the left-aligned section, type 'Greenway Eco-Hub News'. In the right-aligned section, type Candidate details (e.g., John Doe, 1234, GB999). Delete any unused middle section. Go to Insert > Footer > Page Number > Bottom of Page > Simple Number 2 (Centred) to insert automated page numbering.
  4. Type 'Eco-Fair 2024: Sustainable Living for Everyone!' at the top of column 1. Highlight the text, change font to Arial (Sans-serif), size to 18pt, make it Bold, and click the Center alignment button. In the Home tab, find the Shading bucket tool and select a light grey theme color.
  5. Open 'FAIR_TEXT.txt' from the source directory, copy the text, and paste it into column 1 under the heading. Select all the pasted body text. Set the font to Times New Roman (Serif), size to 11pt, select Justify alignment, and set Line Spacing to 1.15 in the Paragraph dialog.
  6. Click near the end of the text in column 2. Go to Insert > Pictures > This Device and select 'green_leaf.png'. Click on the image, open Picture Format, and change the Width to 5 cm (ensuring aspect ratio lock is enabled). Click Layout Options (or Wrap Text) and select 'Tight'. Position the image at the bottom of the column.
  7. Go to File > Save As, name the file 'ECO_FAIR_NEWS', and save as a Word Document (.docx). Go to File > Save As (or Export) and select PDF format, naming it 'ECO_FAIR_NEWS_FINAL.pdf'.

Marking scheme

Total Marks: 17

  • Page Setup (2 marks):
    • 1 mark for A4 size and Landscape orientation.
    • 1 mark for all margins set to 2 cm.
  • Columns (2 marks):
    • 1 mark for dividing the layout into two columns of equal width.
    • 1 mark for setting the gap/spacing between columns to exactly 1 cm.
  • Header and Footer (3 marks):
    • 1 mark for correct header content left-aligned ('Greenway Eco-Hub News') and candidate details right-aligned.
    • 1 mark for automated page numbering in the footer.
    • 1 mark for footer page number being centred.
  • Heading Formatting (3 marks):
    • 1 mark for typing the exact text and setting Sans-serif font (e.g., Arial), size 18pt, and Bold.
    • 1 mark for Centred alignment of the heading.
    • 1 mark for light grey shading behind the heading text.
  • Body Text Formatting (2 marks):
    • 1 mark for copying correct text and formatting with Serif font (e.g., Times New Roman) and size 11pt.
    • 1 mark for setting the alignment to Justified and line spacing to 1.15.
  • Image Insertion and Formatting (3 marks):
    • 1 mark for inserting 'green_leaf.png' in the second column.
    • 1 mark for setting the width to exactly 5 cm (maintaining aspect ratio).
    • 1 mark for setting wrapping style to Tight and positioning at the bottom of the column.
  • Saving and File Formats (2 marks):
    • 1 mark for saving as 'ECO_FAIR_NEWS' in word processing format.
    • 1 mark for exporting/saving as 'ECO_FAIR_NEWS_FINAL.pdf'.

Wondering how well you actually know this?

Thinka is an AI practice app for DSE students — unlimited questions, instant auto-marking, and detailed step-by-step solutions. 100,000+ students use it to confirm they actually know it, not just think they do.

Start Practising FreeSee pricing

Want more questions like this? Practice unlimited on Thinka — instant answers included.

Start Practising Free