Download OU B.Com (Important Questions) B.Com (CBCS) 5th Semester Excel Foundation QB

FirstRanker.com

www.FirstRanker.com

OSMANIA UNIVERSITY

--- Content provided by‌ FirstRanker.com ---

B.Com V-Semester - CBCS (Computer and Computer Applications course)

Excel Foundation

Computer Lab - Question Bank

1. Create a Student table(5 Records) with appropriate Number formatting:

   1. Roll Number

   --- Content provided by‍ FirstRanker.com ---

   2. Name
   3. Class
   4. Date of birth
   5. % of Marks
   6. Fees paid in INR

   --- Content provided by FirstRanker.com ---

   7. Remarks

   Use five data entry techniques while creating the table

2. Create a Student table with appropriate Data Validation criteria with the following columns:

   1. Roll Number

   --- Content provided by‌ FirstRanker.com ---

   2. Name
   3. Sale Quantity
   4. Sale Value
   5. Commission
   1. Sale Quantity and Value should be in whole numbers

   --- Content provided by‌ FirstRanker.com ---

   2. Commission is 8% of Sale value and be in two Decimals
   3. 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:

   --- Content provided by⁠ FirstRanker.com ---

   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

   1. 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)
   2. Credit Points=Credits x Grade point
   3. SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.

   --- Content provided by‍ FirstRanker.com ---

   www.FirstRanker.com for F grade.

5. www.FirstRanker.com www.FirstRanker.com

   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

   1. Use appropriate function to choose the Grade Letter and Grade points (Grade Letter and Grade points for 60-69=B; 70-84=A)

   --- Content provided by‍ FirstRanker.com ---

   2. Credit Points=Credits x Grade point
   3. SGPA=Total Credit points/Total Credits. Adjust to 2 decimals.
   4. 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:

   --- Content provided by‌ FirstRanker.com ---

   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=

   1. Credit points= Grade points x Credits
   2. CGPA= Total Credit points of both I and II Semesters/Total credits of both Semesters
   3. Find Division of the student:

   --- Content provided by⁠ FirstRanker.com ---

   Division	Range of CGPA
   Distinction	9-10
   First	6-7.99
   Second	5-5.99
   Pass

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

--- Content provided by FirstRanker.com ---

9. www.FirstRanker.com www.FirstRanker.com

   Execute the following:

   1. Change a Sheet Tab colour
   2. Rearrange Worksheets
   3. Hide a Worksheet

   --- Content provided by⁠ FirstRanker.com ---

   4. Compare sheets side-by-side
   5. 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

--- Content provided by FirstRanker.com ---

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	80	60	70
    4	Siva	60	50	40
    5	Satish	50	60	70

    From Total column:

    1. Copy only Formula and Paste in the next (Right) cell

    --- Content provided by‌ FirstRanker.com ---

    2. Copy only Values and Paste in the next cell
    3. Copy only Formats and Paste in the next cell
    4. Write a Comment in Total column of Roll No 4
    5. Copy only the Comment and Paste in the next cell

--- Content provided by​ FirstRanker.com ---

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

    1. Resize the table to include one Row and one Column
    2. Apply any table style
    3. Sort the table on Roll No
    4. Select 'Header Row' table style

    --- Content provided by‍ FirstRanker.com ---

    www.FirstRanker.com Calculate Total and Average.

14. www.FirstRanker.com www.FirstRanker.com

    Derive Variances after comparing Total Standard cost with Actuals:

    TASK	LABOUR(V)	MATERIAL(V)	TOTAL	SEMI	TOTAL	ACTUALS	VARIANCES
    	HOURS	RATE	UNITS	RATE	VARIABLE COST(TVC)	FIXED COST	(STD) COST
    1	10	100	20	200			4000
    2	20	100	40	200			12000
    3	20	200	20	400			12000

    1. Semi-FixedCost is 20% of Total TVC if TVC is upto Rs.10000

    --- Content provided by‍ FirstRanker.com ---

    2. 40% if Total TVC if TVC is above Rs.10000

