O-PR-S1 / A LEVEL SOLUTION IT PRACTICAL PAPER SOLVED WITH IMAGES

 O-PR-S1 / A LEVEL SOLUTION

Time Allotted : 03 Hours Max. Marks : 100 (80 Marks for Practical Exercise + 20 Marks for Viva-voce) 

 1. Write your Registration Number and Level in the space provided on the top. 

 2. All the three questions are compulsory. In case of Question No. 3, the candidate must attempt the question based on the subject as opted by him/her in theory examination. 

 3. The ‘Question Paper-cum-Worksheet’ can be used for writing algorithms/flowcharts and documentation of program and the output results with relevant headings etc. 

 4. The maximum marks allotted for each question is given in the parentheses. 

 5. Candidate must return the ‘Question Paper-cum-Worksheet’ to the examiner before leaving the exam hall. 

6. All the questions should be solved on the desktop PC and demonstrated to the Examiner and Observer. 7. Wherever values/data have not been given in the Questions, the candidate can assume the data.

O LEVEL (O - PR) BATCH : S1 − 1. 

(a) Create a worksheet "employee" with the following information. 

Name Phone Floor Office Dept. Design Salary 

Neha 5834 2 11 Admin PM 8000 

Sucheta 2812 2 21 Admin CL 9000 

Amrit 3477 1 17 Mkt CL 10000 

Arunesh 6992 3 14 Mkt MM 8500 

Ashwin 1538 3 12 Mkt SI 5000 

(b) To find floor in which there is no person whose name starts with A. 

Use filter, press Ctrl+shift+L

click on name filter and select text filter and select word not begin enter A

Result:

(c) To display name and office for the persons in office 11 and 14. 

use formula =FILTER(B4:E8,(E4:E8=11)+(E4:E8=14))

result :

(d) To extract name and phone for the persons on the floor between 2 and 4. 

Result:

(e) To extract name and office for persons of Admin department. 

use filter and select dept. , click on admin. We get admin dept. only

result:

OR

name phone floor office dept. design salary neha 5834 2 11 admin PM 8000 suchita 2812 2 21 admin CL 9000 amrit 3477 1 17 mkt MM 10000 arunesuh 6992 3 14 mkt MM 8500 ashwin 1538 3 12 mkt SI 5000
use formula :=CHOOSECOLS(FILTER(A2:E6,E2:E6="admin"){1,4})

and gets result:

neha	11
suchita	21

(f) To extract name and phone for the persons having salary more than 8000/-.

name phone floor office dept. design salary neha 5834 2 11 admin PM 8000 suchita 2812 2 21 admin CL 9000 amrit 3477 1 17 mkt MM 10000 arunesuh 6992 3 14 mkt MM 8500 ashwin 1538 3 12 mkt SI 5000

use formula: =FILTER(A2:B6,G2:G6>8000)

result:

suchita	2812
amrit	3477
arunesuh	6992

(a) In Spreadsheet, Enter the following data. 

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500
Sunita Devi	12500
Roji Sharma	16500
Farida Khan	18500
Gunita Devi	24000
Total Amount
Highest Employee Salary
Lowest Employee Salary
Total Insurance Collected

FOR LAST 4 LINES USES MERGE OPTION WHICH GIVEN AT HOME PAGE SECTION AND MAKE TABLE.

(b) Calculate DA as 80% of Basic Pay and HRA as 10% of basic pay + DA. 

FOR CALCULATE DA = 80% * BASIC PAY

                                           0.8* BASIC PAY

USE FORMULA = =0.8*BASIC PAY

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800
Sunita Devi	12500	10000
Roji Sharma	16500	13200
Farida Khan	18500	14800
Gunita Devi	24000	19200
Total Amount
Highest Employee Salary
Lowest Employee Salary
Total Insurance Collected

HRA as 10% of basic pay + DA

USE FORMULA = = 0.10 * (Basic Pay + DA)

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530
Sunita Devi	12500	10000	2250
Roji Sharma	16500	13200	2970
Farida Khan	18500	14800	3330
Gunita Devi	24000	19200	4320
Total Amount
Highest Employee Salary
Lowest Employee Salary
Total Insurance Collected

(c) Find out Net Salary (giving the deductions of Insurance 10% of Basic pay and P.F. 5% of Basic pay). 

FIRST FIND GROSS PAY :

FOR GROSS PAY USE FORMULA: =BASIC PAY+DA+HRA

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530	16830
Sunita Devi	12500	10000	2250	24750
Roji Sharma	16500	13200	2970	32670
Farida Khan	18500	14800	3330	36630
Gunita Devi	24000	19200	4320	47520
Total Amount
Highest Employee Salary
Lowest Employee Salary
Total Insurance Collected

