Creating Account Lookups on Adaptive Model Sheets

Lookup tables are used in Adaptive Insights modeled sheets to look up a value and then perform a calculation based on that value.  The lookup table can be created for text selector and dimension columns.

For example, you could have a modeled sheet for maintenance revenue details that includes a dimension column for Contract Type and create a lookup table that is used by the appropriate GL accounts to summarize amounts for each specific contract type.  But, what if you wanted to select the general ledger account directly instead of using contract type to summarize by? 

Since GL accounts are not available as columns on modeled sheets there is no account selection available.  To work around this we can create a text selector column that is called GL Account and add the accounts as selector items. 

As an example, let’s assume that we want to provide a lookup for a list of accounts that make up the maintenance revenue.  The list below shows the two GL accounts we will be using; that is, code’s 4021 and 4022.  These accounts will be made available on the Maintenance Revenue Detail modeled sheet to be selected on each row. 

The following steps will create the account lookup:

1. Create or select an existing text selector column in your modeled sheet.  In our example, we will use the GL Account column.  Add a text selector item for each GL account that you want to make available to the sheet.
   

2. Next, create a lookup table by clicking on the New Monthly Lookup Table link. This will open the Lookup Table window.  Give the lookup table a name and leave the ‘Display As’ and ‘Decimal Places’ selections alone. 

3. For each of the accounts in the text selector item list enter the account number (or some other unique lookup identifier) as the value in each period available in the version.  You can use the copy forward function (Right click, then Copy Forward>Copy to End) to easily fill in all periods with the same value.
   

4. Once the lookup table is complete, we need to create modeled accounts that subtotal the revenue for each account.  Click on the New Calculated Account button to open the Calculated Account window.  Give the calculated account a name that corresponds to the GL account for easy reference.  In our case, the 4021Rev calculated account subtotals revenue GL account code 4021.

5. Set the ‘Type’, ‘Display As’, and ‘Decimal Places’ selections to be the same as that of the associated GL account and set the formula as follows: 

iff(ROW.AcctLookup=XXXX,ROW.Amount,0)

Where XXXX is the lookup value of the account (In our case, 4021 or 4022).  For example, iff(ROW.AcctLookup=4021,ROW.Amount,0)

What is this formula doing?  If the account lookup value is 4021, then the formula evaluates the row’s result as the Amount value, otherwise it evaluates the result as zero.  This will subtotal all rows with the account lookup value of 4021.

6. Finally, we will create level formulas for the GL accounts that are associated with our newly created modeled calculated accounts. To do so, open the formulas window from the navigation menu and select a GL account.

7. Select the levels that you wish this formula to apply.  In our case, we will select all levels.
   

8. Click on the Set Formula button at the bottom of the page and then open the Formula assistant.  Once open, select Modeled from the dropdown selector and then scroll to your model sheet.  Once there select the associated modeled calculated account and insert it into the formula. 
   

9. Click OK and then Save.  This will write the formula to each selected level and display the calculated subtotal on sheets and reports that use this GL account. 
   

Summary

To create a GL Account lookup on a modeled sheet a new lookup table can be created on a text selector column that represents the GL account.  This lookup table will be used by modeled calculated accounts to subtotal all amounts associated with the selected account.   And finally the modeled accounts are associated with the GL account using formulas.

Since GL accounts are not available as a column on modeled sheets, this is a good work around that creates an account selector. 

Did you know that BSP offers training sessions? Check out the sessions we have available or contact us to schedule a training session for your team.

About the Author:

Martin Begley, CISA, is Director of Management Consulting Services at Business Solution Partners and is a Certified Information Systems Auditor (CISA).  He specializes in helping small and mid-sized businesses meet their strategic goals through designing, implementing, and executing performance management strategies, technology solutions, and corporate policies and procedures.  You can follow him on LinkedIn at www.linkedin.com/in/martinbegley/​