Financial Performance Management

IBM Planning Analytics Tips & Tricks: PAW Chart Show Totals

July 19, 2022 by Dillon Rossman Leave a Comment

One of IBM Planning Analytics’ purposes is to serve as an aggregation tool. As a result, many of your explorations consist of a series of base level elements plus a total. Here is an example of a simple exploration that shows details of a list of companies plus a total:

Table

Description automatically generated

This exploration can easily be converted into a pie chart by simply changing the visualization to a pie chart. Here is what the pie chart will look like:

Chart, pie chart

Description automatically generated

However, the concept of aggregations has now shifted into a detriment since the total will always appear as 50% of the chart! Planning Analytics Workspace (PAW) allows you to hide the totals by defining properties within each field of your chart. You can do this via the following steps:

While in Edit mode, single click on your pie chart widget.

Select the option for “Fields” at the top, right corner. Not all visualizations use fields, so this may appear blank if you have not yet converted your widget to a pie chart.

Click the three vertical dots associated with the dimension that should not include totals. In my example, the segment is associated with the company dimension.

Select the option to Hide Totals.

Once selected, the same pie chart will look like this:

This approach will allow you to properly define when you want a total to appear, thereby making your chart results beneficial to the user.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Displaying Percentages on a Planning Analytics Workspace (PAW) Pie Chart

IBM Planning Analytics Tips & Tricks: PAW Pie Percent or Value?

IBM Planning Analytics Tips & Tricks: PAW Explorations – Row & Column Headers

Modern Accounting: 6 Essential Qualities for Surviving the Robot Uprising in Accounting

July 14, 2022 by Revelwood Leave a Comment

This is a guest blog post from our partner BlackLine, explaining how you could be more productive with fewer resources, less overtime, and also easily improve the quality of your work.

As accounting and finance professionals, you do so much more than just handle money matters—you’re also critical for creating strategy and driving process improvements across the entire organization.

But if the majority of your days are spent manually reconciling accounts and matching transactions, little time is left for these bigger picture activities.

What if you could be more productive with fewer resources, less overtime, and also easily improve the quality of your work? Not only would you be pleased with this improvement, but it would set you apart in the industry.

Optimizing Your F&A People

Your accounting and finance professionals are at the heart of your organization’s innovation, and crucial to driving strategy and future business growth. But according to a recent BlackLine survey conducted by Censuswide, many mid-sized and large company decision-makers are not fully leveraging this talent.

Manual processes and tedious tasks take up too much time and result in this invaluable skillset being widely underutilized. To unlock the value of your people, begin by automating the manual accounting work that consumes so much of accountants’ time and effort.

When manual processes are automated, accounting and finance teams spend fewer hours on transactional activities. The focus shifts to analyzing the data and reports, and addressing only the exceptions.

This enables everyday accountants to become exceptional accountants, providing high-value services in areas like fraud detection, compliance, data analytics, technology, and business advice.

What Is an Exceptional Accountant?

When you are only researching the anomalies, you can finally refocus on providing strategic guidance to the business, such as improving internal processes or finding cost-saving opportunities. In other words, the added time allows you to apply not just your knowledge and expertise, but your nuanced creativity and intelligence as well.

According to Helen Brand, chief executive of the Association of Chartered Certified Accountants (ACCA), “To succeed as a professional accountant…a vastly different set of skills is required than was necessary just 10 short years ago. And in the next decade, things are likely to change even faster and more dramatically as the global economy continues to evolve at an ever-quickening pace.”

So, what does the exceptional accountant of tomorrow need to cultivate today? Think mastering communication, not macros. Strategic aptitude, instead of being spreadsheet savvy.

In short, capabilities that enable you to deliver predictive insights to leadership, drive data-based decisions and provide expert counsel.

6 Skills You Need to Become an Exceptional Accountant

These six skills needed for accounting work in unison to serve as building blocks to exceptional accountant status.

1. Analytical Skills

