KCSE 2017 Computer Studies Paper 2 with Marking scheme

1. Mavuno Group of hotels offer accommodation services to clients. The accommodation rooms are categorised as single, double or VIP, each attracting different rates. The rooms with fridges stocked with drinks attract an extra cost. The management of the hotel intends to use a spreadsheet program to compute the revenue from the rooms.
   
   1. Open the spreadsheet program and create a worksheet to appear as shown in Figure 1. Save the workbook as room charges.    (15 marks)

      	A	B	C	D	E	F	G	H
      1	SERVICE COST PER DAY
      2	Single (S)	1500
      3	Double (D)	2800
      4	VIP (V)	3200
      5	Friodge(F)	300
      6
      7	Room Id	Guest Id	Days	Room             status	Fridge availability	Room charges	Fridge Charges	Total charges
      8	363	RM001	3	D	Yes
      9	103	RM002	1	D	Yes
      10		RM003	1	S	No
      11		RM004	4	D	No
      12		RM005	5	D	Yes
      13		RM006	1	S	No
      14		RM007	4	D	Yes
      15		RM008	3	D	Yes
      16		RM009	3	V	Yes
      17		RM0010	1	V	Yes
      18		RM0011	1	D	Yes
      19		RM0012	4	S	No
      20		RM0013	5	D	Yes
      21		RM0014	2	D	Yes

                                                                           Figure 1
   2. Name the cell containing the value; 1500 as SR, the cell containing 2800 as DR, the cell with 3200 as VP and the cell with 300 as FR.   (4 marks)
   3.  
      1. In the column with title Room Charges, enter a formula that can be copied down the column to multiply the value in days by SR if the room status value is Sor multiply the value in days by DR if the room status value is D or multiply the value in days by VP if the room status value is V.  (8 marks)
      2. In the column with the title Fridge Charges enter a formula that can be copied down the column to compute Fridge Charges.
         (3 marks)
      3. In the column with the title Total Charges, enter a formula that computes the total of the Room Charges and Fridge Charges for each guest. (2 marks)
   4. Format the Room Charges, Fridge Charges and Total Charges values as currency with zero number of decimal places.  (2 marks)
   5.  
      1. Copy all the contents of the current work sheet to a new worksheet  (1 mark)
      2. Name the initial worksheet as ORIGINAL and the copied worksheet as NEW  (2 marks)
   6.  
      1. In the sheet named NEW, extract only the records whose ROOM STATUS is S.  (2 marks)
      2. Create a column bar chart that compares the Room Charges and Fridge Charges for guests whose Guest Id are RM003, RM006 and RM012.   (4 marks)
      3. Insert the following labels in the chart created in (ii)
         ChartTitle :  Single Room Revenue
         X-axis : GuestID
         Y-axis : Revenue in Ksh.   (3 marks)
      4. Rename the chart sheet as SREVENUE.  (1 mark)
   7. Printout later each of the following:
      1. ORIGINAL Worksheet   (1 mark)
      2. NEW Worksheet   (1 mark)
      3. SREVENUE Chart  (1 mark)
2. The management of a county scout movement intends to award certificates of participation to the scouts who attended a fire rescue seminar. Assuming that you have been tasked to design the certificates.
   1. Open a Desktop Publishing program and make the following page settings.  (4 marks)
      1. Orientation : landscape
      2. Units : centimetres
      3. Papersize : A4
      4. Margins : 2cm all around
   2. Create the certificate as it appears in Figure 2. Save the design as Certificate.  (45marks)
   3. Printout the certificate later. (1 mark)
      

MARKING SCHEME

