Forum Discussion

royari's avatar
royari
Contributor
3 years ago

Goal Seek function equivalent In onestream

Hi - has anyone tried to replicate excel Goal Seek function in Onestream. We have a bonus model calculation in excel which uses excel goal seek and circular reference with iterations options in Excel...
Finance
  • ChrisLoran's avatar
    ChrisLoran
    3 years ago

    Yes, but goal-seeking is definately something you don't want to put into Member Formula, or inside loops, as it is an intensive operation.

    The example code below is a good example, it uses a bisection algorithm to calculate the Square Root of a number. Basically if you know  fn(x) = 9, then  x=3.    The sqrt function is a good example where internally, it iterates and bisects various initial guesses, until it homes-in on a guess that gets to very very close to the goal.

    The function MyCalculation just returns  x * x, or x-squared.   So it uses a binary slicing algorithm to goal-seek which input number (x) would result in the desired x-squared.

    You can then implement your own formula in the MyCalculation() function, to some other calculation, and the algorithm will try to goal-seek the appropriate source amount to get the desired return amount from MyCalculation().

     

    Imports System
    Imports System.Data
    Imports System.Data.Common
    Imports System.IO
    Imports System.Collections.Generic
    Imports System.Globalization
    Imports System.Linq
    Imports Microsoft.VisualBasic
    Imports System.Windows.Forms
    Imports OneStream.Shared.Common
    Imports OneStream.Shared.Wcf
    Imports OneStream.Shared.Engine
    Imports OneStream.Shared.Database
    Imports OneStream.Stage.Engine
    Imports OneStream.Stage.Database
    Imports OneStream.Finance.Engine
    Imports OneStream.Finance.Database

    Namespace OneStream.BusinessRule.Extender.GoalSeekExample
                Public Class MainClass
                            Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object

                                        Try
                                                    Select Case args.FunctionType                                                           

                                                                Case Is = ExtenderFunctionType.Unknown

                                                                            Dim tol As Double = 1E-6
                                                                            Dim [Mid] As Double = 0
                                                                            Dim goal As Double = 9
                                                                            Dim iter As Integer  = 0
                                                                            Dim lo As Double = 0
                                                                            Dim hi As Double = goal                                                                       

                                                                            Do While iter<100
                                                                                        [mid] = (lo+hi) / 2
                                                                                        Dim delta As Double = MyCalculation([mid]) - goal
                                                                                        If Math.Abs(delta) < tol Then Exit Do
                                                                                        If delta>0 Then hi=[mid] Else lo=[mid]
                                                                                        iter += 1
                                                                            Loop
                                                                            brapi.ErrorLog.LogMessage(si,"Found source amount =" & [mid].XFToStringForFormula)

                                                    End Select
                                                    Return Nothing
                                        Catch ex As Exception
                                                    Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
                                        End Try

                            End Function

     

    '---- This is the function that you are trying to goal-seek the input amount , from an expected result ---                       

                            Private Function MyCalculation(x As Double) As Double
                                        Return x*x
                            End Function

                           

                End Class

    End Namespace

ChrisLoran's avatar
ChrisLoran
Valued Contributor
2 years ago

As royari says, first thing I would do is put this into a Finance BR, but I would add this should be specifically on the Custom Finance Calculate , not standard cube calculate : You don't want this sort of expensive calculation to get run again and again for every time period/every entity, every time you run a consolidation.

Then test it for one period : Launch the Custom Finance BR from a simple dashboard button, or from a Data Mgmt job, so it runs a Custom Finance BR (not standard calculate) , and then enter a Time Specification so it runs for just one time period.

Then go back to the Data Mgmt step, and update the Time Specification to an XFBR rule that returns 120 time periods, starting from the current POV Time or  WF time.
The XFBR would need to generate a string like this:
  T#2022.Months, T#2023.Months,  .... etc...   ,   T#2031.Months
  That would re-execute the Custom Finance Rule for each of the 120 time periods.

If you want to do this for multiple entities, then put that in the Entity filter. But that's going to be a lot of data units being calculated , < Num Entities> * <Num Periods> so will likely take a while to calculate.

For long range plans/forecasts, I would consider if yearly data frequency is sufficient for anything over 2-3 years in advance. Don't forget that in OneStream you can have a scenario that has varying time frequency by year, so next year or two maybe monthly data frequency, then more future years are only annual. This would enormously improve processing time vs calculating 120 periods * <num entities>.  

 

Finally don't forget when working with Custom Finance Rules in this way, to set cell values using the Durable option, and explicitly clear previously calculated cells. Otherwise your calculated amounts will be wiped next time you run a consolidate.

  • LeAnnTDang's avatar
    LeAnnTDang
    New Contributor III
    10 months ago

    Hi Chris,

    Thank you for the sample script. I am still a noob with the OS business rule so I apologize for this silly question. Your example seems to be an Extensibility Rules while your recommendation above is to try out with a Finance Business Rule. When do we use one vs the other? 

    Thank you,

    LeAnn

  • LeAnnTDang's avatar
    LeAnnTDang
    New Contributor III
    10 months ago

    Hi Chris,

    Thanks for the note. Although I couldn’t use the loop feature for our case, I managed to break down the investment goal seek calculation into the following steps:

    1. Recognize the target IRR.
    2. Calculate the NPV of the cash flow stream using the target IRR.
    3. Compute the NPV per unit of the project (In our case, we have a number of units corresponding to the investment spends).
    4. Determine the future value (FV) of the product of NPV per unit and the units at the investment period, using the targeted IRR. (For our situation, we know the spending periods of the initial investments).

    Even without the loop, we still need to set up the DM job so that this calculation runs in multiple iterations to capture changes from dependent calculations.

    I hope this adds clarity for future members searching for similar information.