TIME : 2 1/2 HOURS

1.The management of a company owning a chain of hotels intends to use a spreadsheet to compute the revenues in thousands of Kenya shillings for the hotels during the first, second, third and fourth quarters. The hotels are rated as 2 star , 3 star , 4 star and 5 star.Figure 1 shows an extract of the worksheet

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

b) Use a function and cell addresses to calculate:

i. Total revenue for each quarter 2mks

ii. Total revenue for each hotel 2mks

c) i. Insert two rows above row 1 and type the title” MBALAMBALA GROUP HOTELS” in cell A1 2mks

ii. Merge the cells in the range A1:G1 1mks

iii. Apply bold and font size of 15 to the title 1mk

d) using cell addresses only , compute the administrative cost for each quarter given that the cost is a percentage of the total revenue and the percentage rate is in cell B18 3mks

e) apply thick outside border and regular inside border to the cells in the range A3:G18 2mks

f) i) Copy all the contents of the current worksheet to a new worksheet 2mks

ii) Rename the old worksheet as original and new worksheet as formatted 2mks

g) i) Change the page layout orientation of the formatted worksheet to landscape and the page size scaled to 80% 2mks

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

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 cell C20:C23 5mks

iv) Sort the revenues from the hotels in descending order of hotels. 2mks

h) (i) create a column chart that compares the revenues of the hotels in star category 4 for the 1st and 2nd quarter 4mks

ii) Format the chart created as follows :


II. Move the chart to a new sheet and rename it as FourStar Revenues 2mks

i) Save the changes and print later each of the following ;

i. Original worksheet showing the column and row headings; 2mks

ii. Formatted worksheet 1mk

iii. FourStar Revenues chart 1mk

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 2mks

ii) Save the publication to as Task 2 1mk

(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 1mk