1. How-to: Change names or codes for multiple accounts
You can change the names and settings of multiple accounts by performing an "Update" import on the Modeling > Accounts page for your specific account type.
Accounts are matched based on their code which is why codes cannot be changed during an import. It is possible to change account codes by utilizing the updateAccounts API method in conjunction with the exportAccounts API method. The exportAccounts API method can be used to retrieve the internal IDs for the desired accounts. This information can then be referenced in an updateAccounts call and used to update Account codes since the accounts are now being matched by internal ID.
You can also use Adaptive Integration to perform this task since Adaptive Integration uses the API. Our article on How to Use Integration to update Account Codes and/or Names covers this process.
2. How-to: Annualize data in a formula
You can create an assumption account to represent month (or another time period defined in your calendar) for ratios that require annualizing the numerator. Then, you can create a formula to calculate the projected amount.
Example: To get Projected Annual Income (i.e.,
ACCT.Proj_Annual_Inc) for the month of May, you need to take Net Income (i.e,
ACCT.Net_Inc) for January through May, divide by 5, and then multiply by 12.
- Create an assumption to represent the month (i.e.,
- Populate each month of the ASSUM.Month account with a value from 1-12 based on your calendar or fiscal year
- Values for CY-2020: Jan = 1, Feb = 2, Mar = 3, Apr = 4, May = 5, Jun = 6, Jul = 7, Aug = 8, Sep = 9, Oct = 10, Nov = 11, and Dec = 12
- Create a formula to calculate Projected Annual Income
IFF(month(this)=1, DIV(ACCT.Net_Inc, ASSUM.Month)*12, DIV(ACCT.Net_Inc+ACCT.Proj_Annual_Inc[time=this-1], ASSUM.Month)*12)
3. FAQ: What is the difference between IF and IFF in a formula?
IF evaluates both paths of a formula (true and false).
IFF will only evaluate the first path of a formula if the stated condition is true. We recommend initially creating a formula with an
IF statement to make sure that both parts of the formula are valid by testing a scenario for both paths. If the formula is taking a long time to calculate and you validated both paths of the
IF statement, you can try changing
IFF to see if performance improves.
Benefits when using IFF:
- Improved performance
- IFF statements can help avoid circular references in formulas that reference time
4. FAQ: What is the difference between the division functions?
The Div and Divf functions will have the same results; however, formulas that use Divf can calculate faster because the numerator is not evaluated when the denominator is zero. Unlike the Div function, if the denominator is zero, the result of this function is always valid even if the numerator contains an error. Div would report a "Refers to Invalid" error if its numerator is invalid, even if the denominator is zero.
Divf(N,D)f the denominator is zero, the numerator is not evaluated
5. How-to: Modify a master formula for new versions without changing data in past versions
The description field for versions via Modeling > Versions can be used to make version specific changes for master formulas. In cases where past versions should remain unaffected and new versions should reflect the update, you can enter a description via Modeling > Versions for all old versions that you do not want to change. Then, you can use formula logic that references the version description. This will prevent formula-driven data from changing in locked versions while allowing you to make update the formula formula for future budgeting needs.
To use the description field to prevent data from changing in old versions:
- Go to Modeling > Version > and enter a description for all old versions that should remain unchanged such as "Historical"
- Go to Modeling > Accounts > and modify the accounts master formula to include logic that references version description
IFF(this.version.description = "Historical", [Formula for historical versions], [Formula for new versions])
This can also be used for formulas referencing dimensions and attributes because the elements have a description field.
6. How-to: Make version specific formula changes for calculated accounts
7. How-to: Create a formula that calculates year over year (YOY) growth percent
Example: Calculate YOY growth percent for Expenses
- Create a Custom Account with a formula that references the past 12 months for the account
- Create a Metric Account that calculates YOY growth percent
8. How-to: Create a formula reference to filter an account for more than one dimension
You can create a formula reference to filter an account for multiple dimensions by separating the dimensions by commas in the same bracket.
Example: To filter Revenue by the "New" and "Type" dimensions, you would add the following formula:
ACCT.Revenue[New=Yes, Revenue Type=Support]