Kenya Certificate of Secondary Education

2020 Computer Studies – Paper 2

Section A

1.The management of a company owning a chain of hotels intends to use a spreadsheet program to compute the revenues in thousands of Kenya shillings for the hotels during the 1st, 2nd, 3rd and 4th quarters. The hotels are rated as 2 star, 3 star, 4 star and 5 star. Figure 1 shows extract of the worksheet.

ABCDEFG
1
HOTEL
   
STAR
   
CATEGORY   
   
1st   
   
2nd   
   
3rd   
   
4th   
   
TOTAL REVENUE   
2
City Inn
   
4   
   
174,400   
   
177,400   
   
127,900   
   
180,800   
   
   
3
Forkland
   
2   
   
173,700   
   
111,300   
   
194,900   
   
10 ,700   
   
   
4   
Highway   
   
4   
   
182,900   
   
136,300   
   
143,900   
   
144,600   
   
   
5   
Hiltop   
   
5   
   
188,500   
   
124,700   
   
173,500   
   
171,200   
   
   
6   
Membley   
   
5   
   
187,800   
   
118,700   
   
115,900   
   
195,500   
   
   
7   
Voyager   
   
3   
   
139,100   
   
178,400   
   
109,600   
   
179,700   
   
   
8   
Eden   
   
2   
   
200,000   
   
112,200   
   
109,100   
   
117,200   
   
   
9   
Palm tree   
   
4   
   
169,800   
   
163,000   
   
173,000   
   
148,300   
   
   
10   
The Shaza   
   
5   
   
163,900   
   
106,800   
   
107,100   
   
150,500   
   
   
11   
   
   
   
   
   
   
   
   
   
   
   
   
   
12   
   
   
   
   
   
   
   
   
   
   
   
   
   
13   
   
   
   
   
   
   
   
   
   
   
   
   
   
14   
Total Quarterly Re   
   
venue   
   
   
   
   
   
   
   
   
   
   
15
Administrative cost
   
   
   
   
   
   
   
   
   
   
16   
Percentiige 30%   
   
   
   
   
   
   
   
   
   
   
   
   

(a) Open a spreadsheet program and create the worksheet extract as it appears in Figure 1. Save the workbook as Task 1. (11 marks)

(b) Use a function and cell addresses to calculate:

i) total revenue for each quarter; (2 marks)

i1) total revenue for each hotel. (2 marks)

(c) () Insert two rows above row 1 and type the title “MBALAMBALA GROUP HOTELS” in cell Al (2 marks)

ii) Merge the cells in the range Al:GI. (1 mark)

(ii) Apply bold and font size 15 to the title. (1 mark)

(a) Open a spreadsheet program and create the worksheet extract as it appears in Figure 1.

Save the workbook as Task 1. (11 marks)

(b) Use a function and cell addresses to calculate:

(i) total revenue for each quartei,(2 marks)

(ii) total revenue for each hotel.(2 marks)

(c) (i) Insert two rows above row 1 and type the title “MBALAMBALA GROUP HOTELS” in cell Al. (2 marks)

(ii) Merge the cells in the range AI:G1.(1 mark)

(iii) Apply bold and font size 15 to the title.(1 mark)

(a) Using cell addresses only, compute the administrative cost for each quarter given that the cost is a percentage of total revenue and the percentage rate is in cell B18.(3 marks)

(b) Apply thick outside borders and regular inside borders to cells in the range A3: G18.(2 marks)

(c) (i) Copy all the contents of the current worksheet to a new worksheet. (2 marks)

(ii) Rename the old worksheet as oi iginal and the new worksheet as formatted. (2 marks)

(d) (i) Change the page layout orientation of the formatted worksheet to landscape and the page size scaled to 80%. (2 marks)

(ii) Enter the values 2, 3, 4 and 5 in the cell range B20 : B23 respectively representing the hotel star category ratings. (1 mark)

(iii) Using a function and cell references:

I. compute the total revenue for each hotel references;

II. compute the total revenue for each hotel category in the 1st quarter using reference values in the range B20 : B23 in cells C20 : C23. (5 marks)

(iv) Sort the revenues front the hotels 1n descending order of hotels. (2 marks)

(e) (i) Create a column chart that compares the revenues of the hotels in star category 4 for the 1st and 2nd quarter. (4 marks)

(ii) Format the chart created as follows:

I. Chart title “FIRST AND SECOND QUARTER REVENUES”(2 marks)

II. Move the chart to a new sheet and rename the sheet as FourStar Revenues.(2 marks)

(i) Save the changes and print later each of the following:

(i) original worksheet showing the column and row headings;(2 marks)

(ii) formatted worksheet;(1 mark)

(iii) Four Star Revenues chart.(1 mark)

2. Popo City planners intend to use a Desktop Publishing program to draw a plan for a section of a city. Assume you have been given the task.

(a)(i) Open a desktop publishing program and set the page layout orientation to portrait and paper size to A4. (2 marks)

(ii) Save the publication as Task 2. (1 mark)

(b) Create the city plan as it appears in Figure 2 ensuring that the design covers the entire printable area of the page. (46 marks)

(c) Save the changes and print the publication later. (I mark)

Questions and Answers

2020 Computer Studies – Paper 2

Section A

Answer all the questions in this section

Coming soon!!!