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:-
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.
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"
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.
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”
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
This is Last stage.
Hide your data from printing:
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.
Download Example sheet to view the result [select File>download]
Must read following useful Excel tips:-
Convert Rupees in word in Excel Sheet
Enter multiple lines in excel cell
Stay connected with us via Facebook, Google+ or Email Subscription.
Subscribe to Central Government Employee News & Tools by Email [Click Here]
Follow us: Twitter [click here] | Facebook [click here] | Google+ [click here]