What is a sales funnel?

The sales funnel is a systematic evaluation of your active sales opportunities , also known as opportunities . Sometimes it is also called a pipeline or sales forecast. Literally translated, the sales funnel is your “sales funnel”: the funnel starts at the top with a large number of potential customers and narrows towards the bottom as more and more prospects are weeded out or lost in the course of the sales process. Typically, a customer goes through several stages, from the first contact to the final purchase.

To better understand the sales funnel, we first need to explain two terms:

1. The individual phases or sales stages of the sales funnel can vary depending on the company and industry, but often include steps such as prospect identification, qualification, pitch presentation, proposal preparation and closing.
2. The conversion rate indicates how many of these potential customers actually become buyers. The expected conversion rate increases with each sales stage. For each sales stage, you therefore define a value for the proportion of the respective opportunities that experience has shown to lead to sales.

The following table illustrates this using a typical example:

The sales funnel helps you to structure and properly focus your sales activities. With its help, you can identify bottlenecks in the sales process and initiate targeted measures to increase sales.

How do you get from your sales funnel to turnover?

In addition to the sales stage and the conversion rate, we need another ingredient for this: the order value.

The order value of an opportunity indicates how much revenue we would generate if we were to close it.

For example:

If we now use the conversion rates that we set for our sales stages, we can easily calculate the expected revenue for each opportunity.

The formula for this is:
Order value * Conversion rate = Expected turnover

If we add up the expected turnover of all opportunities in our sales funnel, we have the expected total turnover that we can achieve with our funnel. This value is often referred to as the value of the sales funnel, pipeline value or weighted pipeline.

Expressed as a formula:
(order value 1 + order value 2 + order value 3) * conversion rate = value of the sales funnel

In our example, it would look like this:

Congratulations! You have built your first sales funnel. Now we can build on this very simple example to make our sales funnel more meaningful.

The 30% rule: rule of thumb for evaluating your sales funnel

To get an initial impression of how well stocked your sales funnel is, you can use a simple rule of thumb: in most sales organizations, the value of the weighted sales funnel corresponds to around 30% of the sum of the order values. In simple terms, this means that for every EUR 3 of order value, EUR 1 actually leads to sales.

This means that, as a rule, you need around three times your planned target turnover in terms of order values in the pipeline in order to achieve your sales target.

We can easily apply this rule of thumb to our example:

• Total contract value: EUR 350,000
• Weighted value of our pipeline: EUR 115,000
• Ratio: EUR 115,000 / EUR 350,000 = 32.86%

In this example, we are therefore slightly above the thumb value of 30.00%. This means that our pipeline should have a realistic weighting and we can probably achieve our sales target.

Practical tip: Defining conversion rates is a management task!

Perhaps you know this from your sales organization:

Salesman Max Maier tends to overestimate himself. If you ask him about his opportunities, all opportunities are always close to being closed and are at least 80%. In reality, however, he only “closes” a much smaller proportion of his sales opportunities. Saleswoman Sabine Schmidt, on the other hand, usually “stonewalls”: her opportunities remain at 5% for months and then suddenly jump to won, i.e. 100%.

So if you had the members of your sales team determine the probability of closing for each opportunity, you would usually have a much too optimistic expected value for Max Maier and a much too pessimistic expected value for Sabine Schmidt. And even worse: the values between the individual opportunities in your entire sales funnel would not be comparable at all and you would completely lack reliability.

Therefore, engage in a discussion with your team and standardize the definition of conversion rate targets for each sales stage, as we have done in our example. Over time, you can then regularly compare the conversion rates actually achieved with the targets you have set and make adjustments or adapt the targets if necessary.

How you can plan sales on a monthly basis in your sales funnel

In addition to the expected amount of sales, the timing of sales is of course also very important. In order to be able to show this reliably in your sales reporting, we need another ingredient: the sales cycle.

The sales cycle describes the time a potential customer needs from the first contact to closing the sale. For example, you need an average of 9 months from the first contact with a potential customer to closing the sale.

Expressed as a formula:
First contact + sales cycle + duration of invoicing = time of sales

For example:
First contact in January 2024 + 9 months + 1 month = sales expected in November 2024

For a more precise estimate, we can differentiate the typical sales cycle depending on the sales stage. This is because, of course, the expected remaining time to close becomes shorter with each stage that an opportunity passes through. So if we record an expected remaining time to close for each stage, similar to our conversion rates, we can also predict our sales over time.

This estimate will be even more accurate if you have the member of your sales team responsible for each opportunity in the funnel document the expected closing date and check it regularly.

By combining the sales cycle and conversion rate in the sales funnel, you can now plan the expected sales for your company on a monthly basis. This could look like this, for example:

By knowing how many customers are in which phase of the funnel, how likely they are to complete a purchase and the expected completion date, you can create monthly forecasts for future sales development.

Sales funnel, marketing funnel, BWA: which one do you need for what?

When it comes to reporting, there are significant differences between the sales funnel, marketing funnel and financial reporting. These three types of report describe different periods in the business process and are usually fed from different data sources:

1. The sales funnel focuses on the phases of the sales process, from initial contact to closing, and helps to monitor and predict sales progress. This is the focus of our article.
2. Marketing funnel reporting, on the other hand, relates to lead generation and qualification. It focuses on the generation of leads and the further development of Marketing Qualified Leads (MQLs) into Sales Qualified Leads (SQLs) and finally into opportunities. In the sales process, it is therefore upstream of the sales funnel and provides the input, namely the opportunities, for our sales funnel.
3. Financial reporting includes, in particular, the BWA (business management analysis) and has a different focus. It analyzes past financial performance and provides an overview of income, expenses and profits. The BWA provides detailed key business figures and is used for short-term financial analysis. It is therefore downstream of the sales funnel.

It is important that financial reporting only ever looks at the past: what sales were made or not made. In particular, it does not provide an early warning system for impending deviations. Our sales reporting, on the other hand, looks at future sales. Sales reporting is therefore our central tool for achieving and reliably targeting sales targets.

The different reports and perspectives complement each other and together provide an overall picture of your company’s commercial situation. It is important that you pay attention to the consistency of the different perspectives: precisely because the data sources are usually different, you should ensure that the sales forecast in the sales funnel actually end up in the invoice in the further process flow (i.e. invoiced to the customer) and are traceable in financial reporting.

Where do you get the data for your sales funnel?

The data basis for financial reporting is financial accounting, which is usually managed in an ERP system (e.g. DATEV or Microsoft Business Central) and, depending on the size and organization of your company, is created by the company’s internal accounting department or supplied externally by the tax office. Sales funnel reporting, on the other hand, is usually based on a CRM system (e.g. Microsoft Dynamics CRM, Salesforce, Pipedrive or Hubspot) as the data source. In smaller companies, the “CRM” system is often still mapped in Excel spreadsheets.

Every common CRM system manages opportunities. Even if the naming differs between the systems (and instead of “opportunities” there is talk of chances, deals or prospects, for example), the data fields that we need for our sales reporting can be found in all common CRM systems.

In order to understand the data structure in your CRM and, based on this, to carry out the necessary evaluations for your sales reporting and summarize them in a professional sales funnel report, you need a good knowledge of the respective CRM system and the data structures used there, as well as skills in the use of a suitable reporting framework. This can be Excel or other analysis tools such as PowerBI or Tableau. Much more important than the technology used is a precise understanding of the objective and the data and integrations relevant for the analysis. You can download a comprehensive Excel template suitable for the content of this article free of charge using the download form directly below this paragraph.

Excel template to download for your sales report

If you have a sales controlling team in your organization, you can usually make good use of it for this purpose. Alternatively, setting up professional sales reporting based on your CRM and your data structures is a project that can also be easily solved by external consultants. If you need support with this, please do not hesitate to contact us.

Step-by-step guide: How to set up your sales reporting professionally in 7 steps

1. Define goals: Clarify which goals your sales funnel and reporting should support. Which sales lines do you want to focus on? Against which budget planning or other target values do you want to compare in reporting? Define clear KPIs that measure success.
2. Define sales stages: Define the stages of your sales process, such as contact, qualification, presentation, offer, verbal commitment and closing.
3. Determine conversion rates: Determine the probability of closing for each sales stage based on historical data or industry benchmarks.
4. Make existing data sources usable: Make sure that your CRM system (or your Excel world) contains the necessary data fields and that your sales team uses it to record and manage all opportunities according to your specifications.
5. Ensure data quality: Check your data regularly and have your sales team correct and cleanse it if necessary to ensure the accuracy of your sales funnel. Ensure consistency with upstream marketing funnel reporting and, above all, with downstream financial reporting and the BWA.
6. Set up a reporting framework: Use a suitable template such as our sales funnel template or other suitable tools to evaluate and visualize your CRM data. Determine which role or person from your team is assigned the task of creating the report on a regular basis and at what intervals the report should be updated (e.g. weekly). It is also helpful to clearly specify to whom the report is distributed and whether you require a prior approval process.
7. Regular review and adjustment: Continuously monitor the performance of your sales funnel and adjust the conversion rates and sales stages if necessary. Pay attention to deviations between your forecast values and the planned values and initiate countermeasures quickly and decisively if necessary.

These steps will help you build an effective sales reporting system that gives you clear insights and better decisions.

You need this structure and key figures for your sales report