For the finance function, providing leadership with historical data used to be quite sufficient. Yet today, companies also expect to have access to predictive data.

For today’s accountants, this requires knowing how to turn Big Data into concise, decision-driving insights. Vanguard businesses are already hiring accountant/data scientists. Accountants looking toward the future must have both a theoretical and practical understanding of data and analytics.

2. Communication Skills

It’s said ad nauseam: it’s the Age of Information. Yet all this information is just noise if it’s not shared effectively. The exceptional accountant of tomorrow won’t just know why data looks like it does (analytical skills); she’ll also be able to skillfully convey those insights to others.

Accountants must begin to cultivate strong written communication skills: the ability to think critically and translate those thoughts into compelling documents. They will also need strong oral communication skills: the ability to convey pertinent financial information to executive teams and stakeholders.

3. Relationship Skills

During the “good ole days,” an accountant could hide in the back office, subsisting on a few basic greetings at the legendary water cooler.

Yet thriving in tomorrow’s business is going to take more than water cooler-level conversational skills. As automation streamlines transactional tasks, accountants won’t have the “millions of transactions to match” excuse to sidestep human interaction.

The exceptional accountant of the future will know how to manage numbers and people. That requires cultivating a broader range of relationship skills today, such as how to work in a team, how to motivate and engage employees, and how to deliver bad news — without making somebody cry.

4. Creativity

It used to be that nobody wanted a “creative” accountant. But in an era when businesses must quickly identify opportunities while simultaneously mitigating risk, a finance professional who can think outside of the proverbial box is a strategic asset.

Accountants who can combine creativity with a deep understanding of the company’s financial capabilities will be able to solve complex financial—and non-financial—problems faster and more cost-effectively.

5. Business Acumen

Contributing to the business on a strategic level requires more than just an understanding of the numbers. Accountants also need to understand the business as a whole. The ability to provide counsel to the C-suite requires seeing the big picture, from how each functional area works to the best way to acquire and retain talent.

When accountants have the opportunity for stretch assignments, cross-training, and job-sharing, it’s easier to understand—and make decisions based upon—the holistic interplay between a company’s services, employees, customers, and stakeholders.

6. Tech Savvy

Technology isn’t just changing every job function; technology itselfchanges rapidly. Instead of expecting to use the same tool for the next decade, accountants today must be ready to use new technology every year. This requires not just a basic understanding of technology itself, but the ongoing cultivation of flexibility andadaptability.

Benefits of Developing Your F&A Professionals

Today more than ever, companies need finance and accounting professionals who can transcend traditional number crunching. Yet for accountants, making the transition from spreadsheet jockey to strategic expert requires new skills.

Your people are the ones who redesign your processes, and therefore it is essential to encourage, help develop, and hire for a different set of skills needed for accounting. This is the most difficult hurdle to clear because there is so much inertia, with the biggest battle being the comfortable, fixed mindset of “this is the way it’s always been done.”

The tactic to overcoming this is to create buy-in for the Continuous Accounting approach and get your people on board with your vision. For this to be successful, your Continuous Accounting strategy must clearly point to your end goal as an organization, along with a blueprint of achievable milestones.

Fundamentally, Continuous Accounting is a story about unleashing the accounting and finance professionals who have unparalleled vision to experiment, push the limits, try and fail. When you allow your people to drive change, they deliver things that scale in a very exciting way.

And it all begins with empowering your teams with the skills needed for Accounting to propel the entire organization into the future.

Accountants who cultivate business acumen can begin to provide guidance relevant to the entire company, not just the finance department.

Nurturing creativity leads to innovative solutions for some of the biggest challenges in business today, from the unexpected corner of accounting.

Developing analytical capabilities ensures the accounting function can deliver true insight, not just historical information, while building communication and relationship skills guarantee those insights aren’t lost in translation.

Finally, because technology will continue to change at light speed, accountants who not only have basic IT skills but also flexibility and adaptability will always be ready to integrate new, more efficient tools into existing processes.

