You may need to use a formula for an account in your analysis, but only for a specific scenario you're working with. You can do this by creating scenario-based formulas.
This feature is helpful if you want to Forecast using a formula but still want Actuals data coming from a general ledger. For example:
- Actuals and Budget scenarios:
Gross Margin = Revenue - COGS - Forecast scenario:
Gross Margin = MONTHS_AGO(Revenue, 12) * 1.5 - MONTHS_AGO(COGS, 12) * 1.3
Create a scenario-based formula
The steps to create a scenario-based formula are nearly the same as adding any other formula to Cube, except that you'll select a Scenario as a parent and apply this formula to a specific scenario.
1. Log into the Web portal and click Formulas in the navigation.
2. Click on the New button.
3. In the window that opens, name the Formula and Select the Parent Scenario from the dropdown menu
4. Write your formula using quotation marks around the names of Cube dimensions or select the dimension name from the suggestions. See a list of common formulas and supported operations.
5. In the Apply To dropdown menu, select the scenario you'd like this formula to be associated with.
Note: Formulas cannot be applied to the Actuals Scenario, as it has write protection enabled.
6. Click Save Formula to save your changes, and you'll see your formula appear in the list on the Formulas page.
Need further support writing formulas? See this article.