1.  
   1. Typing values in the cells
      • Values in cell range Al: B6 @1
      • Margin cells A1:B1 @ 1
      • Text wrap in the titleA1:31 @ 1
      • Typing column 1(range A9: A22) @ 1
      • Typing column 2 (range B9: B22) @ 2
      • Typing column 3(range 09: C22) @ 1
      • Typing column 4(range D9: D22) @ 1 
      • Typing column 5 (range E9: E22) @ 1
      •  Saving the workbook @1
        Column title text (row 8)
      • Typing column title text (correct, bolded and completeness-A8. H8) @ 2
      • Wrapping titles @1
      • Applying bold face @ 1
      • Applying borders to all the visible cells @ 1    (15 marks)
   2. Naming the cells containing:  
      • 1500 as SR @ 1 
      • 2800 as DR @1
      • 3200 as VP @ 1
      • 300 as FR @1    (4 marks)
   3.  
      1. =If (D9 = "S", C9* SR, if (D9="D", C9* DR, if (D9 = "V", C9*VP)))
         Use of the IF function @ 1
         S selection @2
         D selection @2
         V selection (else) @ 2
         Logic and syntax@1     (8 marks)
      2. =f(E7 = "Yes", FR*C7,0)
         • Use of the function @1 
         • Selection of fridge @ 1
         • Alternative selection @ 1 (3 marks)
      3.  
         • =G7*H7 @ 1
         • Applying other cells @1   (2 marks)
   4.  
      • Currency formats @ 1
      • Zero decimal formats @
      • Formats applied in the correct range @½   (2 marks)
   5.  
      1. Copying the content of the current worksheet to sheet 2   (1 mark)
      2.  
         • Rename sheet 1 as original @ 1
         • Rename sheet 2 as NEW @ 1 (1)   (2 marks)
   6.  
      1.  
         • Enabling filter feature @ 1 
         • Filtering out correct records (displaying S values only) @ 1   (2 marks)
      2.  
         • Creating bar chart @ 1
         • Selecting the correct X fields @ 2
         • Selecting the correct Y fields @ 1   (4 marks)
      3. Insertion of chart elements
         • Chart title @ 1 
         • X axis label @ 1
         • Y axis label @ 1    (3 marks)
      4. Renaming the chart worksheet as
         • SREVENUE @ 1   (1 marks)
   7. Printing the following 
      1. Original worksheet @ 1
      2. NEW worksheet @ 1
      3. SREVEN
      4. UE chart @ 1   (3 marks)
2.  
   1. Page settings
      1. Paper orientation @1 
      2. Units set to centimetres @1
      3. Paper size set to A4 @ 1
      4. Margins set to 2cm @ 1     (4 marks)
   2. Border lines
      1. Outer borders @1
      2. Inner rectangles @1
      3. Corner shapes @ ½x4=2
      4. Position on the page @1     (5 marks)
         "Certificate of Participation" Text
         • Typing text @1
         • Enlarging and italicizing of "of" @2
         • Positioning of this element on the page @1  (4 marks)
           Lines below and above the "Certificate of Participation"text
         • Top lines @ ½x 2 =1
         • Below lines @ ½x 2 =1
         • Correct placement @1    (3 marks)
           "Awarded to:" Text
         • Typing text @1
         • Correct placement this element on the page @1  (2 marks)
           Line below"Awarded to:" Text 
         • Inserting of the line @1
         • Correct placement @1     (2 marks)
           “For the phenomenal......." text
         • Typing text @1 
         • Correct placement @1     (2 marks)
           "Fire Emergency Rescue" Text
         • Typing the text @1
         • Text (font) size @1
         • Fill pattern (outline font) @1
         • Insertion of text box @1 
         • Applying a dotted background in the text box @1
         • Correct positioning of this elements in the page @1    (6 marks)
           "Presented By:"Text
         • Typing text-@ 2
         • Horizontal line below @1 
         • Position on the page @1     (3 marks)
           "On This Day" Text
         • Typing text @1
         • Position on the page @1
         • Horizontal line below @1     (3 marks)
           The Flame and Candle Graphic
         • 2 curved lines @ ½ x 2 =1
         • Flame outline(Filling the inner curve) @2
         • Rectangular shape @1
         • Correct fill pattern on the rectangle @ ½
         • Correct position of all the elements in the page @1   (5 marks)
           The Star Graphic
         • Outer shape (drawing) @1
         • Fill pattern @1 
         • Star shape (drawing) @1
         • White fill colour @1
         • Correct positioning on page @1 
         • Star shape in front @1          (6 marks)
           Second Star graphic
         • Copying (duplicated) @1 
         • Correct positioning of both graphic each2x 1=2
         • Saving the certificate  (1 mark)
   3.  
      • Printing the design    (1 mark)