IBM Planning Analytics Tips & Tricks: PAW Escape Character

July 5, 2022 by Tara Byrnes Leave a Comment

Have you ever been in a situation where you have an element name with an apostrophe (or two) in IBM Planning Analytics? Examples of this might entail last names such as “O’Connor” or “O’Sullivan”?

Although IBM best practices specify that the apostrophe is a special character to avoid in object and element names, in many instances you’ll see an apostrophe used in your data source (especially in names and brand names).

Planning Analytics confuses the apostrophe as a single quote … which is a special character used to define literal strings. You can resolve this situation by using an “escape character.” An escape character invokes an alternative interpretation on the characters which follow.

The Planning Analytics escape character sequence defines 2 single quotes together as the equivalent of 1 single quote.

Example:

‘O’’Connor’ will display as ‘O’Connor’

This escape sequence can be used in both Rules and Turbo Integrator processes.

This approach will allow your use of O’Connor to properly work instead of causing you to convert it into “oh darn”!

IBM Planning Analytics Tips & Tricks: Dynamic Subsets Based on a Cube

June 28, 2022 by Dillon Rossman Leave a Comment

Did you know you can use MDX to create dynamic subsets based on cube values in IBM Planning Analytics? In our example we will only show companies with net sales greater than 30,000,000 in the selected year.

Graphical user interface, text, email

Description automatically generated

An MDX statement that can provide this result may look something like this:

{FILTER({TM1SUBSETALL([bpmCompany])},[bpmFinance].([bpmScenario].CurrentMember, [bpmDepartment].CurrentMember,[bpmAccount].[Net Sales],[bpmCurrency].CurrentMember,[bpmPeriod].CurrentMember,[bpmFinance_Msr].[Balance Post-Alloc]) > 30000000 )}

The components of this statement are:

FILTER({abc}),[Cube].([Dim1].[Value],[Dim2].[Value]…) > 30000000 )
- Filter set ABC to only show members with a value greater than 30,000,000 for the intersection defined in the section after the comma
TM1SUBSETALL([bpmCompany])
- Grab all members within the bpmCompany dimension
[bpmFinance]
- The cube being used for the data
([bpmScenario].CurrentMember, [bpmDepartment].CurrentMember,[bpmAccount].[Net Sales],[bpmCurrency].CurrentMember,[bpmPeriod].CurrentMember,[bpmFinance_Msr].[Balance Post-Alloc])
- The intersection used to get the cube value
- This is like a DBRW formula in the sense that we are defining a view by listing every dimension and providing a member within that dimension
  - CurrentMember will grab whatever element is selected in the cube view for that dimension
  - The other dimensions are using a specific member that we always want to filter by

Below you will see only companies with net sales greater than 30,000,000 for the “Balance Post-Alloc” measure remain in the bpmCompany subset. This will dynamically update as selections for other dimensions are changed.

Graphical user interface, text, application, email

Description automatically generated

Workday Adaptive Planning Tips & Tricks: Choose the Correct TIME Syntax

June 22, 2022 by Michelle Song Leave a Comment

Are you familiar with all the TIME syntax in Workday Adaptive Planning? Do you know what is the best practice for each of them? This blog post will tell you the difference between them.

Below are the most common TIME syntax in Adaptive Planning.

Month(this)
Versionmonth(this) – returns the
This.year.positionof(this.month)
This.version.positionof(this.month)

In the following example, this instance has a custom calendar which is from July – June. The example is created using Month as the stratum. The same logics are applied to Quarter and Year.

Graphical user interface, application, table

Description automatically generated

In July 2021, Month(this) returns the month in the standard calendar, which is month 7. This syntax also assumes that July is from July 1^st to July 31^st. For example, if 7/30/2021 in a custom calendar is actually a date in Aug 2021, then versionmonth(July-2021) and versionmonth(Aug-2021) will return the same number. This same situation also applies to Versionmonth(this).

