FACULTY OF MANAGEMENT, OSMANIA UNIVERSITY

------------------------------------------------------------------------------------------------------------
MBA I Year I Sem
Computer Lab - Practical Question Bank

COMPUTER LAB

MS EXCEL:

1. Enter the given data into an excel sheet. Find Total Marks, Average and Percentage:

Roll No Name MOB AFM MM Elective-I Elective-II

001 Sridevi 50 70 60 55 66
002 Ramdevi 60 50 50 46 65
003 Maya 40 40 45 55 64
004 Sofia 50 60 60 78 66
005 Revathi 74 77 61 46 68

2. For Problem (1) data, Draw a Suitable Chart ( bar, line )

3. Create a worksheet with following fields

EmpNo, EName, Basic Pay (BP), Travelling Allowance (TA),
Dearness Allowance (DA), House Rent Allowance (HRA), Income Tax (IT),
Provident Fund (PF), Net Pay (NP)
Compute Net Pay with given : DA= 30% of BP, HRA=20% of BP, TA=17.5% of BP, IT=15% of
BP, PF=12.5% of BP

4. Apply Auto formatting for the following Table and find total by using Auto Sum option.

Roll No Name MOB AFM MM Elective-I Elective-II

001 Suresh 70 60 70 65 76
002 Ramesh 40 80 60 56 55
003 Mahesh 40 50 55 45 44
004 Somesh 60 40 30 68 76
005 Rajesh 54 67 81 66 78

5. The Marks obtained by the students of MBA Course in four subjects are given below:

Roll No Name MOB AFM MM IT

101 Sai Kumar 62 70 68 57

102 Sai Kiran 54 55 67 67
103 Sai Sudheer 49 65 56 78
104 Sai Prakash 43 64 58 56
105 Sai Sri 53 57 74 66

Find out and List the Students Using Conditional Formatting for the below
a) More than 50 in MOB b) Less than 75 in MM c) Between 50 and 70 in IT
6. The below given data is employees salary components:

Emp ID Name Salary Overtime Incentives

OU001 Ram Kumar 70,000 5000 4400

OU002 Shyam Kumar 54,000 4000 3500
OU003 Manoj Kumar 55,000 3000 7800
OU004 Pavan Kumar 45,000 2500 4600
OU005 Sukumar 66,000 1900 4400

Find out and List the Students Using Conditional Formatting for the below
a) More than 35,000 Salary b) Less than 3,500 Overtime
c) Between 4000 & 5000 Incentives

7. Use data from Q6 and Highlight Students Using Conditional Formatting

a) More than 45,000 Salary b) Less than 3,000 Overtime
c) Between 4000 & 5000 Incentives

8. Draw a suitable Chart for the given data:

Roll No Name MOB AFM MM IT

101 Sai Kumar 62 70 68 57

102 Sai Kumari 54 55 67 67
103 Sai Deepthi 49 65 56 78
104 Sai Prakash 43 64 58 56
105 Sai Sri 53 57 74 66

i) Find out the Maximum Marks in AFM, Minimum Marks in MM

ii) Find out Median of IT Subject and Mode of AFM Subject

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

ROLL NO 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

10. The following are Sales figures of a Firm. Plot the figures in a Line Chart YEAR: 2005
 2006 2007 2008 2009 2010
SALES (Rs. In lakhs): 100 130 110 200 150 135

11. Demonstrate these using Q5 data, Page Setup options/ Print Options

12. Demonstrate these using Q5 data:

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

13.Demonstrate these using Q5 data, Use these Settings

i) No. Of copies 4
ii) Orientation Landscape
iii) Print on both sides
iv) Size A4
v ) insert a page break after few records
v i ) give Wide (Top,bottom,left and right 2.54 cms each) Margins
vii) give appropriate Header and Footer

14. For the given data, Calculate the Amount Payable per Annum.

Principal Amount : 5,00,000

Rate of Interest : 6%
Time period : 8 Years
Amount to be paid : Calculate

Check the Results with these changes too:

a) Rate of Interest: 4% and 7% b) Time Period: 4 Years and 2 Years

15. Create a Excel sheet with the following fields as Sales table.
i) Month ii) Item iii) Quantity iv) Price
v) Commission
Use Data Validation criteria for:
a) Quantity and Price should be whole numbers
b) Commission @ 3.5% of Price should be allowed only two decimals
c) Price should accept 5000 and above values only

16. Prepare Pivot Table for the given data:

Department Employee Name Salary

HR Mumtaz 20,000
Finance Fatima 18,500
IT Sai Deepthi 17,500
HR Stella 13,000
Finance Sai Sri 15,000
 IT Dayana 10,000

17. Create a Student Table in Excel with the following, Use appropriate Formula/Function to fill the
Grade Letter and Grade Point Columns and Find SGPA

Subject Credits Marks Grade Letter Grade Point Credit Points

MOB 5 56
AFM 5 53
MM 5 64
Elective I 4 73
Elective-II 4 67

Note:
A)
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
B). Credit Points=Credits x Grade point
C). SGPA=Total Credit points/Total Credits
18. Use the given data, Draw a Bar diagram with appropriate Design, Formatting options and Chart
headings

