Goal Seek is one of the most commonly used spreadsheet features. The Goal Seek feature back calculates the source value or the input value based on a target value. Using Goal Seek, you can adjust a value used in a formula to achieve a specific goal. This is an incredibly powerful tool when it comes to creating what-if scenarios.
A simple example would be mortgage calculation. If you have the loan principal and the duration, you can create multiple what-if scenarios by changing the monthly payment to arrive at a target interest rate.
During the budgeting and forecasting exercises, multiple what-if scenarios are commonly created in organizations across industries. These help organizations to account for volatile factors such as currency exchange rates, oil prices etc.
However, Oracle Hyperion Planning and Essbase do not have a built-in function to perform the Goal Seek analysis. These products let the users create multiple what-if scenarios using the commonly used Scenario and Version dimensions, but do not offer the built-in functionality of back calculating the source values from the target results.
Use Case
A client that we were working with was routinely using the Goal Seek feature. They had been using Excel for their budgeting and forecasting needs. When we graduated them from using spreadsheets to using Hyperion Planning, it was essential that we provided the same Excel-like Goal Seek functionality.
How We Did It
The Hyperion Planning application in question was being used for cost analysis. The client manufactured thousands of parts and the application helped them analyze the various types of pre-production and post-production costs associated with each part. The planner would enter all the direct and indirect costs associated with a part and then Profit % would be calculated by the formula.
Profit % = (Selling Price – Total Costs)/Selling Price * 100
The requirement was to enter a Target Profit percentage for a part and then back calculate the Selling Price accordingly.
We created a business rule and associated it with a right click menu on the cost input form. We also created a new version called ‘Goal Seek’ to copy all the input values, perform the calculation, and copy the resulting Selling Price back to the input point of view. This was important as we did not intend to overwrite any of the user inputs except for the final selling price. The business rule looped through a calculation that kept updating the selling price until the resulting Profit % matched with the Target Profit %. The resulting Selling Price was then copied back to the input POV and the ‘Goal Seek’ version was cleared.
The Step-by-Step Process
Step 1: The user would enter all the costs and a starting value for the selling price on a data form. The user would also enter the target profit percentage in a separate account.
Step 2: The user would then right click and select a menu item ‘Goal Seek’. The Goal Seek menu item was associated with a business rule. Selecting the menu item would prompt the user for two inputs.
i) An acceptable percentage of error and
ii) A factor to increase or decrease the selling price by called PctChange
The reason these inputs were needed from the user is that the parts were vastly different in costs and prices. A fixed value such as $10 as a unit of change would not have worked in all the cases. For example, if a part had a selling price of $3.5 and another part had a selling price of $495 then a fixed value of $10 as a unit of increment or reduction would not have served well. For the $3.5 part, the calculation would have never arrived at the right result and for the $495 part, the calculation would have taken far too many iterations. By letting the users choose the change factor, we could use the same piece of calculation for all the parts.
The acceptable error percentage provided a range from [Target Profit % – Acceptable Error %] to [Target Profit % + Acceptable Error %]. This helped minimize the number of iterations needed to arrive at a satisfactory value since the calculated profit % would not exactly match with the Target Profit %.
Step 3: Upon entering the above mentioned values, a business rule would run. The rule had three steps.
Step 3.1: Copy the costs and the starting selling price from the input version to the ‘Goal Seek’ version, mentioned above.
Step 3.2: Loop through the conditional calculation of checking if the Calculated Profit Percentage at the Goal Seek version is within the acceptable range of the Target Profit %. The calculation would continue to update the Selling Price using the PctChange as the change factor until the Calculated Profit % would fall between the acceptable Target Profit % range.
LOOP(50,Quit)
“Target Profit %”(
IF(“Target Profit %” < ( “Calculated Profit %” – AcceptableErrorPercent))
“Selling Priece = “Selling Price” – (“Selling Price” * PctChange);
ElseIF(“Target Profit %” > (“Calculated Profit %” + AcceptableErrorPercent))
“Selling Price” = “Selling Price” + (“Selling Price” * PctChange);
Else
Quit = 1;
ENDIF;)
ENDLOOP
Step 3.3: Copy the final Selling Price back to the input POV and clear the data at the Goal Seek version.
The Results
The execution of the business rule through a menu item provided the users with an Excel-like experience of choosing the Goal Seek option from the What-If analysis box and calculating the results. The users in this case had to enter a couple of more values, but that allowed us to use the same piece of calculation for thousands of parts with very different costs and prices.
