This week’s Adaptive Insights Tips & Tricks post pertains to the ever-mysterious Formula Assistant, which is a “wizard” that exists throughout the application. It helps you create the even-more-mysterious and somewhat intriguing formulas themselves.
Any clients attempting to create basic formulas such as “Net Income = Gross Margin – Operating Expenses + Non-Operating Income – Non-Operating Expenses” can search for and find all that is required from within the Formula Assistant. Knowing how to search for and where to look within the Assistant is the “trick” – especially when you are attempting to create a more complex formula. For example, calculating American FUTA tax. This can utilize an Assumption account for the FUTA % and FUTA Maximum amount to be taxed, verifies the calculation monthly, by employee who works for two departments – such as a receptionist for both sales and marketing and allocates the proper FUTA tax amount to each department!
The Formula Assistant comes in many formats, depending on the type of formula you are creating and where you are creating it from. The first Formula Assistant encountered by new users is often within the Standard sheet or while creating a metric account. It is the same Assistant that you encounter while creating a Metric Account of a master formula for a GL Account and it appears like this:
The key trick to understanding how to create formulas in this basic Account Formula Assistant is knowing how to locate the accounts you want in your formula. The left side of the Assistant is designed to help you locate your accounts. If you are looking for a specific account, for example Postage, you can find it by perusing for it in the lower left area as follows:
When you have selected the account you want, it appears in the Account Term Modifier lower right area of the Assistant, where you can now “modify” your selection to choose for example last months data, for a specific level, dimension or attribute combination as follows (in this example I select last month data and Accounting Level as modifiers and using the green arrow apply it to the formula upper right box area of the Assistant;
Another way to find the account Postage is to search for it by typing the account name into the search field in the middle of the left side of the assistant, resulting in the same account becoming available to enter with the green arrow either unmodified or modified as previously demonstrated:
The third way (and I am not including the fourth way, which is to outright type the account into the formula itself) is to use the extremely valuable top left area of the Assistant which houses the “Accounts.” I stress in my classes that memorizing or at least knowing what is behind the arrow (I usually state in my class “think ‘Lets Make a Deal’ game show- what is behind door number 1?”) is how you will smoothly locate what you need for your formulas. However, what is there is not self-explanatory.
Before I open the Accounts option (door number 1) remember that there are six types of accounts in Adaptive Planning: GL, Metric, Custom, Assumption, Model and Cube. Each of the six types is located in the selection behind “door number 1.” You will be able to select for example, Metric (by word) and then choose a Net Income Metric account in the lower left area of the Assistant (if defined in your instance.) The same for all the other types of accounts: EXCEPT for the GL Accounts. You will not see GL Account as an option. Instead, you will see mixed in with the other five specific account type names, the Root Accounts that come with every Adaptive Instance. The Root Accounts exist to enable a starting point for all GL Account Structures and client GL Accounts are imported or entered into any of the Root Accounts – and all GL Client Accounts inherit the “Type” from their Root Account Parent.
In the following screen capture is the basic set of Root Accounts (there are a few more lower level Root Accounts within the basic structure including Current Assets and Long Term Liabilities for example. If you look back at a previous Tips & Tricks blog post, you will find an article specifically about Root Accounts and their usage. The following is the basic set of Root Accounts (top level):
Root Accounts can be identified within the General Ledger Account screen as the only type of account that has the Trash Can unavailable. Root Accounts cannot be deleted. It is the Root Account “Type” in the third column that is so important to memorize. That is what displays instead of the GL Account name, in any Formula Assistant where you are searching for an Account, as follows:
In the screen capture above, I opened ‘Door Number 1” and displayed immediately is a list including ‘All’ (all accounts), Asset (A Root Account type), Assumption (one of the six types of accounts), Cost of Goods Sold (a Root Account Type), Cube (one of the six types of accounts), and Cumulative Translation Adjustment (just to confuse the issue a handful of important currency exchange rate accounts are made available here as well.) The selection list continues on to include all the Root Account types, the other five types of accounts specifically, and the currency accounts within. Knowing what to choose and what is available from this selection is the key trick, tip/ingredient to creating formulas and even to enable account selection for reporting. In my example, I want to add the Postage account into my formula, and knowing that Postage is an Expense enables me to narrow down my selection options by choosing Expense (GL Root Account type) in the top left ‘Door number 1’ area of the Formula Assistant.
In an upcoming Adaptive Insights Tips & Tricks blog post I will explore other types of Formula Assistants including the following from a Matrix Report Custom Calculation – which you may notice does not have the modifier section in the bottom right and has new section at the bottom of the Formula Assistant. To ‘Insert into Formula’ instead of the green arrow insert, red X delete option previously experienced in the Account Formula Assistant. The same options, however, are behind ‘door number 1’ top left of the Assistant.
In summary, it behooves you (yes I DID use behoove in this paragraph!) to memorize the options behind ‘Door Number 1’, the top left area of the Formula Assistant Account wizard to enable you to find the elements you need for your formulas and even for reporting.
The team at Revelwood has been recognized by Adaptive for its thought leadership in the space, commitment to its Adaptive Insights practice, and its rapid achievements of milestones. Visit Revelwood’s Knowledge Center for our Adaptive Insights Tips & Tricks or sign up here to get our Adaptive Insights Tips & Tricks delivered directly to your inbox. Not sure where to start with Adaptive Insights? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.
Read more Adaptive Insights Tips & Tricks!
Adaptive Insights Tips & Tricks: Virtual Versions and Neutral Exchange Rate
Adaptive Insights Tips & Tricks: Overriding the Level Security on Matrix Reports
Adaptive Insights Tips & Tricks: How to Remove Repetitive Total Rows