Roll No Name MOB AFM MM Elective-I Elective-II

OU001 Suresh 70 60 70 65 76
OU002 Ramesh 40 80 60 56 55
OU003 Mahesh 40 50 55 45 44
OU004 Somesh 60 40 30 68 76
OU005 Rajesh 54 67 81 66 78

19. Use the given data of Sales, Draw a Pie diagram with appropriate Formatting options,
including Percentages and Chart headings:

Sales Rs.
North 500000
South 300000
East 100000
West 400000

20. Use Simple data in multiple sheets data and Demonstrate the following:
 a. Change a Sheet Tab colour
b. Rearrange Worksheets
c. Hide a Worksheet
d. Compare sheets side-by-side

21. Use Simple excel sheet data and Demonstrate the following:
Find and Replace with an example

22. Demonstrate MULTI SHEET RANGE

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
OU5 50 OU4 65 OU1 45
OU9 60 OU6 69 OU2 67
OU3 80 OU8 55 OU7 48
OU11 45 OU13 46 OU14 47

23. Use the below given data, Compute Total and Demonstrate the below given

Roll Name MOB AFM MM Elective-I Elective-II Total

No
001 Suresh 70 60 70 65 76
002 Ramesh 40 80 60 56 55
003 Mahesh 40 50 55 45 44
004 Somesh 60 40 30 68 76
005 Rajesh 54 67 81 66 78

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 003
e. Copy only the Comment and Paste in the next cell

24. Use the Data from Q23 and Demonstrate the below given:
a) Apply any table style
b) Sort the table on Roll No
c) Select ‘Header Row’ table style

25. Use the Data from Q23 and Compute Total, Average, Result and Division:
a) Every subject should be 40 or above marks, then “Pass” or “Fail”
b) Average above 60, “First” Division, Average below 60, “Second”, Average below 50,
“Third”

26. Create the following Inventory table:

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) Find out each day’s Closing balance
ii) Previous day Closing balance is next day Opening balance=system should reflect
automatically

27. 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-Fixed Cost is 20% of Total TVC if TVC is upto Rs.10000

ii) 40% if Total TVC if TVC is above Rs.10000
MS ACCESS:

1. Use the Following data to Create a ‘Student’ database.

Roll No Name Course DOB Rank

OU001 Mumtaz MBA I 12-09-1981 100
OU002 Fatima MBA II 11-05-1983 54
OU003 Sai Deepthi MBA II 03-04-1982 23
OU004 Stella MBA I 24-02-1982 4
OU005 Sai Sri MBA I 24-10-1983 9

2. Use the Following data to create a database ‘Club’

Member Name Address Contact Position
ID
DC001 Shastri Deccan Club, Hyderabad 9000000009 President
NC010 Mistry New Club, Hyderabad 8000000008 Secretary
NZC01 Riyaz Nizam Club, Hyderabad 7000000007 Treasurer
CC020 Varma Country Club, Hyderabad 6000000006 Vice-President

3. Use the Following data to create ‘employee’ database

Department Employee Name Date of Joining Salary

HR Mumtaz 04-12-2020 20,000
Finance Fatima 14- 02-2018 18,500
IT Sai Deepthi 24-01-2019 17,500
HR Stella 11-04-2017 13,000
Finance Sai Sri 04-12-2020 15,000
IT Dayana 04-12-2020 10,000

4. Create a database ‘Student’

Roll No Name Course Marks Grade

OU001 Mumtaz MBA I6 65 B
OU002 Fatima MBA II 43 C
OU003 Sai Deepthi MBA II 87 A
OU004 Stella MBA I 61 B
OU005 Sai Sri MBA I 88 A
Demonstrate the following:
a) Sort the data by Roll No
b) Display Roll No, Name and Marks where marks > 70
c) Display Roll No, Name and Marks where Grade= A

7
5. Create a database ‘Library’ and create a table as ‘Books’ and execute the queries given below:

Author Name Book title Book type Year of publication

Gupta Management Accounting Accounting 2002
Jain Financial Accounting 2000
Reddy Corporate Accounting Accounting 1990

a) Display Book title, book type, author name where author = Jain and
book type = Accounting.
b) Display Author Name, book type, year of publication where year above 2000.

6. Create Employee Table by using Design Wizard with 5 fields and 5 records

7. Create Workers Table by using Design Wizard with 4 fields and 5 records

8. Creating a Table Using the Design View

9. Creating a Table Using Table Wizard

10. Create a Table for Student and Use the following
Setting Primary Key, Working with Fields and Records

11. Extracting Data Across Tables

12. Creating and Executing Query in the Design View

13. Creating Database Using blank Database

14. Creating a Database Using the Database Wizard

15. Creating and Using Forms

16. Creating Forms Using the AutoForm

17. Creating Forms Using the Form Wizard

18. Creating Forms Using The Design View

19. Creating and Using reports

20. Creating and Using Reports

21. Creating reports using the Report Wizard

22.Data transfer between Excel and Access