Versionmonth(this) returns the month of the version, which also follows the standard calendar and assumes July is from July 1^st to July 31^st.

Graphical user interface, table

Description automatically generated

This.year.positionof(this.month) returns the fiscal month of the year, which is month 1 for July-2021. This syntax references the custom calendar. For example, if 7/30/2021 in a custom calendar is actually a date in Aug 2021, then This.year.positionof(this.month) returns month 2 in the year, which is Aug 2021. Same situation applies to This.version.positionof(this.month).

Graphical user interface, text, application

Description automatically generated

This.version.positionof(this.month) returns the month in the version since the Start of Version. In this case, the Start of Version is July 2021, therefore it is month 1 in the version.

Other use cases and helpful formulas related to time.

Personnel Sheet:

For Headcount calculation that has a Custom Calendar and Start Date before the system date
- this.Year.positionof(this.Month) – correct result, but shown as RED (error) on Income Statement because the date is outside of the system date.
- versionmonth(this) – might result in incorrect result due to date overlaps in two months.
- Use iff(ROW.PartialHeadcount>0, 1,0) as a workaround.
Cumulative Salary
- Standard calendar:

iff(month(this)>1, ROW.Salary+ROW.CumulativeSalary[time=this-1], ROW.Salary)

Customized calendar:

iff(this.Year.positionof(this.Month)>1,ROW.Salary+ROW.CumulativeSalary[time=this-1], ROW.Salary)

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: Excel CubeDimensionCountGet Function

June 21, 2022 by Lee Lazarow Leave a Comment

Have you ever written a TurboIntegrator process and needed to know how many dimensions are in a specific cube? Maybe you want to loop through each dimension to create a zero out view. You can do this by using the TABDIM function and searching for the first blank result, or you can simply use the CubeDimensionCountGet function.

The syntax of the function is:

CubeDimensionCountGet (CubeName)

This function will result in a numeric value that tells the number of dimensions within the cube.

Other uses of this code will allow you to define the number of arguments needed for a CELLPUT command, compare the number of dimensions between multiple cubes, or replicate an existing cube.

This approach will simplify your code by eliminating the need to create a loop.

FP&A Done Right: The Changing Role of the CFO

June 17, 2022 by Revelwood Leave a Comment

This is a guest blog post from our partner Workday Adaptive Planning, explaining strategy and vision for the Office of Finance.

Companies are asking more of their CFOs than ever before. They’re expected to lead environmental, social, and governance (ESG) initiatives, oversee finance transformation projects, and ensure their teams have the right skills for the future. That’s why finance leaders need the right technology.

The role of CFOs is changing as their responsibilities keep growing. “Today, CEOs and boards are looking to CFOs to help outperform peers while navigating crises,” said Terrance Wampler, group general manager, office of the CFO, at Conversations for a Changing World, a global Workday digital event. “CFOs are moving from a sole focus of maximizing shareholder value to addressing a much broader set of stakeholder values.”

These broader stakeholder values—and expectations—require a greater focus on sustainability, talent acquisition, and digital acceleration.

Simply put, companies are asking more of their CFOs—especially when it comes to having the right technology.

Growing Revenue

“Revenue is all about growth,” Wampler said. “Successful growth requires striking the right balance between quick wins within three months, medium-term operational improvements spanning three to nine months, and longer-term, more strategic initiatives across one- to three-year plans,” he said.

There’s a strong correlation between revenue growth and high-performing financial planning and analysis (FP&A) teams, according to a 2021 study by the Institute of Management Accountants (IMA). Companies with high-performing FP&A teams saw a 21% revenue growth, compared to just 4% growth among companies with the worst-performing FP&A teams.

What does a strong FP&A team do well?

“One of the most impactful things CFOs and FP&A teams can do is model multiple revenue scenarios,” Wampler said.

Workday can help them do that, Wampler explained: “With Workday, customers model and execute on short-, medium-, and long-term scenarios, incorporating financial and operational data and performing what-if comparisons.”

Controlling Costs

