BTerrell Group Blog

Creating a Weekly Forecast in Adaptive Planning

Posted by Jeremy Griffith on Wed, Apr 15, 2015

Adaptive Planning is a great tool to use to forecast your data on a monthly and yearly basis. But, what happens if your company needs to forecast their data on a weekly basis? How would you accomplish this in Adaptive since Adaptive’s time dimension has only months and years available? (As a side note, Adaptive does have adding days to the time dimension on their roadmap.)AdaptiveInsights

To accomplish this you would need to add a new dimension called Weeks. The dimension values would be 1 – 5.

Now, create a cube sheet by creating an account or accounts that you want to forecast at the weekly basis and add the newly created Weeks dimension to the cube as well. Once you create the cube sheet, you can go to the cube sheet and set up the layout of the sheet to have Weeks and Accounts in the vertical axis with Time on the horizontal axis. This allows you to forecast easily at the level detail you might need.

4-15imagea

If you are wanting to roll up the weekly forecasting numbers to a monthly number, and show this on your monthly P&L forecast, you accomplish this by creating formulas on an account on your P&L that pull data from the cube sheet.

You could even become more granule in your forecast if you wanted to do it by day also. This requires the same steps as for weeks, but you create a day dimension with values of 1-31.  

If you need any help with setting this up or anything else in Adaptive, please contact me at BTerrell.

Tags: adaptive insight

Using What-if Scenarios in Adaptive Planning

Posted by Jeremy Griffith on Fri, Mar 13, 2015

A client in the energy industry needed help creating what-if scenarios for their Gross Revenue due to a downturn in the oil industry. This downturn occurred in the middle of creating their yearly budget. To help them adjust their budget easily and quickly, I used Adaptive to create a what-if scenario.

They were budgeting their Gross Revenue at the customer level, but wanted to be able to adjust quickly their total revenue by organization level instead. We accomplished this by creating an “On/Off” assumption to say which months they wanted to make top-level adjustments to revenue and which months they did not.

3-13imagea

Once the assumption was set, I created a cube sheet called “Revenue Modeling,” which gave them 1) the current budgeted revenue by organization level, 2) an input account called “stress test %” to increase or decrease the revenue by a percentage, 3) the calculated Stress Amount, and 4) the New Gross Revenue number. The client used this cube sheet to set the Stress Test percent at each organization level. 

Next, I created a link between the Revenue Modeling sheet and the client’s income statement by creating a new GL account, called “Revenue Stress Test,” that rolled up to Gross Revenue. Last, in this new account, I created a formula to pull the Stress Amount from the previously created cube sheet.

3-13imageb

This what-if scenario worked so well for them that I built more what-if scenarios to make their budget process more flexible.

This example demonstrates Adaptive’s powerful budgeting and forecasting ability and its accuracy and flexibility. For more details on how to build what-if scenarios in Adaptive, please contact me.

 

Tags: adaptive insight

Linking a Cube Sheet to a Standard Sheet

Posted by Jeremy Griffith on Mon, Feb 16, 2015

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
2-13image1 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.

2-13image2Tip: 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.

 2-13image3

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.

 2-13image4

The Formulas screen now shows your newly created formula on each level.

2-13image5

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.

Tags: adaptive insight

Creating a Cube Sheet in Adaptive Planning

Posted by Jeremy Griffith on Wed, Jan 21, 2015

Since joining BTerrell Group, I learned that one of Intacct’s greatest assets is its flexibility to book entries using the eight standard dimensions or any custom dimension. When setting up my first demo environment between Intacct and Adaptive Planning, I needed a way for the user to budget and forecast at the dimension level. To do this, I explored using Cube Sheets in Adaptive. 

People use cube sheets any time a user needs to model multi-dimensional data. To setup a cube sheet, a person with the appropriate rights would need to go to the main drop-down menu, represented by the three black bars (1-21image1) in the upper left hand corner of the users Adaptive instance, and select Admin. From the Admin screen, go to the Sheets and Announcement Pages section and choose “Create a Cube Sheet.”

1-21image2

The next screen asks you to name the sheet and the account group. Remember, the account group name may only include letters, digits, or underscore and cannot contain spaces. 

