Planning & Forecasting

IBM Planning Analytics Tips & Tricks: Alternative Row Formats for Excel Reports

November 10, 2020 by Lee Lazarow Leave a Comment

In the early days of computing, printers often used paper that had alternating white and green areas which allowed the reader to easily follow a line of text across the page. This was known as “green bar” paper. While we no longer use this type of paper, the concept is still applicable for situations where your output contains many columns that are hard to follow.

You can create this approach by using alternate row formats within your Excel reports. This is done by combining two Excel formulas:

The ROW formula returns the row number for a cell reference. For example, ROW(K5) returns the number 5 since the cell is in the 5^th row of the spreadsheet.
The MOD formula returns the remainder of two numbers after division. For example, MOD(9,2) returns the number 1.

When combined, the following formula will result in either a 1 or a 0:

=MOD(ROW(cell),2)

From there, you can use conditional formatting to define the fill colors. If you are doing this in standard Excel, then you can copy the formula throughout the applicable area. If you are doing this within a dynamic report, then you can use it within the formatting area.

This approach allows you to make your wide reports easier to read. This also means that you can recreate the green bar format while you pull out your dot matrix printer and watch classic movies such as Wargames!

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Workday Adaptive Planning Tips & Tricks: Override Formulas in Sheets

November 4, 2020 by Michelle Song Leave a Comment

If you want to write a formula in an account in Workday Adaptive Planning, but still want to give your team the ability to override the formula and enter different data, you can use Shared Formulas to achieve that.

The Shared Formulas function of Adaptive Planning allows users to write level-specific and version-specific formulas for GL and custom accounts. Below is a screenshot of the Shared Formula page in Adaptive.

Workday Adaptive Planning: Override Formula

First, you will select the version, account, and levels that you want to write the formula. Then, you will write the formula in the “Set formula” box. Before you save the formula, you can choose to reserve/remove the user edits in the account if any. If you reserve the user edits, the formula will not override cells that already have data and only perform the formula in the cells that are blank on sheets.

Workday Adaptive Planning: How to override formulas in sheets

You can use the “Import Shared Formulas” function to import/update the formulas in multiple accounts and levels at a time.

Overrding formulas in Workday Adaptive Planning

Unlike Master Formulas, shared formula values are not locked in sheets. You can override the formula with different data directly in a sheet cell. In the example below, I overridden the formula for account 1110 Petty Cash in Mar 2019 with a different number.

Workday Adaptive Planning Tips: Override Formulas in Sheets

Workday Adaptive Planning Tricks: Override formulas in sheets

The team at Revelwood has been recognized by Workday Adaptive Planning for our thought leadership in the space, commitment to our Workday Adaptive Planning practice, and our rapid achievements of milestones. Visit Revelwood’s Knowledge Center for our Workday Adaptive Planning Tips & Tricks or sign up here to get our Workday Adaptive Planning Tips & Tricks delivered directly to your inbox. Not sure where to start with Workday Adaptive Planning? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

IBM Planning Analytics Tips & Tricks: Minimizing the Subset Area

November 3, 2020 by Lee Lazarow Leave a Comment

Are your member names long and hard to see in the subset editor in IBM Planning Analytics? Does it get confusing to see both the available members and the current set at the same time? Both situations can be solved by hiding one side of the subset editor.

Here is an example of the subset editor which shows both the available members and the current set.

IBM Planning Analytics Tips & Tricks: Minimizing the Subset Area

Each of the two sections has an option to minimize the area. This is done by clicking on the minus symbol at the top of the applicable section.

IBM Planning Analytics: Minimizing the Subset Area

Once clicked, the applicable area will compress and the other area will expand. Note that you can only minimize one side at a time. Below are examples of what each screen will look like when compressed.

Minimized Available Members:

IBM Planning Analytics Tips: Minimizing the Subsea Area

Minimized Current Set:

Minimizing the subset area in IBM Planning Analytics

This approach will allow you to further customize your subset editor screen to allow you to focus only on the area you want to see.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Maintaining Subset-Drive Consolidations

IBM Planning Analytics Tips & Tricks: Sort Elements within a Subset

IBM Planning Analytics Tips & Tricks: Subset Control Dimension

IBM Planning Analytics Tips & Tricks: Show Members

October 27, 2020 by Lee Lazarow Leave a Comment

Did you know that while you are doing dimension maintenance, you can quickly view an element’s parents or children in IBM Planning Analytics Workspace (PAW) via a single click? Here’s how you can do this:

Step 1: Select the dimension to be edited. This is done by either right clicking on a dimension and selecting the option to “Edit Dimension” or by dragging the dimension onto a sheet.

Step 2: Right click on an element and select the option to “Show Member.” You will then see two sub-options: one for Parents and one for Children.

IBM Planning Analytics Tips & Tricks: Show Members

If you select the option for Parents then you will see all of the parents associated with the selected element. In this example, there is only one parent within the dimension structure.

If you select the option for Children then you will see all of the children associated with the selected element. In this example, there are three elements in the consolidation.

How to show members in IBM Planning Analytics

Once defined, you can then perform dimension maintenance to each of the elements on your screen.

This approach allows you to easily traverse up or down your dimension while minimizing the amount of extra elements on the screen.

IBM Planning Analytics Tips & Tricks: An Approach to Strengthening your MDX Skills

October 20, 2020 by Lee Lazarow Leave a Comment

Are you new to IBM Planning Analytics and looking for a good way to learn about the various commands associated with MDX? The Planning Analytics subset editor offers a great way to teach you about MDX expressions that can be used to define elements within a dimension.

