EXCEL Assignment

1. An employee worksheet contains the following details

DOJ

Basic

HRA

PF

GROSS

NET

BONUS

a. HRA is calculated as follows: GRADE 1 2 3 HRA 40% 35% 30%

b. Gross = basic + hra + vehicle allowance +bonus + and Net = gross pf c. Vehicle allowance is based on grade provided he/ she own a vehicle indicated by y/n in vehicle code.

GRADE 1 2 3

HRA 5000 3000 2000

d. Count no of people now owning a vehicle e. Find net salary of all employees who own a vehicle f. Extract all employee where grade is 1 or 2, net salary is between 100000 and 200000 both inclusive g. 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 >VA j. Find out average net salary and count number of employees who get net salary below average k. Find out which is the most frequently occurring grade l. Print out grade wise report of all employees with subtotal of net salary , gross salary and also the grand total by using subtotal command m. Extract records where employees name start with A, owns a vehicle and joined in the year 2007 n. 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 priya p. 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 column a. If % >=90 A b. If %>=80 & <90 B c. If %>= 70 & <70 C d. If %>60 & <70 D e. Otherwise fail. b. Display a message happy bday if it is students bday today c. Sort data according to % d. Calculate class average and count no. of students obtaining marks below class average e. Extract records where a. Name is of 5 letter b. Begins with S and % between 80 and 90 c. First name in one column d. Last name in another column e. 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 column j. 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 column b. Protect entire worksheet except name column c. Calculate bonus based upon sale (use vlookup) i. If sale > = 20000 bonus = 10% of sale ii. If sales >= 10000 and <20000 bonus = 7% of sale

d.

e. f. g. h. i. j.

iii. If sales >=5000 and <10000 bonus = 10% of sale iv. Otherwise 0 Calculate commission i. If region is east or west and sales > 100000, commission is 10% of sales otherwise 5% of sales Calculate average bonus and number of salesman who get bonus below an average bonus Which region has the maximum salesman Find total sales made today Calculate average sales maximum sales in region east Sort data according to region (north, south, east, west) Extract all records where i. Date of sale is before year 2000 ii. 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 follows a. If yrs employed <5 vac_allotted = 5 b. If yrs emp>5 & <10 vac_allotted = 10 c. If yrs emp>10 & <15 vac_allotted = 15 d. If yrs emp>15 vac_allotted = 20 2. Draw a stack column graph showing proportion of vac allotted to days taken 3. Display name of the company in the background 4. Highlight all cells in red wherever days left is ve 5. Calculate total salary of people employed for more than 10 yrs 6. Count no of people who have joined today 7. Count cells containing ve values in days left 8. Extract records where a. Hire date is between 2000 and 2010 both inclusive b. Name begin with N and days left is ve

5. E.No fname lname design salary dob doj increment yrs bonus employed

a. Calculate max and min salary being drawn b. 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 this e. Calculate bonus as follows i. If years employed >=20 bonus = 20% of salary ii. If years employed >=15 & <20 bonus = 10% of salary iii. If years employed >=10 & <15 bonus = 7% of salary iv. If years employed >=5 & <10 bonus = 5% v. Otherwise = 0 Use VLOOKUP f. Combine fname and lname and store in another column g. Write a message Happy Bday if today is employee birthday in the remarks column otherwise blank h. Count no of people who have joined today i. Calculate total salary of people belonging to GM or SM, have worked for more than 10 years and have increment >10000 j. Count no of people drawing salary > average salary k. 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 > bonus d. 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 column o. Find max bonus for designation PM 6. Write a worksheet and name it as ITEM MASTER. Ite m cod e Descriptio n Rat e Qty. in stoc k Date of transactio n Custome r name Qty. ordere d Invoic e No Total Discoun t Net orde r valu e Good s retur n

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 0 b. 5000 10000 2% of the value c. 10000 25000 5% of the value d. 25000 50000 7% of the value e. >50000 10% of the value f. Net order value is value of goods ordered discount g. 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 ordered h. 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 expensive c. 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 fee marks% income concession region remarks

