Pinelands High School

Computer Studies SG - November 2006 - Grade 11

Last updated 05 Jan 2007

Practical examination - 3 hours + 15 mins read = 3¼ hours

The grade 11 learners and a few adults annually attend the Klein Karoo National Arts Festival. The tour leader has asked for your assistance with the planning of the excursion. The planning is done for ten days and nine nights

Question 1 - Spreadsheet - 32 marks

Certain calculations must be done on a spreadsheet. Open the spreadsheet QU1 and save it as YourSurnameFirstNameQU1. 

  1. Insert your surname (left) and the question number (right) in a page header. 
  2. An example of the final spreadsheet appears in the Appendix. You may use the answers but will only be marked on the use of formulas.

Changes - 3 marks

Now bring about the following changes:

  1. Column width: Wide enough for the data to be seen on a small screen. (1)
  2. Wrap text of all the sub headings, bold and centred vertically as well as horizontally. (1)
  3. Type the following in the relevant cells as in the example: (All cells with ? must get data)

Accommodation - 3 marks

In column B it is indicated the maximum number of persons that can be accommodated in each type of accommodation. For each type there is a fixed amount payable per night (column C) plus a further amount per night per person (column D).

  1. F4: Make use of a formula and absolute cell reference to calculate the cost for 20 persons for one night in the tents
  2. F5 to F7: Copy this formula to cells. (1)
  3. G4 and H4: Do the same for 40 and 60 persons and copy the formula again. Change it where necessary. (1)
  4. Change the colour to red (bold) for the best option for accommodation for each size group. (1)

Transport - 6 marks

A choice must be made between three different sizes of buses. In column B it is indicated the maximum number of passengers that can be transported in each type. For each type a fixed amount is payable for the hire of the bus (column C) plus a further amount per kilometre (column D).

  1. F10: Make use of a function and absolute cell reference to calculate the cost if 20 persons must be transported by kombi.
  2. Copy the formula to cells G10 and H10. Change it where necessary. (1)
  3. F11 and F12: Do the same for the larger vehicles and copy the formula to cells G11 to H12. Change it where necessary. (1)
  4. Change the colour to red (bold) again for the best option for transport for each size group. (1)

Food - 6 marks

Accommodation does not include meals. There are two options: Buy food every day or Supply it. The estimated amounts per meal for each option are given in columns C and D.

  1. F15: Make use of a function and absolute cell reference to calculate the cost to supply food for the group of 20.
  2. Copy the formula for 40 and 60 persons. Change it where necessary. (1)
  3. F16: Do the same to calculate the cost if the group must buy food every day. (1)
  4. Change the colour to red (bold) again for the best option for meals for each size group. (1)

Recreation - 5 marks

  1. The group will get the opportunity to attend four different productions. The prices for single tickets are given.
  2. F19: Make use of a nested IF-statement and absolute cell reference to do the following calculation: Discount is given for group reservations according to the following criteria: i.e. If the group is larger than 50, Multiply the total cost by the number of persons and then by 0.9 Otherwise if the group is still larger than 25, multiply by the number of persons and then by 0.95 Otherwise multiply by the number of persons. (3)
  3. Copy the formula to G19 and H19. Change it where necessary. (1)
  4. Change the colour to red (bold) in cells F19 to H19. (1)

Final calculations - 5 marks

  1. F21 to H21: Make use of formula to add all the best options for each size group. (1)
  2. F22 to H22: Make use of a formula to calculate the cost per person for each size group. (1)
  3. Change the colour to red (bold) for the ideal group size. (1)
  4. All amounts must be formatted as currency (R). (1)
  5. Show gridlines for cells A2 to H22 as well as F2 to H2 as in the example. (1)

Summary - 4 marks

Make a summary of the best option expenses for a group of 40 at the bottom of the spreadsheet. 

  1. Copy the relevant amounts to cells B24 to B27 so that the contents will automatically be corrected if any changes are made to the existing spreadsheet. (1)
  2. Create a pie chart in which it is indicated what portion of the expenses for this group goes to each commodity.
    1. Chart heading: Expenses for group of 40 (1)
    2. No legend must be shown. (1)
    3. Labels as well as percentages must be shown on the chart. (1)

    [32]

