ASSIGNMENT – 2
Q-1:
Create a following worksheet and do as directed:
A B C
1. All India Drama Contest
2. Year Ticket sold Revenue
3. 1997 20000 989050
4. 1999 26123 1645890
5. 2000 24670 1198070
1. Insert a column 'number of teams' between columns 'year' and 'tickets sold' with values
'32','38','39'.
2. Insert a row between row3 and row4 with values '1998','37','23290','1200490'
3. Delete column 'Revenue'.
4. Rename the sheet1 with name 'format cells'.
5. Delete sheet 3.
6. Hide row4.
7. Insert a sheet and rename it with name 'insdel'.
Q-2:
Create following worksheet and do as directed:
Profit and Loss Statement
January February March April
Cost 60000 80000 90000 75000
Overhead 10000 15000 20000 15000
Sales 80000 100000 120000 95000
Profit
1. Calculate the profit using the formula as sales - (cost + overhead)
2. Using minimum numbers of key strokes move the cell pointer to following cells: Write your
answer which key will be used
• G20
• h28
• A1
• The last cell in current row
• The last cell of worksheet
Q-3:
Create following worksheet & find the average & total marks for each student.
Name Maths Science English
Jitul 79 82 74
Bela 71 66 75
Ashu 63 69 71
Kavit 45 59 52
Anjana 55 61 64
Kruti 80 79 82
Nikita 76 67 68
Yesha 38 49 52
Zeel 67 75 79
Shenoy 58 66 67
Q-4:
Create following worksheet and do as directed:
A B C
1. Orient Industries Ltd.
2. Item Quantity Unit Price
3. Pc 2 30000
4. Printer 2 16000
5. Diskette 40 30
1. Change the column width of column B to 15.
2. Change the column width of column D to G to 20.
3. Change the column width of column A & B to 14.
4. Calculate total sales for each item & store result in column D
Hint: total sales = Quantity*unit price
5. Calculate total sales for all the items & store result in cell B6.
6. Copy unit price for pc in cell D7. Move total sales from cell B6 to D8.
Q-5:
Date Arithmetic
Raj Kamal Paper Mills Ltd.
Wage/day Rs 55
Name of employee Start date End date Gross salary
Neel Bhatia 10-Mar-98 20-Mar-98
Keshav Kalsi 3-Mar-98 24-Mar-98
K Shrikant 25-Mar-98 25-Apr-98
1. Calculate the gross salary payable to first employee in cell D7 & copy formula down to range
D8:D9. Manually calculate the gross salary & verify your results.
2. Change the Date format to dd/mm/yy. Save the worksheet.
Q-6:
Enter the following data in calc sheet.
Student Name Marks1/80 Marks2/80 Marks3/80 Total Percentage (%) Grade
Abhishek 76 65 64
Bhavesh 45 54 56
Chirag 65 62 68
Jainik 45 65 65
Chintan 57 41 42
Niral 45 45 47
Jay 24 26 27
Nikhil 65 74 24
1. Calculate total & percentage for given data using formula
2. Also calculate grade using conditional calculation in form of 'pass' or 'fail' where the student is
pass if he is scoring more than 40% in all subject.
3. Create bar graph displaying student total marks.
Q-7:
Calculate sales tax using given conditions:
If sales tax=G then 4% of MRP
If sales tax=M then 6% of MRP
Sales Tax Regular
Product MRP State Sales tax Total
Premium Padmini 360000 G
Honda splendor 41000 M
Bajaj super 22000 M
Kinetic Luna 13000 G
TVS Scooty 190000 M
TI Cycle 1900 M
Kinetic Honda 34000 G
Hero Winner 2700 M
Q-8:
Create calc file and enter following information & do as directed:
Month Salesman Region Sales
Feb Ritesh North 30000
Jan Mitesh North 40000
Feb Jigna North 35000
Feb Mitesh South 25000
Jan Ritesh South 40000
Jan Jigna West 50000
Mar Mitesh South 45000
Mar Ritesh West 36000
1. Calculate the commission for each salesman per region & month as given below:
total sales commission
<=30000 0
30000-40000 4%
40000-50000 6%
>50000 8%
2. count the number of salesmen getting commission less than 2000
3. display the detail of salesman who has sold products in month of February or March
4. calculate total sales for each month
5. create a 3-D pie chart showing month wise total sales. Give appropriate chart little and show
values as data labels.
Q-9:
ABC Ltd. Wants to purchase computers & accessories. It has received the following quotation:
Vendor Ribbon Printer Pc Diskette
K& co 100 18000 35000 30
R K Brothers 80 19000 34000 40
Sham & co 100 20000 35000 35
Pc supplier 85 18500 38000 35
Enter the quotations in a new worksheet file starting from row 1. Now complete the following
exercise:
1. Sort the database in ascending order of the PC price.
2. Temporary hide records of those vendors where the cost of PC is Rs 35000.
3. Display only those records where the cost of ribbon is less than Rs 100. & that of diskette is
less than Rs. 35.
4. Find out vendors who can supply the printer at Rs. 18000 or 20000.
5. Copy the result of Q4 & Q5 to a vacant area of worksheet with proper heading.
6. Find out the vendor who can supply diskette at Rs.40.
7. Find out the maximum quoted price for printer for those vendors who have quoted the diskette
price of Rs. 30 or more. Display the resultant data below database with proper heading.
8. Find out average price for printer for those vendors who have quoted the diskette price of Rs.
35.Display the resultant data below database with proper heading.
Q-10:
Enter the following data in worksheet & do as following instructions:
Profit & Loss Statement
Prev. year Jan Feb Mar April May
Cost 1000000 60000 80000 90000 75000 75000
Overhead 150000 10000 15000 20000 15000 15000
Sales 1300000 80000 100000 1200000 95000 95000
Profit 15000 10000 5000 10000 5000 5000
Total sales
Total profit
a) Draw a graph for comparing the value of cost, overhead & sales for month of 'Jan, Feb and Mar'.
The graph should follow the following instructions:
• It should be column graph.
• The heading of chart should be “Comparison of three months”.
• X axe should be labeled as 'month’. Y axe should be labeled as “Rupees”.
• The chart must contain data table.
• Legend value should be displayed at top area.
• Apply chart area color.
• Create this chart in a new chart sheet.
b) Draw a pie chart for comparison of sales value from the month Jan to May. Apply necessary
labels on your own to chat & also format it properly with your own chosen colors. Create this
chart in same sheet.
c) List down the different types of chart with excel supports.
