As shown in the hierarchy below, assume that no members are tagged as Dynamic Calc, and that the Profit per Ounce member (under Ratios in the Measures dimension) is not included in the calculation.

Assume that, before running the goal-seeking calculation script, the data values are:

Product, Market, Budget   Jan
Profit                    12,278.50
   Margin                 30,195.50
      Sales               49,950.00
      COGS                19,755.00
   Total Expenses         17,917.00
      Marketing           3,515.00
      Payroll             14,402.00
      Misc                0
Inventory                 Label Only member
Ratios                    Label Only member
   Margin %               60.45
   Profit %               24.58

Example script:

/* Declare the temporary variables and set their initial values*/

VAR
   Target = 15000,
   AcceptableErrorPercent = .001,
   AcceptableError,
   PriorVar,
   PriorTar,
   PctNewVarChange = .10,
   CurTarDiff,
   Slope,
   Quit = 0,
      DependencyCheck,
      NxtVar;

/*Declare a temporary array variable called Rollback based on the Measures dimension */

ARRAY Rollback [Measures];

/* Fix on the appropriate member combinations and perform the goal-seeking calculation*/

FIX(Budget, Jan, Product, Market)
   LOOP (35, Quit)
      Sales (Rollback = Budget;
      AcceptableError = Target * (AcceptableErrorPercent);
      PriorVar = Sales;
      PriorTar = Profit;
      Sales = Sales + PctNewVarChange * Sales;);
      CALC DIM(Measures);
      Sales (DependencyCheck = PriorVar - PriorTar;
      IF(DependencyCheck <> 0) CurTarDiff = Profit - Target;
         IF(@ABS(CurTarDiff) > @ABS(AcceptableError))
            Slope = (Profit - PriorTar) / (Sales - PriorVar);
            NxtVar = Sales - (CurTarDiff / Slope);
            PctNewVarChange = (NxtVar - Sales) / Sales;
         ELSE
            Quit = 1;
         ENDIF;
      ELSE
         Budget = Rollback;
         Quit = 1;
      ENDIF;);
   ENDLOOP
   CALC DIM(Measures);
ENDFIX

Essbase performs the following calculations:

1. Declares the required temporary variables using the VAR command. Where appropriate, the initial values are set.

2. Declares a one-dimensional array called Rollback to store the Budget values.

   The size of Rollback is based on the number of members in the Measures dimension.

3. Fixes on the Jan -> Budget values for all Product and Market members.

4. Ensures that the commands between LOOP and ENDLOOP are cycled through 35 times for each member combination. If, however, the Quit variable is set to 1, the LOOP is broken and the calculation continues after the ENDLOOP command.

5. Cycles through the member combinations, performing the following calculations:

   1. Places the Budget -> Sales value in the Rollback temporary array variable.

   2. Calculates the acceptable error, by multiplying the Target value (15000) by the AcceptableErrorPercent value (0.001). The result is placed in the AcceptableError variable.

   3. Retains the current Sales value, and places the Sales value for the current member combination in the PriorVar temporary variable.

   4. Retains the current Profit value, and places the Profit value for the current member combination in the PriorTar temporary variable.

   5. Calculates a new Sales value by multiplying the PctNewVarChange value (0.1) by the current Sales value, and adding the current Sales value. The result is placed in Sales.

   6. Calculates and consolidates the Measures dimension.

   7. Subtracts the PriorTar value from the PriorVar value, and places the result in the DependencyCheck temporary variable.

   8. Checks that DependencyCheck is not 0 (zero) (IF).

      • If DependencyCheck is not 0, subtracts the Target value (15000) from the current Profit and places the result in the CurTarDiff temporary variable.

        The IF command checks whether the absolute value (irrespective of the + or – sign) of CurTarDiff is greater than the absolute value of AcceptableError:

        • If greater than AcceptableError, calculates the Slope, NxtVar, and PctNewVarChange temporary variables.

        • If not greater than AcceptableError, breaks the LOOP command by setting the value of Quit to 1. The calculation continues after the ENDLOOP command.

      • If DependencyCheck is 0, places the value in the Rollback array into Budget. Essbase breaks the LOOP command by setting the value of Quit to 1. The calculation continues after the ENDLOOP command.

6. Calculates and consolidates the Measures dimension.

The results for product 100-10:

Product, Market, Budget   Jan
Profit                    15,000.00
   Margin                 32,917.00
      Sales               52,671.50
      COGS                19,755.00
   Total Expenses         17,917.00
      Marketing           3,515.00
      Payroll             14,402.00
      Misc                0
Inventory                 Label Only member
Ratios                    Label Only member
   Margin %               28.47839913
   Profit %               62.49489762