a. Count the number of students whose a. Name is having exactly 5 letters b. Address is having text VIVEK VIHAR. Exact match is required c. Add/Income/Marks are not available d. Name start with a and ends with a e. First name is having exactly 6 letters f. Bday 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 false e. 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

Subject:
Teacher's Name: I II III IV Year: 2010-11 V VI VII VIII

Paper No:
Course - BBS - III Semester VI (Finance B) IX X XI XII XIII XIV XV

S. No.

Class Roll Enrollment No. No. Name of the Student

Marks of Written assignement out Marks in of 33 each Project / Term Paper out I II of 34

Aggregate Marks of Marks of Scale Marks of Total of Written Marks of No. of No. of Marks of House Down to House VIII , XI, assignement Column VII Lectures lectures Attendance Examinat Column Examination XIII out and Projects out of 10 Delivered attended out of 5 ion out of XIV out out of 45 of 25 / Term Paper 10 of 15 out of 100

a. b. c. d.

S.No should be auto generated Merge cells to write label for col V Col VII should be calculated as I +, II of V + VI Validate I and II of V with marks not greater than 33 and VI with marks not greater than 34 e. Calculate the aggregate marks out of 10 in col VI f. Round off marks up to 2 decimal places for all the columns wherever applicable but up to o decimal places in the col XIV g. 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% = 3 v. >=85% & above = 5% h. Roll No should have the following format: XXXX/YY Where X is any digit between 0 to 9 and YY are two digits of year of admission i. Sort the data Roll No wise in ascending order j. If the YY is less than the current year then write EX Student in REMARKS Column k. Calculate median , mode , standard deviation of project/ term paper marks l. No of lectures attended should not be more than the no of lectures delivered m. 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 attendance n. 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 total e. 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 background h. 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 192 147 164 104 105 135 105 184 180 176 186

123 132 144 139 193 153 111 166 152 166 151 134

135 151 151 190 163 190 118 129 128 131 150 122

102 107 155 158 174 187 102 115 145 137 181 150

200 145 133 153 131 116 167 155 168 149 161 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 background iii. 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 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) Jan Feb march Total

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 list 2. Ticket charges are based on the region South 200 East 125 West 150 North 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 A 5. Children charges are 3/4 of the adult charges 6. Total Amount is (Ticket charge+ entertainment tax) * no of adult * no of Children 7. if snacks required is Y then the total charge is incremented by 10% of the total charge 8. find out the most occuring region for watching movie 9. Extract records a. Show timings are before 12 noon b. 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 Y 11. 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 meal customer id description name 1001A 1001B 1001C 2001A 2001B

order no

shipping required Y/N

shipping charges Remark

1. Description will be based on 1st digit if the meal id if a. Burger b. Sandwitch c. Cold drinks d. Chips e. Chocolates a. 2. Add Comment as detailed description of meal (for eg. - if cheese burger if id 1001A, Aloo tikki burger if 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 Nil 10 kms - 20 kms 50 21 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 hr 6. Extract all those records where a. add a remark column and enter meal "free of cost" if the meal is delivered after 1 hr b. Meal is type burger and order is not today c. distance is more than 15 kms and remarks is blank 7. Insert a 3 D bar chart to show the meal description and their amount 8. Count the order where the shipping charges have been paid. 15. population census

Area

Person Name

No of members in the family DOB

Educational Qualification Address HSC/ SSC/ Grad/ Post Grad

house rented vehicle /owned owned

Family Income

No of No of members people dependent ill

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 East 2. Extract initial of first name followed by the last name from the person name column.

3. Find out the Age of the person 4. 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 dependents c. DOB before 1980 and educational qualification is post grad (use auto filter) 6. Make pivot table and chart by using some dimesions of the data 7. Count the sum of illness area wise 8. Calculate sum of the total income for those persons who have dependent more than the average dependent.