“Tips and Tricks”

September 20, 2010

Excel Tip of the week-

Ever been stuck with a table containing hundreds of rows and columns?
Scared of applying formatting to it? How much time would it take to apply formatting to a table containing hundreds of rows and columns…..1 second….confused…Let us show you how.
– Click on Home Tab
– Click on Styles
– Click on Format As Table
– Select the range of your table
– Choose a design
And its done!!

ATI, one of the leading training institute in India is glad to announce a brand new course which will aid all the professionals/fresher’s interested in making a career in Business Research to achieve their goals

Business research is an important management activity that helps companies determine which products will be the most profitable for them to produce. Business Research is used toidentify opportunities, and threats and to provide corrective action.

Business research can also take the form of an industry research, a competitive landscape report or a market estimation report- all valuable inputs to a company’s decision-making process

Objectives of the Course

This course aims to help you develop an understanding of how business research is carried out in a management setting, and to appreciate some of the specific problems and issues you’re likely to face. It will provide a solid understanding of business research processes and methodologies

The course will also help you examine the strengths and weaknesses of competing approaches and tools for business research and some of the main ethical and practical problems that you’re likely to confront in carrying out research into organizations.

The course will provide you with frameworks and insights into different aspects of Business Research – including Company Analysis, Industry Research, Market and Competitive Intelligence

Our internship program will make you industry ready , where in you will get a chance to apply the methodologies and techniques of carrying out Business Research.

Mr. GautamMunshi , the CEO of ATI said “ The Business research course is the best possible medium for anybody who wants to get into research field.

Business Research can be done before the inception and after the establishment of the business.  It begins with understanding your target segments and making competitive plan.  Once actual transactions start happening it is conducted to know your customer better so that they can be retained and new customers can be attracted”

Business Research helps in understanding the key trends prevailing in the market with respect to Company, Industries, Sectors, Regions and more. It is applicable in all the Industries like Finance,Sales and Marketing, Retail and more.

Faculty Profile –

  • A Senior Consulting professional with 15 years of experience across Business Research, Analytics, and Consulting.
  • Experienced in setting up and leading business research and analytics teams, and delivering high impact research insights.
  • Has Held management positions with multiple MNCs over a decade.
  • Engineer from NIT Warangal and Masters in Management from IIM Lucknow.

Key areas of expertise include – Business Research Company, Industry and Market Track, Marketing Analytics & Insights, Presales Consulting,  Mentoring and Motivating Analyst Teams to deliver their best research.

About ATI –

Analytics Training Institute brings together industry professionals who are passionate about numbers and decision making skills. Analytics Training Institute has a combined experience of 30 years in the field of Analytics, which includes designing courses that empower every student/working professional with integral skills and techniques for the corporate world. We have trained In numerous leading Multinational organizations as well as well renowned educational institutions.

The beauty of Excel really is the number of unusual things this tool can do.  When people write the history of analytics and research in the years to come, it won’t surprise us if it is divided into two distinct aeons – B.E- Before Excel and A.E- After Excel.(and boy, are we glad that we are working in the enlightened A.E aeon!)

Here are 3 lesser known functions we have found useful on their day. There are many other similar ones,  that we have never used before. If you know any such , share them in the comments below!

1. INFO – Have you ever walked into the client site and been handed a brand new computer? Not sure how to use it, you breathe a sigh of relief when you notice Excel and quickly begin work there. But try as you might, you can’t access the computer’s control panel or properties to find out more about the machine. Let Excel help you. Want to check how much = total memory is available for use on the system? Click on any cell and type in ‘=INFO(“totmem”). Not sure which OS version you are working on? Try ‘=INFO(“osversion”). And once open, don’t know where this file is from? Try ‘=INFO(“directory”)’

2. Date and Timestamps- Working on a long financial model over many days and weeks- and want to remember when you last corrected each row? Just go to the end of the row and Click CTRL +; The day’s Date will be stamped right there. Move to the next column and click CTRL+SHIFT+; the current system time will be stamped. This function is also very useful for teams working in different shifts. Ask your evening shift colleague to leave this time stamp on every row he tampers with. Come in next morning, and you know that all the rows with timestamps after 6:00 PM have not been changed by you.

