INSTRUCTIONS TO CANDIDATES

• Type your name and index number at the top right hand corner of each print out
• Write your name and index number on the diskette/CD-R provided
• Write the name and version of software used in each question on the answer sheet (d) Answer ALL the questions
• Passwords should not be used while saving in the diskette/CD-R
• All answers MUST be saved in the diskette/CD-R
•  Make print out of answers on the answer sheet provided

Question 1
The figure below shows an extract from Muhindi Mbingu secondary school fees income. The data titled 2022 school fee increment shows the percentage increment for the year 2022.

1. Create the worksheet as it appears in the figure in a spreadsheet package and save it as Incomestatement. (9 marks)
2. Copy the content of incomestatement to a new sheet and name it as incomestatement1. (1 mark)
3. insert column in between the following labels TUITION FEE and UNIFORM FEE, UNIFORM FEE and CAUTION FEE, CAUTION FEE and REMEDIAL FEE, REMEDIAL FEE and BURSARY. Label the new columns using their predecessor column adding the text ‘TOTAL’ before. (3 mark)
4. Use a function that references only to cells to calculate:
             TOTAL TUITION FEE for each class not forgetting that the TUITION FEE indicated is for one student. apply the same concept to obtain the totals for other columns. (3 marks)
5. Format the Worksheet as follows:
   1. Resize the titles to font size 16 and apply bold. (2 marks)
   2. Apply outline border around data 2021 SCHOOL FEE. (1 mark)
   3. Align to the right all the column headings in the worksheet. (1 mark)
   4. Apply a grey background colour to all column headings. (1 mark)
6.   
   1. Copy the content of the current worksheet to a new worksheet and rename the new worksheet Modified. (1 mark)
   2. copy the data labeled ‘2021 SCHOOL FEE’ and paste it below the data labeled ‘2022 school fees increment’. Delete the values of the newly copied work leaving the labels and the data on the columns named CLASS and NO. OF STUDENTS. Change the title to 2022 SCHOOL FEE. (3 mark)
   3. Compute the school fee for the year 2022 using data titled ‘2022 school fees increment’. use absolute cell referencing to obtain the correct data where necessary. (6 marks)
   4. using formulae compute the total fees collected in the year 2022. Clearly indicate the sub totals and finally the grand total. (5 marks)
   5. write a function in cell B40 that sums up the bursary in class having more than 25 students only. (3marks|)
7. Create a well labeled pie chart to compare the total fees per class. (5 marks) h) Print the following Worksheets: (4 marks)
   1. Incomestatement.
   2. incomestatement1.
   3. Modified
   4. Chart

Question 2
M/s Ann Momanyi, an ICT consultant with Makueni Distributors is in the process of developing a Management Information System (MIS) for the company. Currently, she is designing sales database using three tables namely: - customers, products and transactions. The details for each table are given below:-

Customers Products Transactions Customer ID Product ID TransID
CustomerName ProductDescription clientName PostalAddress Packaging TransacDate Town unitPrice Product
Phone Stock Quantity

Questions

1. Create a database named MIS and in it create three relations. (12marks)
2. Identify the most appropriate field as the primary key in each table(3marks)
3. Establish relationships among the three tables and enforce the referential integrity (6marks)
4. Create a form for each table. Save the forms as Customer Form, Product Form and Transaction Form. (9marks)
5. Use the respective forms to enter the following records in each table (6marks)
   CUSTOMER TABLE
   

   Customer ID	Customer Name	Postal Address	Town	Phone
   H001	Andrew Mwove	209	Mutituni	200870
   H002	Erick Onsongo	68	Nyamira	248567
   H003	Brenda Nyabuti	100	Bomet	789678
   H004	Lydiah Winzaa	250	Mwingi	778009
   H005	Jeff Ogero	330	Keroka	666790
   H006	Maureen Makuthu	550	Kakaeni	780906

   
   PRODUCT TABLE
   

   PorductID	Product Description	Packaging	Unit price	Stock
   AA001	Cooking Oil	20kg Gal	16 000	80
   AA003	Rice	50kg bags	4 500	60
   AB004	Detergents	5 litres	11000	40
   AC006	Fertilizer	50kg bags	4 700	30
   AA007	Mineral water	1.5 litres	8 200	400

   
   TRANSACTION TABLE
   

   Customer ID	Client Name	TranscDate	Products	Quantity
   1001	Andrew Mwove	06/04/2007	Cooking Oil	6
   1002		26/4/2007	Mineral water	8
   1003	Erick Onsongo	23/04/2007	Detergents	10
   1004		14/4/2007	Cooking Oil	12
   1005	Maureen Makuthu	02/05/2007	Rice	4
   1006		08/05/2007	Fertilizer	3
   1007	Erick Onsongo	16/05/2007	Detergents	25

6. Create a query that displays the customer Name, Product Description, Quantity, Unit price and calculates field total payable by each customer. Save the query as MISQuery (5marks)
7. Generate a report from MISQuery. Save the report as MISReport (4marks)
8. Print the Customers, Products, Transaction table’s design, MISQuery and MISReport (5marks)