Question 2 Database - 22 marks

Open the database KKK. 

Make the following queries from the table Details - 12 marks

  1. Paid less than R500 
    1. Show only the records of learners who at this stage have paid in less than R500. 
    2. Include and show the following fields: Name and Amount_in 
    3. Sort  the records in alphabetical order according to the names of the learners. 
    4. Save the query as 1 Less than R500 (2) 
  2. Indemnity form 
    1. Show the names of learners who have not yet submitted an indemnity form. 
    2. Save the query as 2 No form (2) 
  3. Special request 
    1. Show the names of learners with some or other special request.
    2. Save the query as 3 Request. (2) 
  4. Outstanding 
    1. Create a query to show the name Name, Amount_Total and Amount_in and the Amount_Outstanding  
    2. Show only the names and outstanding amount of all those who still owe something. 
    3. Save the query as 4 Outstanding (2) 
  5. Age 
    1. Create a query which will calculate the age of the learners. 
    2. Show only the name of the learners, date of birth and age. 
    3. Save the query as 5 Age (2) 
  6. Forms in 
    1. Create a query to show the numbers of forms in or not returned. 
    2. Show the totals returned or not returned. 
    3. Save the query as 6 Form summary (2) 

Report - 10 marks

Create a report using the table Details. 

  1. The following fields must be included in the report: - Medical_name, Name and Amount_in.. 
  2. Group the report according to the name of the medical aid and sort the records alphabetically according to the names of the learners in each group. (2) 
  3. Add a summary for each group indicating how many people belong to each medical aid. (2) 
  4. At the end of the report the following summary must appear: 
    1. Number of persons in the touring group. (2)
    2. Total amount paid in by the whole group. (In Rand and cent) (2) 
    3. Average amount paid in by each learner. (In Rand and cent) (2) 
  5. Save the report as Details. 

[22]

Question 3 - Integration - 6 marks

Open the file QU3. Save the files as YourSurnameFirstNameQU3. 

  1. Copy only the summary of the spreadsheet QU1 to the location as indicated in the example. (2) 
  2. Include the correct fields from the table Details in the database KKK and create form letters for all the learners going on tour. Save the file. (2) 
  3. Merge your letter and save it as YourSurnameFirstNameQU3Merged (2) 

[6]

Question 4 - Spreadsheet - 12 marks

Some people wish to go to Oudtshoorn as a family to a hotel and not in a group.  An investigation into the prices of hotels in Oudtshoorn was done, but not completed. The spreadsheet Expensive has been supplied to you. The file consists of two worksheets, i.e. Hotels and Calculation. Save the file QU4 as YourSurnameFirstNameQU4

  1. Hotels worksheet
    1. Use a function to determine the price for children per night. Hotels graded 4 stars or less charge 10% of the price for adults, for a child per night. In hotels graded more than 4 stars, the children can stay for free. Save the value as Currency. (2)
  2. Calculation worksheet
    1. Cell C3: Use a function to choose any hotel from the list in the Hotels worksheet. (2) 
    2. Cell C5: Use a function to obtain the price for an adult from the Hotels worksheet. (2) 
    3. Cell C7: Use a function to obtain the price for a child from the Hotels worksheet. (2) 
    4. Cell C12: Enter a formula to calculate the total amount for any number of people for any number of nights in any hotel. (2) 

    5. Test your answer if the family chooses the Country Estate hotel, for 6 adults, and 2 children. They would like to stay for 3 nights. (2) 

[12]

Question 5 - Web - 18 marks

