CRM

CRM (Customer Relationship Management) represents the concept of managing relationships with potential and existing clients, customers or consumers (depending on what and to whom we sell). Its task is to obtain new and retain existing clients. Although there are plenty of software that very well support the CRM concept, when you are a beginner in business you do not need anything more than Excel.

In the following „recipe“ I will show you how to make a small usable CRM in Excel. CRM software supports a large number of documents and all activities within them are grouped into three major areas: marketing, sales and customer support. When starting own business you need to be short-term effective and then you need to maximize sales. At that time there is no purpose to keep complex records. Therefore, our CRM will be an Excel document with several worksheets. In this case we are following exclusively B2B sales.

Let’s create an Excel document with four worksheets, each of which will contain one structured table. They are more convenient than regular tables, since they by default contain filters whics help us narrow data view according to given criteria. The tables we create are called: PARTNERS (PARTNERI), CONTACTS (LICA), OPPORTUNITIES (POSLOVI) and ACTIVITIES (AKTIVNOSTI).

The PARTNERS table is located in the worksheet of the same name and here we need to record data on potential or existing clients – legal entities. In the columns we record in sequence the following: the ID of the partner (whole number growing from 1 to n), Status (active / inactive), Company ID, VAT ID, Name, Address, ZIP code, Town, telephones, e-mail addresses, remarks, etc. Since in the case we sell solely to legal entities the input of partner data is always the first step in the process.

Further we enter data in the CONTACTS table which is in the worksheet of the same name. Here we enter data about the people with whom we establish contact: ID, Status (active / inactive), Gender, Name, Last Name, Function, Education … Here we can also add a large number of contact’s personal data, such as the importance, impact, the role in the sales process, the date of birth etc. The more information we collect we will have a better overview of the people with whom we’ll communicate later on.

The main table of Excel CRM solution is the one in which OPPORTUNITIES are recorded (leads, prospects, accounts). They include data about specific offers that we are preparing for a potential or existing customers, and here we monitor the phases of sales process. We can visualize data later by creating a sales funnel chart based on columns containing phase and offer value. In OPPORTUNITIES table we first enter ID, start date and the end date of the job, as well as the status. The status of the job is whether it is a potential (lead), whether it is qualified (a client who showed a desire for cooperation akka the prospect), or sold, lost, postponed… Best choice is not to enter statuses manually but to choose them from a list, by previously applying Data Validation option. Further we enter the Subject (a short description of what we offer), the Value (the amount of the offer in a given currency), the Sales Phase, as well as the partner’s code and the person’s code. We can communicate with several contacts that belong to certain partner and in this column should add an ID of key person. Once we enter IDs in separate columns, by creating the formulas in which we use the VLOOKUP function, we show actual names and titles. Next, we can enter information about source, notices etc.

Finally in the ACTIVITY table we record all the activities we have done to a particular person (e-mail, phone call, meeting, presentation, etc.). We record the date, (if necessary) the activity time, the ID of the employee who performed the activity, Partner ID, Contact ID and a brief description of the activity. Activities can also have statuses and we can track them as planned and realized if there is a need for such.

CRM process takes place by recording following data in tables I was talking about. First step is entering data about a partner, one or more contacts, and then filling OPPORTUNITES record. Further we add activities which are connected to opportunities, and repeat the process of changing OPPORTUNITES and ACTIVITIES table until opportunity is sold or lost.

Since all of given tables have filters, by changing their values we can narrow down or expand the set of data we observe. For example, if we access the OPPORTUNITIES table and change the Status filter to the value “Qualified” we will see all qualified sales jobs, which are in fact active sales opportunities. Next, we can filter this table by the phase or bid value, and the procedure is repeated until the desired information is reached.

In this example I have also created several macros to simplify the use of Excel CRM solution. Macros are assigned to shapes that you can see above each of the tables and they mostly serve to filter or remove filters, as well as to position on certain table in another worksheet. For example, if you are in the OPPORTUNITIES table after clicking on the “O-P” button Excel will get you to PARTNER worksheet and filter given table to a partner that belong to chosen opportunity. By clicking „O-L“ same thing will happen with the contact which is linked to opportunity. This is an upgrade of the basic concept. Further you can add several more tables to describe different new processes within marketing, sales or service delivery.