fbpx

Customer Relationship Management Excel

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp

Brief

Free CRM Template Excel Download

Please note that the use of the free version for commercial/ corporate purposes violates our terms of use. This version of the tool is only intended for personal purposes. For corporate use of this tool, kindly refer to our commercial version.

Previous
Next

          A well-structured customer relationship management system and process often leads to a decrease in labor costs, better customer engagement, lower error thresholds, clearer ownership and even insights on the business itself. Unfortunately, a good structure often means thousands of dollars spent in process development, tools and labor that sometimes provides limited value to the organization and still fails to, for example, ensure that clients are engaged throughout the sales pipeline process. In this article, we will look at a free Business Development (BD) CRM template on excel, a relatively simple solution that GPetrium has created to help businesses gain control of their CRM and to automate some of the steps to cut down CRM cost.

Join thousands who have taken the next step in improving operational efficiency and insight analytics with GPetrium solutions.

100 +
and counting

What is the GPetrium’s Excel Customer Relationship Management?

          GPetrium’s Excel Customer Relationship Management (Excel CRM) is a free excel tool that facilitates customer relationship management for businesses, consultants and freelancers. The tool is designed to track information to support the rapid creation of e-mails. It features a handy data visualization segment that helps guide decision making.

          The tool is divided into 4 key segments, the ‘Dashboard’, ‘E-mail’, ‘Sales Data’ and ‘Config’. The ‘Dashboard’ segment provides insight on key variables impacting the overall CRM of the company. The ‘E-mail’ segment is a pre-built, auto-populated e-mail that can be easily copied and pasted into the e-mail. The ‘Sales Data’ segment keeps an itemized control of all the customer relationships that have been or will be processed. The ‘Config’ segment is a configurable drop-down list for a majority of the items within the Sales Data, it helps decrease time spent writing the data and the number of errors.

Dashboard tab