15. Calculate Total, Average and Result of the following:

    ROLL NO	NAME	MARKS	TOTAL	AVERAGE	RESULT
    NO		S1	S2	S3
    1	A	80	90	100
    2	B	60	70	20
    3	C	90	80	10

    1. For Pass, every subject should be 40 or above marks
    2. For Fail, any one subject be Less than 40

    --- Content provided by FirstRanker.com ---

16. 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	2000
    104	D	2000
    105	E	5000

    1. DA is 50% of Basic
    2. HRA is Basic + DA
    3. HRA is 15% of Basic

    --- Content provided by‍ FirstRanker.com ---

    4. Gross pay=Basic+DA+HRA
    5. PF is 12% of Basic+DA
    6. ESI is 5%
    7. Net Pay= Gross-PF-ESI

--- Content provided by‌ FirstRanker.com ---

17. www.FirstRanker.com

    Complete the following Income Statement for 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:

    --- Content provided by‌ FirstRanker.com ---

    ROLL NO	NAME	MARKS
    1	A	82
    2	B	92
    3	C	52
    4	D	62
    5	E	72

    1. Findout the topper of the class
    2. Findout the least scorer of the class
    3. Findout who got exactly 62 marks

19. Create the following Inventory table of Product No100 Product Name:Book:

    --- Content provided by‌ FirstRanker.com ---

    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

    1. Findout each day's Closing balance
    2. Previous day Closing balance is next day Opening balance=system should reflect automatically
    3. An entry about destruction of Books numbering 20 on 25.1.2018 should be taken now
    4. If the unit value is Rs.100, what is the closing stock value as on 31.1.2018?

--- Content provided by⁠ FirstRanker.com ---

20. www.FirstRanker.com www.FirstRanker.com

    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 :

    1. More than 50 in S1
    2. Less than 50 in S2 and

    --- Content provided by​ FirstRanker.com ---

    3. Between 50 and 70 in S3

21. 21.Create the following table:

    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

    1. To declare 'Pass', to get >=40 marks in every subject.
    2. To declare 'Fail', to get <40 in any one subject

    --- Content provided by‍ FirstRanker.com ---

    3. Division is only for 'Pass' candidates

    Division= Distinction above 90%

    First 80%-<90%

    Second 60%-<80%

    Pass 40%-<60%

    --- Content provided by‌ FirstRanker.com ---

    <40%

22. 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. 23 Create the following table:

    --- Content provided by‌ FirstRanker.com ---

    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

    1. Find out the Maximum score in S1, Minimum score in S2 and use Count S3
    2. Find out Median of S1 scores and Mode of S2 scores

24. www.FirstRanker.com www.FirstRanker.com

    24.Create a table with the following and Calculate Fees Concession:

    --- Content provided by‍ FirstRanker.com ---

    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%

    1. In all other cases there is NO concession.
    2. Fees paid by each one of them is Rs.10000

25. 25.Create the following table and calculate Incentive:

    --- Content provided by​ FirstRanker.com ---

    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%

--- Content provided by⁠ FirstRanker.com ---

26. 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%

    1. If the payments are Monthly, instead of Annual, what is the instalment
    2. If the payments are quarterly, instead of Annual, what is the instalment
    3. If the rate of interest is changed to 15% on Annual payment basis, what is the instalment

--- Content provided by FirstRanker.com ---

27. 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. www.FirstRanker.com

    --- Content provided by​ FirstRanker.com ---

    First create a table showing the difference between VAT system and GST system and find the Manufacturer's invoice value:

    Value to Manufacturer:

    
    

    This download link is referred from the post: DUET Last 10 Years 2011-2021 Question Papers With Answer Key || Delhi University Entrance Test conducted by the NTA

    	Under VAT	Under GST
    Production Cost	1000000	1000000
    + Profit (20%)
    +Excise duty (10%)
    =Total Production cost
    + VAT (18%)

--- Content provided by FirstRanker.com ---