How I Built It: Real-time goal-seek in Anaplan
About the Author: Tristan Colgate is a Certified Master Anaplanner and Managing Director at Fidenda.
Hello, Anaplan Community!
One modeling challenge that we sometimes come across — especially when replacing a spreadsheet-based solution — is how to automatically goal-seek to calculate a result, when the calculation is circular. In Excel we have the goal-seek function, but this is clunky and manual. Our goal in Anaplan is to make the experience as real-time, automated, and easy to use as possible.
In this ‘How I Built It’ tutorial, I show you a technique I have used to automate the calculation of Residual Land Value. This is a concept from the real estate industry and is a calculation that determines the price that should be paid for a piece of land to achieve a certain profit margin. It’s circular because we calculate interest on a loan to fund the project, which impacts the profitability.
I would be really interested to hear from anyone who has other examples of where this technique can be used.
Questions? Leave a comment!
P.S. If you missed my first ‘How I Built It’ on solving the world’s hardest Sodoku puzzle, check it out here!
All ‘How I Built It’ tutorials can be found here.
Comments
-
Lovely stuff. Guess if you had a dimension attached to this then have a select time all periods would be the better option rather than doing timesum?
1 -
@tristan.colgate Thanks for sharing, Without a doubt, this solution looks absolutely great.
At present, we are using import actions to goal seek, having multiple actions(20+) in a process to iterate the right value. Can't wait to try this method!
1 -
Excellent way of hacking the time dimension! I will try this out soon
1 -
Thank you for contributing to the community again Tristan!
0 -
Great stuff Tristan !!! Kindly share the idea of workaround where the underlying module also has a time dimension. I came across these circular calculation frequently in Supply Chain use cases.
0 -
@mukulsharma because the time dimension is required for the "iterative" calculation, I believe the only way to get that to work on data already on the time dimension is to convert the data to a "fake time" list - you can see an example of that here:
That way the calculation can occur in the native time dimension. But I'd love to hear if anybody else knows of a different method!
0 -
Hello Tristan, nice to see here your posts!
Thats a great way to workaround some limitations. And with time it is really easy to refer to the next or previous iteration. Time dimension also could be used for a number of workarounds like compound % calculations impact,.Another example if there is a dependency between your list items - you can map them to the time dimension and make them work toghether.
Good point about Balance sheet limitation - it was first think come into my mind.
Please share more things and would be happy to catch up with you!
0