FOR FIND INSURANCE: 10% GIVEN IN QUESTION 

USE FORMULA FOR INSURANCE: = 0.10 * BASIC PAY

Insurance = 10% of 8500 = $8500 \times 0.10 = 850$

$RESULT:$

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530	16830	850
Sunita Devi	12500	10000	2250	24750	1250
Roji Sharma	16500	13200	2970	32670	1650
Farida Khan	18500	14800	3330	36630	1850
Gunita Devi	24000	19200	4320	47520	2400
Total Amount
Highest Employee Salary
Lowest Employee Salary
Total Insurance Collected

TO FIND P.F:

P.F. = 5% of 8500 = 8500×0.05=425

USE FORMULA : =0.05* BASIC PAY

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530	16830	850	425
Sunita Devi	12500	10000	2250	24750	1250	625
Roji Sharma	16500	13200	2970	32670	1650	825
Farida Khan	18500	14800	3330	36630	1850	925
Gunita Devi	24000	19200	4320	47520	2400	1200
Total Amount
Highest Employee Salary
Lowest Employee Salary
Total Insurance Collected

USE FORMULA FOR NET SALARY:

=GROSS PAY -(INSURANCE +P.F.)

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530	16830	850	425	15555
Sunita Devi	12500	10000	2250	24750	1250	625	22875
Roji Sharma	16500	13200	2970	32670	1650	825	30195
Farida Khan	18500	14800	3330	36630	1850	925	33855
Gunita Devi	24000	19200	4320	47520	2400	1200	43920
Total Amount
Highest Employee Salary
Lowest Employee Salary
Total Insurance Collected

(d) Find out the Highest and lowest Salary of the Employee. 

TO FIND OUT THE HIGHEST SALARY OF THE EMPLOYEE USE FORMULA:

 

=MAX(SELECT NET SALARY COLUMN I16:I20)

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530	16830	850	425	15555
Sunita Devi	12500	10000	2250	24750	1250	625	22875
Roji Sharma	16500	13200	2970	32670	1650	825	30195
Farida Khan	18500	14800	3330	36630	1850	925	33855
Gunita Devi	24000	19200	4320	47520	2400	1200	43920
Total Amount
Highest Employee Salary							43920
Lowest Employee Salary
Total Insurance Collected

TO FIND OUT THE LOWEST SALARY OF THE EMPLOYEE USE FORMULA:

 

=MIN(SELECT NET SALARY COLUMN I16:I20)

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530	16830	850	425	15555
Sunita Devi	12500	10000	2250	24750	1250	625	22875
Roji Sharma	16500	13200	2970	32670	1650	825	30195
Farida Khan	18500	14800	3330	36630	1850	925	33855
Gunita Devi	24000	19200	4320	47520	2400	1200	43920
Total Amount
Highest Employee Salary							43920
Lowest Employee Salary							15555
Total Insurance Collected

(e) Calculate the total Insurance collected from all the employee. 

the total Insurance collected from all the employee  use formula:= sum(select insurance range G16:G20)

RESULT:

 

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500	6800	1530	16830	850	425	15555
Sunita Devi	12500	10000	2250	24750	1250	625	22875
Roji Sharma	16500	13200	2970	32670	1650	825	30195
Farida Khan	18500	14800	3330	36630	1850	925	33855
Gunita Devi	24000	19200	4320	47520	2400	1200	43920
Total Amount
Highest Employee Salary							43920
Lowest Employee Salary							15555
Total Insurance Collected							8000

(f) Apply two decimal notations to all the numeric values.

FOR APPLY TWO DECIMAL NOTATION USE OPTION GIVEN IN HOME PAGE(DECIMAL) WHICH SHOWN IN IMAGE, CLICK 2 TIMES ON THIS SYMBOL(.00):

RESULT:

Employee Name	Basic Pay	DA	HRA	Gross Pay	Insurance	P.F.	Net Salary
Anil Kumar	8500.00	6800.00	1530.00	16830.00	850.00	425.00	15555.00
Sunita Devi	12500.00	10000.00	2250.00	24750.00	1250.00	625.00	22875.00
Roji Sharma	16500.00	13200.00	2970.00	32670.00	1650.00	825.00	30195.00
Farida Khan	18500.00	14800.00	3330.00	36630.00	1850.00	925.00	33855.00
Gunita Devi	24000.00	19200.00	4320.00	47520.00	2400.00	1200.00	43920.00
Total Amount
Highest Employee Salary							43920.00
Lowest Employee Salary							15555.00
Total Insurance Collected							8000.00