Excel assignment

1. 1.Answer the following using spread sheet.
The following data has been entered in a work book
A B C D
1 Item Quantity Rate Amount
2 Hard Disk400 3000
3 CD Drive 350 1100
4 Monitor 354 6100
5 RAM 254 1200
6 Mouse 652 250
Calculate amount of each item in cell D2:D6
2.Answer the following using spread sheet.
The following spread sheet contains the amounts invested, the rate of
interest and the period of investment in years. Write the steps to obtain the
simple and compound interest in columns E and F.
A B C D E F
S
n
o
Name AMOUNT Years Interes
t
SIMPLE
INTEREST
COMPUIND
INTEREST
2Ragul 200000 7 13
3Ravi 350000 8 12
4Ali 150000 25 11
5Xavi 750000 25 4
6Sachin 850000 4 5
7Ganguly 6500 6 9

2. Given that, Simple Interest =(p*n*r)/100
Compound Interest =p*(1+r/100)
n
-p
3.Answer the following using spread sheet.
The following spread sheet contains the amounts invested, the rate of
interest and the period of investment in years. Write the steps to obtain the
simple and compound interest in columns C and D.
A B C D
1 AMOUNT
INVESTE
D
SIMPLE
INTEREST
COMPUIN
D
INTEREST
2Ragul 200000
3Ravi 350000
4Ali 150000
5Xavi 750000
6Number of years (n) 5
7Rate of Interest (r%)8.5
Given that, Simple Interest =(p*n*r)/100

3. Compound Interest =p*(1+r/100)
n
-p
========================================
4.The following data has been entered in a spreadsheet
A B C D E F G
1RNoName A/CEcoCompAverageTotal
2301ANIL 65 9598
3403BINA 40 4578
4255CAVAS65 1545
5242AGNEL82 8528
6
Write the steps to compute Average and total in Columns F and
========================================
5.Answer the following using spreadsheet.
Consider the following spreadsheet showing the basic salary of 4 persons
A B C D E F
1 Name BasicHRADA TaxNet
Salary
2 Anita8652
3 Sachi
n
9582
4 Sunil11852
5 Rahul4525
Write the steps to compute the following
i) HRA as 25% of Basic salary
ii)DA as 110% of basic salary
iii)Tax as 20% of Basic
iv)Net=Basic+HRA+DA-TAX
6.Answer the following using spreadsheet.
A B C D E F G H I
1NAME ENGHINECOBKA/CTAXAVERAGE TOTAL
2GUPTA 76 78 98 5415 65
3SURI 45 56 54 4512 84

4. 4KAHN 36 85 56 8965 54
5ALI 25 45 54 2578 47
6KUMAR 45 25 12 1445 54
For the above spreadsheet write the steps to obtain the average and
total
========================================
7.Calculate Selling Price:
A B C D
1Item Purchase
Price
Profit(%)Selling
Price
2Monitor 5000 25
3Printer 4000 20
4Mouse 500 25
5Keyboard 500 20
8.Consider the following worksheet showing the cost of machinery, scrap value and its
life. Using straight line method, explain the procedure to display yearwise depreciation
and depreciation value for each year till life.
Depreciation = Cost − Salvage (Scrap)Value/Life in Number of Periods
A B B D E F
1Cost
Scrap
Value
Life in
YearsYearsDepreciation
Depreciated
Value
2 4 1
3 2
4 3
5 4

5. 9.The following data has been entered in a worksheet:
A B C D
1Emp No
Emp
Name DepartmentSalary
2 555Raj HR 400000
3 665Sachin ADMIN 56000
4 458Saurov HR 45000
5 255Dravid SALES 23000
6 125Ali HR 59000
7 488Khan ADMIN 60000
Write the steps to do the following:
a)Arrange the data in alphabetical order of employee name.
b)Create a pivot table showing the average salary department wise in
column E.
10.Write steps to do the following:

a)Sort the data in the ascending order of Dept.
b)Calculate subtotal of salary for each department.

6. Notes:
1.Read all questions carefully and answer all questions in one Excel file with
Ten different sheets.
2.Excel file name should be division-class-rollno-name; Eg: B-TYBCOM-92-
ragul
3.Excel sheet name should be question1, question2, quesion3……question10.
4.Students should start work on their roll no rows. Eg: For roll no 20, she/he
should start work from row number20 of each sheet.
5.Mail this to [email protected] on or before 31-08-2012: 11:59 PM.
6.Student should be solved all these sums in papers by handwritting and the
same should be submited on or before 31-08-2012 (As per Mumbai
university norms)

7. Notes:
1.Read all questions carefully and answer all questions in one Excel file with
Ten different sheets.
2.Excel file name should be division-class-rollno-name; Eg: B-TYBCOM-92-
ragul
3.Excel sheet name should be question1, question2, quesion3……question10.
4.Students should start work on their roll no rows. Eg: For roll no 20, she/he
should start work from row number20 of each sheet.
5.Mail this to [email protected] on or before 31-08-2012: 11:59 PM.
6.Student should be solved all these sums in papers by handwritting and the
same should be submited on or before 31-08-2012 (As per Mumbai
university norms)