Solved

How to Avoid Circular References in Rollforwards

  • 7 October 2022
  • 5 replies
  • 234 views
J
Rank
Userlevel 2
Badge +4

I am trying to create something in Pigment similar to the simple excel model shown below.  The key piece I cannot currently achieve is making the Churn amount be calculated as: Beginning ARR * a percentage driver.

Same as above with formulas shown.  Formula works as it is cell specific

When trying to do the equivalent in Pigment, I get a circular error.

Dec 21 Ending ARR is hardcoded here for the sake of the example

For the sake of the example, I’m assuming Jan is actuals and Feb is forecast.  For reference, my Pigment formulas are as follows:

  • Beginning ARR: 'Ending ARR'[select: Month-1].  I have also gotten the same result with PREVIOUS(Month)+Bookings[select: Month-1]+Churn[select: Month-1]
  • Bookings: no formulas - direct entry
  • Churn: attempted formula was Beginning ARR * Churn_Percent, but resulted in circular error
  • Ending ARR: previous(Month)+Bookings+Churn
  • Churn_Percent: Churn/'Beginning ARR'.  I allowed overrides on this metric to be used as a driver in forecast months

Is there any workaround for this?

 

 

icon

Best answer by francois 10 October 2022, 11:40

View original

5 replies

francois
Rank
Userlevel 6
Badge +14

Hi Justin,

Thanks for pointing this out, there is indeed a workaround.
 

Here’s a small re-creation of the sheet you’ve shared. Cells in yellow are inputs, cells in blue are calculated.

If you look at C7’s formula, you’ll see that it takes its values from C3 (actually B7), but also C5 (also taking values from B7).

 

As a rule of thumb, when dealing with potential circular references, I try to avoid cross-referencing several lines, and start with making the “simplest” excel I can come up with, following this rule: try to only reference cells above, or above and left. In this case, the C3 cell does reference something below, let’s see if we can manage to build something that does follow this rule.

Here’s what I’ve build (accessible here):

As you can see, although the lines don’t make a lot of sense for display (you can re-arrange them later), we have managed to set everything so that we only take the above of left values. From there, here are the formulas:

Bookings, Churn, Churn % : input

Ending ARR : PREVIOUS(Month) + Bookings + PREVIOUS(Month) * 'Churn %'

Beginning ARR : 'Ending ARR'[SELECT: Month - 1]

Churn (displayed) : IF(Month.'Period Type' = 'Period Type'."Actual", Churn, 'Beginning ARR' - 'Ending ARR')

Churn % (displayed) : 'Churn (displayed)' / 'Beginning ARR'

 

I hope this solves your problem!

François (Pigment)
J
Rank
Userlevel 2
Badge +4

Thank you Francois for the helpful response.  This seems to work except for the first month of ending ARR, cell B7 in this case.  The formula evaluates to 10 rather than 105, since the previous month is 0.  Is there any solution for this?

EDIT - I believe the Ending ARR calc would need an additional piece of logic as follows:

if(Month.'Period type'='Period type'."Actual",
PREVIOUS(Month)+Bookings+Churn,
(PREVIOUS(Month)+Bookings)+(PREVIOUS(Month)*Churn %))

 

EDIT 2 - the Churn (Displayed) formula should be as follows:                                                       
if(Month.'Period type'='Period type'."Actual",
Churn,
Beginning_ARR*Churn %)

 

  

francois
Rank
Userlevel 6
Badge +14

Yes! I didn’t add the initial conditions (based on the Actuals value) so your Edits should handle it well indeed.

François (Pigment)
J
Rank
Userlevel 2
Badge +4

Thanks Francois.  To confirm, what is your recommendation for the first month of Ending ARR (cell B7 in your example)?  As noted, the suggested formula evaluates to 10 rather than the correct value of 105.   

francois
Rank
Userlevel 6
Badge +14

It really depends on what you have before. The correct answer will be as you’ve suggested, so differentiating between Actuals and Forecast.

You can either go with a metric Override to input the correct data at one point in time, use another Actuals metric if you have one, or use the formula you’ve suggested if you can go up to the start of your business.

To my understanding it’s quite common to just input the last-known value as a metric override if you’re mainly looking at a forecast / you don’t have Bookings / Churn from previous months / years.

François (Pigment)

Reply


Ask your question here!

Terms & ConditionsCookie settings