By default, planning models don't support data entry for formulas. But with inverse functions, you can enable data entry for the formula result and then specify one or more ways to reverse the formula, meaning it is possible to add inverse functions to calculate a value that is the result of the formula.
Single Inverse Formula
Let's use a scenario where you want to input values for revenue and cost and have the system calculate the profit. You also want to be able to enter the profit and have the system back-calculate cost.
In the example below, the two possible outcomes are displayed:
- Without an Inverse Formula: Profit is calculated and input is not possible.
You can see the Account dimension with the calculated member Profit that is calculated as [Revenue] – [Cost].
With this formula, you can't enter data for the Profit account since it's the result of a formula.
- With an Inverse Formula: Profit is calculated and input is possible.
You can see the formula [Revenue] – [Cost] | INVERSE ([Cost] := [Revenue] – [Profit]).
With the inverse formula option of Cost = Revenue - Profit, if you enter a value for Profit in a story, then Cost adjusts while Revenue remains constant.
Note
You can also input data for an account that is a percent type. For example, profit %.
Display the Formula Bar
To display the dimension formula:
- Go to … More in the Tools menu.
- Select Formula Bar.
- Highlight the account with the formula.
Multiple Inverse Formulas
You can define multiple inverse formula options in a given formula. When doing so, the order in which they are typed determines the priority.
Inverse formulas require the following characters:
| Character | Description |
|---|---|
| | | A vertical bar (pipe) marks the end of the base formula and the beginning of one or more inverse functions. |
| := | You must add a colon before the equal sign when defining an inverse function. |
In some cases, it may not be possible to change the value for cost. For example, if a cell lock is applied to cost, or values are entered for cost and profit simultaneously. To allow data entry in these cases, you can define a secondary inverse formula that assigns value to revenue:
[Revenue] – [Cost] | INVERSE([Cost] := [Revenue] – [Profit]) OR INVERSE([Revenue] := [Profit] + [Cost])When the first inverse formula can't be applied, the data entry can be carried out using the secondary formula. As a result, the revenue value is adjusted.
Additional Information
Visit the SAP Help Portal for more information: Inverse Formulas