MS-EXCEL
SHEET-1 RGCSM
1. Rename sheet RGCSM1, RGCSM2, RGCSM3.
2. Insert a new sheet with a new name RGCSM4.
3. Do the following calculation in RGCSM1
4. Prepare the following series :
a) 1,3,5,7,9,11,13. . . . . . . . . .
b) 3,9,27,81,243,729. . . . . . . . .
5. Prepare the custom list :
a) Ram, Shyam, Jadhu, Madhu
6. Background fill with any picture.
7. Delete background.
8. Tab color RGCSM1.
9. Hide sheet RGCSM1.
10. Unhide sheet RGCSM1.
11. Delete sheet.
12.Protect your document.
SHEET-2 RGCSM
Basic = Primary Income ( প্রাথমিক আয় )
Da = Direct Allowance ( মহার্ঘ্য ভাতা )
Hra = House rental Allowance ( ঘরভাড়ার জন্য টাকা )
Ta = Travelling Allowance ( যাতায়াতের জন্য টাকা )
P. Tax = Profession tax / Personal tax ( সরকারকে যে টাকা দিতে হয় )
Gross = Total Income ( মোট আয় )
Net = Total Income – P. Tax ( মোট আয় – খরচ )
Prepare the Calculation.
Code Basic Da Hra P. Tax Gross Net
E001 5600
E002 3200
E003 1800
E004 3900
E005 4700
E006 8000
E007 1000
i) Da will be 20% of Basic
ii) Hra will be 15% of (Basic+Da)
iii) Insert a column between Hra & P. Tax with name Ta
iv) Ta will be 12% of Basic + 5% of Da
v) P. Tax will be 3% of Basic
vi) Gross & Net will be calculate
SHEET-3 RGCSM
TOTAL CONCEPT FUNCTION (IF, MIN, MAX)
1. Do the following calculation :
Code Basic Da Ex Hra P. Tax Gross Net Grade Bonus
Amount
E001 5600
E002 3200
E003 1800
E004 3900
E005 1000
i) Da will be 20% of Basic of Rs. 700 whichever is upper.
ii) Extra amount treated
Whose basic>=4000, his option “yes”
Whose basic<4000, his option “no”
iii) Hra treated
Whose option “yes” he will not get any hra
Whose option “no” he will get 10% of basic
iv) P. Tax will be 2% of gross value
v) Gross & Net will be calculated
vi) Grade calculation
Whose net>4000, ”manager”
Whose net>3000, ”junior manager”
Whose net<3000, ”cleark”
vii) Whose basic above 3000, user will get bonus 25% of net, otherwise 0.
SHEET-4 RGCSM
1.
Name Category Section Group Total Additional Grand Total
Ram Good 40
Salma Poor 65
Neha Excellent 30
Rishi Good 78
Joy Excellent 20
Riya Poor 45
Tapan Excellent 89
i) If category is excellent then section would be “A”, good then “B”, poor then “C”
ii) If section is “A” then group total 600, ”B” then 480, ”C” then 360
iii) If he gets above 34 in additional subject then access number will be added with grand total
2. Do the following calculation
Sales Man Zone Product Target Actual Sales Unit Price Sales Commission
value
Ram North Sony 35 22 42500
Shyam West Samsung 45 20 25000
Jadhu South LG 20 10 35000
Madhu East Dell 25 13 12000
i) Calculate sales value
ii) Commission will be payable to the sales man on the following entry
Target Achieved Commission
>75% 20% of sales value
>60%-<75% 15% of sales value
>50%-<60% 10% of sales value
>40%-<50% 5% of sales value
<40% Nill
SHEET-5 RGCSM
1. Do the following calculation
Code Dept. Basic Da Hra Ta Total
E001 Mkt 5500
E002 Pur 6500
E003 Sales 4200
E004 Sales 2000
E005 Mkt 5200
i) Da will be calculate 15% only those persons whose basic>6000, 20% for those basic is
>3500,else 25%
ii) Hra will calculate 20% of basic salary but it will be minimum 1200
iii) Whose dept mtk, ta will be earn 700, otherwise 200
2. Do the following calculation
Code Dept. Designatio Basic Da Ta Total Tax Net
n
E001 Mkt Manager
E002 Sales Manager
E003 Pur Manager
E004 Mkt Officer
E005 Pur Officer
a) Basic use formula
i) Manager or mkt dept = 25000
ii) Manager or pur dept = 20000
iii) Officer or mkt dept = 18000
iv) Others department = 7500
b) Da will be 20% of basic for manager & 10% basic for officer
c) Ta will be allowed 5% of basic but only for those persons whose dept either mkt or sales
d) Total will be calculated
e) Tax calculation
Total Tax Rate
>18000 20%
>10000 15%
<10000 Nill
f) Net will be calculated.
SHEET-6 RGCSM
1. Do the following calculation
Sales Man Product Amount
Raju Ram 7600
Probir CPU 18000
Dhiman UPS 12500
Ratan CPU 17000
Kamal Ram 10000
Anisur UPS 17300
Nilu CPU 19000
Total sum of product
Count the product whose value above 16000 >16000
2. Prepare the following table (Commission) and calculate the commission using absolute cell
reference :
NOVEMBER TABLE DECEMBER TABLE
Name Total Sales Commission Name Total Sales Commission
Anil 26000 Anil 28000
Sunil 65000 Sunil 15000
Bimal 32000 Bimal 32300
Jamal 68000 Jamal 16000
Rate 10% Rate 10%
i) Make a copy of commission table (NOV) in another location of the same worksheet using
paste special feature.
Check the commission it will show without formula.
ii) Update the commission total commission for the month (NOV) & (DEC).
iii) Paste link the sales amount from the commission table (NOV) in another location than
change mother table & see the effect.
iv) Transpose the commission table (NOV) in another location.
SHEET- 7 RGCSM
1. Mixed cell reference formula
5% 10% 15% 20% 25% 30%
65000
75000
35000
25000
45000
500+ 600+ 700+ 800+ 900+ 1000+
12000
13000
14000
15000
16000
2. Do the following
Party outstanding report
Party Amount Over due Remarks Integer Reminder Square Rounded
Name by days return root Figure
Alok 1246.56 45
Abhi 3562.78 90
Amit 326.54 25
a) Prepare remarks based on over due by days.
b) Find the integer value of each number.
c) Divide all the integer value each number by 2 & find out the reminder under reminder head.
d) Round od all square root figure up to 2 decimal places.
e) Copy the above table in another location give heading as proper case change the party name as
upper case.
SHEET- 8 RGCSM
1. VLOOK UP :
Emp Code Basic Da Hra
E001 4000 10 % of basic 5 % of (Basic + da)
E002 5000 10 % of basic 5 % of (Basic + da)
E003 6000 10 % of basic 5 % of (Basic + da)
Pay slip as below
Employee code
Basic
Da
Hra
P. Tax will be 5%
Net salary payable
2. Create the following table after that create chart
Product Quantity Rate Amount
CPU 12 4500
VDU 15 3600
Mouse 25 250
Keyboard 05 550
a) Create a bar chart product & amount column.
b) Create a pie chart on product & amount column show percentage.
SHEET-9 RGCSM
HLOOK UP IN EXCEL PROGRAM
SALES REPORT
YEAR RUPAK KUNTAL ROHAN SIMA PUJA
2013 15200 13200 6500 14980 6630
2014 13600 8960 13500 11300 12000
2015 19200 7800 9800 19000 17200
2016 25100 9850 9720 22100 16300
2017 23100 11500 11250 32000 15200
2018 26650 27500 17650 29900 23000
EMPLOYEE NAME 2016 2017 2018
Prepare the following table
Outgoing Income
Mortgage 1200 15000
Fuel Bills 500
Tax 200
Credit Card 1000
Food 3000
Clothes 600
Phone Bill 600
Direct Debits 1200
Total Outgoing
Income left
a) Calculate the total outgoing and income left.
b) See the total outgoing & cell auditing features.
c) What will be the fuel bill if you change the income left as 7000 using goal seeks features.
SHEET-10 RGCSM
1. In sheet 1 for the first column you have to set & error message for user whoever use entering date is not
satisfying the criteria message (your entry is wrong >=2000<=8000).
2. In sheet 1 second columns only accept “number” any “text” not to allow.
3. In sheet 1 third columns only accept “text” any “number” not to allow.
4. In sheet 1 fourth columns except “text”, “number”, any duplicate not to allow.
5. Create a validation list India, Pakistan, Australia, West Indies.
Country Name
a) If you choose India automatically display Sourav.
b) If you choose Pakistan automatically display Akmal.
c) If you choose Australia automatically display Ponting.
d) If you choose West Indies automatically display Gayle.
6. Assume that someone entering marks in sheet 3 column b set formatting
i) If the marks less than 30 that will show color red with bold.
ii) If the marks greater than 80 that will show pink color with blue border.
iii) Otherwise all marks will show color green & light shade.
SHEET-11 RGCSM
1. Put the Data
Name S1 S2 S3 Add Total Group Total Remarks
Debraj 45 68 35 12
Sanjib 60 50 75 100
Sahin 58 90 52 35
a) Total will calculate subject total
b) If he gets above 30 in additional subject then excess number will be added with group total
field.
c) Student must be gotten above 40 marks in each subject (accept additional) as well as total must
be >=150 than remarks will show “pass” otherwise “fail”.
d) Whose student remarks “pass” & “fail” this name automatically colorful.
2. Do the following
Item P. rate Stock Total
Mother Board 3500 10
Monitor 4200 5
Mother Board 2200 35
Monitor 3200 20
Hard Disk 2500 22
Mother board 4100 18
Hard Disk 2000 15
Monitor 2200 14
Hard Disk 6500 21
a) Sort the above table group wise ascending & descending
b) Apply the filter & display all the item mother board.
SHEET-12 RGCSM
1. Do the following the table :
Code Name Join Date Basic Da Bonus Total
C001 Ranjit 05/05/12 5600
C002 Sanjib 02/03/13 4200
C003 Debraj 06/05/13 6500
C004 Uttam 08/09/11 8500
C005 Raja 12/12/12 4700
i) Da will be calculate whose basic>5000 he will be earn 15% of basic, whose basic>4000
he will be earn 10% of basic, otherwise 5% of basic.
ii) Bonus will be calculate 10% of basic or 500 whichever is lower.
iii) Total will be calculate.
iv) Extract the details whose basic above 5000 location in same sheet.
v) Extract the details whose basic under 5000 location in same sheet.
vi) Extract the details whose Bonus above 600 location in same sheet.
vii) Extract the details whose basic above 5500 & name begin with “R” location in same
sheet.
2. Calculate the chargeable amount :
Call Charge Jio Airtel VI BSNL
0.40 0.60 1.00 1.20
Consumer Unit 1 Unit 2 Unit 3 Unit 4 Net Amount
Name
Raja 62 47 51 40
Sada 26 65 56 81
Pratap 89 46 33 52
Rahul 43 78 74 39
Kamal 81 24 69 48
SHEET-13 RGCSM
1. You have to deposit Rs 60000 to bank account as a fixed deposit for 5 years at 12% per annum.
You have to calculate how much money received after 5 years. (ans Rs 105740.50).
2. You can open a monthly recurring system of Rs 1000, 12% rate of interest for 5 years. You have
to calculate how much money received after 5 years. (ans Rs 82486.37).
3. Suppose you are giving a loan of Rs 25000 to your friend at, 18% interest per annum for 3 years
& he will back the money through a monthly installment system. Find out what will be the
installment amount. (ans Rs 903.81).
4. Find out the breaks up of the above installment amount for 3 years. You have to calculate how
much principal and interest he has paid every installment.
5. Find out the present value of an investment that pays Rs 250 at the end of every month for 2
years. The money paid out will earn 7.5% interest annually. (ans Rs 5555.61).
6. A person deposit certain amount in a bank 10% cumulative interest per annum in such a way
that after 5 years he will get total sum of Rs 50000 to get his daughter married. Find out the
amount of deposit. (ans Rs 31046.07).
7. You are paying insurance premium Rs 1626 quarterly to get Rs 200000 at the end of 20 years.
Then find out the rate of interest per annum. (ans 4% per annum).
PV = PRESENT VALUE
FV = FUTURE VALUE
PMT = PER MONTH TERMS
IPMT = INTEREST PER MONTH TERMS
PPMT = PRINCIPAL PER MONTH TERMS
