calculation of professional tax

Normally professional tax in not applicable all over the country but there are some state where the government take professional tax 

Karnataka,
Bihar, 
West Bengal,
AndhraPradesh,
Telangana, 
Maharashtra,
Tamil Nadu, 
Gujarat,
Assam, 
Kerala,
Meghalaya,
Odisha, 
Tripura, 
Madhya Pradesh
Sikkim.

 Professional tax caluclted on two basis  1. Professional Tax on Total Salary.

2. Professional Tax on Basis Salary.

So Here is the Exanple

Formula:                                                                                                                                 =IF((E7>=$A$2)*(E7<=$B$2),$C$2,IF((E7>=$A$3)*(E7<=$B$3),$C$3,IF((E7>=$A$4)*(E7<=$B$4),$C$4,IF((E7>=$A$5)*(E7<=$B$5),$C$5,IF((E7>=$A$6)*(E7<=$B$6),$C$6))))) 


($ it is the sign that fix the cell, select the cell and press F4 or Fn+F4)

2. Calculation of professional Tax according to the basis salary

Golden rule of account

TALLY

Accounting is the systematic and comprehensive way recording of financial transactions related to a business, and it also refers to the process of summarizing, analyzing and reporting these transactions to oversight agencies and tax collection entities. Accounting is one of the key functions for almost any business; it may be handled by a bookkeeper and accountant at small firms or by sizable finance departments with dozens of employees at large companies

Personal Account:- As the name show "personal account" it means account for an individual person or the firm. it can be a person Eg. Ram, Shyam, joy Etc or the firm or a company it will count in personal account.


Real Account:- A real account is a general ledger account that does not close at the end of the accounting year. The balance of real account carry forward to next yeat.
real accountsa are balance sheet item like- asset accounts (cash, account receivable, buildings, etc.), liability accounts (notes payable, account payable, wages payable, etc.), and stockholder equity account etc.


Nominal Acccount:- Nominal accounts are directly related to Excpens and income of the firm Expense can be direct and indirect both.


Monetary traction of Business is to be recorded in the book of account of account by the method of Double accounting system.

                                                Accounting ledger

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

Personal account	Real account	Nominal account
1.Person 2. Firm 3. Company Etc	1. Cash 2. Building 3. Furniture 4. Machinery etc	1. Expenses & losses 2. Income and gain

                              

                                                                  GOLDEN RULE    

Personal	Real	Nominal
1.       Debit the receiver 2.       Credit the Giver	1.       Debit what comes in 2.       Credit what goes out	3.       Debit All expense 4.       Credit all income & gain

   

                                                

shortcut keys

                                               SHORTCUT  KEYS OF TALLY                                 


Shortcut Keys are helpfull to work fast. These keys are always helpfull. it save the time and boost the speed  of work.

1.         F1                           To select a Company

2.         Alt+ F1                  To select the inventory
                                       ( To view the detailed of entries)

3.         Ctrl+F1                  To see payroll Voucher

4.         F2                           To change the current date

5.         F3                           To select company

6.         F4                           To select the contra Voucher
                                    (For Cash to Bank or Bank to cash entry)

7.         F5                           To select the payment voucher

8.         F6                           To Select the receipt voucher

9.         F7                           To select the journal voucher     

10.      F8                           To select the sales voucher

11.      Ctrl+F8                  To Select the credit note

12.      F9                           To select the purchase voucher

13.      Ctrl+F9                  To select the debit note

14.      F10                         To select the reversing Journal Voucher

15.      Ctrl + F10             To select the memorandum  voucher

16.      F11                         To select the function and features screen

17.      F12                         To select the Configure Screen

Convert negative value in postive

                                  Convert Negative Value in Positive


Replace all Negative value in positive. Here is the table

1. Select the whole negative row

2. Press Ctrl+F

3. Click on Replace All.

Click on Ok 

Now all the negative values are changed into Positive.

Excel introduction

