1. Describe the usage of mail merge option in Ms-Word and how to prepare New Year wishes letter for each student from MBA department?
2. Write a step by step procedure for creating an exercise for employee salary sheet preparation; the following columns will be used in this exercise
Employee Department Salary
Kumar IT 15000
Suresh IT 17000
Rajesh Advertising 16000
Sangeetha HR 20000
Ashwin HR 45000
Barathi Accounting 13000
Sineha Advertising 23000
Tamizh Accounting 20000
Naveen IT 14000
a. Calculate the number of employees in the Accounting department
b. Calculate the number of employees with a salary greater than 15000.
c. Calculate the number of employees who are NOT in the IT department. (Hint:The not equal sign is <>)
d. Calculate the number of employees with a salary greater than or equal toSuresh’, and less than Sineha. Remember to use cell addresses (Hint: Use
two countif operations.)
e. Calculate the sum of all salaries that are below the average of salaries.
f. Using AVERAGEIF, calculate the average of all salaries that are less than or equal to Tamizh.
g. Create a new filter that only shows the data for IT department.
h. Show the pivot table information for department wise.
3. Write a step by step procedure for creating an exercise for student mark sheet preparation. The following columns will be used in this exercise.
a. Rollno Instruction:
b. Name a. To calculate the total, average
c. Department b. To find out the result (all subject should be >=50) and grade (Average is >=80 ‘S’)
d. Gender c. To find out the department wise percentage
e. Subject 1 d. To find out the department wise total students
f. Subject 2 e. To find out the gender wise average for each department
g. Subject 3 f. To create a line chart for department wise average
h. Total g. To create a bar chart for gender wise average for each department
I. Average
j. Result
k. Grade
8
4. Write a step by step procedure for creating employees salary sheet, the following options and formulas of MS-Excel will be used in this exercise.
Sum(range), if(), sumif(), Countif(), Data Data Validation
Emp- ID Name Basic
Salary
House
Rent
ConAllowance
Medical
Allowance
Gross
Salary Tax Salary Net
1 Suresh 12000
2 Ashwin 14000
3 Abirami 11500
4 Naveen 12500
5 Meena 13000
6 Barathi 13500
7 Ganesan 8000
Questions:
Type the following worksheet:
a. In ‘Basic Salary’ column values greater than 10,000 not allowed
b. Calculate House rent (if Basic Salary is greater than 12000 then 45% otherwise 30%)
c. Calculate Conv. Allowance (if Baisc Salary is greater than 13000 then 30% otherwise 20%)
d. Calculate Medical Allowance (if Basic Salary is greater than 14000 then 60% otherwise 45%)
e. Calculate Gross Pay
f. Calculate Tax (if Gross is greater than 25000 then 10% otherwise 0)
g. Calculate Next Pay
h. Calculate total net salary of those employees whose salary is less than 20000
i. Count No. of employees who are not giving tax