Total Contract Value Goal Tracking (YTD and Remaining Annual Revenue)

This is a great tool to use for any business that has mid-size contract sizes that last for a given amount of months. Based on 4 simple data inputs, you can see your current YTD billings and your remaining billings. The logic is 100% automated. A dashboard view allows the user to see how they are projected to finish the year based on some annual sales goal.



Pricing
*Note, after purchase you will be sent the view only google sheet link. Once you have that, go to 'File>Make a Copy and you will then have your own editable version of the model.

This is built in google sheets because of its advanced formulas that lend to building the summaries you see in the video.

Let's get down to it. The 4 data points you enter when a new contract is closed are:
  1. Date
  2. Client Name
  3. Total contract value
  4. Term of contract (months)

Based on those entries, you will be able to see:
  • # of months remaining (lifetime)
  • # of months billed (lifetime)
  • monthly billing value
  • remaining billing months for the current year
  • remaining billings value for the current year
  • amount billed YTD
  • amount billed lifetime
  • amount remaining to be billed lifetime
There are also 4 filter views that allow the user to see various contracts based on their remaining months of billing (lifetime), their billings so far, their billings left (lifetime), and their billed months so far based on those data points being more or less than an inputted figure.

Conditional formatting was built to show contracts with 'x' months remaining on their contract will turn yellow and red based on defined variables.

Finally, the 'dashboard' view will allow the user to put in an annual billings goal and based on the revenue billed to date and the revenue expected to be billed for the rest of the year, you can see how close you are to your annual revenue goal. Conditional formatting was also used here (green and red) to show if you have reached the goal yet this year and if you are projected to reach the annual goal based on the remaining billings for the year.

This google sheet financial model is going to work through time no matter how many years you are in the future. All the date logic is made to know the current month and year. Based on that, the data points are able to be known based on the 4 inputs by the user. This was designed to have minimum inputs and simple inputs that are hard to mess up.