Scribd Logo
Upload

Welcome to Scribd!

  • 134 views

    9626 m18 QP 02 PDF

    Uploaded by

    Ahmed Ziad

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    9626 m18 QP 02 PDF

    Uploaded by

    Ahmed Ziad
    134 views8 pages

    Original Title

    9626_m18_qp_02.pdf

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    134 views8 pages

    9626 m18 QP 02 PDF

    Uploaded by

    Ahmed Ziad

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 8

    Cambridge International Examinations

    Cambridge International Advanced Subsidiary and Advanced Level

    INFORMATION TECHNOLOGY 9626/02


    Paper 2 Practical February/March 2018
    2 hours 30 minutes
    Additional Materials: Candidate Source Files:
    M18Bus.csv
    * 0 0 0 9 2 4 2 3 2 2 *

    M18Destination.csv
    M18Driver.csv
    M18Seats.csv

    READ THESE INSTRUCTIONS FIRST

    DO NOT WRITE IN ANY BARCODES.

    Carry out every instruction in each task.


    Save your work using the file name given in the task as and when instructed.

    The number of marks is given in brackets [ ] at the end of each task or part task.

    Any businesses described in this paper are entirely fictitious.

    You must not have access to either the internet or any email system during this examination.

    You must save your work in the correct file format as stated in the tasks. If work is saved in an incorrect file
    format, you will not receive marks for that task.

    This document consists of 5 printed pages and 3 blank pages.

    DC (SC) 145169/3
    © UCLES 2018 [Turn over
    2

    You work for Tawara International Airport and will analyse data about bus journeys taken each morning
    during one week.
    Dates are to be displayed in dd/mm/yyyy format. Unless specified otherwise, all times are to be
    displayed in hh:mm format and are set to GMT (Greenwich Mean Time).
    Unless specified otherwise, do not edit, delete or sort any data in the source files.
    You must use the most efficient method to solve each task. All work must be of a professional standard
    and suit the business context.

    You are required to provide evidence of your work, including screen shots at various stages. Create an
    Evidence Document named:

    M18_Centre number_candidate number

    e.g. M18_ZZ999_9999

    Place your name, Centre number and candidate number in the header of your Evidence Document.

    1 Open a new spreadsheet.

    Place these files as separate


    Ensure these worksheets are called:
    worksheets within your workbook
    M18Bus.csv Bus
    M18Destination.csv Dest
    M18Driver.csv Driver
    M18Seats.csv Seats

    Examine the data in these worksheets.

    Analyse the contents of the worksheet named Dest


    Identify any errors and describe these in your Evidence Document. Add an evaluation of the
    potential impact of these errors.

    Evidence 1

    A description of the errors and your evaluation of their impact.

    Correct the errors you have identified.

    Evidence 2

    Save this spreadsheet as Q1_ followed by your Centre number_ then your candidate number,
    for example: Q1_ZZ999_9999
    [6]

    © UCLES 2018 9626/02/F/M/18


    3

    2 In the worksheet Bus insert a new row between rows 1 and 2.

    Enter the text:

    • Destination into cell A2


    • Mean delay into cell B2

    Make only the contents of rows 1, 2, 12 and 13 centre aligned and bold. [4]

    3 For each journey use formulae in the:

    • Date column to extract the date from the Bus Code column. For example: if the first 6 digits
    are 140118 the date would be 14 January 2018
    • Destination column to extract the name of the destination using the single letter embedded in
    the Bus Code column
    • Late column to calculate the number of minutes the bus was late. If the bus was early this
    should return 00:00
    • Minutes column to calculate the minutes the bus was late as an integer value
    • Passengers column to extract the 9th character of the Bus Code and look up the maximum
    number of seats on the bus for this journey. [35]

    4 In the Mean delay column use functions to calculate the average delay to each destination. Ensure
    that any errors are trapped and not displayed. Display the Mean delay in hh:mm:ss format.

    Evidence 3

    Save this spreadsheet as Q4_ followed by your Centre number_ then your candidate number,
    for example: Q4_ZZ999_9999
    [14]

    5 Create and fully label a chart to compare the average delays to each destination for the whole
    week. [5]

    6 Create a pivot table to show, for each destination, the total number of minutes late on each day.
    This must display the totals for each day but not the totals for each destination. Gridlines must be
    visible.

    Create a report for the manager of the airport, which includes:

    • your pivot table


    • your chart.

    The report must:

    • fit on a single portrait page


    • include in the footer your name, Centre number and candidate number. [11]

    7 Export this report in portable document format.

    Evidence 4

    Save this document as Q7_ followed by your Centre number_ then your candidate number, for
    example: Q7_ZZ999_9999.pdf
    [1]

    © UCLES 2018 9626/02/F/M/18 [Turn over


    4

    Your manager wants a report on the drivers who have the fewest delayed journeys per week.

    8 Insert in the Bus worksheet the Driver ID and full name of each driver. The Driver ID for each
    journey is stored as the 12th and 13th digits in the Bus Code. The names of the drivers can be
    found in the Driver worksheet.

    Copy a list of the Driver IDs and names into a new worksheet called Report. For each driver
    calculate the number of journeys that were on time or early.

    Create a report listing all the drivers who had more than one journey on time or early.

    The report will be saved in rich text format and must be in ascending order of the number of
    journeys. It must be set out like this:

    Your title with your candidate details


    Driver Number of journeys

    Karl Roth 3

    Holly Jenkinson 3

    Note that the numbers shown are examples; they are not correct.

    At the end of the report add the text:

    The name of this week’s best driver is:


    followed by the driver with the most journeys that arrived on time or early.

    Evidence 5

    Save this spreadsheet as Q8_ followed by your Centre number_ then your candidate number,
    for example: Q8_ZZ999_9999

    Evidence 6

    Save the report as Driver_ followed by your Centre number_ then your candidate number, for
    example: Driver_ZZ999_9999.rtf
    [28]

    © UCLES 2018 9626/02/F/M/18


    5

    9 The following was entered in a spreadsheet:

    =IF(F4*RANDBETWEEN(1,10)>60,”WIN”,”LOSE”)

    John said this was a formula and Stuart said it was a function. Explain, using examples from the
    above, who is correct.

    Evidence 7

    Place your explanation in your Evidence Document.


    [6]

    10 Save your Evidence Document.

    © UCLES 2018 9626/02/F/M/18


    6

    BLANK PAGE

    © UCLES 2018 9626/02/F/M/18


    7

    BLANK PAGE

    © UCLES 2018 9626/02/F/M/18


    8

    BLANK PAGE

    Permission to reproduce items where third-party owned material protected by copyright is included has been sought and cleared where possible. Every
    reasonable effort has been made by the publisher (UCLES) to trace copyright holders, but if any items requiring clearance have unwittingly been included, the
    publisher will be pleased to make amends at the earliest possible opportunity.

    To avoid the issue of disclosure of answer-related information to candidates, all copyright acknowledgements are reproduced online in the Cambridge International
    Examinations Copyright Acknowledgements Booklet. This is produced for each series of examinations and is freely available to download at www.cie.org.uk after
    the live examination series.

    Cambridge International Examinations is part of the Cambridge Assessment Group. Cambridge Assessment is the brand name of University of Cambridge Local
    Examinations Syndicate (UCLES), which is itself a department of the University of Cambridge.

    © UCLES 2018 9626/02/F/M/18

    You might also like