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'nselect: Month-1]. I have also gotten the same result with PREVIOUS(Month)+BookingsBselect: Month-1]+Churn1select: 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?
Page 1 / 1
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.
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:
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:
EDIT 2 - the Churn (Displayed) formula should be as follows: if(Month.'Period type'='Period type'."Actual", Churn, Beginning_ARR*Churn %)
Yes! I didn’t add the initial conditions (based on the Actuals value) so your Edits should handle it well indeed.
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.
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.
