How to Triage your Q2 Forecast - Forecast Template and Guide
TL;DR: Forecasting and the underlying opportunity management process is an essential part of sales leadership, but most of the time it is purely subjective and inconsistent between leaders. In the current market where deals are scarce, CFOs are extremely hesitant to spend, and when your company desperately needs the revenue, there is an increased need to do this well. The good news is there are some simple tactics that can help you get started quickly to triage your team’s current pipeline and hopefully move the needle this quarter and even more so in H2.
Starting with the end in mind, here is the desired output for you to analyze the team’s current pipeline. Directions at the bottom of the blog to set up your document. What can we learn from the breakdown below?
As you review your file, red is bad and the more red the worse it is, but there is a hierarchy of importance I have outlined below you should think about when reviewing your data.
-
Opportunity Age - rarely do old deals come back and win especially in tough markets like this so tread carefully to confirm it’s even real. One good validation to see if we are living on hopes and prayers, is seeing multiple pushes in close dates.
-
Time in Stage - deals are like cheese, the longer they sit, the more they start to stink. Review the last action taken (if possible review the call) and next steps to gather if this is actually dead but the rep doesn’t want to accept. Use executive outreach to confirm.
-
% of Pipeline - deal value plays a key role here as losing a larger deal has a huge impact on your ability to make up the difference. Focus on deals with larger values/ones in red that represent 5% or more of your pipeline, since deal denial for reps is often greater in larger deals. Pipeline coverage needs to be both revenue in dollars but also volume - if you lose a larger deal you might need two small deals to make up the difference so pay close attention to this to mitigate risk.
-
Amount - the deal amount is a key element to opportunity management. Reps are often unrealistic or aspiration when valuing deals. The red deals here are ones that represent a variance greater than 33% higher than your average win amount, meaning as a company you have limited success and experience winning at this rate, making it a key risk to mitigate. Speak to the rep about why this amount and confirm with Gong or call recording the prospect themselves has validated budget or agreement to this price point. Also, critical to check where they are in the process and if not through legal and financial/procurement review might be prudent to reduce the value down to your average value.
-
Close Date - usually a more data discipline issue, but sometimes can be indicative of what the rep actually knows about the close process. Any date in the past is obviously a red flag, but drill deeper into the close plan or lack thereof and validate what are the remaining steps. When are they scheduled and with which stakeholders to validate a prudent plan of action.
Please note we are not taking into consideration the many nuances in the market or for that matter comprehensive in the areas to review and consider. It should provide some directional guidance that you can act on and review that should yield substantial improvements in action plans, forecast accuracy and your ability to stack rank your time and attention that will be mapped to real world outcomes.
Step-By-Step Forecasting How To
Follow the steps to improve your deal reviews and improve forecast accuracy:
Step 1:
Export your current Q2 Pipeline with the following columns of data, cut and paste into Google Sheets.
Step 2:
Go into the “View” option at the top of the page, click on it, select freeze, select “1 Row” and release. This will freeze the column header for review of the data.
Step 3:
Right click on the top of the “Amount” column and select “Sort Z to A” this will rank your pipeline by value with the largest value at the top.
Step 4:
Right click on the “Next Steps” column and select “Insert Column Left”. Then title it “% of Pipeline”. We will be using this shortly for a calculator to show how important this deal is to your quarter.
Step 5:
Go to the bottom of the “Amount” column after the last row of data and enter the following “=sum(“, then drag your mouse from the first value of the column to the last value and release. It should summarize the total value of your pipeline, so in the cell should be a number. If you click back into the cell it should look like this “=sum(F2:F21)”. If the values in this column are not dollar amounts, select the whole column and then click the “$” in the top toolbar to change. Then in the cell next to this, which should be at the bottom of the “Time in Stage” column, write in “Total Pipeline”.
Step 6:
Make a note of the field value that has the sum it should be F##, displayed like F22. Write this down. Then click on the first empty cell in “% of Pipeline” Column. Then enter this “=(then click on amount field to left)/$F$22, then hit enter. So it should look like this when done 0.011. When you click on the cell, it should look like this “=F2/$F$22”. The “$” before the letter and number you wrote down means it stays constant when we do the next step.
Step 7:
Now select the cell again you just used in the “% of pipeline” column and click on the % sign in the toolbar, it should change 0.011 to 1.1%. When this is done, select the cell again and drag all the way to the last row of data in the “% of pipeline” column. This now shows you the % contribution that each deal makes in your pipeline.
Step 8:
Select the whole column (minus the heading in row 1), then click on the “Format” tab and select “Conditional Formatting”. This will open a toolbar to the right. In the “Format Rules” select the dropdown and select greater than. Then enter .05 in the box below, then change the color shown there to red, then click “Done”.
Step 9: We are now going to do this same process for the “Close date” column. Select the whole column (minus the row 1 heading), then click the “Format” tab and select “Conditional Formatting”. This will open a toolbar to the right. In the “Format Rules” select the dropdown that says “Date is after”. In the box below select “today”. Let the color stay green. Now click “Done”. Now click “Add Another Rule”, click in the dropdown and select “Date is before”. In the box below select “today date”. Change the color to red and click Done.
Step 10: We are now going to do this process for the “Opportunity Age” column. Select the whole column (minus the row 1 heading), then click the “Format” tab and select “Conditional Formatting”. This will open a toolbar to the right. In the “Format Rules” select the dropdown that says “less than” and enter your average win sales cycle value minus 10. In this example, we have a 90 day win rate, so I will enter 80. If yours is 60 enter 50 etc., then click Done. Now click “Add Another Rule”, click in the dropdown and select “is between” then enter the value you just entered, for me it is 80 and then add 30 to that and enter that value, for me it is 110. Change the color to yellow and click Done. Now click “Add Another Rule”, click in the dropdown and select “Greater than” and enter the last value, for me it is 110, change the color to red and click Done.
Step 11: We are now going to do this process for the “Time in Stage” column. Select the whole column (minus the row 1 heading), then click the “Format” tab and select “Conditional Formatting”. This will open a toolbar to the right. In the “Format Rules” select the dropdown that says less than and enter the answer to this (your average win sales cycle/# of opp stages). So if your average win cycle is 90 days and you have 5 opportunity stages that would be 90/5 = 18, enter this value and click Done. Now click “Add Another Rule”, click in the dropdown and select “is between” then enter the value you just entered, for me it is 18 and then add 10 to that and enter that value, for me it is 28. Change the color to yellow and click Done. Now click “Add Another Rule”, click in the dropdown and select “Greater than” and enter the last value, for me it is 28, change the color to red and click Done.
Step 12: We are now going to do this process for the “Amount” column. Select the whole column (minus the row 1 heading), then click the “Format” tab and select “Conditional Formatting”. This will open a toolbar to the right. In the “Format Rules” select the dropdown that says less than and enter your average win ACV, for me it is $75,000, enter this value and click Done. Now click “Add Another Rule”, click in the dropdown and select “is between” then enter the value you just entered, for me it is $75,000 and then add 33% more to that and enter that value, for me it is $99,750, so I rounded to $100,000. Change the color to yellow and click Done. Now click “Add Another Rule”, click in the dropdown and select “Greater than” and enter the last value, for me it is $100,000, change the color to red and click Done.