The example below shows the results of a subset that includes all descendants of Total Department. The subset was created by simply dragging Total Departments into the current set while having the member insertion defined as descendants.

Once created, I can see the MDX expression that is used by clicking the MDX button at the top, right corner of the screen. The resulting expression is:

However, this button is not only used to view your newly created MDX expressions. The button also allows you to edit the expression. In this case, I can change the root element to be Sales and Marketing by simply typing over the existing MDX expression.

After clicking the OK button, the elements in the current set are updated.

This approach can help you to learn the fundamentals of MDX expressions, which are helpful for many different components of Planning Analytics.

IBM Planning Analytics Tips & Tricks: Sort Elements within a Subset

October 6, 2020 by Lee Lazarow Leave a Comment

Did you know that there are two different ways to sort elements within a subset in IBM Planning Analytics?

Below is an example of a subset which shows a standard time dimension:

Subset sort options in IBM Planning Analytics

One approach sorts all elements either ascending or descending, regardless of level. These are done via the following icons:

The result of this type of ascending sort will put all elements in alphabetical order, regardless of level:

Understanding subset sort options in IBM Planning Analytics

The other approach also sorts elements either ascending or descending, but this approach takes levels into consideration. These are done via the following icons:

The result of this type of ascending sort will put all elements in alphabetical order while keeping the levels intact:

Learn subset sort options in IBM Planning Analytics

This approach will allow you to sort your list of elements in various ways, which offers different opportunities based on the elements within the dimension.

IBM Planning Analytics Scores 13 Top Rankings and 21 Leading Positions in BARC’s The Planning Survey 20

October 5, 2020 by Lisa Minneci Leave a Comment

IBM Planning Analytics, which is built on the well-known and proven TM1 engine, received numerous high marks in this year’s annual Planning Survey from BARC. The survey provides a “detailed quantitative analysis of why customers buy planning tools, what they use them for, they everyday problems that users experience with the tools and how successful they are.”

The survey findings include:

954% of surveyed users rate IBM Planning Analytics’ coverage of planning-specific requirements as excellent or good, compared to 89% for the average planning tool
50% of surveyed users chose IBM Planning Analytics because of its flexibility, compared to 48% for the average planning tool
43% of surveyed users chose IBM Planning Analytics because of its convincing performance, compared to 31% for the average planning tool.

“Besides planning, customers mainly use IBM Planning Analytics for ad hoc query and reporting (91 percent), standard/enterprise reporting (77 percent) and basic data analysis (72 percent). 51 percent of respondents plan to use it for advanced analysis in the future … 67 percent of Planning Analytics users are planning users – just above the survey average of 65 percent – reflecting the fact that Planning Analytics is essentially a planning tool with complementary BI and analytics functionality.” – BARC Comment

Download the highlights to learn details on IBM Planning Analytics’ rankings, read customer quotes, and more.

IBM Planning Analytics Tips & Tricks: Editing Chores While Active

September 29, 2020 by Lee Lazarow Leave a Comment

Did you know that IBM Planning Analytics Workspace (PAW) allows you to edit chores while they are active? Perspectives required you to de-activate a chore, make your modifications, and then re-activate it. However, the two “bookend” steps of this approach have been eliminated in the PAW environment.

To edit a chore, right click on the name of the chore and select the option to “Edit Chore.”

IBM Planning Analytics Tips & Tricks: Editing Chores

The chore will open to show you information about the chore. You will then be able to edit the details associated with the schedule and the specific processes to run.

You have the option to disable the schedule at any time, but this is no longer a requirement.

Editing chores in IBM Planning Analytics Workspace

This approach allows you to quickly modify your chores without having to perform the mundane steps of de-activating it and then re-activating it. This also means that you no longer need to remember to turn the chore back on after making your changes!

Office of Finance Benchmark Data

September 28, 2020 by Lisa Minneci Leave a Comment

Do you know how your Office of Finance compares to others? A recent report from Ventana Research looks at change in the Office of Finance and provides benchmarks against which senior finance executives can compare their departments against their peers.

Ventana has researched the performance of the finance organization for 15 years. The report provides a comprehensive look at how the Office of Finance is transforming, the importance of Finance IT, key insights for the Office of Finance, and 10 best practice recommendations.

Benchmark data

Ventana shares plenty of interesting benchmark data in this report, including:

76% of finance leaders surveyed cite analytics as critical for improving their performance
The share of companies that have a Finance IT group has increased to 59% from 45% in 2014
More than one in five said their process for creating finance analytics works very well
32% said they use analytics to improve performance, compared to 14% in 2014
46% of senior executives report full availability of finance analytics
72% report reviewing their close process either monthly or quarterly
Companies that close their books within six days after the end of the quarter are more likely to provide executives with timely information
66% report their budgets remain relevant through the period
52% review their actual to budgets within six days; 19% do it within three business days

IBM Planning Analytics Tips & Tricks: Alternative Row Formats for Excel Reports

Read more IBM Planning Analytics Tips & Tricks posts:

Workday Adaptive Planning Tips & Tricks: Override Formulas in Sheets

Read more Workday Adaptive Planning Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Minimizing the Subset Area

IBM Planning Analytics Tips & Tricks: Show Members

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: An Approach to Strengthening your MDX Skills

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Sort Elements within a Subset

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Scores 13 Top Rankings and 21 Leading Positions in BARC’s The Planning Survey 20

IBM Planning Analytics Tips & Tricks: Editing Chores While Active

Read more IBM Planning Analytics Tips & Tricks:

Office of Finance Benchmark Data

Benchmark data

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

Planning & Forecasting

Read more IBM Planning Analytics Tips & Tricks posts:

Read more Workday Adaptive Planning Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Benchmark data

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT