Hi - we have a calculated variable ‘Agreements Won’, which is the value of a new agreement (contract) won in a period. And we have an input variable ‘Ave. Project Length’, for the typical project length in months from the agreement won date.
How can we model a ‘Revenue’ variable to spread the ‘Agreement Won’ value across periods? For example if ‘Agreement Won’ in March was £10,000, and the ‘Ave. Project Length’ was 4, ‘Revenue’ would show £2500 for March, April, May, June.
I am sure the spread function can do this, but I’m struggling to apply the guidance to this use case.
Hi @joemillson - this is a great use for Causal. It combines a few different core concepts which are outlined below.
I have also created a clonable demo model for you that has variables broken down by category for “Win Date”, “Agreements Won”, and “Contract Length”. The “Revenue” variable combines these to create your forecast.
-
Categories: each contract will be a category item so that you only need to write the formula once for all of the categories
-
Formulas: you’ll use an if statement to say that as long as the date in the model if equal to or greater than the “date won” and less than the contract length, you’ll recognize revenue in that month
-
Time (and time in formulas): you’ll notice that the second part of the if statement in the “Revenue” variable contains
"Date Won" + "Contract Length". Time is a fundamental component in your model, so each timeStep is assigned an integer value.
Let us know if you have any other questions!
1 Like
Thanks for this Tim, do you think there’s a way of doing it without having to explicitly model single contracts within a category? We may have hundreds so not sure it would work for us.
Kind regards,
Joe
Joe Millson
Director
Millson Group
Book a call
Call Direct: 0161 513 1507
Call Mobile: 07540 399 904
www.millsongroup.co.uk
This email and any attachments are confidential and intended only for the recipient. Views or opinions are those of the author. Millson Group Ltd. Head office: Parsonage Chambers, 3 Parsonage, Manchester, M3 2HW, United Kingdom.
@joemillson
Tim may have other thoughts, but I posted a similar question a few weeks ago.
I’m modeling 1200 individual contracts to show daily revenue recognition. The performance has been incredible - it took some time to set up but I have a level of visibility into my business I could never achieve with a spreadsheet or other modeling tools.
If feasible, I’d suggest giving it a try. You can always aggregate up downstream in Causal.
1 Like
Thanks @Chris_Williams! Here’s the link the the thread you referenced.
Here is another public template that you could use, following @Sanjeev_Sandhar’s directions in the thread.