We have a sales pipeline board that we’re using to track our leads and open opportunities. In this board, we track the estimated deal value, the project start date, and the project end date. We’re a consulting firm, so we sell projects that last a certain number of months.
So, for example, a line in our board might be for a single project worth $100,000 that’s projected to last from April 1 to December 31.
My question is: Is there a formula I can use to calculate projected revenue per quarter from a deal? While the total deal value is $100,000, we will bill for time over the course of the project length. For a project that lasts 9 months, we’d be pulling in $11,111.11 per month, or $33,333.33 per quarter. How could I use a formula to do this?
This should be achievable through the Formula Column. You will just need to divide your cost column by the number of months or quarters. The question is how exact the result needs to be vs. how much manual input are you looking for?
You can have the formula calculate the days between your two dates and then convert to Months or Quarters automatically and then run the rest of the calculation from there.
{Cost} = your total cost column name
{End Date}/{Start Date} = names of your date column(s). You can use a Timeline column like you see in my quarters example.
DAYS = built in Monday formula to return # of days between two dates
ROUND = Makes the result a uniform month number. In this case you would also add “/3” in order to convert to Quarters but that may or not be reliable if your have revenue spread over a # of months not divisible by 3.
30.417 = Googles suggested number to convert days into months. Up to you how you want to manage this.
-OR-
You could have another numbers column where you manually input the number of quarters/months you want the revenue spread over. The formula would be much simpler and controllable that way but obviously require an additional manual step to get the result.