I have been focusing on the setting up of Sage SalesLogix (SLX) Visual Analyzer (VA), specifally the Microsoft Excel Spread Sheets that support Key Performance Indicators (KPI). The Opportunity KPI is next on the list.
Using the SLX Eval database I have entered the SLX as Admin and I have navigated to the VA main view by using the Nav bar icon. Along the top you see numerous tabs but my only concern for the moment is the one labled KPIs. Under the ‘Name’ list box I have our friend Lee Hogan highlighted with June 2008 selected under End Date.
As you can see a goal has been established in the VA KPI spread sheet for Lee to have a total of $3,600,000 of open Opportunities in 2008. With standard VA doing the math and since this is June the Goal reflected in the dial is $1,800,000.
The “Actual” amount reflects the current Pipeline for each user by summing the Weighted amount of all Open Opportunties that have a close date in or before the selected month (In this example June) and the Selected year (2008). This means unless the Opportunities are updated when the new year comes around all open Opportunites with a Close date within the old year will not display. This is the case with our example, Lee shows none but when you dig into his records you will find plenty of open Opportunities from 2007.
In order to come up with a Opp goal numbers for 2008 in my client database installation, I ran the following SQL Query to help discover goals for this KPI:
SYSDBA.USERINFO.USERNAME AS [User]
, Sum(SYSDBA.OPPORTUNITY.SalesPotential) As TotalPotential
, Count(SYSDBA.OPPORTUNITY.OpportunityID) AS NumberofOpps
, YEAR(SYSDBA.OPPORTUNITY.CREATEDATE) AS [Year]
FROM SYSDBA.OPPORTUNITY INNER JOIN SYSDBA.USERINFO
ON SYSDBA.OPPORTUNITY.ACCOUNTMANAGERID = SYSDBA.USERINFO.USERID
–Where SYSDBA.OPPORTUNITY.STATUS = ‘Closed – Won’
GROUP BY SYSDBA.USERINFO.USERNAME, YEAR(SYSDBA.OPPORTUNITY.CREATEDATE),SYSDBA.OPPORTUNITY.ACCOUNTMANAGERID
ORDER BY YEAR(SYSDBA.OPPORTUNITY.CREATEDATE), SYSDBA.USERINFO.USERNAME
In our evaluation database this query told me that Lee Hogan had 42 Opportunites worth over 18 million dollars in the system in 2007.
Removing the “–” in front of the SQL Where clause told me that in 2007, Lee won 17 Opportunities which added up to just over 8 million dollars.
The numbers from these queries told us that Lee Closed and Won just over 40% of the Opportunties, this added up to about 45% of the SalesPotential entered into the system.
If you gave Lee a goal of $8,800,000 dollars for 2008 based on a 10% increase of Sales from last year and divided that by 12, gives Lee has a monthly goal of $733,333 Sales per month.
Taking in to consideration the 40% succesful close rate, our client decided to multiply this goal by 1.6 giving the user a yearly goal for Open Opportunities of $14,080,000 which rounds out to about $1,173,000 a month.
To update the KPI spread sheet with data decided above I followed these steps:
Log into the SLX Database as Admin.
Navigate to the VA interface by the NAV Bar button.
Along the top center of the view you will see a tool bar box with a number of icons, The second icon from the right has a drop down, select the “Manage Configuration” from the list.
The Manage Configuration view will appear. Select the “Open in Excel” button and select the KPIs.xls file from the windows open view. The KPI spread sheet should open.
Navigate to the KPIGoals(2) sheet.
The first thing I did was delete all of the data in the coulmns to the right of colum A. I entered the names of each of the users for each year I want to track based on the list established in Part 1 of this series of posts.
For each user and year, I added the Goal and then deleted or added the value of “Opps” in column A as necessary.
Save and close the KPIs.xls file.
On the Manage Configurations view, select the “Load From Excel” button
Again the select the KPIs.xls file from the windows open view. The data sheet should now be updated with your data.
Now refresh to see the changes in VA.
After digging into this KPI, I have to say I feel there could be a better way of looking at the PipeLine. Why would you want to leave Opportunties open in the month they are originally estimated to close? Wouldn’t you continue to manage your Opportunities Estimated Close date through the close of the Opportunity. Isn’t that what managing Opportunties all about? Of course I know that everyone will have thier own opinion but I think a better method of measurement for this dial would be to display the Total Weighted Potential for all Open Opportunites and compare that to an Opportunity Goal based on the Sales Person Closed – Won rate. In other words, if Lee’s Sales goal is $733,000 each month to reach $8,800,000 this year, and you know that on average he closes and wins 45% of the Sales Potential in the system. To establish his Opportunity Goal the equations could be $733,000 multiplied by 1.55 to equal $1,136,000 which says this is the amount of Open Opportunites you want Lee to have in the system at all times.
I tried to use the standard functiality to get the VA to think this way but was unsuccesful. You will probably see this modification in a future post. Next up, the Activities KPI