EXCEL ASSIGNMENT for B.
Com(H) II Sem (Bharati college)
CATEGORY -2 PAYROLL
Q1. M/s Alpha Ltd pays to its employees the salary on the basis of Basic pay. The Gross salary of an
employee consists of Basic, Dearness Allowance, and House Rent Allowance (H.R.A). The
components of salary are computed according to the following terms of contract of service:
Dearness Allowance is paid @ 10% of basic pay subject to a
maximum of Rs. 5400.
HRA is computed as per the following scale:
Basic Pay HRA (Rs.)
Up to 10,000 25% of Basic Salary + DA
From 10,001 to 25,000 30%
More than 25,000 35%
Required: Prepare payroll of 10 employees of M/s Alpha Ltd for the month of March 2007, in the
format given below according to above terms of contract. Fill information about, Name of Employees
and their Basic Pay on your own.
Payroll for the Month of March 2007
Empld Name of Employees Basic DA HRA Gross
Q2.M/s Beta Ltd pays to its employees the salary on the basis of Basic pay. The Gross salary of an
employee consists of Basic, Dearness Allowance, and House Rent Allowance (H.R.A). The
components of salary are computed according to the following terms of contract of service:
Dearness Allowance is paid @ 10% of basic pay subject to a
minimum of Rs. 6000.
HRA is computed as per the following scale:
Category HRA
A 10%
B 20%
C 30%
Required: Prepare payroll of 10 employees of M/s Beta Ltd for the month of March 2007, in the
format given below according to above terms of contract. Fill information about, Name of Employees
and their Basic Pay on your own.
Payroll for the Month of March 2007
Empld Name of Category Basic DA HRA Gross
Employees
Q3. Prepare a payroll statement of a company in the format given below in a spreadsheet to compute
net salary payable to TEN employees of the company:
Name of Components of Salary Gross Net
Empld TDS
Employees Basic Pay DA HRA Salary Salary
The Gross salary consists of Basic pay, Dearness allowance (D.A), House Rent Allowance (H.R.A).
the rules governing the payment of allowances are as enumerated below:
Dearness Allowance: the D.A is calculated @39% of basic pay, subject to a minimum of Rs.
2500.
EXCEL ASSIGNMENT for B.Com(H) II Sem (Bharati college)
House Rent Allowance: The H.R.A is paid according to the following scales of basic pay:
Basic Pay H.R.A
Up to Rs.8,000 Rs.3,000
Next upto Rs.15000 Rs.5,000
Thereafter Rs.9000
Net salary is calculated as gross salary less deductions, rounded off to nearest rupee.
Tax deducted at source: Tax is deducted at source @15% for each employee, rounded off to
nearest ten rupees.
Q4. A company AA Pvt. Ltd. pays a monthly salary to its 10 employees. Prepare a Payroll Statement
for the month of Feb.2010 in the following format on the basis of following terms and conditions:
Categor Basic Gross Income Net
Name DA HRA PF
y Salary Salary Tax Salary
Terms & Conditions:
DA is to be calculated 120% of basic for those having a basic salary up to 20000 and 100% for
those having more than 20000 with a condition to that he must get a Minimum DA of 30000.
HRA for employee of category “A” is Rs.2000, for “B” is Rs.1500, and for “C” is Rs.900.
Gross salary will be the addition of Basic, DA and HRA.
25% of gross salary is deducted as income tax.
He contributes 10% of Basic and DA in PF.
Net salary will be equal to Gross Salary –PF-ITAX (rounded to ten rupees).
Q5 M/s XYZ Ltd pays to its employees the salary on the following terms and
conditions:
The Basic Salary (Given)
DA is 15% on Basic Salary.
HRA is Rs.3300 for Basic Salary <15000 and Rs.5500 for others.
Bonus is payable @8.33%of Basic+DA and should not exceed Rs.3000.
TA Rs.800/- per month for Monthly Basic Salary less than Rs.20000/- and Rs.1000/- per
month for Monthly Basic Salary from Rs.20,000/- onward.
P.F. 12.5% of Basic Salary,and I.T. is 11.5% Of Gross Salary.
Gross salary=BASIC +DA+HRA+BONUS +TA
Total Ded.=PF+IT
Net salary=Gross salary - Total Ded(rounded to hundred rupees).
Q6 Prepare a payroll statement of a company in the format given below in a spreadsheet to compute
net salary payable to TEN employees of the company:
A B C D E F G H I J K
1 S.no Employee Is PF Components of Salary Gross P. TDS Net
2 . Name Payable? Basic D.A H.R. C.C. Salary F Salary
Pay A A
EXCEL ASSIGNMENT for B.Com(H) II Sem (Bharati college)
3
4
The Gross salary consists of Basic pay, Dearness Allowance (D.A), House Rent Allowance (H.R.A).
The rules governing the payment of allowances are as enumerated below:
Dearness Allowance: the D.A is calculated @43% of basic pay, subject to a minimum of Rs.
4000.
House Rent Allowance: The H.R.A is paid according to the following scales of basic pay:
Basic Pay H.R.A
Up to Rs.15,000 Rs.6,000
Next upto Rs.25000 Rs.8,000
Thereafter Rs.10000
City Compensatory Allowance: The CCA is calculated and paid @10% of the sum of Basic
pay and dearness allowance, subject to a maximum of Rs.3000
Net salary is calculated as gross salary less deductions, rounded off to nearest rupee.
The deductions are:
I. Provident Fund Contribution: An employee is required to contribute 8% of his
salary to PF if the ‘Is PF payable?’ condition is “yes”.
II. Tax Deduction at Source: Tax is deducted at source @15% for each employee,
rounded off to nearest ten rupees.
Q.7 M/s Alpha Ltd pays to its employees the salary on the basis of Category. The Gross salary of
an employee consists of Basic Salary + Dearness Pay + DA +HRA + TA+CCA. The GPF and IT are
the two Deductions. The components of salary are computed according to the following terms of
contract of service:
Rules for payment of Basic Salary, TA, and CCA are as under:
Basic
Category Salary TA CCA
A 25000 5000 1000
B 20000 4000 800
C 15000 3000 500
Rules for payment of DP, DA, and HRA are as under:
DP 50% of Basic
DA 41% of (Basic +DP)
HRA 30% of (Basic +DP)
Rules for Deduction of GPF, and IT are as under:
GPF 10% of Gross
IT 8% of Gross
You are required to prepare a payroll statement in the given format making maximum use of cell
referencing facility
0.5 0.41 0.3 0.08 0.1
Is
HRA is
to be Total
Code Name Cat Paid Basic TA CCA DP DA HRA Gross IT GPF Ded Net
1 Y
EXCEL ASSIGNMENT for B.Com(H) II Sem (Bharati college)
2 N
3
4
Total
Q. 8 From the following particulars compute the salary of Mr. X for the Financial Year 2011-12
(March 2011 to Feb. 2012)
Basic salary (Allow any Basic salary in the pay scale of Rs.10000-Rs.325-Rs.15200)
DP is 50% of Basic Salary
DA (as a Percentage of Basic + DP) is declared after every six month in the month of April
and October to be effective from January and July respectively. The Arrears of DA for the
months of Jan to March and July to September is paid along with the Salary for the month
of April and October respectively. The DA Rates w.e.f. Jan. 2010 are given in Table-1.
Month of Increment is May each year
HRA is to be paid @ 30% of (Basic plus DP) to those whom HRA payable is yes
TA is to be paid @ Rs. 800 PM if Basic Salary is Less than Rs.12000, otherwise the TA is
Rs. 1000 PM.)
CCA is to be paid @ Rs. 300 PM if Basic Salary is less than Rs.12000/- otherwise the CCA is
Rs. 500 PM.)
Gross salary is the sum of Salary and all other allowances
Deduction: a) GPF 10% of (Basic +DP) ) subject to a minimum of Rs.2000/- b) IT 10% of
Gross Salary
Net salary is Gross salary minus total deductions.
The above worksheet should be generalized to the extent possible. Also compute totals for all the
components of salary. Table - 1
DA rates
Jan.2010 35%
July 2010 45%
Jan. 2011 51%
July 2011 58%
Jan. 2012 65%
Q9 From the following particulars compute the salary of Mr. X for the Financial Year 2011-12
(March 2011 to Feb. 2012)
Basic salary (Allow any Basic salary in the pay band of Rs.37400-67000)
Grade pay Rs. 9,000/- per Month
DA (as a Percentage of Basic + Grade Pay) is declared after every six month in the month of
April and October to be effective from January and July respectively. The Arrears of DA
for the months of Jan to March and July to September is paid along with the Salary for the
month of April and October respectively. The DA Rates w.e.f. Jan. 2010 is given in Table-
1 of ques 8.
Month of Increment July Each Year, amount of increment is 3% of (Basic Plus Grade Pay)
rounded off to the next multiple of ten Rs. provided the increment amount less previous
multiple of ten Rs. is not less than Rs.1/-
HRA is to be paid @ 30% of (Basic plus Grade Pay) to those whom HRA payable is yes
TA is to be paid @ Rs. 3200 PM plus DA on TA as given in the Point no, 3.
Gross salary is the sum of Salary and all other allowances
Deduction: a) GPF 10% of (Basic +DP) ) b) IT 10% of Gross Salary
EXCEL ASSIGNMENT for B.Com(H) II Sem (Bharati college)
Net salary is Gross salary minus total deductions.
The above worksheet should be generalized to the extent possible. Also compute totals for all the
components of salary.
Q10. Arrear Computation Under 6th Pay Commission w.e.f. Jan. 2006 to Aug. 2008
Old Pay scale
Basic salary (Allow any Basic salary in the pay scale given below)
DP is 50% of Basic Salary
DA (as a Percentage of Basic + DP) is declared after every six month in the month of January
and July. The DA Rates w.e.f. Jan. 2006 are given in Table-1.
Month of Increment (from Jan. to Dec.) is dependent on Joining or Promotion Date
Amount of Increment is Rs. 325/-
New Pay Under 6th Pay Commission
The revised Basic Salary and Grade Pay as on 1/1/2006 are given in Table-2
The revised DA (as a Percentage of Basic Salary + Grade pay) is given in Table-1
Month of Increment is Fixed for all employee in the month of July every year.
Amount of increment is 3% (of Basic Salary + Grade Pay) rounded off to the next multiple of
Rs.10/-
Information given
1. Basic Salary as on 1/1/2006 (Use List box)
2. Old Month of Increment (Use List box)
Required
1. Compute Arrears Under 6th Pay Commission w.e.f. Jan. 2006 to Aug. 2008
2. Your Excel sheet should be generalized to accept any Basic Salary and any Month of
Increment as input and select corresponding revised salary and grade pay and compute
arrears accordingly.
DA Rates Old Basic Salary and corresponding revised Basic Salary and Grade Pay
Year Old New Old Basic Revised Basic Salary Grade
Rate Rate Salary (1.86*Old Basic Salary) Pay
Jan-06 24% 0 10000 18600 7000
Jul-06 29% 2% 10325 19210 7000
Jan-07 35% 6% 10650 19810 7000
Jul-07 41% 9% 10975 20420 7000
Jan-08 47% 12% 11300 21020 7000
Jul-08 16% 11625 21630 7000
Table - 1 11950 22230 7000
12275 22840 7000
12600 23440 7000
12925 24050 7000
13250 24650 7000
13575 25250 7000
13900 25860 7000
14225 26460 7000
14550 27070 7000
14875 27670 7000
15200 28280 7000
Table -2
EXCEL ASSIGNMENT for B.Com(H) II Sem (Bharati college)
