PHS CyberSchool

Computer Studies SG Grade 11 - 2003

Modified 23 November 2003

Name .........................

November Practical examination - 2 hours

Only do what is required and nothing else.  Be sure to save on a regular basis.  If you have a technical problem call for help.  

Question 1 - 42 marks

All questions must be totally correct for the marks or given marks as indicated.

The spreadsheet contains details of properties sold in the Cape Town area advertised in the Cape Argus on 1 November 2003. Copy the file 0311ssnovprac.xls and save in your login as YoursurnameFirstname. 

  1. To the worksheet add a greenish background. (1)
  2. Merge the heading "Properties advertised in Cape Argus 1 November 2003" across the selection area, as illustrated below. (1) 
  3. Format the column headings as illustrated below, centred horizontally and vertically. (2) 1 each
Properties advertised in Cape Argus 1 November 2003 
Name Correct Name Price 

Comment 

 First Name  Surname  Full name  Town  Postal code 
  1. In Column B use a formula to change the capitalisation of all the companies in Column A into a format where each word begins with a capital letter, illustrated in B3. (1) 
  2. Freeze the rows and columns so that the headings are visible, and the name of the agencies. (2) 1 each
  3. In Column D use a formula to add a comment to all houses that cost R500000 or less, "Possible". Make sure that there is no comment and nothing in the cells where the cost is more than R500000. (2) 
  4. In Column G use a formula to join the agents' first names and surnames, illustrated in G3. (2) 1 off if no space between first and surnames
  5. Use conditional formatting to change the background colour of the town to red of all properties that cost less than R500001. (3) 
  6. In Column I use a formula and the data from the Postal codes worksheet to add the Postal code which matches each of the towns. (2) 
  7. In A43 insert a Comment "The bond rate has dropped to 12%" with your name as the author. It should look similar to the illustration below. (2)  1 for author's name bold, and 1 rest of message

  1. Create a pivot table showing the total value (currency) of the properties for sale in the different towns (1), with Tokai excluded (1). Put the table on a new sheet. Rename the sheet 'Value per town'. (1) It should look similar to the table below. 
Xxxxxxxx   
xxxx Total
Kenilworth R 2,100,000.00
Pinelands R 24,362,000.00
Sea Point R 825,000.00
Thornton R 199,500.00
Grand Total R 27,512,000.00
  1. Create a pivot table showing the average price (Currency) of the properties for sale in the different towns (3). Put the table on a new sheet. Rename the sheet 'Average price per town'. It should look similar to the table below. 
Average of Price  
Town  Total
Kenilworth R 2,100,000
Pinelands R 974,480
Sea Point R 825,000
Thornton R 225,000
Tokai R 1,995,000
Grand Total R 1,017,483

        

  1. On the spreadsheet there are a number of questions. Use formulaes to find the answers. Fill in the answers in Column E next to the questions. 
    1. Total price of all the properties (1) 
    2. Highest price of all the properties (1) 
    3. Lowest price of all the properties (1) 
    4. Average price of all the properties (1) 
    5. Selling price of 6th most expensive property (1) 
    6. Number of properties listed (1) 
    7. Number of Pam Golding properties (2) 
    8. Total price of all Pam Golding properties (2) 
    9. Total price of all Pinelands properties (2) 
    10. Monthly repayments of a bond of R500 000 at interest rate of 12% over 20 years (3) 
    11. Value in 20 years of monthly savings of R5000 at 12% (3) 

Question 2 - 35 marks

Copy the web Pinelands or zipped from Pupils / CSSG / Examold.  Rename the web as YoursurnameFirstnamePinelands.  Save your work on a regular basis.

In FrontPage open the web and complete the following tasks based on the specified files. 

Instruction Mark Mark

Index.htm - Under the title add a marquee with the words 'Pinelands - the Garden City of South Africa' with the text yellow and the background green.

 2  
Index.htm - To the marquee add a small picture of the Pinelands High School badge (no other picture and it must be small) 2  

Index.htm - Add a hyperlink to a web site from which this extract was taken.  Hyperlink the words 'History of Pinelands' to the URL home.mweb.co.za/dv/dvdmerw/history_of_pinelands.htm

 1  

Index.htm - In the image, on a road area bottom left, make a large hotspot to hyperlink to the file roads_and_transport.htm

 1  

Index.htm - In the image, on a large area of houses top left, add a hotspot to hyperlink to the file guesthouse.htm

 1  
Index.htm - Copy the PowerPoint file 0311pr or zipped to your folder and rename it YoursurnameFirstnamePinelands.  (Command buttons do not work on a web, but do not worry.)  At the bottom of the page add the words 'View presentation' and hyperlink to the presentation.  The PowerPoint must show as a web page (s). 2  
Index.htm - The page is boring.  Add a dynamic effect to the body text. 1  

Guest_house.htm - Remove borders from the table

 1  

Guest_house.htm - Add a mailto to the words 'Ron Strybis.'  His address is strybis@netactive.co.za

 1  

Guest_house.htm - Position the South African welcome logo on the right hand side of the text.

 1  

Guest_house.htm - Add a clipart background.  It must be gentle, light in colour and not a picture.

 2  

Roads_and_transport.htm -  To the picture Nice car today / Old car in the 1950s, add a mouseover with the BPGarage.jpg picture found in the Images folder

 1  

Roads_and_transport.htm - Make sure that the picture Nice car today / Old car in the 1950s is accessible to a blind person with the words 'Modern expensive car / Car from the 50s'

 1  

Roads_and_transport.htm - To the picture Wide residential roads, add a wide (10) red double border.

 2  

Roads_and_transport.htm - The picture Busy intersection, is too dark.  Brighten the picture.

 1  

Residential_area.htm - Merge the centre column of the table

 1  

Residential_area.htm - Make the table just big enough.  (Do not put a large shoe on a small foot.)

 1  

Residential_area.htm - Insert a new table with one row and two columns.  Insert the image thatch.jpg into one cell as a thumbnail and the image house.jpg into the other cell as a thumbnail. 

 3  
Graph ... - Add a graph showing property prices of the different agents, see data  below.  The graph must change as data is changed (4).  
  • Pam Golding - R740 000
  • Jenni Pike - R729 000
  • Era Steer - R865 000
  • Aida - R650 000

Make sure the graph complies with the following.

  • Bar graph (1)

  • Chart title 'Prices'  (1)

  • Remove any legend  (1)

  • Units on the Y axis currency as Rand  (1)

  • Names of all the agencies showing in any way (1)

 10  

Total

 35  

 

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