In a previous blog post, I showed how to setup an Adaptive cube sheet that allows flexibility to handle dimension levels from Intacct. This cube sheet allows planning at a more detailed level. In my example, I created a Revenue by Customer and Department sheet, which allows budgeting by location (organization level), by individual department and customer (dimensions). Here is how to combine this revenue sheet with expenses, planned at the location level, to give a full P&L. I do this by linking the revenue cube sheet to a standard sheet.
To link a revenue cube sheet to a standard sheet, go to the Formulas tab by clicking the three black bars
in the upper left-hand corner of the screen. Change the version selector to either Actuals or your most current forecast or budget. Next, choose the GL account(s) to populate from the cube sheet data. In the example below, I chose account 4100 – 4100 Revenue – Liquids. After selecting the GL account, a list of the available Levels appears. Either click Select All or choose each level individually. Next, link to the cube sheet by clicking Set Formula.
Tip: You can put a different formula at each Level, if needed. This allows complete flexibility in planning at the different levels of your organization.
When the Set Formula box appears, click Formula Assistant. To link this account to a cube account, click the drop-down selection just above the Search bar and choose Cube.
By selecting Cube, it only lists the cube accounts in your model under the Search bar. Find the cube account by either scrolling or typing the name of the cube account in the Search bar.
If you want to link this GL Account to the Cube Account in the same Time and Level, no changes are needed to the default Term Modifiers. However, if you want to reference a different Time period or a specific Level in the formula, you will need to change the Term Modifiers.
After selecting the Cube Account, click Insert into Formula. Click OK when done and click Save on the next page.
The Formulas screen now shows your newly created formula on each level.
Repeat these steps to link any cube account with any GL Account.
Check to make sure your new GL account formula works by finding a standard sheet that contains the GL Account. Right click on a value of the GL account and chose Explore Cell. Drill down (by clicking the respective hyperlinks) until you get to the appropriate level of detail.
If you have any questions on this process, feel free to reach us on the Contact Information page of the BTerrell Group’s website.