1.Potential Clients Contacted: Provides a quick overview of the number of clients that have been contacted per quarter. For example, this graph, coupled with the slicers (#6) and the other graphs can help an organization determine whether to increase/decreases the sales efforts among other things.

2.Opportunity Probability Pipeline: Quick overview of the number of opportunities and its respective odds. Keeping tabs on the expected odds of winning a contract can be detrimental to a company sales pipeline strategy, helping to determine whether to increase or decrease efforts(money) in certain business developments (BD) to maximize organization’s success.

3.Opportunities by Sales Segments: Provides an overview of which sales segments are having more or less success. This information can help drive decisions such as increasing focus in the areas with higher profitability/odds, dropping segments that don’t seem to pan out, etc.

4.Opportunities by Region: Provides a view of the major regions that your organization covers in relation to the number of opportunities at hand. This information can help guide sales efforts (E.g. Should we search for other clients in the Asian market to decrease our dependency on Canada?).

5.Pipeline Stage: The pipeline stage helps the organization to keep track of the number of opportunities in each pipeline stage according to the parameters set in the slicers. This information coupled with other graphs and filtering information such as Sales Executive can help the organization keep track of how certain executives are moving forward with their sales and whether they are keeping a healthy pipeline. This information can also be filtered into by Sales Segment, helping to determine whether the pipeline is trending in the right trajectory.

6.Lead Method: The ‘Lead Method’ graph can help organizations determine the varying degrees of success per lead method, helping to jumpstart further inquiry over why certain lead methods are succeeding while others may not and whether there is a need for a change in sales strategy.

7.Slicers: [Sales Region], [Sales Segment], [Sales Executive], [Pipeline Stage], [% Prob], [Year] and [Quarter] these slicers can be used to dig into the visualizations to better understand what is happening throughout the CRM journey.

Email tab

CRM email sampler
Semi-automated e-mailer for the CRM excel template

          These scripts are auto-populated after a row in ‘Sales Data->E-mail Post’ is set to Yes, allowing you to copy the person’s e-mail, subject and body into the relevant message.

  1. E-mail: This section is filled out based on the ‘Contact Info’ column at the right end of the ‘Sales Data’ tab.
  2. Subject: User needs to manually change this information to conform to their own script subject.
  3. Body: User needs to manually change this information to conform to their own script subject. The first paragraph contains a formula that automatically adds the name of the Sales Executive and the location that the prospect was found. The same formula can be moved around as needed on the script.

          At this point, the ‘E-mail’ tab is set out to have 2 scripts, allowing the organization to do an A/B testing to help determine which message is more successful. More scripts can be added to the file as needed.

Sales Data tab

          There are currently 22 columns that can be populated into the ‘Sales Data’ tab. Although not every column is critical to the functioning of the tool (as exemplified above), each one can help the individual and organization to keep a better track of their CRM, helping to improve the customer experience and potentially increase sales. Below we will look at each column:

  • E-mail Post: Is a drop-down option that allows you to fill out the ‘E-mail’ tab whenever ‘Yes’ is chosen. To ensure that only the relevant e-mail is set-up, make sure to keep all the other rows blank. The ‘E-mail’ tab formulas are set-up to pick-up the first ‘Yes’ it finds in that column.
  • Sales Region: Currently configured at a country/region basis, this drop-down selection can be changed to fit your personal needs.
  • District/City: An open-text section to choose district/city of organization/contact.
  • Company: Name of the customer/organization.
  • Division: Division of the company being contacted.
  • Project Name: Name of the project/good expected to be sold.
  • Sales Segment: Drop-down of the product/segment being sold to the client.
  • Contact: Name of contact in the organization.
  • Title: Drop-down title of the contact
  • Sales Executive: Drop-down name of the representative of your company for that prospect
  • Date Called/E-mailed: As the name suggests, can help determine when to contact the prospect again. It also helps to date information in the Dashboard.
  • Script: Name of script used to reach-out to the prospect. Helps to gather data regarding the degree of success of each script.
  • Lead Method: Drop-down of the form of lead for the client/project.
  • # of times contacted: Drop-down for the number of times the prospect has been reached.
  • Follow-up: Open text follow-up information
  • Meeting Booked: [Yes] or [No] drop-down on whether the meeting has been booked.
  • Pipeline Stage: Drop-down for the pipeline stage of the project/prospect.
  • % Prob: Odds of winning the contract/project/prospect.
  • Year: Auto-fillable information according to [Date Called/E-mailed]
  • Quarter: Auto-fillable information according to [Date Called/E-mailed]
  • Location: Location of where the source’s information has been found. It is currently used in a formula for the body of the ‘E-mail’ scripts.
  • Contact Info: Usually the e-mail address to be contacted via the script. It is currently used in a formula for the email of the ‘E-mail’ scripts.

Config Tab

          This section helps to configure the drop-down list for [Sales Region], [Title], [Sales Executive], [Lead Method], [E-mail Sent?], [Contact Occurred?], [Meeting Booked], [Pipeline Stage], [Sales Probability], [Sales Made] and [Sales Segment]. If you would like to decrease or increase the number of options in the config tab, you will need to change the Data Validation for that column, please refer to this external website for guidance.

Conclusion

          A good CRM process and solutions can be beneficial to a smooth customer and prospecting experience. The GPetrium Excel CRM tool provides a baseline for clients of all sizes to move in the right direction by automating segments of their CRM process, gain insight on CRM data all while using a single, widely available tool. There is plenty of space to improve the tool by automating e-mailing, providing error handlings, keeping track of the number of times a client has been reached for payment and others.

FAQ

Q: I have updated the dataset, unfortunately the Dashboard did not update. What do I do?

A: To maximize cybersecurity, the team did not use VBA/macros for the solution. Therefore, in order to update the dashboard, you will need to go to ‘Data->Refresh All’.

Q: I tried to add a new drop-down item to the [Title] Config tab, unfortunately, it does not appear on the Sales Data.

A: The drop-down system is based on the ‘Data Validation’ excel feature, it restricts the entries based on a subset of cells that have been chosen. To recalibrate, you will need to choose all cells in that respective column then on the access toolbar go to ‘Data->Data Validation->Settings’ and change the Source to cover all the items you expect to have on the drop-down.

Our Latest Posts

Toolkit for a Successful Project Manager

Tools and techniques can help project managers in their conquest to deliver effective projects that fulfil the described constraints. In efforts to assist project managers to achieve success in their projects, in this article, we look to provide pragmatic knowledge on tools and practices that may help individuals to manage their projects and tasks more efficiently.

Read More »
Receive a monthly newsletter with updates, insights and solutions from GPetrium!