excel assignment (2)
TRANSCRIPT
EXCEL Assignment
1. An employee worksheet contains the following details
emp name
grade (1/2/3) DOJ Basic
Vehicle code (y/n)
Vehicle allowance HRA PF GROSS NET BONUS
a. HRA is calculated as follows:
GRADE HRA1 40%2 35%3 30%
b. Gross = basic + hra + vehicle allowance +bonus + and Net = gross –pfc. Vehicle allowance is based on grade provided he/ she own a vehicle indicated by y/n in
vehicle code.
GRADE HRA1 50002 30003 2000
d. Count no of people now owning a vehiclee. Find net salary of all employees who own a vehiclef. Extract all employee where grade is 1 or 2, net salary is between 100000 and 200000
both inclusiveg. An additional bonus is given to all employees who belong to grade 1 or 2 and had
joined before year 2000. h. Find maximum, minimum and average salary of all employees belonging to grade 1 i. Count number of people where bonus is >VAj. Find out average net salary and count number of employees who get net salary
below averagek. Find out which is the most frequently occurring gradel. Print out grade wise report of all employees with subtotal of net salary , gross salary
and also the grand total by using subtotal commandm. Extract records where employees name start with A, owns a vehicle and joined in the
year 2007n. Write remarks as promotion due if the employee birthday is today and no of years
employed is more than 5 years
o. make a name column and write the name as last name followed by first name eg. Gupta
priyap. Validate the DOJ column. The employee have attained the min age of 22 and max of
35.
q. Protect the entire worksheet except grade column.
2. A student worksheet contains the following data
RNO Name DOB M1 M2 M3 Total % Performance Remark
a. Based upon the % write the following performance columna. If % >=90 Ab. If %>=80 & <90 Bc. If %>= 70 & <70 Cd. If %>60 & <70 De. Otherwise fail.
b. Display a message “happy b’day” if it is student’s b’day todayc. Sort data according to %d. Calculate class average and count no. of students obtaining marks below class
averagee. Extract records where
a. Name is of 5 letterb. Begins with S and % between 80 and 90c. First name in one columnd. Last name in another columne. Where grade is A or B (use auto filter)
f. Insert 3 sector header which contains current date, name and address of the college and class (BBS/ BFIA – sem I)
g. Protect column M1, M2, M3 , total and %h. Draw column graph for top 3 students to show comparative performance in each
Draw a pie chart showing %marks in each subject by the topper i. Calculate age of the student in a separate columnj. Highlight in red all students who are failing k. Use file – page set up. Give the name of the lecturer in the footer
3. Consider the following worksheet:-
Salesman No.
Name Sales Date of Sale
commission Region Bonus
a. Extract last name in another columnb. Protect entire worksheet except name columnc. Calculate bonus based upon sale (use vlookup)
i. If sale > = 20000 bonus = 10% of saleii. If sales >= 10000 and <20000 bonus = 7% of sale
iii. If sales >=5000 and <10000 bonus = 10% of saleiv. Otherwise 0
d. Calculate commission i. If region is east or west and sales > 100000, commission is 10% of sales
otherwise 5% of salese. Calculate average bonus and number of salesman who get bonus below an average
bonusf. Which region has the maximum salesmang. Find total sales made todayh. Calculate average sales maximum sales in region easti. Sort data according to region (north, south, east, west)j. Extract all records where
i. Date of sale is before year 2000ii. Region is east or west and bonus is between 1000 and 2000
iii. Last name ends with Ana
4. Consider the following worksheet
Emp No.
Name
Salary Hire Date
Yrs Employed
Vacation_allotted Days_left Days_taken
1. Calcualate vacation allotted using hlookup as followsa. If yrs employed <5 vac_allotted = 5b. If yrs emp>5 & <10 vac_allotted = 10c. If yrs emp>10 & <15 vac_allotted = 15d. If yrs emp>15 vac_allotted = 20
2. Draw a stack column graph showing proportion of vac allotted to days taken3. Display name of the company in the background4. Highlight all cells in red wherever days left is –ve5. Calculate total salary of people employed for more than 10 yrs6. Count no of people who have joined today7. Count cells containing –ve values in days left8. Extract records where
a. Hire date is between 2000 and 2010 both inclusiveb. Name begin with N and days left is –ve
5.
E.No fname lname design salary dob doj increment
yrs employed
bonus
a. Calculate max and min salary being drawnb. Calculate total salary of employee who belong to designation GM
c. Calculate increment as follows i. If designation = GM (General Manager) incre = 20% of salary
ii. If designation = SM(Sales Manager) increment = 15%iii. If designation = PM (Production Manager) increment = 12%
Use hlookup and if function separately
d. Calculate years employed based on thise. Calculate bonus as follows
i. If years employed >=20 bonus = 20% of salaryii. If years employed >=15 & <20 bonus = 10% of salary
iii. If years employed >=10 & <15 bonus = 7% of salaryiv. If years employed >=5 & <10 bonus = 5%v. Otherwise = 0
Use VLOOKUP
f. Combine fname and lname and store in another columng. Write a message “Happy B’day “ if today is employee birthday in the remarks column
otherwise blank h. Count no of people who have joined todayi. Calculate total salary of people belonging to GM or SM, have worked for more than 10 years
and have increment >10000j. Count no of people drawing salary > average salaryk. Extract all records where
a. DOJ is between the years 2005 abd 2008 b. Designation GM or PM and salary between 10000 and 20000 both inclusive c. Increment > bonusd. Salary > Average salary
l. Using data subtotal command to calculate rank wise subtotal of salary, bonus ad increment m. Draw a stacked column graph to show designation wise salary, bonus and increment n. Protect salary columno. Find max bonus for designation PM
6. Write a worksheet and name it as “ITEM MASTER”.
Item code
Description
Rate
Qty. in stock
Date of transaction
Customer name
Qty. ordered
Invoice No
Total Discount
Net order value
Goods return
a. Add detail description of the goods as comment b. Value of stock = rate * qty. on hand c. Qty. ordered should not be more than the qty in stock d. Invoice no is first 4 digits of the item code of the goods ordered followed by last name of the
customer followed by the month and year of date of transaction.
e. Discount is calculated as follows: Value Discount
a. <5000 0b. 5000 – 10000 2% of the valuec. 10000 – 25000 5% of the valued. 25000 – 50000 7% of the valuee. >50000 10% of the value
f. Net order value is value of goods ordered – discountg. Goods return should have value only when the goods has already been sold means date of
transaction should be there and qty of defects should not be more than qty orderedh. Sort the worksheet on the basis of date of transaction, order no, and item no. i. Filter the records that meet the following criteria
a. Item whose net value is between 1000 and 25000 b. Items that are moderately expensivec. Items whose net value is greater than the average net value d. Items that were sold between a particular quarter (take quarter as per your choice)
and whose order value was greater than 5000
7. Consider the following worksheet
Rollno name address dob household income
fee concession
marks% region remarks
a. Count the number of students whose a. Name is having exactly 5 lettersb. Address is having text “VIVEK VIHAR”. Exact match is required c. Add/Income/Marks are not availabled. Name start with a and ends with a e. First name is having exactly 6 lettersf. B’day falls in the month of June or July
b. If address is having any occurrence of text DELHI/ CHENNAI/ KOLKATA/AHMEDABAD then write NORTH/SOUTH/EAST/WEST respectively in region column
c. Calculate the min and max age of students d. If Income is <=5000 and marks >=80% then fee concession will be true otherwise falsee. IF length of Roll No. is greater than 7 write INVALID ROLL no in the remarks column. If
remarks is already having some text then concatenate new text with the existing one. f. Wrap the column heading text if it is large.
8. Consider the following worksheet
I II III IV VI VII VIII IX X XI XII XIII XIV XV
I II
Subject: Paper No: Teacher's Name:
Year: 2010-11 Course - BBS - III Semester VI (Finance B)
V
S. No.Class Roll
No.Enrollment
No. Name of the Student
Marks of Written assignement out
of 33 eachMarks of House
Examination out of 45
Marks of House
Examination out of
10
Total of VIII , XI, XIII out of 25
Scale Down to Column XIV out
of 15
Marks in Project /
Term Paper out
of 34
Aggregate Marks of Written
assignement and Projects / Term Paper
out of 100
Marks of Column VII
out of 10
No. ofLectures Delivered
No. of lectures attended
Marks of Attendance
out of 5
a. S.No should be auto generatedb. Merge cells to write label for col Vc. Col VII should be calculated as I +, II of V + VI d. Validate I and II of V with marks not greater than 33 and VI with marks not
greater than 34e. Calculate the aggregate marks out of 10 in col VIf. Round off marks up to 2 decimal places for all the columns wherever applicable
but up to o decimal places in the col XIVg. Calculate marks of attendance (Col XI) as follows:
i. Less than 67% = 0 ii. >=67 but <70% = 1
iii. >=70% and <75% = 2 iv. >=75% and <80% = 3v. >=85% & above = 5%
h. Roll No should have the following format: XXXX/YYWhere X is any digit between 0 to 9 and YY are two digits of year of admissioni. Sort the data Roll No wise in ascending orderj. If the YY is less than the current year then write EX Student in REMARKS Columnk. Calculate median , mode , standard deviation of project/ term paper marksl. No of lectures attended should not be more than the no of lectures deliveredm. Count the no of students
i. Where attendance is less than 2 ii. Who did not appear in House Exam
iii. Who scored more than 20 in overall internal assessment iv. Who scored less than 8 in the individual assessment v. Whose marks are less than the average marks in House Exam
vi. Got full marks in attendancen. Calculate Max. and Min. marks from Col XV
9. Consider the following worksheet
District Jan Feb Mar Quarter Total
c. Enter the Sales data for the quarter district wise
d. Calculate district wise total and month wise totale. Calculate average and std dev month wise abd compare the results f. Calculate amount of growth from last year to this year (district wise and month
wise) Use absolute, relative and mixed referencing g. Insert name and logo of the company in the backgroundh. Insert name of the town as a comment. Print all comments in the end
10. Below is the record high temperature (in degree Fahrenheit) for 50 weather stations 151 123 135 102 200192 132 151 107 145147 144 151 155 133164 139 190 158 153104 193 163 174 131105 153 190 187 116135 111 118 102 167105 166 129 115 155184 152 128 145 168180 166 131 137 149176 151 150 181 161186 134 122 150 122
a. Calculate following statistical parameters:i. Range, mean, mode, median , max, min, std. dev., total no of
observation , variance , frequency in range 100-120, 120-140, 140-160, 160-180, 180-200
ii. Insert a backgroundiii. Highlight in red all cells where values is >=120
11. Create 4 worksheets (DivA, Div B , Div C, DIV D) whoch contains the following data
Product Jan Feb march Total
P1
P2
P3
a. Create a 5th worksheet that consolidates the data of the 4 division a. Bu using a formula b. By using data consolidate command (by position)
b. Alter data in such a way that each division sells some common products an some are different. Now consolidate by category. Create link
c. Store the data of 4 divisions in 4 different workbooks and use data consolidate command in 5th workbook (by category). Use Max functions
d. In the final work book sort the data in ascending order of sales for a product (First transpose a data)
e. How much sales of January to be increased in the 5th work book if the total sales is increased by 10 %
12. You have been asked to collect data for business organisation. Feed the data in excel and apply the mathematical and statistical function wherever applicable for the analysis of the data. Also create a pivot table and pivot chart with the relevant details. Also apply column, pie, bar, doughnut scatter chart with the relevant details wherever applicable.
13. movie worksheet
1. Show timing should be taken from the list2. Ticket charges are based on the region
South 200East 125West 150North 175
3. Entertainment tax is levied only when the movie type is A or (U/A)4. Validate No of children column. It should not take the data if the movie type is A5. Children charges are 3/4 of the adult charges6. Total Amount is (Ticket charge+ entertainment tax) * no of adult * no of Children7. if snacks required is Y then the total charge is incremented by 10% of the total charge8. find out the most occuring region for watching movie9. Extract records
a. Show timings are before 12 noonb. region is E or W and total person to watch movie is more than the average no. of person per movie
10. Calculate total charges for all those movie which are of type A and snacks required is Y11. Count the no of children who have watched the evening show.12. Make a Pivot table to analyze data region wise , cinema hall , movie type etc.13. Insert a clustered column graph to show the percentage of childeren and adult come to watch the movie
region wise
14. Order details worksheet
Meal id
meal description
customer name order no
shipping required
shipping charges Remark
1001A Y/N1001B1001C2001A2001B
1. Description will be based on 1st digit if the meal id ifa. Burgerb. Sandwitchc. Cold drinksd. Chipse. Chocolates
2. Add Comment as detailed description of meal (for eg. - if cheese burger if id 1001A, Aloo tikki burgerif 1001B and so on)
3. Order No. is first 2 digit of meal id + initials of first and last name of customer + day & month of today's date
4. Shipping charge is only if the shipping required is Y and charges are as follows:<10 kms Nil10 kms - 20 kms 5021 kms - 30 kms 75>30 kms 100
5. Add a remark column and enter meal "free of cost" if the meal is delivered after 1 hr6. Extract all those records where
a. add a remark column and enter meal "free of cost" if the meal is delivered after 1 hrb. Meal is type burger and order is not todayc. distance is more than 15 kms and remarks is blank
7. Insert a 3 D bar chart to show the meal description and their amount8. Count the order where the shipping charges have been paid.
15. population census
AreaPerson Name
No ofmembers inthe family DOB
Educational Qualification Address
house rented /owned
vehicle owned
Family Income
No ofmembers dependent
No ofpeople ill
HSC/ SSC/Grad/ PostGrad y/n
1. Area is based on the address : if address have any occurrence of Delhi / Punjab/Haryana/ J&K – North Zone, Chennai/ AP/ Karnataka – South , Maharashtra / Gujrat/ Rajasthan / MP – Central, West Bengal / Assam / Meghalaya / Bihar – East2. Extract initial of first name followed by the last name from the person name column.
3. Find out the Age of the person4. Validate no of dependents and no of illness. It should not be more than the no of dependents 5. Extract all those records who have
a. owned house, car and monthly family income > 50000 b. no of illness > no of dependentsc. DOB before 1980 and educational qualification is post grad (use auto filter)
6. Make pivot table and chart by using some dimesions of the data7. Count the sum of illness area wise8. Calculate sum of the total income for those persons who have dependent more than the
average dependent.