1-21image3

Now you set up the accounts, assign the dimensions and levels, and set any restrictions. Click the edit link next to Cube Accounts. From this screen, you set up the accounts. Unfortunately, cube accounts cannot be imported like general ledger and custom accounts. Each account must be set up manually. Once the accounts are set up, click Done.

Next, click the Edit link next to Dimensions and Levels. To add a dimension, go to the left side of the screen and drag and drop any dimensions into the “Drag here to add a dimension" area. Once you choose the dimensions, select the values for each dimension that you want available to the users. This can be done by either clicking each individual item or clicking Select All. Another option is to check or uncheck "Edit Dimension on Sheet." This will allow you to add a new value directly from the sheet instead of setting it up in the “Define Dimensions” link on the Admin page.

The last step to setting up the dimensions and levels is to choose what Organization Levels you want to be available. You can select each one individually by clicking the check box or you can click “Select All” to choose all values.

Once you choose the organization levels and the dimensions you want, you can reorder the columns by either dragging and dropping the column header to a new location or by clicking the left or right arrows next to the header names. Save when done.

 1-21image4

The last step is Restrictions. Restrictions are used to tell Adaptive which combinations of accounts and dimensions should not be available in the cube. Here is the description from the Restrictions screen:

“Restrictions allow you to specify that certain combinations of dimensions should not be available in a cube. For example, if a Region dimension exists, you may want to create an Organization-Region restriction in order to ensure that individual level owners see only those regions for which they are responsible. Combinations which are eliminated from the cube using restrictions do not appear in selector menus or on any displayed axis. Restricted locations might still contain data, if that data was entered prior to the restriction being created, but there is no way to edit, enter, or delete data from a restricted location in a cube. Creating a restriction begins with the selection of a pair of dimensions in the cube, for which some combinations should not appear in the cube.
There are no restrictions for Revenue by Customer and Department. Click on Add New to create a restriction. “

If there are any restrictions you want in place, click the Edit link next to Restrictions. You can come back after the sheet is created and add restrictions.

You are now done setting up the new cube sheet. Provided the user has access to the levels the sheet is available on, it should be visible in the Sheet's drop-down menu. When loading data to this cube sheet, you need a separate file from the load file for your standard sheet(s). 

This new cube sheet allows the user more visibility and detail while forecasting and budgeting. For any questions on setting up and configuring Adaptive to work with Intacct, please contact me.

 

Tags: adaptive insight

BTerrell Now Offers Products and Services from Adaptive Insights

Posted by Jeremy Griffith on Wed, Dec 10, 2014

BTerrell now offers a great financial planning tool, Adaptive Insights. I have about three years experience using Adaptive and I also have experience using Hyperion Planning and Host Analytics, so I have some experience with different products out there. Out of the three budgeting applications I have used, I believe Adaptive is the best. Adaptive is easy to use, requires no IT help, and allows for great collaboration across your company when doing your forecasting and budgeting.

Adaptive Insights offers more than just a budgeting solution under their umbrella.  Their products are Planning, Discovery, Consolidation, Reporting, and Integration. 

  • Planning provides you comprehensive budgeting, planning, and forecasting. Planning is very intuitive and has an “Excel-like” feel that makes for an easy transition for anyone currently doing their budgeting and forecasting in Excel.
  • Discovery is Adaptive’s dashboard module that provides visual insight into your financial data for analysis.
  • Consolidation is used to consolidate all of your financial data in one place that includes journal entry management, intercompany eliminations, account reclassifications, and even collaborative close process management.
  • Reporting provides comprehensive financial, management, board, and transactional reporting that allows for variance reporting between your budget/forecast and your actuals along with building your whole monthly financial reporting package. Reporting is an easy drag and drop interface. OfficeConnect is a new offering from Adaptive that allows you to retrieve data for reporting straight to Microsoft products Excel, Power Point, and Word.
  • Integration connects on-premise and cloud investments to your Adaptive suite through connectors.

12-10image1

BTerrell offers several services with Adaptive Insights as well as the product. For more information on our services, click here.

Tags: adaptive insight