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.
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.
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.
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.
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.
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:
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.
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.
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.
Effective Charismatic Leadership can be natural, yet, for most people it is acquired through hard work, experience and tact.