Download OU (Osmania University) B.Com (Bachelor of Commerce) (Important Questions) B.Com (CBCS) 5th Semester Excel Foundation QB
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
17.Complete the following Income Statement for year 2017:
I-REVENUE Rs. In Lakhs
Sales 2000
Services 200
------
Total ?
------
II-EXPENSES
Salaries 300
Cost of Goods sold 400
------
Total Expenses ?
------
III-NIBT(Net Income Before Taxes) ?
(Total Revenue-Total Expenses)
Income Tax ?
-------
NET INCOME(NIBT-I Tax) ?
-------
(income tax=NIBT upto 200=Nil; 201-400=10.12%, 400 above=20.24% on NIBT)
18.Create the following table of a class:
ROLL NO NAME MARKS
1 A 82
2 B 92
3 C 62
4 D 62
5 E 72
i) Findout the topper of the class
ii)Findout the least scorer of the class
iii)Findout who got exactly 62 marks
19.Create the following Inventory table of Product No100 Product Name:Book:
DATE OPENING PURCHASES ISSUES CLOSING
1.1.2018 0 300 50
10.1.2018 200 50
20.1.2018 100 100
31.1.2017 100 50
i)Findout each day?s Closing balance
ii)Previous day Closing balance is next day Opening balance=system should reflect
automatically
iii)An entry about destruction of Books numbering 20 on 25.1.2018 should be taken
now
iv) If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
17.Complete the following Income Statement for year 2017:
I-REVENUE Rs. In Lakhs
Sales 2000
Services 200
------
Total ?
------
II-EXPENSES
Salaries 300
Cost of Goods sold 400
------
Total Expenses ?
------
III-NIBT(Net Income Before Taxes) ?
(Total Revenue-Total Expenses)
Income Tax ?
-------
NET INCOME(NIBT-I Tax) ?
-------
(income tax=NIBT upto 200=Nil; 201-400=10.12%, 400 above=20.24% on NIBT)
18.Create the following table of a class:
ROLL NO NAME MARKS
1 A 82
2 B 92
3 C 62
4 D 62
5 E 72
i) Findout the topper of the class
ii)Findout the least scorer of the class
iii)Findout who got exactly 62 marks
19.Create the following Inventory table of Product No100 Product Name:Book:
DATE OPENING PURCHASES ISSUES CLOSING
1.1.2018 0 300 50
10.1.2018 200 50
20.1.2018 100 100
31.1.2017 100 50
i)Findout each day?s Closing balance
ii)Previous day Closing balance is next day Opening balance=system should reflect
automatically
iii)An entry about destruction of Books numbering 20 on 25.1.2018 should be taken
now
iv) If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?
20.Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
Using Conditional Formatting highlight, who scored :
i)More than 50 in S1 ii)Less than 50 in S2 and iii) Between 50 and 70 in S3
21.Create the following table:
MARKS
ROLL NO NAME S1 S2 S3 % RESULT DIVISION
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
i) To declare ?Pass?, to get >=40 marks in every subject.
ii) To declare ?Fail?, to get <40 in any one subject
iii) Division is only for ?Pass? candidates
Division= Distinction above 90%
First 80%-<90%
Second 60%-<80%
Pass 40%-<60%
??? <40%
22.Create Column chart for S1 and S3 only
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
23 Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 50 80
3 C 40 50 30
4 D 70 50 40
5 E 50 60 70
i) Find out the Maximum score in S1, Minimum score in S2 and use Count S3
ii) Find out Median of S1 scores and Mode of S2 scores
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
17.Complete the following Income Statement for year 2017:
I-REVENUE Rs. In Lakhs
Sales 2000
Services 200
------
Total ?
------
II-EXPENSES
Salaries 300
Cost of Goods sold 400
------
Total Expenses ?
------
III-NIBT(Net Income Before Taxes) ?
(Total Revenue-Total Expenses)
Income Tax ?
-------
NET INCOME(NIBT-I Tax) ?
-------
(income tax=NIBT upto 200=Nil; 201-400=10.12%, 400 above=20.24% on NIBT)
18.Create the following table of a class:
ROLL NO NAME MARKS
1 A 82
2 B 92
3 C 62
4 D 62
5 E 72
i) Findout the topper of the class
ii)Findout the least scorer of the class
iii)Findout who got exactly 62 marks
19.Create the following Inventory table of Product No100 Product Name:Book:
DATE OPENING PURCHASES ISSUES CLOSING
1.1.2018 0 300 50
10.1.2018 200 50
20.1.2018 100 100
31.1.2017 100 50
i)Findout each day?s Closing balance
ii)Previous day Closing balance is next day Opening balance=system should reflect
automatically
iii)An entry about destruction of Books numbering 20 on 25.1.2018 should be taken
now
iv) If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?
20.Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
Using Conditional Formatting highlight, who scored :
i)More than 50 in S1 ii)Less than 50 in S2 and iii) Between 50 and 70 in S3
21.Create the following table:
MARKS
ROLL NO NAME S1 S2 S3 % RESULT DIVISION
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
i) To declare ?Pass?, to get >=40 marks in every subject.
ii) To declare ?Fail?, to get <40 in any one subject
iii) Division is only for ?Pass? candidates
Division= Distinction above 90%
First 80%-<90%
Second 60%-<80%
Pass 40%-<60%
??? <40%
22.Create Column chart for S1 and S3 only
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
23 Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 50 80
3 C 40 50 30
4 D 70 50 40
5 E 50 60 70
i) Find out the Maximum score in S1, Minimum score in S2 and use Count S3
ii) Find out Median of S1 scores and Mode of S2 scores
24.Create a table with the following and Calculate Fees Concession:
ROLLNO NAME CATEGORY % FEES CONCESSION
1 Iyer N 90
2 Nair D 60
3 Nambiar N 50
4 Krishnan D 70
5 Ambal G 40
Concession Policy:
CATEGORY % CONCESSION
N above 50 10%
D above 50 20%
G above 40 15%
i) In all other cases there is NO concession.
ii) Fees paid by each one of them is Rs.10000
25.Create the following table and calculate Incentive:
EMP ID NAME SALES(Rs) INCENTIVE
101 A 10000
102 B 20000
103 C 10000
Policy:
Sales between 10000-15000=5%
>15000-<20000=6%
>=20000-<30000=8%
26. Calculate Annual payment/instalment for a loan using an appropriate function:
Loan amount: Rs. 10,00,000
Years of repayment: 10 years
Rate of interest 10%
a) If the payments are Monthly, instead of Annual, what is the instalment
b) If the payments are quarterly, instead of Annual, what is the instalment
c) If the rate of interest is changed to 15% on Annual payment basis, what is the
instalment
27.Create a Pivot table with the following:
Days\Periods I II III
MON ENG FA IT
WED ENG FA IT
FRI ENG FA IT
Inter change the Rows into columns, using the Pivot table
The Pivot table be placed in a New Worksheet
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
17.Complete the following Income Statement for year 2017:
I-REVENUE Rs. In Lakhs
Sales 2000
Services 200
------
Total ?
------
II-EXPENSES
Salaries 300
Cost of Goods sold 400
------
Total Expenses ?
------
III-NIBT(Net Income Before Taxes) ?
(Total Revenue-Total Expenses)
Income Tax ?
-------
NET INCOME(NIBT-I Tax) ?
-------
(income tax=NIBT upto 200=Nil; 201-400=10.12%, 400 above=20.24% on NIBT)
18.Create the following table of a class:
ROLL NO NAME MARKS
1 A 82
2 B 92
3 C 62
4 D 62
5 E 72
i) Findout the topper of the class
ii)Findout the least scorer of the class
iii)Findout who got exactly 62 marks
19.Create the following Inventory table of Product No100 Product Name:Book:
DATE OPENING PURCHASES ISSUES CLOSING
1.1.2018 0 300 50
10.1.2018 200 50
20.1.2018 100 100
31.1.2017 100 50
i)Findout each day?s Closing balance
ii)Previous day Closing balance is next day Opening balance=system should reflect
automatically
iii)An entry about destruction of Books numbering 20 on 25.1.2018 should be taken
now
iv) If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?
20.Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
Using Conditional Formatting highlight, who scored :
i)More than 50 in S1 ii)Less than 50 in S2 and iii) Between 50 and 70 in S3
21.Create the following table:
MARKS
ROLL NO NAME S1 S2 S3 % RESULT DIVISION
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
i) To declare ?Pass?, to get >=40 marks in every subject.
ii) To declare ?Fail?, to get <40 in any one subject
iii) Division is only for ?Pass? candidates
Division= Distinction above 90%
First 80%-<90%
Second 60%-<80%
Pass 40%-<60%
??? <40%
22.Create Column chart for S1 and S3 only
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
23 Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 50 80
3 C 40 50 30
4 D 70 50 40
5 E 50 60 70
i) Find out the Maximum score in S1, Minimum score in S2 and use Count S3
ii) Find out Median of S1 scores and Mode of S2 scores
24.Create a table with the following and Calculate Fees Concession:
ROLLNO NAME CATEGORY % FEES CONCESSION
1 Iyer N 90
2 Nair D 60
3 Nambiar N 50
4 Krishnan D 70
5 Ambal G 40
Concession Policy:
CATEGORY % CONCESSION
N above 50 10%
D above 50 20%
G above 40 15%
i) In all other cases there is NO concession.
ii) Fees paid by each one of them is Rs.10000
25.Create the following table and calculate Incentive:
EMP ID NAME SALES(Rs) INCENTIVE
101 A 10000
102 B 20000
103 C 10000
Policy:
Sales between 10000-15000=5%
>15000-<20000=6%
>=20000-<30000=8%
26. Calculate Annual payment/instalment for a loan using an appropriate function:
Loan amount: Rs. 10,00,000
Years of repayment: 10 years
Rate of interest 10%
a) If the payments are Monthly, instead of Annual, what is the instalment
b) If the payments are quarterly, instead of Annual, what is the instalment
c) If the rate of interest is changed to 15% on Annual payment basis, what is the
instalment
27.Create a Pivot table with the following:
Days\Periods I II III
MON ENG FA IT
WED ENG FA IT
FRI ENG FA IT
Inter change the Rows into columns, using the Pivot table
The Pivot table be placed in a New Worksheet
28.Create a table showing the differences between VAT system and GST system. Find out
the Manufacturer?s invoice value:
Value to Manufacturer:
Under VAT Under GST
Production Cost 1000000 1000000
+ Profit (20%)
+Excise duty (10%)
=Total Production cost
+ VAT (18%)
+State GST (9%)
+Central GST(9%)
MANUFUCTURER?S
INVOICE VALUE
-Excise duty and VAT apply to VAT system only
-State and Central GST apply to GST system only
29.Create a table of 5 records with your own data showing the following:
ROLLNO NAME S1 S2 TOTAL MKS RESULT
30. Create a Pie chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
-% and Names of the expenditure should be displayed
-Change the colour of any one food expenditure
31 . Create a COLUMN chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
- Names of the expenditure should be displayed on each column
-Change the colour of any one food expenditure\item
- legend should be on left side
32. Create an Inventory Re-ordering Report with the following columns:
ITEM STOCK (Kgs) REMARKS
Steel 1000
Iron 600
Brass 500
-In Remarks column mention ?Reorder?, if the Stock of any item goes below 600 Kgs
-If the stock is 600 or above mention Remark ?No Need?
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
17.Complete the following Income Statement for year 2017:
I-REVENUE Rs. In Lakhs
Sales 2000
Services 200
------
Total ?
------
II-EXPENSES
Salaries 300
Cost of Goods sold 400
------
Total Expenses ?
------
III-NIBT(Net Income Before Taxes) ?
(Total Revenue-Total Expenses)
Income Tax ?
-------
NET INCOME(NIBT-I Tax) ?
-------
(income tax=NIBT upto 200=Nil; 201-400=10.12%, 400 above=20.24% on NIBT)
18.Create the following table of a class:
ROLL NO NAME MARKS
1 A 82
2 B 92
3 C 62
4 D 62
5 E 72
i) Findout the topper of the class
ii)Findout the least scorer of the class
iii)Findout who got exactly 62 marks
19.Create the following Inventory table of Product No100 Product Name:Book:
DATE OPENING PURCHASES ISSUES CLOSING
1.1.2018 0 300 50
10.1.2018 200 50
20.1.2018 100 100
31.1.2017 100 50
i)Findout each day?s Closing balance
ii)Previous day Closing balance is next day Opening balance=system should reflect
automatically
iii)An entry about destruction of Books numbering 20 on 25.1.2018 should be taken
now
iv) If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?
20.Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
Using Conditional Formatting highlight, who scored :
i)More than 50 in S1 ii)Less than 50 in S2 and iii) Between 50 and 70 in S3
21.Create the following table:
MARKS
ROLL NO NAME S1 S2 S3 % RESULT DIVISION
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
i) To declare ?Pass?, to get >=40 marks in every subject.
ii) To declare ?Fail?, to get <40 in any one subject
iii) Division is only for ?Pass? candidates
Division= Distinction above 90%
First 80%-<90%
Second 60%-<80%
Pass 40%-<60%
??? <40%
22.Create Column chart for S1 and S3 only
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
23 Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 50 80
3 C 40 50 30
4 D 70 50 40
5 E 50 60 70
i) Find out the Maximum score in S1, Minimum score in S2 and use Count S3
ii) Find out Median of S1 scores and Mode of S2 scores
24.Create a table with the following and Calculate Fees Concession:
ROLLNO NAME CATEGORY % FEES CONCESSION
1 Iyer N 90
2 Nair D 60
3 Nambiar N 50
4 Krishnan D 70
5 Ambal G 40
Concession Policy:
CATEGORY % CONCESSION
N above 50 10%
D above 50 20%
G above 40 15%
i) In all other cases there is NO concession.
ii) Fees paid by each one of them is Rs.10000
25.Create the following table and calculate Incentive:
EMP ID NAME SALES(Rs) INCENTIVE
101 A 10000
102 B 20000
103 C 10000
Policy:
Sales between 10000-15000=5%
>15000-<20000=6%
>=20000-<30000=8%
26. Calculate Annual payment/instalment for a loan using an appropriate function:
Loan amount: Rs. 10,00,000
Years of repayment: 10 years
Rate of interest 10%
a) If the payments are Monthly, instead of Annual, what is the instalment
b) If the payments are quarterly, instead of Annual, what is the instalment
c) If the rate of interest is changed to 15% on Annual payment basis, what is the
instalment
27.Create a Pivot table with the following:
Days\Periods I II III
MON ENG FA IT
WED ENG FA IT
FRI ENG FA IT
Inter change the Rows into columns, using the Pivot table
The Pivot table be placed in a New Worksheet
28.Create a table showing the differences between VAT system and GST system. Find out
the Manufacturer?s invoice value:
Value to Manufacturer:
Under VAT Under GST
Production Cost 1000000 1000000
+ Profit (20%)
+Excise duty (10%)
=Total Production cost
+ VAT (18%)
+State GST (9%)
+Central GST(9%)
MANUFUCTURER?S
INVOICE VALUE
-Excise duty and VAT apply to VAT system only
-State and Central GST apply to GST system only
29.Create a table of 5 records with your own data showing the following:
ROLLNO NAME S1 S2 TOTAL MKS RESULT
30. Create a Pie chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
-% and Names of the expenditure should be displayed
-Change the colour of any one food expenditure
31 . Create a COLUMN chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
- Names of the expenditure should be displayed on each column
-Change the colour of any one food expenditure\item
- legend should be on left side
32. Create an Inventory Re-ordering Report with the following columns:
ITEM STOCK (Kgs) REMARKS
Steel 1000
Iron 600
Brass 500
-In Remarks column mention ?Reorder?, if the Stock of any item goes below 600 Kgs
-If the stock is 600 or above mention Remark ?No Need?
33 Create a Student Information Table with 5 records with your own data:
ROLLNO NAME PHONE ADDRESS DOB
Sort the table on Roll No and then by Name
34. Create a table and use any 5 Formatting options.
Move the table to Sheet 2
Rename the sheet
Add one column to the right and one row down to the table
Format as a Table.
35.The following are Sales figures of a company. Plot the figures I a Line chart:
YEAR: 2000 2001 2002 2003 2004 2005
SALES (Rs. In lakhs): 1000 1200 900 500 2000 1500
36. Set any 5, Page setup options/print options/sheet options for the following table with
your own data for 5 records:
ROLL NO MARKS
37. Create the following table:
ROLL NO SUBJECT MARKS
1 ECONOMICS 90
1 ECONOMICS 90
3 ACCOUNTS 90
2 ACCOUNTS 80
2 ACCOUNTS 80
4 ECONOMICS 50
I) Remove duplicate rows
II) Prepare Subject-wise Sub-Totals
38. Create the following table with own data:
ROLLNO NAME
i. Open a New Window containing current document
ii. View Side-by-Side
iii. Freeze top row
39. Find the following:
Amount to be received Rs.1000000
Rate of Interest 10%
Time 10 years
Amount to be invested at Present ?
i) If the rate of interest is 12% or 8%
ii) If the time period is 12 years or 8 years how much to be invested
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
17.Complete the following Income Statement for year 2017:
I-REVENUE Rs. In Lakhs
Sales 2000
Services 200
------
Total ?
------
II-EXPENSES
Salaries 300
Cost of Goods sold 400
------
Total Expenses ?
------
III-NIBT(Net Income Before Taxes) ?
(Total Revenue-Total Expenses)
Income Tax ?
-------
NET INCOME(NIBT-I Tax) ?
-------
(income tax=NIBT upto 200=Nil; 201-400=10.12%, 400 above=20.24% on NIBT)
18.Create the following table of a class:
ROLL NO NAME MARKS
1 A 82
2 B 92
3 C 62
4 D 62
5 E 72
i) Findout the topper of the class
ii)Findout the least scorer of the class
iii)Findout who got exactly 62 marks
19.Create the following Inventory table of Product No100 Product Name:Book:
DATE OPENING PURCHASES ISSUES CLOSING
1.1.2018 0 300 50
10.1.2018 200 50
20.1.2018 100 100
31.1.2017 100 50
i)Findout each day?s Closing balance
ii)Previous day Closing balance is next day Opening balance=system should reflect
automatically
iii)An entry about destruction of Books numbering 20 on 25.1.2018 should be taken
now
iv) If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?
20.Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
Using Conditional Formatting highlight, who scored :
i)More than 50 in S1 ii)Less than 50 in S2 and iii) Between 50 and 70 in S3
21.Create the following table:
MARKS
ROLL NO NAME S1 S2 S3 % RESULT DIVISION
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
i) To declare ?Pass?, to get >=40 marks in every subject.
ii) To declare ?Fail?, to get <40 in any one subject
iii) Division is only for ?Pass? candidates
Division= Distinction above 90%
First 80%-<90%
Second 60%-<80%
Pass 40%-<60%
??? <40%
22.Create Column chart for S1 and S3 only
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
23 Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 50 80
3 C 40 50 30
4 D 70 50 40
5 E 50 60 70
i) Find out the Maximum score in S1, Minimum score in S2 and use Count S3
ii) Find out Median of S1 scores and Mode of S2 scores
24.Create a table with the following and Calculate Fees Concession:
ROLLNO NAME CATEGORY % FEES CONCESSION
1 Iyer N 90
2 Nair D 60
3 Nambiar N 50
4 Krishnan D 70
5 Ambal G 40
Concession Policy:
CATEGORY % CONCESSION
N above 50 10%
D above 50 20%
G above 40 15%
i) In all other cases there is NO concession.
ii) Fees paid by each one of them is Rs.10000
25.Create the following table and calculate Incentive:
EMP ID NAME SALES(Rs) INCENTIVE
101 A 10000
102 B 20000
103 C 10000
Policy:
Sales between 10000-15000=5%
>15000-<20000=6%
>=20000-<30000=8%
26. Calculate Annual payment/instalment for a loan using an appropriate function:
Loan amount: Rs. 10,00,000
Years of repayment: 10 years
Rate of interest 10%
a) If the payments are Monthly, instead of Annual, what is the instalment
b) If the payments are quarterly, instead of Annual, what is the instalment
c) If the rate of interest is changed to 15% on Annual payment basis, what is the
instalment
27.Create a Pivot table with the following:
Days\Periods I II III
MON ENG FA IT
WED ENG FA IT
FRI ENG FA IT
Inter change the Rows into columns, using the Pivot table
The Pivot table be placed in a New Worksheet
28.Create a table showing the differences between VAT system and GST system. Find out
the Manufacturer?s invoice value:
Value to Manufacturer:
Under VAT Under GST
Production Cost 1000000 1000000
+ Profit (20%)
+Excise duty (10%)
=Total Production cost
+ VAT (18%)
+State GST (9%)
+Central GST(9%)
MANUFUCTURER?S
INVOICE VALUE
-Excise duty and VAT apply to VAT system only
-State and Central GST apply to GST system only
29.Create a table of 5 records with your own data showing the following:
ROLLNO NAME S1 S2 TOTAL MKS RESULT
30. Create a Pie chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
-% and Names of the expenditure should be displayed
-Change the colour of any one food expenditure
31 . Create a COLUMN chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
- Names of the expenditure should be displayed on each column
-Change the colour of any one food expenditure\item
- legend should be on left side
32. Create an Inventory Re-ordering Report with the following columns:
ITEM STOCK (Kgs) REMARKS
Steel 1000
Iron 600
Brass 500
-In Remarks column mention ?Reorder?, if the Stock of any item goes below 600 Kgs
-If the stock is 600 or above mention Remark ?No Need?
33 Create a Student Information Table with 5 records with your own data:
ROLLNO NAME PHONE ADDRESS DOB
Sort the table on Roll No and then by Name
34. Create a table and use any 5 Formatting options.
Move the table to Sheet 2
Rename the sheet
Add one column to the right and one row down to the table
Format as a Table.
35.The following are Sales figures of a company. Plot the figures I a Line chart:
YEAR: 2000 2001 2002 2003 2004 2005
SALES (Rs. In lakhs): 1000 1200 900 500 2000 1500
36. Set any 5, Page setup options/print options/sheet options for the following table with
your own data for 5 records:
ROLL NO MARKS
37. Create the following table:
ROLL NO SUBJECT MARKS
1 ECONOMICS 90
1 ECONOMICS 90
3 ACCOUNTS 90
2 ACCOUNTS 80
2 ACCOUNTS 80
4 ECONOMICS 50
I) Remove duplicate rows
II) Prepare Subject-wise Sub-Totals
38. Create the following table with own data:
ROLLNO NAME
i. Open a New Window containing current document
ii. View Side-by-Side
iii. Freeze top row
39. Find the following:
Amount to be received Rs.1000000
Rate of Interest 10%
Time 10 years
Amount to be invested at Present ?
i) If the rate of interest is 12% or 8%
ii) If the time period is 12 years or 8 years how much to be invested
40. Create the following table with your own data:
ROLLNO S1 S2 TOTAL
i) Total by using a Function
ii) Using Paste Special perform the following:
a) copy formula and paste in another cell
b) copy only values from formula and paste in another cell
c) Perform Add, Subtract operations
41. Show the following concepts by using appropriate examples:
i) Merge and Center
ii) Format Painter
iii)Wrap text
Iv) Shrink to fit long data in a cell
v)Fill colour in a cell
vi) increase column/row height/width
42. Sales figures of GPS for two months are as follows:
Product 1 Product 2
Range 1 =Jan 1000 2000
Range 2=Feb 3000 4000
Combine values from Ranges 1 and 2 into one new Range using Consolidation.
43. The following is the stock position of Excel Foundation Book in a Library:
OP STOCK RECEIPTS ISSUES CL STOCK
100 200 120
i) Findout the closing stock
ii)Hyperlink the Receipts quantity to Sheet 2 of the same Workbook to know details of
Receipts
iii)Hyperlink Issues to Sheet 3 of the same Workbook to know details of Issues.
44. Findout the Break-even output with the following:
Fixed Cost: Rs.40000
Average Variable Cost Rs.8
Market Price Rs.13
Output to produce to Break-Even ?
BE in Quantity=Fixed cost\(Market price-Average Variable cost)
BE in Sales =Sale price *BE in Quantity
FirstRanker.com - FirstRanker's Choice
FACULTY OF COMMERCE
OSMANIA UNIVERSITY
B.Com V-Semester ? CBCS (Computer and Computer Applications course)
Excel Foundation
Computer Lab ? Question Bank
Time: 60 Minutes Record: 10
Skill Test: 15
Total: 25
1. Create a Student table(5 Records) with appropriate Number formatting:
i) Roll Number ii) Name iii) Class iv) Date of birth
v) % of Marks vi) Fees paid in INR vii) Remarks
Use five data entry techniques while creating the table
2. Create a Student table with appropriate Data Validation criteria with the following
columns:
i) Roll Number ii) Name iii) Sale Quantity iv) Sale Value
v) Commission
a) Sale Quantity and Value should be in whole numbers
b) Commission is 8% of Sale value and be in two Decimals
c) Sale value column should accept only values from 5000
3. Construct a table of a student with the following:
Paper % Marks Grade Letter Grade Point
I 90
II 80
III 50
IV 40
V 65
Use appropriate function to choose the Grade Letter and Grade points basing on the
following logic:
Range of % Marks Grade Letter Grade Point
85-100 O 10
70-84 A 9
60-69 B 8
55-59 C 7
50-54 D 6
40-49 E 5
Less than 40 F 0
4. Find out Semester Grade Point Average (SGPA) of a student for Semester I with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 60 4 8
II 50 4 6
III 70 4 9
a)Use appropriate function to choose the Grade Letter using a suitable logical
function (Grade Letter for 60-69=B; 50-54=D; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade.
5. Find out Semester Grade Point Average (SGPA) of a student for Semester II with the
following:
PAPER %Marks CREDITS GRADE POINT GRADE LETTER CREDIT POINTS
I 70 4 9
II 65 4 8
III 70 4 9
a)Use appropriate function to choose the Grade Letter and Grade points
(Grade Letter and Grade points for 60-69=B; 70-84=A)
b) Credit Points=Credits x Grade point
c) SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
d) No SGPA for F grade
6. Find out Cumulative Grade Point Average (CGPA) of a student for Semesters I and II
with the following using appropriate functions:
Paper SEM I SEM II
Credits Grade points Credit points Credits Grade points Credit points
I 4 8 4 9
II 4 6 4 8
III 4 9 4 9
CGPA=
DIVISION=
a) Credit points= Grade points x Credits
b) CGPA= Total Credit points of both I and II Semesters/Total credits of both
Semesters
c) Find Division of the student:
Division Range of CGPA
Distinction 9-10
First 8-8.99
Second 6-7.99
Pass 5-5.99
7. The following are the Marks obtained by Students in three subjects. Draw a Bar
diagram with appropriate Design, Formatting options and Chart headings:
ROLL NO NAME S1 S2 S3
101 A 50 60 70
102 B 60 40 80
103 C 70 60 50
104 D 60 50 60
105 E 50 90 40
8. The following are the details of Expenditure. Draw a Pie diagram with appropriate
Formatting options, including Percentages and Chart headings:
Expenditure Rs.
Food 10000
Rent 5000
Clothing 1000
Fees 4000
9. Execute the following:
a) Change a Sheet Tab colour
b) Rearrange Worksheets
c) Hide a Worksheet
d) Compare sheets side-by-side
e) Use Find and Replace with an example
10. From the following table, select Non-contiguous cells having values 10,20,30 and
calculate Total, Average and Multiplication, using Define Name concept:
Paper S1 S2 S3
1 10 40 50
2 60 20 70
3 80 90 30
4 40 50 60
11. Add Sheet 1 values and Sheet 2 values with Sheet 3 values using Multi Sheet Range
concept:
Sheet 1 Sheet 2 Sheet 3
Roll No Marks Roll No Marks Roll No Marks
1 10 1 100 1 50
2 20 2 200 2 60
3 30 3 300 3 70
12. Create the following table:
Roll No Name S1 S2 S3 Total
1 Sastry 50 60 70
2 Prasad 80 90 100
3 John 90 80 70
4 Siva 60 50 40
5 Satish 50 60 70
From Total column:
a) Copy only Formula and Paste in the next (Right) cell
b) Copy only Values and Paste in the next cell
c) Copy only Formats and Paste in the next cell
d) Write a Comment in Total column of Roll No 4
e) Copy only the Comment and Paste in the next cell
13. Create the following table and apply formatting options as mentioned:
Roll No Name S1 S2
1 A 90 90
2 B 100 99
4 C 90 90
3 D 95 95
a) Resize the table to include one Row and one Column
b) Apply any table style
c) Sort the table on Roll No
d) Select ?Header Row? table style
e) Calculate Total and Average of each student
14. Derive Variances after comparing Total Standard cost with Actuals:
LABOUR(V) MATERIAL(V) TOTAL SEMI TOTAL ACTUALS VARIANCES
TASK HOURS RATE UNITS RATE VARIABLE FIXED (STD)
COST(TVC) COST COST
1 10 100 20 200 4000
2 20 100 40 200 12000
3 20 200 20 400 12000
I) Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000
ii) 40% if Total TVC if TVC is above Rs.10000
15. Calculate Total, Average and Result of the following:
ROLL NAME MARKS TOTAL AVERAGE RESULT
NO S1 S2 S3
1 A 80 90 100
2 B 60 70 20
3 C 90 80 10
i) For Pass, every subject should be 40 or above marks
ii) For Fail, any one subject be Less than 40
16 Prepare a Payroll with the following:
EMP ID E.NAME BASIC DA HRA GROSS PF ESI NET
101 A 1000
102 B 2000
103 C 3000
104 D 2000
105 E 5000
i) DA is 50% of Basic
ii) HRA is Basic + DA
iii) HRA is 15% of Basic
iv) Gross pay=Basic+DA+HRA
v) PF is 12% of Basic+DA
vi) ESI is 5%
vii) Net Pay= Gross-PF-ESI
17.Complete the following Income Statement for year 2017:
I-REVENUE Rs. In Lakhs
Sales 2000
Services 200
------
Total ?
------
II-EXPENSES
Salaries 300
Cost of Goods sold 400
------
Total Expenses ?
------
III-NIBT(Net Income Before Taxes) ?
(Total Revenue-Total Expenses)
Income Tax ?
-------
NET INCOME(NIBT-I Tax) ?
-------
(income tax=NIBT upto 200=Nil; 201-400=10.12%, 400 above=20.24% on NIBT)
18.Create the following table of a class:
ROLL NO NAME MARKS
1 A 82
2 B 92
3 C 62
4 D 62
5 E 72
i) Findout the topper of the class
ii)Findout the least scorer of the class
iii)Findout who got exactly 62 marks
19.Create the following Inventory table of Product No100 Product Name:Book:
DATE OPENING PURCHASES ISSUES CLOSING
1.1.2018 0 300 50
10.1.2018 200 50
20.1.2018 100 100
31.1.2017 100 50
i)Findout each day?s Closing balance
ii)Previous day Closing balance is next day Opening balance=system should reflect
automatically
iii)An entry about destruction of Books numbering 20 on 25.1.2018 should be taken
now
iv) If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?
20.Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
Using Conditional Formatting highlight, who scored :
i)More than 50 in S1 ii)Less than 50 in S2 and iii) Between 50 and 70 in S3
21.Create the following table:
MARKS
ROLL NO NAME S1 S2 S3 % RESULT DIVISION
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
i) To declare ?Pass?, to get >=40 marks in every subject.
ii) To declare ?Fail?, to get <40 in any one subject
iii) Division is only for ?Pass? candidates
Division= Distinction above 90%
First 80%-<90%
Second 60%-<80%
Pass 40%-<60%
??? <40%
22.Create Column chart for S1 and S3 only
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 70 80
3 C 40 40 30
4 D 60 50 40
5 E 50 60 70
23 Create the following table:
ROLL NO NAME S1 S2 S3
1 A 80 60 70
2 B 60 50 80
3 C 40 50 30
4 D 70 50 40
5 E 50 60 70
i) Find out the Maximum score in S1, Minimum score in S2 and use Count S3
ii) Find out Median of S1 scores and Mode of S2 scores
24.Create a table with the following and Calculate Fees Concession:
ROLLNO NAME CATEGORY % FEES CONCESSION
1 Iyer N 90
2 Nair D 60
3 Nambiar N 50
4 Krishnan D 70
5 Ambal G 40
Concession Policy:
CATEGORY % CONCESSION
N above 50 10%
D above 50 20%
G above 40 15%
i) In all other cases there is NO concession.
ii) Fees paid by each one of them is Rs.10000
25.Create the following table and calculate Incentive:
EMP ID NAME SALES(Rs) INCENTIVE
101 A 10000
102 B 20000
103 C 10000
Policy:
Sales between 10000-15000=5%
>15000-<20000=6%
>=20000-<30000=8%
26. Calculate Annual payment/instalment for a loan using an appropriate function:
Loan amount: Rs. 10,00,000
Years of repayment: 10 years
Rate of interest 10%
a) If the payments are Monthly, instead of Annual, what is the instalment
b) If the payments are quarterly, instead of Annual, what is the instalment
c) If the rate of interest is changed to 15% on Annual payment basis, what is the
instalment
27.Create a Pivot table with the following:
Days\Periods I II III
MON ENG FA IT
WED ENG FA IT
FRI ENG FA IT
Inter change the Rows into columns, using the Pivot table
The Pivot table be placed in a New Worksheet
28.Create a table showing the differences between VAT system and GST system. Find out
the Manufacturer?s invoice value:
Value to Manufacturer:
Under VAT Under GST
Production Cost 1000000 1000000
+ Profit (20%)
+Excise duty (10%)
=Total Production cost
+ VAT (18%)
+State GST (9%)
+Central GST(9%)
MANUFUCTURER?S
INVOICE VALUE
-Excise duty and VAT apply to VAT system only
-State and Central GST apply to GST system only
29.Create a table of 5 records with your own data showing the following:
ROLLNO NAME S1 S2 TOTAL MKS RESULT
30. Create a Pie chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
-% and Names of the expenditure should be displayed
-Change the colour of any one food expenditure
31 . Create a COLUMN chart basing on 5 records with your own data :
FOOD ITEM EXPENDITURE
- Names of the expenditure should be displayed on each column
-Change the colour of any one food expenditure\item
- legend should be on left side
32. Create an Inventory Re-ordering Report with the following columns:
ITEM STOCK (Kgs) REMARKS
Steel 1000
Iron 600
Brass 500
-In Remarks column mention ?Reorder?, if the Stock of any item goes below 600 Kgs
-If the stock is 600 or above mention Remark ?No Need?
33 Create a Student Information Table with 5 records with your own data:
ROLLNO NAME PHONE ADDRESS DOB
Sort the table on Roll No and then by Name
34. Create a table and use any 5 Formatting options.
Move the table to Sheet 2
Rename the sheet
Add one column to the right and one row down to the table
Format as a Table.
35.The following are Sales figures of a company. Plot the figures I a Line chart:
YEAR: 2000 2001 2002 2003 2004 2005
SALES (Rs. In lakhs): 1000 1200 900 500 2000 1500
36. Set any 5, Page setup options/print options/sheet options for the following table with
your own data for 5 records:
ROLL NO MARKS
37. Create the following table:
ROLL NO SUBJECT MARKS
1 ECONOMICS 90
1 ECONOMICS 90
3 ACCOUNTS 90
2 ACCOUNTS 80
2 ACCOUNTS 80
4 ECONOMICS 50
I) Remove duplicate rows
II) Prepare Subject-wise Sub-Totals
38. Create the following table with own data:
ROLLNO NAME
i. Open a New Window containing current document
ii. View Side-by-Side
iii. Freeze top row
39. Find the following:
Amount to be received Rs.1000000
Rate of Interest 10%
Time 10 years
Amount to be invested at Present ?
i) If the rate of interest is 12% or 8%
ii) If the time period is 12 years or 8 years how much to be invested
40. Create the following table with your own data:
ROLLNO S1 S2 TOTAL
i) Total by using a Function
ii) Using Paste Special perform the following:
a) copy formula and paste in another cell
b) copy only values from formula and paste in another cell
c) Perform Add, Subtract operations
41. Show the following concepts by using appropriate examples:
i) Merge and Center
ii) Format Painter
iii)Wrap text
Iv) Shrink to fit long data in a cell
v)Fill colour in a cell
vi) increase column/row height/width
42. Sales figures of GPS for two months are as follows:
Product 1 Product 2
Range 1 =Jan 1000 2000
Range 2=Feb 3000 4000
Combine values from Ranges 1 and 2 into one new Range using Consolidation.
43. The following is the stock position of Excel Foundation Book in a Library:
OP STOCK RECEIPTS ISSUES CL STOCK
100 200 120
i) Findout the closing stock
ii)Hyperlink the Receipts quantity to Sheet 2 of the same Workbook to know details of
Receipts
iii)Hyperlink Issues to Sheet 3 of the same Workbook to know details of Issues.
44. Findout the Break-even output with the following:
Fixed Cost: Rs.40000
Average Variable Cost Rs.8
Market Price Rs.13
Output to produce to Break-Even ?
BE in Quantity=Fixed cost\(Market price-Average Variable cost)
BE in Sales =Sale price *BE in Quantity
45. Using Built-in Excel Template, prepare Personal Monthly Budget.
46. Using Built-in Excel Template, prepare Billing Statement/Invoice
47. Generate a table with only RollNumbers till 20 using Autofill concept
Set the following printing options:
i)No. Of copies 10
ii)Orientation is Landscape
iii)Print on both sides
iv)Size A4
v)insert a page break after Roll No 8
vi)give Wide (Top,bottom,left and right 2.54 cms each) Margins
vii)give appropriate Header and Footer
48. The following is a Projected P&L Account of ABC Co for the year ending 31.3.2019
Cost of Production 100 Sales 150
Selling Expenses 20 Misc Income 20
Using IF() or PRODUCT() functions:
i) Calculate Gross/Net profit or loss
ii) Effect on Net profit or loss, if the Cost of Production is increased by 50%
iii) Effect on Net profit or loss, if the Sales are decreased by 50%
49. Create the following table and calculate Cash Discount:
PROD ID P.NAME SALES(Rs) CASH DISCOUNT
10 A 10000
15 B 20000
20 C 10000
Policy:
If Sales are between 10000-15000=3%
>15000-<20000=5%
>=20000-<30000=10%
50. Findout Future Value of the following, payable to a customer:
Rs.10000
Rs.20000
Rs.30000
i). If the rate of Interest is 10%, Time period is 10 years
ii).If the rate of interest is 10%, Time period is 10 years but compounded half yearly.
iii).If the above amounts are Future values, what are the Present values if Rate is 10%
and Time period is 10 years
***
FirstRanker.com - FirstRanker's Choice
This post was last modified on 27 January 2020