Sales Pipeline Template: Google Sheets - Includes Contract Renewal Probabilities

Ok, so this is an update to the previous sales pipeline tracking template I had made and it is geared more toward enterprise level clients with bigger contracts. The new model is a bit different and uses more complex mathematical formulas in order to predict revenues over a 5 year period assuming you have various contract lengths and various % probabilities of a client renewing the contract.




Pricing
*The google sheet link and excel will be sent to you after purchase. Note, the original sales pipeline model will also be included in the purchase at no extra charge.

Most builds you will see involve a lifetime value as the final figure and back into revenues based on that. This is more of a bottom up approach. Also, instead of trying to guess if each contract will renew or not after its term is up, you can instead assign probabilities to each contract.

So, the matrix that was done for this is pretty cool. It runs off an initial pipeline tab where you define the contract name/client, the term in months, the total contract value, and the % of probability the contract has to renew.

Based on that, we can now see a matrix of possible active contracts over time. This uses the rand function in google sheets / excel to accomplish the desired analysis. Basically it checks on each renewal date to see if the contract renews or not, if it does, the revenue keeps going until the next renewal date (based on the contract length not changing). If it fails to renew on a renewal date, the value of the contract goes to $0 for the future months.

This gives some interesting scenario and analysis planning insight that may be hard to otherwise see.

Note, this tool is for a business that has larger clients and not that many total clients. You could apply this to situations where a large client represents a given $ amount per month but then they have a lot of smaller users. As long as you can derive a total $ value of the contract and term that applies to all that revenue, it would work.

In scenarios where you need to adjust the contracts key inputs, it is probably going to become less usable after about 100 clients. You could fit more, but it becomes cumbersome to enter/adjust all those contract terms and %'s over time, but if you need it, it is possible to run this with thousands of clients.

I added a total 5-year contract value and test scenarios so you can play with the assumptions and see what the various total revenue looks like over the 5 years after running the probabilities multiple times as well as changing the %'s for each contract.

A more comprehensive pipeline manager can be found here: http://www.smarthelping.com/2018/09/customer-pipeline-manager-crm-in-google.html