Excel was Developed by Microsoft and first released on September 30, 1985, Excel is capable of creating and editing spreadsheets that are saved with a .xls or .xlsx file extension. General uses of Excel include cell-based calculation, pivot tables, and various graphing tools. For instance, with an Excel spreadsheet, you could create a monthly budget, track business expenses, or sort and organize large amounts of data.
                Microsoft Excel is a spreadsheet program that is used to record and analyse numerical data.We all deal with numbers in one way or the other. We all have daily expenses which we pay for from the monthly income that we earn. For one to spend wisely, they will need to know their income vs. expenditure. Microsoft Excel comes in handy when we want to record, analyze and store such numeric data.
   How to start excel                       

• Click on start menu
• Point to all programs
• Point to Microsoft Excel
• Click on Microsoft Excel

Alternatively, you can also open it from the start menu if it has been added there. You can also open it from the desktop shortcut if you have created one.

Randbetween

                                       Randbetween Function


To Get a random value in a table we use this Formula(Randbetween).

Syntex: 
=Randbetween(Bottom,Top)

  Bottom= Here Put The Botton Value
  Top      = Here put the Top Value

Example- we want a random table between 500 & 1000. Here is bottom value is 500 and                         top value 1000.

Bank Reconciliaton Format

                                    BANK RECONCILIATION STATEMENT

Bank Reconciliation statement if we say in simple word this is the one way to know that how much balance is in our Company  Account have in a particular branch or Account. In this Statement we take only those cheque which is not cleared in Bank.It Can be current month cheque last amount. (In the reconciliation we take last three months not cleared cheque)

Here is the format of Bank Reconciliation statement

 Formula:     (Balance as per Book + Cheque issued but yet not Present - Cheque Deposit But yet not Cleared) = Balance as per Bank


Balance as per Book : It mean that according to you  how much balance in Company      account

 Add: Cheque issued But yet not present in Bank  

 It's mean that we issued the cheque to the supplier but they didn;t not Present it  in to the bank (Except those Cheque which is cleared in the Bank Account)


  Less: Cheque deposit But yet not cleared
 It mean that we receive cheque and deposit it but it is not cleared in the Bank. (Except those Cheque which is cleard)

Ø There is a factor which effect the reconciliation statement is reverse cheque.

Reverse Cheque--- Reverse Cheque means it will come in Debit side in the Book. Because when we issue the cheque we enter it credit side.

Here we can use the Golden Rule

·         DEBIT THE RECEIVER

·         CREDIT THE GIVER

Suppose We issue cheque in  june 2017 and due to some reason the cheque not present in bank in the month of june.
 So we will take it in the reconciliation of june. 

Ø  If it is cleared in July then we will remove it from the reconciliation.

Ø  If it will not cleared within 3 month then we will reverse the cheque  

We can carry forward any cheque till three months Because the Validity of any cheque is only three month after that it will not valid. Then we can make the cheque reverse.

Delele number without deleting Formula in Excel

Sometime we have date with number and Formulas. Suppose now we want to delete the number but condition is that the formula should not be delete So how it is possible. It is possible through the an Excel formula.
1.Select the Whole Data

2. Then press F5 (if you are using laptop to Fn+F5)

3. Then click on Special

4. Then Select constants

5. Then only Select the number

6. Then press OK
7. Now press the Delete button
When you will press Delete button you will find that only numeric value are delete not the formula.

How to covnvert rows in column

There are few steps to transform column in row or Rows in column 

1. Select and copy  the row you want to transform.

2. Then select the cell where you want to paste and right Click and paste special.

3.After then click on transform.

4. The column will chage in Row

Use of sumifs

Sumifs This foumula is a conditional formula. Like when we have some condition and we have to find out the sum with some condition. So then we will use this function.

Through the formula of sumifs we can find the sum with the condition.

For Example we have 5 Sales men and we want to find that how much product sale by each sales men. So we can easliy find.

Syntex__ =sumifs(sum range,criteria_range1, criteria1, creiteria_range2,cretria2)

Sum_range          =  Select the sum range

Creteria_range1  = is select the range First column 

Criteria 1             = Slect the name which you find

Cretria_range2    = Select the range Second column

Cretria2               =Select the product you want to find

When you will change the salesmen name (where you apply the formula) it will show you the total sale of that particular salesmen.

How to use vlook up

VLOOKUP foumula, as the formula name show its use. This formula is use when we have data in bulk and we have to find one person detail.

