GET THE KCSE PREDICTION COMPUTER STUDIES Paper 2 MARKING SCHEMES HERE

Bunyore

 QUESTION 1 – SPREADSHEET

A Company in Mombasa sells computer spare parts to its customers. The Company wishes to work out the pay details for its employees.

EMPLOYEE NAME  YEARS WORKEDBASIC PAY [KSHS]DEPARTMENTSALES [KSHS]HOURS OF OVERTIME
RUKENYA KWENA524,000ADMIN16,00010
BILLY LUCAS1328,000SALES25,00011
LILIAN OKOTH717,000MARKETING22,00012
EVANS ONDIEKI1118,000SALES12,00015
GEOFFREY MUTUMA1526,000ACCOUNTS11,00022
HUMPHREY LOKI1025,000ADMIN30,00012
CEDRIC MUKUI1119,000SALES35,00033
FREDRICK CHEGE1525,000MARKETTING14,00014
OSMAN HUSSEIN1423,000ADMIN25,000
JEREMY NYAMU1827,000ACCOUNT14,0007

(a) Using the information above, design a spreadsheet and enter the given data as it appears. Give it the title “COMPANY PAYMENTS”. Save the workbook file as COMPANY1                                          (14marks)

(b) (i) Copy the data into Sheet 2 and rename it as COMPANY2 and use it to answer the questions that follow (2 marks)

   (ii) Calculate the total sales and total mileage giving them an appropriate label                            (2marks)

   (iii) Rotate the column headings to 45°                                                                                           (2 marks)

   (iv) The employee’s sales commission is calculated as 12% of the employee’s sales. Input this commission rate in cell C20 and label it appropriately. Bold the label and change its font to size 16         (4marks)

    (v) Insert a new column labeled ‘Sales commission’ between ‘sales’ and ‘hours of overtime’.   (2marks)

    (vi) Create a formulae to give the amount of sales commission for each employee by making references to sales commission cell.                                                                                                               (3marks)

See also  EXCERPTS FROM A DOLL’S HOUSE KCSE REVISION QUESTIONS- EXCERPT 1-5

(c) (i) Convert the basic pay and sales to two decimal places.                                                           (2marks)

     (ii) Use a function in a new column labeled REMARK to put the remark ‘EXCELLENT’ for only those employees whose sales is greater than 22,000, ‘GOOD’ those employees whose sales  are between 15000 to 21999  otherwise the remark should be ‘LOW SALES.                                                         (6marks)

      (iii) Apply both outline and inside double line border to the worksheet portion with data         (3marks)

(d) Overtime payment is done by multiplying 5% of sales with the hours worked. Use a formula to calculate the overtime pay for each of the employees in a new column labeled “OVERTIME PAY”     (2marks)

(e) Use a function to compute the Total payment of each employee. It should be summation of Basic pay, Sales Commission and Overtime pay. Give it the heading TOTAL PAYMENT. Save the changes. (2marks)

(f) Use an appropriate subtotals function to show how much TOTAL PAYMENT the company gives to  employees in each department                                                                                                       (4marks)

(g) Print COMPANY1, COMPANY2 and all the formulas used in company2.                         (3marks)

GET THE KCSE PREDICTION COMPUTER STUDIES Paper 2 MARKING SCHEMES HERE

QUESTION 2 – DATABASES

Assuming that you have been approached by an automobile Showroom company to help manage their vehicles database whose details are given below:

(a)        Create database named Magari                                                                                             (2marks)

Car MakeRegNoTypeYearValueOwner IDOwner Name
ToyotaKBD 949UCoupe20101,200,000M0001Faith N.
NissanKCT 149EWagon20142,500,000M0002Jacob W.
IzuzuKDD 977WTroupe20164,500,000M0003Dan C.
ToyotaKBA 241VTroupe2009900,000M0002Jacob W.
ToyotaKBD 049XCoupe20101,150,000M0004Rachael R.
NissanKCV 518CSaloon20121,700,000M0004Rachel R.
SubaruKCY 123ZSaloon20142,100,000M0001Faith N.

(b)        Design two Tables named Cars and Owners to be used to hold the above data. Assign appropriate primary keys for each table. Prepare appropriate input masks to help validate both RegNo and Owner ID field entries                                                                                                                        (18marks)

See also  Form 3 Business Studies End Term 1 Exam 2023 With Marking Schemes

(c)        Create a relationship between the tables.                                                                               (2marks)

(d)       Create forms named “CarDetails” with a heading and “OwnerDetails”. Use them to add car details and owner details records respectfully.                                                                                  (4marks)

(e)        Insert the record below having the following respective details.                                           (4marks)

VolkswagenKCV 321DBeatle20121,325,0000002Jacob W.

(f)        Add a column into the car table labeled “Date of Service”, and add the following dates. Save the changes made.                                                                                                                        (4marks)

Date of ServiceOwnerID
20/092021M0001
21/10/2021M0002
10/10/2021M0003
11/10/2021M0002
19/11/2021M0004
21/10/2021M0004
22/11/2021M0001

(g)        Create a query that retrieves a list of cars and their owners to be serviced on 21/10/2021 or on 22/11/2021. Name it Service Query.                                                                                     (5marks)

(h)        Create a tabular report named NumbOfCars displaying the cars and their owners; indicating the number of cars each owner has; sort the records with Name in ascending order.                 (6marks)

(i)         Create a report named TotalValue Report that computes and displays the total value of the cars owned by each owner.                                                                                                                        (5marks)

(j)         Print

            (i)         The two tables

            (ii)        The query

            (ii)        The two reports

Leave a Reply

Your email address will not be published. Required fields are marked *