An effective sales report should contain the following key figures and structures to provide a comprehensive overview of your sales activities:

This structure and key figures enable you to analyze the sales performance of your sales organization in detail and support you in making well-founded decisions.

In our experience, it is worth aiming for a weekly update of the sales funnel in order to recognize developments very quickly and be able to react at an early stage. Initially, a monthly reporting cycle usually makes sense in order to give you and your organization the necessary time to update the data and carefully review all report content as long as the processes are not yet well practiced.

How much time should you expect to spend on creating a sales funnel report?

The creation of a sales funnel report usually takes several weeks and can sometimes even take months. This depends heavily on various factors. First of all, the availability of resources in your company plays a major role. If you have a dedicated controlling and CRM team that you give a high priority to this project, the process will go faster. Experience in setting up sales reporting is of course a great advantage.

Another important factor is the quality of the data in your CRM system. The cleaner and more complete the data is, the less time is required for data cleansing and preparation. The complexity of the interfaces to other systems also influences the time required. If your CRM is seamlessly integrated with other important systems, setting up reporting will be less time-consuming.

It is important to take enough time for planning and implementation to ensure that the reporting is accurate and reliable. Patience and careful work pay off here.

Correctly evaluate one-off vs. recurring sales in sales reporting

In our examples so far, we have assumed a single “sales line” for the sake of simplicity. However, many companies offer very different products and services with very different sales characteristics. In this section, we therefore want to show you how you can recognize the sales lines that are relevant to you and take them into account correctly in your sales funnel report.

Non-recurring revenue, such as product or license sales, must be planned and managed differently than recurring revenue, such as subscriptions. One-off sales can still have a significant impact on the financial year even if they are concluded in December. A successful product sale in the last month can have a positive impact on the overall annual result. The individual deal usually has a higher volume than recurring sales, so very careful tracking through the sales stages and definition of conversion rates is advisable.

In the case of recurring sales such as subscriptions or service contracts, on the other hand, the timing of the deals is crucial: a deal in January generates 12 monthly values per year, while a deal in December only generates one monthly value. Timing deviations therefore have a major impact on target achievement. Close tracking, especially of the expected closing dates, is therefore essential for reliable target achievement.

The availability of resources plays a decisive role in services . If capacities are not available until later, revenue recognition is postponed accordingly. For example, a service order in December could only be implemented in the following year, which would also postpone sales to the following year.

In the sales funnel, it is therefore necessary to distinguish between different sales lines and use specific key figures. The relevant key figures include the number of monthly contracts, the average contract term for subscriptions and the time to conversion for services. The differentiation of these sales lines in the sales funnel enables more precise planning and control of sales activities. This is the only way to identify potential bottlenecks and opportunities at an early stage and act accordingly.

What are baselines and how can you compare your sales funnel with previous years?

Another ingredient to make your sales funnel reporting more accurate and meaningful are baselines. A baseline is a reference value that helps you to compare current data with previous periods. For example, the sales funnel of the previous month against which you can compare the sales funnel of the current month.

In particular, this allows you to take seasonal fluctuations throughout the year into account in your sales forecasting. For example, customer activity is often low during the summer vacations, but increases sharply after the vacations until before Christmas. This seasonality can only be recognized by comparing it with the previous year’s figures. This allows you to set realistic target values for the individual months and adjust your sales strategy accordingly. A comparison of September with August of the same year could therefore be less meaningful, whereas a comparison of September with September of the previous year could be very revealing.

One challenge here is that a CRM system usually only shows the current point in time and does not store any historical data. For example, in many CRM systems you cannot see which sales stage a particular opportunity was in at the start of the year and how it has developed since then. It is therefore important for sales funnel reporting to regularly save data at defined points in time, e.g. at the end of the month, quarter and year, and to make it available outside the CRM as a baseline for your sales funnel reporting. These saved data statuses allow you to compare them with your current data and thus help you to track and assess the development of your sales funnel.

Conclusion and outlook: how you can continue

In this article, we have discussed the individual steps for setting up successful sales funnel reporting. You now know the definition and meaning of the sales funnel, the most important KPIs and sales stages, conversion rates and many other “ingredients” for successfully setting up your sales reporting.

To integrate sales funnel reporting into your overall reporting, combine it with the financial report and marketing report. Use automation tools to make the process more efficient and obtain accurate reports and, if necessary, let experienced employees or external consultants support you in setting up and optimizing your sales reporting.

Next steps:

1. Implement the steps and tools discussed.
2. Check and optimize your sales funnel regularly.
3. Integrate sales funnel reporting into your overall strategy.

Start implementing now to optimize your sales processes and sustainably increase sales. Professional sales reporting is the key to well-founded decisions and long-term success.