3.  Custom Views: You know how it is. You are creating a detailed budget plan, with many assumptions, some of which you don’t necessarily want your boss to see. You have that giant ugly worksheet with a million calculations, 5 different regions, and  a hundred assumptions that you have created. Then you hide some rows and columns, format some others, size the page up into a single- page print layout and print this version for him. “Great work”, he says, and asks you to share just the Kolkata figures with your Kolkata team mate. You filter the relevant information, take another print out and are just about to fax it when you notice an error in the calculations.

Don’t fret. Use Custom Views. Click on View>Custom View> and Create one View called Boss which has only the rows that you want to show your Boss, and another called Kolkata, created after filtering for the region. Work on the entire unhidden, unfiltered sheet and whenever you need to rush for a print out, just select the relevant custom view!

Business Analyst Manish Tripathi teaches you how to avoid jumping between Word and Excel

Have you ever wished that you could perf simple basic calculations while working on Word? You are preparing a project estimate and suddenly you felt the need of simple arithmetic operations to be performed. What do you do then? The obvious way is to open an Excel spreadsheet and do calculations there or open the desktop Calculator and calculate.

Well, why to take so much pains when you already have the facility in Word. Surprised??

Welcome to Calculate!!!

So where do we find the Calculate tool?? For that my friend you need to explore MS Word a bit. Frightened? Don’t worry, we are here to help you out J

By default Calculate command is not on the Ribbon, but we will bring it there.

Follow the steps to locate Calculate Tool:


The Office Logo on Word–>Word Options –> Customize.

Under Choose Commands from drop down, select All Commands

Under All Commands select Calculate. Click On Add to include it in the adjacent Quick Access Toolbar

Select Ok

You will notice that a round Green Button appears on the Quick Access Toolbar(beside the Office Logo)

Now in order to find the sum of Two nos. For e.g. 13.5+47.5

Write the two nos. whose sum is to be found just like the above. Select them by moving the mouse over them and press the green Calculate button on the Quick Access Toolbar.

And voila, the result is displayed briefly on the status bar at the bottom.

Apart from the Addition, the Calculate tool performs six arithmetic operations:

Addition, Subtraction, Multiplication, Division, Percentage , Exponentiation and Roots.

So, the next time you need some basic calculations to be performed while writing , you know what to use!!!

Priyadarshini Bishnu, Head of our Kolkata operations, advises young professionals to manage their online identities better

Now what is online identity management? Online identity management or online image management as it is sometimes known is a set of methods by which the information about a particular person can be received through the primary search engines like Google, Yahoo and MSN. The purpose of the online identity management is that it generates web presence for the person on the internet. This presence can be found in any kind of the media like for instance the online newspaper, blogs, forums, personal websites, photos and videos.

Online identity management also deals with increasing traffic both in quality as well as in quantity. Online image management is therefore related with search engine optimization. SEO is the short form for Search engine Optimization. It is a coding process used by the programmers to make the website speak to the primary search engines like Google, yahoo and MSN. By this process the website can be placed in the top of the listings of these search engines.

The way it works is quite simple. When users’ type in their requirement, the primary search engine asks the websites that are in their databases three questions to determine who is going to be seen in the first place and who is going to be seen in the last. The website that answers these three questions most accurately gets to be on top of the page and whichever website answers these questions worst gets a position at the end of the last page. In the case of those websites which cannot answer the questions at all they do not exist in the listings at all, therefore, no user can see these websites through the search engines. Hence in order to increase traffic into a website it is crucial that the website answers the three questions as accurately as possible.

The amount of traffic is directly related to search engine optimization and hence the amount of income of these websites is also totally dependant on this factor. The only difference between a search engine optimization and online identity management is that the only keyword that can be used here is the name of the person one is searching for. Therefore this method does not support the search of business organizations and other groups but deals with individual people. An example of this is that through the online identity management one cannot research on the United States government but can do research on Obama. Therefore, it is people specific. Another difference between search engine optimization and online image management is that the optimization object is not necessarily a single website but it can be a conglomerate of several websites that are either related to each other or are totally non-related. The objective of such a system is to get high rankings for as many websites as possible which are related to the person who is being searched. This kind of search is made through the primary search engines like Google, Yahoo and MSN and often when a person is searched on Google, people refer to it as ‘to Google’ a name of a certain person.