Reining in costs remains a primary concern for finance leaders: In a 2022 PwC survey, almost one-third (30%) of CFOs ranked reducing cost as a percentage of total revenue as a top priority.

When looking at organizations that are successful at optimizing spend and driving predictable cash flow, one of the biggest opportunities to manage discretionary spend lies with their suppliers and procurement practices.

As they grow revenue while containing costs, CFOs also must ensure that capital investments maximize ROI.

“A CFO is the guardian of the capital that he or she has been entrusted with,” Wampler said. “Planning in Workday can help organizations get the most return from their capital investments with improved forecasting of capital expenses, effective and efficient capital project planning, and, of course, fast and accurate reporting and analysis for the complete lifecycle of your capital assets. You can also plan and model capital asset acquisitions, automate depreciation methods, and gain complete visibility into the impact of capital on your financial statements.”

Meeting ESG Goals

Organizations and their CFOs face increasing pressure to deliver not only financial impact but environmental, social, and governance (ESG) impact as well. For 58% of corporations, there was a positive relationship between ESG and financial performance, according to a 2021 meta study by the NYU Stern Center for Sustainable Business and Rockefeller Asset Management.

Companies that have made ESG factors a priority can benefit from the ability to track sustainability data—not just within the organization but extending to third parties, as well. “Data you could track include things like a carbon footprint, climate change, even risk to the supply chain … all through a configurable dashboard,” Wampler said.

Recruiting and Retaining Talent

As the “Great Resignation” sees workers leaving their jobs in record numbers, CFOs must consider how they can best recruit and retain talent in a rapidly changing economy. Of employed U.S. adults, one in four plans to look for opportunities with a new employer once the pandemic subsides, according to a Prudential survey.

Employers need to leverage technology that can give them an edge in hiring.

“A good software user experience translates to a great employee experience,” Wampler said. That’s why Workday prioritizes developing products that improve employee engagement, in part by automating processes, he explained. With intelligent process automation, Workday enables teams to work smarter, not harder.

Machine learning (ML) and artificial intelligence (AI), along with the opportunity to learn new technology skills, are additional ways to keep employees engaged, Wampler added.

Beyond providing better technology solutions, a cloud-based platform also helps support workers outside of the office. As many employees have adapted to remote or hybrid work environments due to the pandemic, Workday’s robust functionality allows employees to perform their duties regardless of where they’re located and gives them additional flexibility, Wampler said.

The CFO Stakes

Employees, suppliers, customers, and communities can benefit when companies invest in transforming back-office processes and systems—suggesting that there’s a lot at stake today’s CFO.

The good news: 75% of CFOs say the pandemic has accelerated the digitization of their operations, according to the Hackett Group. “Next-generation technology is now considered table stakes not only to survive but to thrive in the new normal of uncertainty,” Wampler said.

IBM Planning Analytics Tips & Tricks: PAW Chart Multicolors

June 14, 2022 by Revelwood Leave a Comment

One of the visualizations in Planning Analytics is a column chart. This type of chart is a good way to compare items since all the lines are in proportion to each other.

An easy way to create a column chart is to simply create an exploration and change the visualization into a column chart. Here is an example of a stacked exploration that compares companies by scenarios and the related column chart:

Chart, histogram

Description automatically generated

This chart is great since it’s easy to compare the high and low values. However, it’s very hard to differentiate the sub-categories since all columns are the same color. In this case, it’s hard to compare Actuals vs. Final Budget.

You can configure your chart to use multiple colors via the following steps:

While in Edit mode, single click on your chart widget.

Select the option for “Fields” at the top, right corner. Not all visualizations use fields, so this may appear blank if you have not yet converted your widget to a chart.

Move the applicable dimension from the Length category into the Color category. In my example, color is associated with the scenario dimension.

Graphical user interface, text, application, chat or text message

Description automatically generated

Once moved, the same chart will look like this:

Chart, bar chart

Description automatically generated