When the learners are at the Festival they are not allowed to have cell phones.  If parents want to communicate with them they have to complete a form on Netfirms.  The form is submitted to the tour leader's email.  The tour leader has a feature on her cell phone that when she receives an email she is notified, and goes to an Internet cafe to check her mail or checks it via her cell phone. Create the site in preparation for the tour.

  1. Create a web space on Netfirms called YourSurnameFirstNameTour.
  2. The site will be marked on Netfirms and not the exam login.
  3. Create a web called YourSurnameFirstNameTour with two pages - Index and Response
  4. In the blue heading area at the top of BOTH pages, the name of the page must clearly show, Index and Response. (1)
  5. Insert a feature that will indicate when the page was last modified on BOTH pages.(1)
  6. Index page
    1. Insert the image cactxtr as a background image. (1)
    2. Add a heading saying Klein Karoo National Arts Festival - Futurelearn School. Copy the following text and add it to the Index page. (1)

Parents - The learners are not permitted to walk around the Festival with cell phones. If you wish to communicate with them you must complete the Response form.  Your request will be attended to within 2 hours of submitting it.  

Note the picture of the staff of our school.  The responsible teacher is clearly marked on the page.

  1. Insert the image 2006ACE which MUST show when it is on Netfirms. It must be clear and fill at least half the page.  It must not be a thumbnail. (2)
  2. On the picture, under any face, write your name bright and large. (1)
  3. Make sure that a visually impaired person can 'see' the picture.  (1)
  4. Near the bottom of the page add a feature showing today's date which must change so that the current date always shows. (1)
  5. At the bottom of the page add a link so they can email you with comments. (Add your real email address.) (1)
  6. Create a link from the Index page to the Response page. (1)
  1. Response page
    1. When parents complete the page and it is submitted, it must go to the mailbox of docpam@gmail.com so it can be seen and marked.
    2. On the Response page 
      1. add form features so you can collect the Parents' name, Parents' email address, Cell number, Learner's name and Area of concern. Make sure that when the email is submitted to Docpam it should be clear what each entry is about.  (5)
      2. add drop down options indicating what action to take such as Options to Email home, Phone home, Read email. Make sure that when the email is submitted to Docpam it should be clear what each entry is about.  (1)
  2. Upload your web to Netfirms so that the parents can see it.   In pencil add the URL of the page: ................................. 
  3. Zip your web and save it in your exam login to keep as evidence and to check that you can use WinZip. (1)

[18]

Question 6 - Word processing - 20 marks

The secretary had to type the indemnity form. Unfortunately she made a mistake and saved it as a text file. You are asked to help format it to appear as the example again.   

Retrieve the text file QU6. Change the filename to YourSurnameFirstNameQU6 and save it as a WORD PROCESSING document. Now bring about the following changes as indicated on the attached Appendix. 

  1. Page size: A4 (1) Margins: Top, bottom, left and right: 2.5 cm (1) 
  2. Font: For the whole document: Arial 12 pt (until other instructions) (1) 
  3. Alignment: Justified (1) 
  4. Single line spacing (until other instructions) (spacing before and after paragraphs).  (1) 
  5. Type your exam number or surname left and the question number right in the page header. (1) 
  6. Centre the main heading in capital letters and 18pt (Name of the school). (1) 
  7. Centre the sub heading in capital letters and 16 pt (Indemnity form). (1) 
  8. Place a border around this section as in the example. (1) 
  9. Replace all occurrences of "parent" with "parent/guardian" and "course" with "tour". (1) 
  10. Set your own tabs with leaders to form the lines. (1) 
  11. Set your own tabs above the words SIGNATURE OF PARENT/GUARDIAN and DATE as in the example. (3) 
  12. Change the section (Mention here ......) to 11 pt and italics. (1) 
  13. Make use of numbered bullets at the bottom section of the document. Position of the bullet at 0.5 cm and text to follow at 1.5 cm. (1) 
  14. Align the section on the medical aid and text that follows indented at 1.5 cm. (1) 
  15. Add the last paragraph by copying it from the text file INSERT_TEXT. (1) 
  16. The whole new paragraph is in Arial 11 pt, justified and italics. (1) 
  17. Change the line spacing to 1.5 for the sections, which have to be filled in. (1) 
  18. Everything must still fit onto one page. (1) 
  19. Save the final version.

[20] 

TOTAL [110]

This work may be freely copied for non-commercial use only. Copyright © Pam Miller.