How can a person build up online identity? Today it is not very difficult for even the common man to build up an online portfolio for himself. It can easily be done in the following ways:

  • By creating a blog:  one can easily create a blog for free today. A blog is a social forum where one can write down his thoughts, share his expertise, express his opinions and generally interact with the other internet users. Today not only the common man but even the celebrities have started using this method to promote themselves. Writing blogs have become so popular that often celebrities hire people to write blogs on their behalf. There are many blogging sites available today and one just has to register there for free to become a member. It can be treated as a journal which the entire world can access.
  • Through the social networking systems- individuals have started promoting themselves through social networking systems like Facebook, twitter, flicker, orkut and MySpace. These sites are social sites where one can talk freely with another person without even knowing that person physically. Not only can information and texts be exchanged but also photos, videos and other personal information. It is a great platform to build a public profile on as millions and millions of people visit such sites. Most of these websites have high page ranks in the primary search engines like Yahoo, Google and MSN and hence a person who is using such a website gets noticed easily.
  • Other mediums like the www.ezinearticles.com are a great platform for individuals who want to promote themselves. A person can easily write articles in this website and get known in the search engines. This website allows a person to add their web address at the bottom of each article which exposes the person’s website considerably.
  • People often maintain their own website to attract people’s attention. This is the easiest way of promoting oneself as the content of the website is totally controlled by the concerned person. So he can manipulate it anyway to bring about a positive online image.
  • Online identity management is particularly easy when the concerned party is a celebrity as then he would be mentioned in various aspects of the internet starting from blogs, social networking systems to the online newspapers and magazines. It is also easy to analyze these profiles as they are mentioned widely all across the internet.
  • Another way in which a person can promote himself is by adding their job profiles in sites like www.linkedIn.com. This is a hundred percent job related website where people upload their educational qualifications and their job profiles. They meet other professionals through this website as well, creating a community for themselves, where they can share information and data about the current jobs available and the competitive salary packages.

One may ask what is the purpose of promoting oneself on the internet or in other words is online identity management really necessary. The answer is quite simple. It is a big ‘yes.’ This is because to majority of the recruiters check the online profiles of the potential candidates before hiring them. Therefore it is really crucial to have a blemish free and positive online appearance. As even a slight tarnish in the online image can cause a person to either lose his job or to not be selected when he applies for a new one.

A recent research held by www.careerbuilder.com found that every one out of four hiring managers’ use search engines like Yahoo, Google and MSN to screen the potential candidates. The recruiting managers have become so dependant on the internet for their information on the candidates that out of every ten managers at least one look through the social networking profiles of the candidates before selection. This just goes to show how important it is to maintain a clean and positive web image.

Therefore, the objectives or the purpose of highlighting one’s image on the internet is quite clear. It is done to attract the recruiting managers, probably potential love interests and other people who are likely to come in handy when required.

Online identity management in essence is not very different from the search engine optimization as they both deal in optimizing their subjects. The only difference therefore, between the two is that search engine optimization gives exposure to business houses and other websites with no emphasis on a particular individual whereas, the online identity management, focuses sole on individual identities, giving them focus and prominence. They maximize the appearance of positive online references of individual personalities, targeting both the people who proactively search for this individual on the primary search engines like Yahoo, MSN and Google but also to people who eventually reach the individual through browsing of the internet.

People also use this process that is, online image identity to build profiles on the internet especially when the concerned person’s web presence is non- existent or very little. By this process he increases his presence considerably which allows him to be either Googled or found out through the other search engines. They increase his chances to be exposed to the recruiting managers which allow him to get jobs faster.

The online identity management or the online image management also helps in solving online reputation problems. If one has a bad reputation online he can alter it with the help of reputation management. This again helps him to get more or better jobs.

Therefore, though it is not mandatory yet it is essential that one maintains an online profile that is easy to comprehend and is possible. The more information there is the more chance there is to get a job or to meet someone that he likes. Therefore, today more and more people are resorting to online identity management.

Neha Jain, our Bangalore Advanced Analytics Trainer, teaches you how to solution an optimizing problem in Excel


