Ministry of Finance has been issued Order to enhance DA from July, 2012
[click here to see Order]. Here we will discuss about some skill to prepare DA
arrear statement in Microsoft Excel. DA Arrear calculation in Excel is not so
hard, only simple calculation method will give you accurate result and you may
prepare any large statement for your office DA Arrear Bill. Here you will find
some skill to prepare your DA Arrear statement in perfect way to make in
traditional arrear statement where one should sign with revenue stamp to
complete official obligation.
Click on pictures to see procedure:-
Stage 1
At First we need to prepare a database for last month salary
entitlement July.
- Database must contain Name, Post, Pay in PB, Grade Pay to calculate dearness
allowance. - You may add Employee No. or Employee code, Group, Division, Section, Office
Name, Bank Account No. (for bank advice) as per your official need. - Your database must contain any one unique value field i.e. Employee No.,
Name or Sl. No. which not to be repeated in records. Suppose that if you using
name field as unique field then add First (I) (II) for any duplicate or repeated
name for your office. - You may import your salary database from your normal pay bill programme.
You should use only last month database of arrear bill.
Now open a blank Excel sheet and create field name as example given below or
open your imported data sheet or open your salary database or copy paste your
salary database in new sheet delete any unwanted field or column:-
Note: 1. Place every salary component from Column “J” on which Dearness
Allowance to be calculated as per your official entitlement.
2. You may not use “From” “To” date column if you have not any official with
changes in monthly salary due to EOL, HPL, Promotion. You may hide further
these columns if you feel so.
3. You may use separate data sheet i.e. section wise, group wise, office wise
for different bill as per your need.
After Column M
Put formula in column “N” to Column “U” as shown above in every corresponding
cell of 2nd row of your sheet. Please keep Column “V” & “W” blank for total
rows will use in next stage.
Select/copy 2nd row’s formula and drag/paste in rest corresponding rows to
complete full month.
There is nothing special in above stated procedure. You should complete all
data for the last month of arrear i.e. September and should check value of
“Month” column “F” during dragging or copy/pasting it should be same value for
particular month.
Now change your Month value with 01-07-2012 [July-12] in Column “F”
Stage 2
After completing the preparation of July months’ data in all ways i.e. data
and formulae
– make a copy of all rows of July month data with selection & Ctrl+C
command and paste [Ctrl+V] it just below the July month without leaving any
blank rows.
– Change the value of “Month” Column “F” with “Aug-2012” in new pasted data
– Again repeat above action: – make a copy of all rows of last month data and
paste it just below earlier pasted data [for the Month of “Sep-12” ]
Change the value of “Month” Column “F” with “Sep-2012”
Now you have prepared database for all months check the entitlement for all
staff for all months. For broken period for any employee in any month due to
promotion/EOL/HPL/Upgradation insert duplicate row of concerned employee just
below and change period in column “G” & “H” [From/to ] and change
entitlement in Column “I” to “M” as required. You may do check accuracy
at this stage or later.
As repeated earlier should check value of Month, it should be same in
corresponding monthly database.
All above processes are normal.
Stage 3
Now purpose of writing of this article will starting now, be
read carefully its easy to understand and use:-
Now copy again Last Month Database [Sep-12] which was prepared in stage2 and
Paste it just below the Sep-2012 month data and change the month value Oct-12.
These new copied rows will be used to show Total for every
employee, therefore you should delete/clear all value of Column “G” [From] “PB”
to Column “W” [Net Payable Arrear]
Type “Total” value in Column “G”
Type formula to sum above three rows value in every column from Column “N” to
“U”. If your Oct data is starting from rows 10 you may type in
=sum(n7:n9) in cell N10.
Now Column “V” & “W” will be used here which was left blank in Stage 1 :
Type formula =round(U10*10%,0) in Column “V” & Type formula =U10-V10 in
column “W”
Select/Copy this cell and drag/paste it from Column “O” to Column “W”.
After completing the first row of your Oct/Apr month select/copy the cells of
this row from Column “G” to Column “W” and drag/paste it to all rows of rest
Oct/Apr month data.
Avoid any improper value displayed in Column “G” to Column “W”
Stage 4
Need of this stage : if you want an blank row after every employee details,
otherwise skip this stage
Now copy all rows again Last Month Database [Sep-12] which was prepared in
stage 2 and paste it just below the Oct-12 database
Change Column “F” “Month” column with value Nov-12
Empty all Cell from Column “G” to Column “W” of newly pasted database of
Nov-12
Stage 5
This is Last stage.
Hide your data from printing:
– Select
Sort: Now select your prepared database from start
to end
i.e. Cell A1 to last row of Column “W” using mouse or
using Shift [first click in cell A1 & press Shift button then scroll down
and select cell of Column “W”]
and after proper selection filter your database by clicking Data Menu/Tab
> Sort
and Sort your database on your unique value column/field [as earlier
discussed in stage1]
for sorting Click Data [menu/tab] > Sort> check on My data has header
> select field Emp No.
and see your Excel Work Sheet – You have done your job
At last you have to adjust column width, page layout, Number formatting in
all value column use your Excel Knowledge for that.
Must read following useful Excel tips:-
Convert Rupees in word in Excel Sheet
Enter multiple lines in excel cell