SYNTEX: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Lookup_value    = Select the cell which you find
Table_array        = Select the whole table
Col_index_num = Column number is the table have the numer of column
range_lookup     =  0 (zero is true value)

Here is an Example of vloolup

Here is the link

https://docs.google.com/spreadsheets/d/1GmZYiQ6-J_qKjteDQIJcaNu_vLOK4b23nWt_zitpc5w/edit#gid=0`

Forcast Formula

Forcast formula is used to predict anything in excel.
 For example- We sale somthing and we have the detail of sale for the month of jan, feb, March, April and now we want to know that what will be the sale in December month. So then we use forcast formula. You Can't totally Dependent  that result which is given by the formula but It will give you the approx value.
So here is an Example.


SYNTEX- (X,Known_y's, known x_'s)

X               =is month you want to know the sale like october
known_y's= known result (the Sale )
known_x's= known Result( month)

PMT FORMULA

Basically PMT formula is use to calucalt the amount of loan.We will try to uderstand the formula throgh the Example. 

Suppose we have taken loan form the bank and we have to find out the EMI Amount. 

Syntex

=pmt(rate,nper,pv[fv],[type])

 rate  = rate is The percent which is fix by the bank on the loan

 nper = nper is the time which is in month like 5 years(60 months)

 pv    = pv is present value (we can say the loan which is taken by us)

The value will show in minus because we are paying the money.

Home Function (shortcut Keys)

shortcut keys are the most importnat in excel by using the shortcut keys we can make speed in excel and To work in excel will be very easy to use so these are some shortcut keys.

1. Auto Sum                         Alt + (Apply this formula in that cell where you want to sum)



2. one to another page         Ctrl  page up ( Press these two key ctrl and page up for the next page and
                                                Ctrl page down for previous page) for excel sheet


3. one sheet to another         Atl  tab (Press these two key it will help to go in another sheet)


4. select the whole Row       Shift and space (Press the Shift and Space key Together)


6.  Find                                  Ctrl F


7. close the whole sheet         Alt F4


8. Minimize the Excel sheet      Window M(Press the Window key and then M)

9. Go at the top                            Ctrl and upper arrow

10. Go at the bottom                   Ctrl and down arrow

11. Apply Bordre                       Alt H B A(press these key one after one)

12. Remove Border                   Alt H B N

13. Merge cell                            Alt H M M

14. Unmerge cell                        Alt H M U

15. Increse font Size                  Alt H F S( Then set the Size of Font)

16. Wrap Text                           Alt H W( It is use when We want a name in one cell) eg - Aditya                                                          Singh negi we want this name in one cell then We will use this key

17. Insert Cell                        Alt H I I(Then Enter)

18. Preview file                        Ctrl F2

19. Minimize Excelsheet          Ctrl F9

20.Fill Color in Cell                 Alt HH (Then Select the color)

21. Duplicate                            ctrl D

22. Freez Row                         Alt then W then F then R (for the Row)

23. Freez Column                   Alt then W then F then C (For the column)

24. Freez Row and column     Alt then W Then F then F (to Freez the row and column)

Frequency

               Frequency formula

We use this formula when we have to sapparate the data in Interval. Using this formula we can saprate the data into the part.
for Example we hav a data of the student weight and we have to find that how many student have weight is 0-15 and how many have 15-50 and how many have 50-100

=frequency(select data , then select interval )  Data_ array   = Select the range of weight(Kg)  bins_ array    = Weight interval column First of all select the whole that is highted with red color in that cell we will apply the formula of frequency. (=frequency(select data , then select interval ) then press the three key together (ctrl shift enter) here is the link https://drive.google.com/drive/my-drive

use of Sumif

 USE OF SUMIF

This is the Fantastic formula of excel and this formula is use for when we have the data in bulk and we have to find out the sum of the same product

For example-  We have a data and we have to find that how much Quantity sold of one product.

So Here is the Example.

Range required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. For Example The Column of Product as Shown in Example.

Criteria required. Criteria is that select the product that you want to sum.

 As shown in the Example select the Mango

    sum range: The actual cells to add, if you want to add cells other than those specified in the range argument. For Example the column of Quantity.

If you want to find that all prroct how much Quantity has been Sold so just Copy the 
Formula in another Cell you will get the how much Quantity of another produch Sold.