Suppose a  company wants to assign 15 employees to 10 offices. There are four offices that require 2 people. Each employee has given their preference.   1 means first choice, 2 means second choice, etc. How should the company assign the people to the offices to optimize the preferences of the employees?


We can easily solve this problem using solver.

1) The variables are the assignments of the people to different offices. On worksheet Offices these are given the name Assignments.

2) There are the following logical constraints:

Assignments = binary

and the other constraints

Assignments_per_employee = 1                                                                                                                                                                  Total_employees = Required_employees

3) The objective is to optimize the preference of the employees. That means we have to minimize the sum of the total preferences given to the assigned offices, defined on the worksheet as Total_preference.


When everybody wants a different office, there will be no problems. If all employees prefer the same office (more likely!), the problem gets more difficult and it might be necessary to give an employee 7th or 8th choice. It might be wise, in that case, to add a constraint to say that no assignment worse than 5th choice is given, for instance. This may cause the problem to be infeasible, i.e., there is no possible solution. If this happens, you will have to relax the constraint on the assignments, e.g. no worse than 6th or even 7th choice.                                                                                                                                                                                                                                                                              

Our Hyderabad Head Trainer, Mukta Phadnis reports:

Ever wondered what made Uninor, Aircell, enter into Indian market when there already were 12 mobile operators in India, including two government-owned companies?

It is expected that the Indian mobile market will double in the next three to four years and there is nothing called one-size-fits-all for a 1.2 billion India. Uninor, Aircell followed customer segmentation strategy and are targeting only those customers who will benefit from its services.

Customer segmentation is nothing but division of customers into homogenous groups of people having similar characteristics in order to adjust the portfolio of products and services, but also sales and public relations etc., to their various needs.

Now it’s a bigger challenge for other leading GSM telecom operators in India namely Airtel, Vodafone, Reliance Communications, Tata DoCoMo, and Loop Mobile to retain existing customers and grow their customer base amidst  the stiff price war posed by the new entrants in the market. Geographic segmentations, Psychographic or Lifestyle Segmentation, are just the first steps towards an integrated customer segmentation based strategy

For most customer segmentation analysis various types of cluster analysis is used. Cluster analysis is well-defined statistical technique that group people according to the proximity of their ratings. Correlation and Regression can be used to study for psychographic segmentations. Factor analysis is also a powerful technique to identify the statements and groups of statements that account for much of the variance in the attitudinal data set. These techniques can help you identify the most important statements  (i.e., attitudes, perceptions, values). Then, these statements become the inputs to the final segmentation analysis. Many different methods can be used to cluster or group the statements at this point.

Let’s take a critical business decision of retaining just the high-value customers (assuming that some will attrite any way). If we knew which customers of ours were likely to quit to a competitor, and which of these are really worth keeping, we could intervene in a timely manner and take steps to retain them. This will ensure ongoing revenue from them, reducing costs of replacing them with the new customers and maintaining market share.

So where does analytics come in? Analytics will help build predictive models to identify high value customers at every point of interaction. Once the high value customers and potential attritors are identified, one can devise strategies to retain only those customers that are profitable

Telecommunications companies hold rich data on their customers. Personal information, demographic information is gathered while subscribing to a new connection. Call detail records like Call volume, Usage, Calling Patterns, Roaming Behavior can be used to study customer behavior.

A statistical model can be built that evaluates customer and classifies them on a matrix of profitability and loyalty. Those that are high on profitability and on loyalty can be rewarded for their behavior with incentive schemes. Those lower on the loyalty axis but high on profitability can be enticed with discounts on essential services.  Effort can also be made to retain them by excellent customer service. Those high on loyalty and low on profitability can be made more profitable by being offered spends promotions that encourage them to spend more. While as a company, fewer resources should be spent on those low on both loyalty and profitability. The retention metrics for the company therefore, is not how many customers are retained but how many profitable customers are retained, and how much the per customer profitability is increased in a time period

Telecom operators operate in highly dynamic markets; customer preferences change quickly based on fashions, trends, competitors’ actions and the products, services and packages the provider offers hence ongoing market research and analytics becomes paramount to the success.