Goal Seek

peter_mcanena
peter_mcanena
in Modeling

Given a complex function y = f(x), you sometimes need an inverse function that finds the value of x required to produce a given value of y. In Excel this can be done using the Goal Seek feature, but that's not available in Anaplan. You can do this in Anaplan by using the Time dimension as the basis of a recursive calculation. This allows you to keep track of minimum and maximum values for x, narrowing them successively in each period until you find a value that produces your desired y. The attached blueprints contain an implementation of this in two modules. Use the Goal Seek Settings module to set your target y value, your tolerance (i.e. the required accuracy of your result) and your initial minimum and maximum search values for x. The Goal Seek module will then solve for x and put the result in Solution. Whatever function you want to solve, you should put that in Goal Seek.Result of Current Approximation. This example solves a simple y = x² + x³ function but your function can be whatever you like, with as many inputs as you like (as long as only one of them is the x that you're trying to solve). If your function has an inverse relationship with its input (i.e. if y increases as x decreases), then you need to tick Goal Seek Settings.Inverse Relationship? to indicate this. You can change the dimensionality of these modules to whatever your particular problem requires, so that the function is solved for multiple values at once. You can't use time, though, because the time dimension is required for the recursion. If you need to solve for multiple periods, then you'll have to map your inputs onto a custom "time" dimension before feeding into Goal Seek, then map them back onto Anaplan's native time dimension when they come out the end. Some potential improvements would be to automate the calculation of Inverse Relationship, and to optimise the approximation by using a Regula Falsi method (my implementation is a simple bisection method).

Tagged:

Answers

Categories