This last quarter, I met several interesting startups, that had a clutch of good customers. When I asked them “How can I help you?” at least three of them asked for help with sales. Not what you’d think, as in find me customers or introduce me to prospects but how do I manage my sales pipeline. In fact two of them specifically had the question “How do I track my sales pipeline?”
Over the last several years, while I’ve used a variety of tools from mere contact managers through sophisticated deal trackers to full-fledged CRM suites, I’ve found myself returning each time to a simple spreadsheet-based sales tracker, at least in the early days. The tracker has not only evolved as I’ve learned but stayed surprisingly simple and has worked just as well in a fund-raising function at non-profits as it has in a for-profit startup.
As I promised these founders, I’m open sourcing the sample tracker as an Microsoft Excel spreadsheet as well as Google docs template. The tracker can be used for selling products or services or combinations thereof. You can download it here.
The tracker has three parts.
1. Setup – your business basics
Based on the nature of your business (product or service), actual sales offerings and the sales process your business may have to follow, you can tweak the setup. All this is done in a single worksheet (the last one, titled “Stages, Categories, etc.” of the online sales tracker). This one time set up of your product or service offerings, your sales persons (or deal owners), and stages of your selling process, makes maintaining your sales tracker easy and minimizes human or data entry errors.
Figure 1 – Typical Sales Stages
Sales stage this is simply the series of steps you have to go through from start to finish to close a sale. It begins with you first identifying a potential target customer for your product or service and runs all the way through receiving payment from the customer (never forget collecting the money is a critical part of making a sale). Figure 1 shows one such typical sales cycle.
Figure 2 – Sales stages for a demo-based sale
Sales stages obviously can vary for your particular business – one common variant that I encounter is when a demo installation or trial period needs to be offered to a customer (something you ideally want to get away from, but unavoidable particularly at tech startups in the B2B space). In this case there may be more interim steps (or stages) in your sales tracker.
Similarly you can set up your product or service offerings, as in actual names or code names that tell you what product or service you are talking about.
Tip: Typically I’ve found it useful to precede the offering name with a numeral such as 1-Bluetooth Stack or 2-SEO Consulting, as this makes sorting and other types of numeral based operations easier. For instance variants could all be numbered within say 100-200 so reports can be easily generated.
2. Sales Tracker
The sales tracker is a straightforward spreadsheet, with each prospective sale or deal on a separate row. For each deal, the row (or record) spells out, who the customer is, what is it that’s being sold (opportunity or offering), what revenue (or selling price) you expect, what sales stage is the specific deal at, who owns the deal and what the target close date is. You can of course have additional fields such as comments, or next steps, key customer contact. Figure 3 below shows a sample tracker for product sales.
Figure 3 Sales Tracker
The tracker also has variants of the sales tracker, if you need to track number of units (N) and have a unit price (P) and therefore compute deal size based on NxP (tab, Sales_Tracker_B_Units). Similarly there’s a tracker variant for service or project selling, (tab, Sales_Tracker_C_Project) where you can add descriptors for a project in addition to any opportunity or offering name you provide. Of course your business may require yet another variant, but you can simply by adding columns make the tracker your own.
By using the Filter function in Excel, you can look up deals
- of a particular size or greater
- expect to close prior to a specific date
- belonging to a particular sales owner or product (or both)
- at or before a certain sales stage
- that have closed but you’ve not gotten payment
In other words, an individual sales guy (that’s you) can see which of his deals he should focus on this week to close, what is the value of deals you intend to close this month (or week or quarter), which deals have NOT moved for more than a month – you get the idea, you can pretty much filter it any way you need.
3. Summary Report
Figure 4 Report Master
The first tab Report_Master, is a quick overview report of your sales pipeline. It presently has both #deals and deal value by sales stage. I’ve set these up as formulas – these could just as easily be set up as pivot tables if you so desire. You could do without this master report sheet, by merely filtering the sales tracker sheet itself. Alternately if you find that you are running some searches often, you can just have them set up as reports. Its also useful to have a report if you multiple folks are using the tracker and you want a big picture view.
Good luck with your sales – as and when you make improvements do share and spread the love and knowledge. If you have any questions please feel free to ask questions in the comments below. Spread the word. Happy selling!
I must confess that although I have been using “One page CRM” for all my sales related action tracking, I keep falling back to this simple template that you gave me whenever I need a snapshot view of what’s going on. Amazingly simple to maintain.
I must confess that although I have been using “One page CRM” for all my sales related action tracking, I keep falling back to this simple template that you gave me whenever I need a snapshot view of what’s going on. Amazingly simple to maintain.
Thanks for the sharing the Excel template and the post. I had a detailed look into all these and I think this kind of framework is at most applicable to a sole proprietorship business owner with a small setup. And it can never be a smart solution (with respect to a cloud based CRM solution) due to the following reasons:
1) What will happen in case of any mistake during data entry?
2) How you can collaborate this sales framework with other sales guys? Don’t say sharing with Google docs at it has its own limitation of not able to track changing others data (knowingly or unknowingly). Google docs is not yet smart to keep track of all these data changes and done by whom. In short there is no detailed revision history provided by Google docs.
3) How you can keep track of the revision history of all communications with your prospective customers? Generally it takes long time (though it is dependent on kind of products/services) to convert a lead to an account and lots of conversations happened during this time. Each of these conversations are very very important to capture. Capturing all these over Excel spreadsheet is unmanageable.
A smart cloud based CRM solution can handle all these. Yes, CRM solution also has its own problems and the most important issue with CRM solutions are they are not customizable based on one’s suitability. Having said all these still I see cloud based CRM solution is a better alternative to Excel spreadsheets. Yes, you can perform your sales activities with Excel (in fact you have already did that and achieved your goal), but that one can’t be a smart way of doing things.
Never hesitate to correct me if you think otherwise.
Thanks for the sharing the Excel template and the post. I had a detailed look into all these and I think this kind of framework is at most applicable to a sole proprietorship business owner with a small setup. And it can never be a smart solution (with respect to a cloud based CRM solution) due to the following reasons:
1) What will happen in case of any mistake during data entry?
2) How you can collaborate this sales framework with other sales guys? Don’t say sharing with Google docs at it has its own limitation of not able to track changing others data (knowingly or unknowingly). Google docs is not yet smart to keep track of all these data changes and done by whom. In short there is no detailed revision history provided by Google docs.
3) How you can keep track of the revision history of all communications with your prospective customers? Generally it takes long time (though it is dependent on kind of products/services) to convert a lead to an account and lots of conversations happened during this time. Each of these conversations are very very important to capture. Capturing all these over Excel spreadsheet is unmanageable.
A smart cloud based CRM solution can handle all these. Yes, CRM solution also has its own problems and the most important issue with CRM solutions are they are not customizable based on one’s suitability. Having said all these still I see cloud based CRM solution is a better alternative to Excel spreadsheets. Yes, you can perform your sales activities with Excel (in fact you have already did that and achieved your goal), but that one can’t be a smart way of doing things.
Never hesitate to correct me if you think otherwise.
Dear Ujjwal,
Thank you for your detailed comment. The simple tracker (hence its name) is not intended to replace CRM tools online or otherwise but is primarily intended to cultivate the habit of tracking. The issue is not that there aren’t tools out there but for entrepreneurs, of any size, to have an intuitive sense of their business and pipeline. Feature creep of tools and often founders (including myself) having engineering/tech backgrounds, its easy to lose sight of the forest for the trees, as we tweak our favorite CRM tool. Another challenge I’ve had in multiple firms I’ve been at, is to get all the sales folks to adopt/use – all the various functions of the CRM itself. Elsewhere I’ve written extensively about BatchBook (my own favorite CRM 🙂 and my own failures with CRM rollouts
The only quibble I have is with your assertion that this can work for only a proprietorship. I’ve run & helped others run businesses up to several million dollars of revenue with simple trackers.
As you see with project management tools or even online CRM tools there are at least 30+ serious options as each tries to address one or more needs or perceived shortcomings in the other. Each user/company will have to figure what works for them.
There is no doubt the CRM solutions, cloud or client based are far more suited than any “simple” tracker to run serious businesses of any size. Every business that I’ve helped/mentored, already had at least one cloud based CRM in play – yet I found them having bits of paper or worse yet, no real feel at fingertips of what their pipeline was. Think of this as starter wheels!
Dear Ujjwal,
Thank you for your detailed comment. The simple tracker (hence its name) is not intended to replace CRM tools online or otherwise but is primarily intended to cultivate the habit of tracking. The issue is not that there aren’t tools out there but for entrepreneurs, of any size, to have an intuitive sense of their business and pipeline. Feature creep of tools and often founders (including myself) having engineering/tech backgrounds, its easy to lose sight of the forest for the trees, as we tweak our favorite CRM tool. Another challenge I’ve had in multiple firms I’ve been at, is to get all the sales folks to adopt/use – all the various functions of the CRM itself. Elsewhere I’ve written extensively about BatchBook (my own favorite CRM 🙂 and my own failures with CRM rollouts
The only quibble I have is with your assertion that this can work for only a proprietorship. I’ve run & helped others run businesses up to several million dollars of revenue with simple trackers.
As you see with project management tools or even online CRM tools there are at least 30+ serious options as each tries to address one or more needs or perceived shortcomings in the other. Each user/company will have to figure what works for them.
There is no doubt the CRM solutions, cloud or client based are far more suited than any “simple” tracker to run serious businesses of any size. Every business that I’ve helped/mentored, already had at least one cloud based CRM in play – yet I found them having bits of paper or worse yet, no real feel at fingertips of what their pipeline was. Think of this as starter wheels!
The download links are not working. Please provide the right download link.
Muthuraja,
thanks for your note. The file had gotten moved by mistake and LINK now works – at https://dl.dropboxusercontent.com/u/605558/Sales_Opportunity_Tracker_DesignofBusiness.xlsx
Hi Ksrikrishna,
The link doesn’t work anymore. Could you please direct me to the correct location to download ?
@Amey – here’s the updated link
https://www.dropbox.com/s/twfgmsxldgkcn0h/Sales_Opportunity_Tracker_DesignofBusiness.xlsx?dl=0
The download links are not working. Please provide the right download link.
Muthuraja,
thanks for your note. The file had gotten moved by mistake and LINK now works – at https://dl.dropboxusercontent.com/u/605558/Sales_Opportunity_Tracker_DesignofBusiness.xlsx
Hi Ksrikrishna,
The link doesn’t work anymore. Could you please direct me to the correct location to download ?
@Amey – here’s the updated link
https://www.dropbox.com/s/twfgmsxldgkcn0h/Sales_Opportunity_Tracker_DesignofBusiness.xlsx?dl=0