Wednesday 15 April 2015

Use of Vlookup formula in Telecom

Vlookup Formula used in Telecom

Telecom is all about the numbers, Be it subscriber,revenue,churning or retention, Telecom people talks only in numbers and about the numbers.This needs several analysis methods which all based on mapping of the right database against the right set of subscribers. This mapping can be done many methods but we use one of the most reliable excel function, called as 'VLOOKUP'

There are 4 components in the VLOOKUP formula. Three are mandatory fields and one is optional.With VLOOKUP, You will filter out the needed information for you regardless of the volume or organization of the database. 

1) Lookup_value – The value you want to look up. 
2) Table_array – The table of data you want to look up 
3) Col_index_num – If you find a match, which column of data you want to pull. 
4) Range_lookup – Put in FALSE to find only exact matches

We will utilize the previous sample excel to understand Vlookup in better way.The below image shows the criteria which we needs to follow to get desired results.
Criteria
Once you fill up the above criteria ,The VLOOKUP formula would automatically look through the list of your objects and only pick the corresponding data. The mapped value is the result of 'VLOOKUP' formula.
The sample excel contain the data of LOCAL usage done by subscribers and has mapped against Master sheet.

The below image is showcase the scenario by using VLOOKUP formula.
Vlookup Formula

Vlookup can make your work easier and faster when you want to look up some data from the huge volume.
This is the basic formula which every working professional should know, especially if you want to work in the analytical environment then VLOOKUP is the must for them.

Friday 10 April 2015

Use of Max and IF Excel formula's in Telecom

MAX & IF Formula's used in Telecom

Max formula used by many analyst and MIS executive to find out the key insight of the database. It helps them in segregation and targeting the database depends upon the usage of the customers.

Let us See the use of MAX formula to find out the high usage from the services utilized by demo database.

The below sheet represent some column with services utilized by customers for certain period.

All the Business strategies in telecom or any other similar sector depends on the usage of the customers, Telecom people call it as "ARPU" means Average Revenue Per Users is being most important element in the sector and lots of strategies can built to get the HIGH ARPU from their customers.

Calculating ARPU with available database is the easiest task one can do but get the category wise usage one needs MAX formula.As per the below sheet their are usage has given in the column against every subscriber/number.

Now, We have to find out the maximum usage from different categories.Let make use of MAX formula now.

Demo Database
1. Select the criteria from the sheet.
2. Write the MAX formula as  =MAX(D4:I4) or =MAX(Select field)
3. Enter and then you will see the Max value from the usage.
4. Drag the formula and apply for rest cells in column.

MAX Formula 

Though, We got the MAX usage from the list but now we will mentioned Maximum utilized service/product by using IF formula and complete the task for further analysis which can allow us to find out which product has been utilized max by the customers. 

Hence, We will make use of IF formula in the below mentioned way and prepare the database for ARPU calculation on the basis of product/service utilized.

IF Formula

1. Select the criteria from the sheet.
2. Write the IF formula as =IF(J4=D4,$D$3,IF(E4=J4,$E$3,IF(G4=J4,$G$3,IF(H4=J4,$H$3,$I$3))))
3. Enter and then you will see the Service/Product mentioned against the MAX usage.
4. Drag the formula and apply for rest cells in column.

If you work as a MIS Executive or Business Analysis in telecom or related industry these 2 formula's will help you to give you good information about your data and help you in analysis.