Excel Unused Formula No.2 Min & Max

This is in continuation to our first article Excel Unused Formula No.1 Concatenate

Thanks for the amazing support, we hope that this support continues to all our future posts as well.

Today we will see how minimum function and maximum function works

Minimum Function

We come across a number of clauses in the income tax act where we need to find the minimum amount. For E.g. Gratuity, HRA, 80GG, Leave en-cashment and so on..

We will see "how to automate these calculations in excel"

Here is a snapshot of the HRA Calculation

Figure 1: HRA Caclulator

We were able to make this calculation in 2.5 miniutes, don't believe then try it out yourselves

Minimum function is very simple just type MIN and then choose the data for which you require the minimum value.

Figure 2:Minimum Formula

Maximum Function

Maximum function is the reverse of minimum function. If under any circumstance if you want to find out the maximum value then use MAX and choose the values. You should have the maximum value without much pain.

TAX SAVING TECHNIQUES FOR SALARIED CLASS

 

We have a seen a number of salaried class people being bamboozled during the time of declaration of savings.

 This problem is faced as there is lack of proper knowledge within the organization as the companies are busy planning the company’s transactions over that of employees.

However, there are certain companies which conduct a seminar to all the employees every year helping them to understand the nuances in the area of taxation.

We have a feeling that the finance and accounts department in every organization must have a person who has the basic knowledge at least on the methods in which tax can be saved for employees.

We hope this article will be useful not only to the employees filing up their tax saving declarations but also to the people working the accounts and finance departments.

1.       CONVEYANCE

800 per month are allowed as deduction. Many companies have this standard deduction. In case you are not using it please first claim it.

2.       EDUCATION AND HOSTEL ALLOWANCE

100 per month per child (maximum of 2 children) is allowed as deduction for education & 300 per month per child (maximum of 2 children) is allowed as deduction if they are staying in a hostel. 8 of 10 cases we have found that this deduction has not been used.

3.       HOUSE RENTAL ALLOWANCE (HRA)

Dependent on the following factors: a)      Place of employment b)      Salary received c)       Rent Paid d)      HRA Received Calculation of HRA is as follows Least of: a)      Actual HRA received b)      50% of the salary (metros: Delhi, Kolkatta, Chennai, Mumbai) 40% of the salary (others) c)       Rent paid in excess of 10% of salary SALARY = BASIC PAY + DEARNESS ALLOWANCE                 Essentials for claiming the HRA Deduction a)      HRA should be a part of the salary b)      Rent must be paid by the employee ·         Rent receipts are required ·         If you are paying any amount to your parents and staying in their house not to worry you can show that as rent as well. (but consider taking an advice before taking this step) ·         If no rent is paid then no deduction.

Did you know?

Rent receipts need not be collected from the employees if the value of rent paid is less than Rs.3000/- However, if there is an assessment then rent receipts needs to be produced before the AO by the employee

4.       FOOD COUPONS

o   Food coupons up to Rs.60000 p.a. is allowed as deduction. o   Companies like Sodexho are providing these coupons. o   You can buy your daily groceries by using these coupons

5.       LEAVE TRAVEL ALLOWANCE

Within a block of 4 years one can make use of this twice allowance. If in the previous block you have not used the benefit then you have to option to carry forward the benefit to the next block. Only the travel expenditure is exempt all other expenditures are not allowed as deduction

6.       80C DEDUTIONS

·         You get deductions on certain savings and expenditure up to Rs.1,00,000/- p.a.

·         Some of the allowed payments are life insurance, tution fees (college and school fees, PF, PPF, ELSS or MF’s, NSC’s and so on….)

There are many more deductions available, which we will bring out in the next edition stay tuned for that.

We would also like to say that there are quite a few transactions that could be planned in such a manner that both the company and the employee can claim benefit of. There will be certain clerical work to done if you have plan such transactions.

Please leave your email id or telephone number if you want to know more about this.

Excel Unused Formula No.1 Concatenate

Excel is really a great gift from Microsoft, but are we making use the most of it? This is one question that might strike to all of us. Forget the macros, other than the macros you have a lot of formulae which could help you do your work in a much more simpler and timely manner.

UNUSED FORMULA # 1: CONCATENATE

Figure # 1: Screenshot of the formula concatenate when entered in excel

Concatenate formula helps you to join the contents of two or more cells (max 30) in to a single cell.

We can understand how concatenate can be used through an example.

Example 

I had to enter a list of invoices raised by a company in to excel. All the invoice numbers had a prefix “April”. The invoice numbers were as follows April/1, April/2…… April/100. This is how I used concatenate to reduce my work.

How to pay income tax online?

How to use VLOOKUP formula in excel?

How to use VLOOKUP formula in excel?

Through this article we will explain you “how Vlookup formula works?”

VLOOKUP

When you type ‘=Vlookup’ in your excel sheet you will find the formula help box which states the use of the formula. See Figure 1

Figure 1: The screen which you see when you are entering the formula Vlookup

 

What is VLOOKUP ?

Vlookup looks for a value in a given table, and then returns a value in the same row or column based on your requirements.

 

What is the use of this formula?

We have a small hypothetical database of a cricket team with 7 players in the form of Figure 2

Figure 2: A hypothetical database of a cricket team

We have chosen a small table for explanation purpose only. The real usage of a lookup formula is felt when the database is very large. Now if we were to search the name of the player using his unique sl no. this is how we can do by using the lookup formula

Now let’s learn the components of the formula

a) Lookup_value = It denotes the value you want to lookup it can be cell or the exact value which you want to lookup. In my example let’s say 5

b) Table_array = It denotes the table in which you want to search the values. Here you can select the table from Sl No. to 100, the complete table

c) col_index_num = It denotes the column no. in which you want the value. For example here if I want to find the name of the player 5 then I will say column no. 2 because it denotes name in our column.

d) [range_lookup] = It denotes whether you want exact match or approx match. Here we want exact match hence we will say the same in the formula

If you use the formula then you will get the required answer i.e. "VINAY"

Test for a few and you will learn the art of using the formula lookup.