Streamline Education Pipeline Tracking Spreadsheet with airSlate SignNow

How to create outlook signature

welcome my name is mark umber and in this video we're going to analyze a sales pipeline in excel to retrieve revenue growth assumptions for your financial planning to make this a bit more interesting here's a little story it is september 1st 2020 and you're about to start your due diligence on a b2b software company that provides hr solutions to large enterprises last week you took a closer look at the pitch deck and you knew that this company would fit very well to your funds investment strategy the company has been rapidly growing and reached an mrr of 1 million euros in december 2019. you asked the management to send a detailed excel spreadsheet containing all leads and opportunities in their sales pipeline and you were very specific about the format to make sure you have all the information you need the email containing the file just came in and now you're eager to see if it would support the revenue forecasts that were shown in the pitch deck the pipeline goes back to january 1st 2019 showing all stages and leads including those lost the company sells annual licenses that will be paid on a monthly basis the company assumes an average customer lifetime of five years and the company's financial plan forecasts a revenue increase by 11.2 million euros for 2020 and another increase by 25.1 million euros in 2021. you know that in the b2b enterprise software industry sales cycle are typically very long and therefore it is pivotal that a substantial fraction of future revenues have to be visible in the sales pipeline today now the central question in your sales due diligence is are the forecasted revenue supported by the sales pipeline to answer this question you need to look at several aspects first are the assumptions with regards to probabilities and time span consistent with the company's past is the sales funnel consistent which means is there stability or improvement across cohorts along the timeline and finally once all of the above have been checked or corrected what is your best guess on the revenues for 2020 and 2021 given the sales pipeline this exercise is designed in such way that you can model everything that we do in this video at home so i encourage you to go on our website download the excel spreadsheet so you have the best learning experience every row shows you the details to one potential customer let's take a closer look at one example in row twilio will make a monthly recurring revenue of 60 000 euros given a five year customer lifetime this translates into a total contract of 3.6 million now this value is an estimate twilio became a qualified lead on january 8 2019. this was the day where twilio contacted the company after some marketing activities and twilio said you offer an interesting product let us learn more about what your company has to offer is when basically the sales process starts it is very important to understand how leads evolve through the sales process and move along stages qualified stages until they're either lost leads or one customer so the goal of sales processes is always to win the customer over and this takes time sales process is typically divided into several stages or phases now on january 8 2019 twilio became a lead this is the initial indication that will you might be interested in the products after that the sales personnel contact twilio and try to qualify the needs and whether these needs can be met by the product the company offers on may 16th the company actually sent out a proposal to further engage with twilio in becoming a future customer on july 25th 2019 details have been all the major details have been dealt with and the official negotiations of the contract started on october 5th 2019 twilio communicated that it would be interested in buying the product this is a verbal or even an email indication that they will be purchasing the product the actual day where the contract was signed and the purchase order came in was december 7th in 2019 and the actual contract started on january 9th 2020 which was the day the first invoice was sent to twilio as you move down the list you see various leads become opportunities we call early stage potential customers in the sales process leads and later stage potential customers in the sales process and opportunity and you see as you move down the list that there have been lots of companies contacted few of them made it far and it's very important to analyze this time structure behind individual needs now twilio was a lead in january 8 2019 let's jump to a more recent picture here you see leads that came in in january 2020 and the pipeline clearly indicates in what sales phase different leads currently are for instance cebu holdings is currently in negotiation pakar gave the verbal approval subaru is currently in negotiation and schindler holding just received a proposal if we put this in categories this is what we find in column l here we see the category of stage every individual lead is in as of today in the sales pipeline this column l will become very handy because it gives us a snapshot where all leads are currently in what we will call our sales funnel now keep in mind this is the current status and if we knew with what probability certain leads will become future customers we can use statistics to get our best estimate on the total contract value or the total monthly recurring revenue that is currently in the sales pipeline to do so we need probabilities or how we will call them likelihoods now if you download a standard sales software tool nowadays every single stage comes with default probabilities here's the first pitfall that young founders in nascent companies fall in it's in absence of any better judgment they use the standard likelihoods that come with the software up here we have a little lookup table a table that has likelihoods for every single sales phase with a little vlookup we can simply attach a probability to each and every category this is what we find in column m for starters we will take these default likelihoods now we can calculate probability weighted values such as the total contract value this is what we did in column p column p takes the likelihood and multiplies that with the total contract value this is what column p does now we get our first glimpse on the total value that is in the sales pipeline because now we can calculate first of all a total contract value by looking into the future and we can calculate a weighted a likelihood weighted total contract value for all deals that are still to come for this we look at column j in column j we have the actual or the estimated start of the contract so for every one deal we know the true date of the start of the contract for every other deal we have an estimate of when this contract is about to start this estimate will be more precise for deals that are already in later stages of the sales process and it will be more inaccurate for leads that are still in a qualified lead stage let's move to today these are the opportunities for the near future and for those we want to calculate the likelihood weighted total contract value to see what's still in the pipeline meaning value contract value that is about to come in the near future for this we go in cell b5 and we already have this formula provided by the management this is an array formula we take the sum of the weighted total contract value this is p14 to p489 under two conditions the first condition is that the sales phase must not be a lost deal a lost deal translates into a likelihood of zero percent the second condition is that the deal is yet to come meaning that the estimated start of the contract is after today when closing an array formula you have to press ctrl shift and enter once you've done this excel will add little curly braces to the beginning and the end of your formula if you simply hit enter excel will try to interpret this formula in a different way and now comes some value this can be an error this can be the last known value as it as as it is here and it can be very misleading so make sure make 100 sure that you press ctrl shift and enter otherwise the formula will not work okay now back to the economics the weighted total contract value of this pipeline later than august 31st 2020 has a total value of 137 million now keep in mind that this was based on a customer lifetime of five years so if we want to convert this into an annual value we are talking about 27 million euros which seems to be a very large number but we are still not there yet so we need to first analyze whether this sales pipeline is consistent as a first step we will add an array to the right side of this table that will report monthly revenues along the timeline for every lead and opportunity which is weighted at its respective likelihood to make it a bit easier for you to follow i'm going to hide a couple of columns for the next step here we will start with our array to the right side of the tape reporting this area of mrrs we're gonna add one month do this till the end of let's do end of year 2023 that's enough the main interest is on 2020 and 21 so this should just be enough now we need a formula that will report an mrr for those months where a contract is actually in place this can be an actual contract such as in the past or an estimated contract if we forecast the future as a formula we need a little if class with two conditions first of all the month has to be larger or equal to the start of the contract and this month has to be smaller than the end of the contract if that is true the formula shall report the mrr multiplied by the likelihood if it's wrong we want to have an empty value okay before we use the auto fill we need to make sure that some rows and columns are fixed to do so i use the f4 key with a date on top of this array we want this state to remain in row 13 same here the start of the contract should be in always in column j the end of the contract should always be in column k the mrr shall always be in column b and the likelihood is always in column m this should be it now we can use the autofill to fill out all formulas to the right and can use the autofill to fill out the entire array let's see this looks very good excellent this is an important step which brings us further to our goal to have aggregated mrrs ing to different sales stages i'm gonna unhide some of these columns from the next step so let's put this down using a little concatenate little formula here to get towards okay so this is basically what we're look what we're after and again we need this timeline and now we can create an array formula to have the sum of mrrs ing to a month filtering on certain sales stages so i'll start with the sum and the condition we need one condition meaning the likelihood has to be equal to the likelihood of the respective stage if that is true we need the formula to take the sum over this array all values that do not meet this condition shall be empty before we can use the autofill again we need to make sure some of the columns and rows are fixed i press the f4 button this is 14 to row 889 this is entirely fixed and this needs to be in column m see if this works out very good always try and test those formulas where you use the autofill and don't forget once you edit the cell you need to close it again by pressing ctrl shift and enter we are also interested in the total mrr as the sum over this respective month make this a bold number and now we can generate our first chart i'm gonna plot the data from january 2019 till december 2021 as this is the time period that we are mostly interested in okay i'll have a stacked area this is what comes out of it we need to make some adjustments before let's take this up front first and let's make this a little bit nicer now first of all change some colors i'm gonna add a title let's increase the figures a bit that's also cosmetics and one very important thing if we look at the charts we have a stacked area where the mrr that has been one is on top we want to reverse the order because this way the chart makes little sense i want to chart whether one mr is on the bottom and then in decreasing order all other sales stages should stack up to do so i go and select data and i can rearrange the items verbal approval is here okay that looks much better now we have a stack chart where we have in decreasing order the highest probability lower lower lower lowest probability on top looking at this chart two things are very important to notice first of all we have a very smooth ramp up this is what you want to see in a sales pipeline you don't want spikes you don't want troughs you don't want the relative sizes to deviate much so this would be a good sales pipeline where every sales stage is filled with volume ing to to contracts in future revenue let's take this chart to an output sheet so we have all the relevant outputs on one sheet as a next step we want to add some sales pipeline statistics and charts that are very typical meaning the number of leads and opportunities ing to different stages and the weighted total contract value i'm going to add this piece of information here number of leads weighted weighted contract value let's take this okay to count the number of leads ing to different sales stages we can make use of an every formula that is very straightforward we want to count if the likelihood is equal to a certain stage that is true count the individual observations and if that is not true replace these values with it with an empty value it's an area formula so press ctrl shift and enter and this gives me 162 leads before we can use the auto fill we need to make sure that columns and rows are fixed so we can use the autofill very good let's test this is true very good the areas didn't move that is exactly what we need okay similar with the weighted total contract value i'm gonna use the autofill here but i'm i have to change the formula because now we are interested in the sum it's the same logic instead of count we need to take the sum let's make this a more comprehensible number very good this is the typical sales pipeline statistics and keep in mind that this is a forward-looking statistic why is that so it is forward-looking because we take the likelihood of every single lead and opportunity and this likelihood is as of today either a lost opportunity a one opportunity a sales qualified lead and so on so this type of statistics is forward-looking since this sales prime line is similar to a funnel we're gonna take a funnel chart to show this piece of information um number of leads do the same with total contract value take the funnel crease the this way very good very nice let's take these two charts and put them in our output sheet let's have a look at these illustrations the number of leads shows you a funnel that is very thin this is a good funnel you want a sales team that is highly effective in sorting out those leads they will only waste your time looking at the weighted total contract value you want to have a funnel that has sufficient volume in every sales phase this is a good sign what you don't want is certain stages to stick out you want a smooth funnel-like structure you want to have significant volume in each and every stage and this is what we have here so that is a good sign so far let's go back we are not done yet given the history back until the 1st of january 2019 we are interested in a couple of statistics we do want to know more about the number of leads the empirical likelihoods and the empirical conversion rates and we do want to analyze this for the different stages see here we go so the sales funnel statistics the first statistic that we're after is the number of leads ing to sales stage such as being a sales qualified lead we want to count how many leads are in there to see how many were converted afterwards into into a customer now we can simply take the count function and count all the leads this would be very misleading why is that so we need to make sure that we don't introduce any survivorship buyers we need to give this sales qualified lead a chance to become a paying customer so we need to cut off in such way that an actual or an estimated start of the contract would be possible so we construct an array formula but with a couple of conditions and this condition has to be the actual or the estimated start of the contract needs to be earlier or equal to today if that is the case count all dates in the sales qualified lead phase else fill in with empty values again this is an area formula ctrl shift enter let's see this looks much more promising before we use the autofill we need to fix a couple of columns and rows and here we do only want to make sure that the rows are constant but the columns should move as we use the autofill let's try out very good having these opportunities and leads we can now calculate the empirical likelihoods very simple out of 381 sales qualified leads only 22 became an actual customer as a percentage this is 5.8 percent this is structure of empirical likelihoods and we can directly see that they deviate from what we have used so far so far we use the default given by sales software tool but now based on our sales history of this company we have more precise likelihoods now finally what is always very interesting to see is how many leads and opportunities are converted from one stage to another so we see that 37 of sales qualified leads become a technical win this is a fairly high number looking at the sales funnel you want to see that the conversion rates become higher values towards the end of the sales process you want to have a big filter in the beginning and this filter should decrease now that we know our empirical likelihoods we can override the default values a win is a win and a hundred percent changing this lookup table the way we set up the entire spreadsheet will already have a big impact on the charts we do see that the overall volume here reduced dramatically not the number of leads but the volume because the value or the likelihood weighted contract value changes when the likelihood changes also the entire sales pipeline monthly revenue chart decreased because the probabilities are lower than initially and now is the time where we can finally answer the question if we go back to the initial task we wanted to see whether the management's forecasts are plausible now we can see how much revenue is in the pipeline for 2020 and we can see how much revenue is in the pipeline for 2021. how so this is simply the sum of and i'm going over to take january to december the sum is an estimate of 9 million and we'll do the same january 21 to december 21 is 27 million of additional revenue now we need to keep in mind that the company already had an mrr the end of 2019 of 1 million now we can go back to the initial question regarding the revenue forecast of the management the pitch deck that was sent by the management reported a forecast of revenues of 23.2 million in 2020 and 47.3 in 2021 now we can calculate our own estimates based on the insights we just created from the sales pipeline we know that the sales pipeline is able to create 9 million 9.2 million of additional revenues in 2020 but we also had the company also had a level of revenues knowing that we had 1 million in monthly recurring revenue in december 2019 so we're going to take an approach of an annualized figure this is our best guess for the revenues in 2020 given the sales pipeline and the same we do for the revenue in 2021 39.4 so what we do see is there is quite some gap and this gap of almost 8 million is substantial so as an outcome of your due diligence you would sit down with the management and talk about the financial forecasts i hope you enjoyed the video and feel free to send me your comments thank you