Here is the overview of the steps to follow for the Sales Pipeline Manager (CRM) Power BI.
Note: All files need to be uploaded to Microsoft OneDrive/SharePoint folder.h
1. The Settings Sheet:
The Settings sheet has all the relevant sales attributes, which can be entered just one time.
Enter the 10 intermediatory Deal stages in your sales pipeline.
And other details like the Lead source, industry, location, Lost Reason as defined by your organization, etc
Note: All the individual sales person should have a view access to the Settings sheet.
2. Data File:
Note: You can have multiple copies of the Data File for each salesperson under the Data folder as shown below. The Data folder should not contain any other files other than the Template Data file.
Overview of Steps to Enter Data in each sales person's Files
1. Refresh the data to import settings
2. Entering a new deal in the Deals table
3. Updating a deal as you make progress
4. Closing a deal
Step 1: Refresh the data to import settings:
Refresh the data to import settings from the settings sheet.
Step 2: Enter new deals:
We can enter new deals by typing in the Deals table.
Each row is a separate deal. There are some key types of information we enter in the table. Let’s look at them one by one.
Deal Name: Give each deal a unique name. This is a required field. If no deal name is entered, the deal does not get counted in the Dashboard. Having a unique name allows one to identify a specific deal easily.
Deal Value is the total value of the deal. Deal Value can be estimated if it has not been determined yet. But please enter a value so that the pipeline can be evaluated.
Created Date is the date when the deal was originally entered. This allows us to track the sales process and timeline from this date.
As the template can handle 10 intermediatory stages, there are 10 intermediatory stage date columns.
By default, the first and the last stages are NEW and CLOSED.
If your sales pipeline process has only 4 stages, then you can hide the remaining columns that will be shown as "Hide this Column". Do not delete. Just hide the columns.
When a deal passes each stage, enter the date when it reached that stage. If a deal has not reached a specific stage yet, leave the cell blank.
Enter Win %, Status, and Close Date
Then, we enter a Win % for each deal. For deals that are not closed yet, we would enter our estimate of the chances of winning a deal. If we have a 50-50 chance of winning or losing a deal, we would enter 50. The template will automatically convert to 50%.
If the deal is still open, leave the status blank.
Enter a win % for all open deals to calculate the sales pipeline value.
Deal Attributes: There are 5 deal attributes provided. Lead Source, Industry, Location, Product and Company are provided as default attributes.
These attributes are used in the Power BI dashboard in the analysis of your sales pipeline to understand what types of deals are being successful versus not.
Contact Attributes: 3 columns are provided as placeholders to store the Contact name, Email address, and Phone number.
Step 3: Updating a deal
As we continue to work on deals, new information may arrive. Based on that, we need to update the deals.
Update Win % for deals as needed, so that your active pipeline is always reflecting the reality.
Enter stage dates when the deal reaches each stage.
Step 4: Closing a deal
When a deal has closed, choose the status as WON or LOST and enter a Close date.
Deals can be closed if a decision has been taken by the customer to purchase. This is the scenario of a deal WON.
If the prospect decides not to purchase or if it has been a while since the prospect has been in touch, we can close the deal as LOST.
We can enter this information by entering the STATUS to be ‘WON’ or ‘LOST’. The values must be exactly WON or LOST. Other values are not allowed.
Then, we should enter the CLOSE DATE. This date is used to calculate the Average time taken to close a deal.
Lost Reason: This column can be used to store the reason why the deal was lost. Enter only for deals lost.
Check for Errors:
For every entry in the Data sheet, kindly check whether you've entered any erroneous data by looking at the ERROR column at the end. This ensures that all the data that enters into the Power BI are error-free for accurate insights.
Next Step - Setting up a connection between Excel and Power BI file:
Click here to set up proper connections in Power BI with the Settings and Data files in SharePoint.