This approach will allow you to use your color palettes (defined within the properties) to differentiate the dimensions within your stacked column chart and make your analyses easier.

Workday Adaptive Planning Tips & Tricks: Revenue Cohort Modeling

June 8, 2022 by Ailenette Cruz Leave a Comment

Do you know about cohort modeling in Workday Adaptive Planning?

A cohort model provides meaningful insight of data grouped into subsets based on any characteristic important to the company. The groups are called “cohorts.” Cohorts can be based on time (i.e. season, monthly, yearly), segment (i.e. commercial, residential), or size (i.e. low, medium, high), just to name a few. Cohorts follow a pattern of behavior that helps analysts project future trends. This can help companies focus efforts on lowering churn and optimizing revenue.

Cohort model vs. Regular model

Compared to a regular model, a cohort model provides a more granular view of the data. Projecting revenue at the cohort level helps companies understand outliers that would otherwise be missed in a regular revenue model.

For example, in a regular model, projected revenue shows overall revenue for the year and may show growth. However, if you dig further into the segments, you will see that one cohort, such as small businesses in the manufacturing industry, had a large decline in sales.

If this information was available early in the year, further decline could have been prevented and management could have strategized to avoid a blow to the bottom line in the following year. Maintaining and analyzing multiple cohort models on a regular basis is crucial for successful strategic planning.

Where to use cohort modeling

Subscription base revenue – tech, gaming, food, streaming, service, etc.
Contract billing – by milestone, period, ASC 606 amortization, etc
Seasonality base revenue
Freemium – lead conversion
Sales Rep capacity or conversion

How to build cohort models in Workday Adaptive Planning

Cohort model in Adaptive may comprise of multiple sheets. Below is an example of a cohort model structure.

1. Planned # of Customers/Cohort

Modeled sheet
Create number data entry column to input number of customers
Create calculated account and link the data entry column
Input by cohort dimension – this can be by month/period, type of cohort, etc (ex. Jan, Feb, Mar – the month new customers are acquired)
No timespan

2. Cohort Drivers/Curves

Modeled sheet
Create number data entry columns, display as % (ex. 12 columns representing 12 months of the year) – this is where to input cascading percentages (aka curve)
Create calculated accounts for each data entry column and link
No timespan

3. Revenue per product/customer by month

Modeled Sheet
- With timespan

4. Cohort Waterfall

Cube sheet
Create standard account as a trigger – in this case using active month designation
- The active month designation to trigger what month the cohort waterfall calculates for each acquisition month
Create calculated account with formula using iff and switch (this serves as an index match)

iff(isblank(ACCT.Cohort_Waterfall.Cohort_ActiveMonths),blank(),

iff(this.version.isactuals,blank(),

ACCT.NewCustomers_byCohort.No_Customers[Sales Region=this, Sales Channel=this, Order Type=this, Products=this, Cohort Acquisition Month=this]

switch(ACCT.Cohort_Waterfall.Cohort_ActiveMonths,

1,ACCT.Cohort_Drivers.M1[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

2,ACCT.Cohort_Drivers.M2[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

3,ACCT.Cohort_Drivers.M3[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

4,ACCT.Cohort_Drivers.M4[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

5,ACCT.Cohort_Drivers.M5[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

6,ACCT.Cohort_Drivers.M6[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

7,ACCT.Cohort_Drivers.M7[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

8,ACCT.Cohort_Drivers.M8[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

9,ACCT.Cohort_Drivers.M9[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

10,ACCT.Cohort_Drivers.M10[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

11,ACCT.Cohort_Drivers.M11[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

12,ACCT.Cohort_Drivers.M12[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

0)))

Cohort modeling is a powerful feature in Workday Adaptive Planning. Try it out and see what insights you can discover.

IBM Planning Analytics Tips & Tricks: PAW Chart Show Totals

Modern Accounting: 6 Essential Qualities for Surviving the Robot Uprising in Accounting

Optimizing Your F&A People

What Is an Exceptional Accountant?