Budgeting Planning & Forecasting

IBM Planning Analytics Tips & Tricks: Excel EOMONTH

February 21, 2023 by Lee Lazarow

Thirty days has September …

Do you sing that out loud every time you want to determine the number of days in a month? If so, do you have to pause and think about the end when defining the last day of February? More importantly, do you have to write complex logic into your spreadsheet to define this? If so, you may want to learn more about the EOMONTH function in Excel!

The EOMONTH function is designed to tell you the last day of a month – either the current month, a future month, or a historical month. The function has two parameters:

=EOMONTH(start_date, months)

The start_date parameter defines the initial date of your calculation
- Microsoft recommends using the DATE function for this value
The months parameter defines the number of months from the start date
- A zero value results in the current month
- A positive value results in a future month
- A negative value results in a previous month

Graphical user interface, application, table, Excel

Description automatically generated

This approach will help you in various planning models such as …

Workforce planning – when to start calculating benefits
Asset planning – when to begin the depreciation calculations

This can also be used to define start dates by simply adding 1 to the result and determining the first day of the next month. Now we just need a formula to help us when someone obnoxiously tells us to “just perform the task on each day that ends in Y.”

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!

IBM Planning Analytics Tips & Tricks: DefineCalc

January 3, 2023 by Revelwood

IBM Planning Analytics for Excel (PAx) version 80 introduced a new function called “DefineCalc.” This function allows a user to create a custom calculation using TM1/Planning Analytics data via the use of MDX.

There are a series of parameters, and the key ones are:

sDataSource – The connection URL used when adding your connection to Pax. You can enter “*”if you would like the calculation to apply to all data sources.

sServerName = Your TM1/PA server name. You can enter “*” if you would like the calculation to apply to all servers.
sCalcMUN = The name for the calculation. This will be referenced in your DBRW formulas.
sExpression = The MDX expression being used for the calculation (note: if you would like to remove a custom calculation then set this variable to be blank).

One use case for this function is to grab the total value of a subset. In the example below, we have a report that uses a subset called “DefineCalc Example”:

IBM Planning Analytics Tips & Tricks: DefineCalc

IBM Planning Analytics Tricks: DefineCalc

The following DefineCalc formula was used to create a total:

Now, let’s break this apart by each parameter:

sDataSource – “*”
- This will apply to all data sources (if multiple)
sServerName = “*”
- This will apply to all TM1/PA servers (if multiple)
sCalcMUN = “[bpmAccount].[Example 1]”
- This first half of this value defines the bpmAccount dimension
- The second half of this value consists of a unique name within the bpmAccount dimension
sExpression = “AGGREGATE(TM1SubsetToSet([bpmAccount],’DefineCalc Example’))”
- This is an MDX expression that will give the total of the “DefineCalc Example” subset within the bpmAccount dimension

The result of this formula is populated in the cell as “Example 1.”

We can now reference “Example 1” within the DBRW formula as an element in the bpmAccount dimension.

The =Sum formula of the three data rows matches the DBRW that references the DefineCalc function.

This shows one common use case for the new DefineCalc function, but the use MDX allows for countless other applications!

IBM Planning Analytics Tips & Tricks: Top 3 Blog Posts of 2022

December 27, 2022 by Revelwood

What was your favorite IBM Planning Analytics Tips & Tricks blog post in 2022? We did some analysis and determined the three most popular PA Tips & Tricks blog posts. They are:

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

Read Dillon Rossman’s blog post to learn how to use MDX to create dynamic subsets based on cube values.

2. IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via a Dynamic Report

This post presents an interesting situation. If your Planning Analytics model contains a cube that uses an index or slot dimension, you will need to create a template that allows your users to add data to an unused index.

3. IBM Planning Analytics Tips & Tricks: Pax – Rebuild Book vs Recreate Book

In this post, Lee Lazarow explains the difference between the “Rebuild Book” button and the “Recreate Book” button in Planning Analytics for Excel (Pax).

Are you receiving our IBM Planning Analytics Tips & Tricks every Tuesday? If not, sign up to subscribe to our newsletter. You’ll get the latest post direct to your inbox.

FP&A Done Right: ESG – An Imperative for Growth

November 18, 2022 by Revelwood

As part of our series on ESG reporting, we are featuring guest blog posts from our partners. This post from Workday Adaptive Planning highlights thoughts from finance leaders on ESG and more.

How can companies “walk the talk” to create value for society and improve business outcomes by investing in environmental, social, and governance (ESG) efforts? Barbara Larson, a finance leader at Workday, joined McKinsey & Company partner Giulia Siccardo and Ann Dennison, executive vice president and CFO at Nasdaq, to discuss this topic and more at Conversations for a Changing World.

Almost 9 in 10 (87%) people believe a company should create value for society, not only its shareholders. But only half think that companies actually place people over profit, said Giulia Siccardo, partner and environmental, social, and governance (ESG) expert at McKinsey & Company. Siccardo added there’s much at stake in closing that gap, because companies that invest in ESG are able to deliver higher returns to shareholders.

At our digital event Conversations for a Changing World, Siccardo teed up a discussion on how companies can “walk the talk” when it comes to ESG. Ann Dennison, executive vice president and CFO at Nasdaq, and Barbara Larson, senior vice president of accounting, tax, and treasury at Workday, shared deeper insights on how organizations can roll out these efforts.

Dennison explained that Nasdaq has begun helping to shape ESG reporting in the U.S. “We believe in a sustainable future, and we believe we can be part of helping to build a sustainable future,” Dennison said of Nasdaq, which has been carbon neutral for three years.

As one example of its progress: In August, the SEC approved Nasdaq’s new rule requiring listed companies to annually disclose board-level diversity statistics using a standardized template. “We believe this is about transparency that will help build a better reporting framework for the future, and help drive knowledge and diversity across the listed companies,” Dennison said.

ESG isn’t a nice-to-have. For Nasdaq, Dennison said, ESG is imperative for growth. “Gone are the days of the investor being the only stakeholder,” she said. An organization’s stakeholder base now includes its customers, employees, and communities.

“If you want to grow your investor base, you need to be focused on ESG,” Dennison said.

Nasdaq provides several solutions to help companies do that. With its ESG Data Hub, investment managers enter their diversity data, while asset owners assess that data to determine how to allocate their dollars. Nasdaq OneReport assists corporate clients in navigating the reporting complexity of ESG. And with Nasdaq’s carbon removal marketplace Puro.earth, corporate clients can procure offsets to neutralize their carbon footprint.

To bolster its own ESG reporting, Nasdaq placed its ESG function within its finance function within the past year. That shift in its ESG reporting structure is part of Nasdaq’s long-term vision, Dennison said, “to fully leverage our data across the organization.” For instance, Nasdaq has used Workday data to get a holistic look at its suppliers’ diversity. “Without that data, we can’t have a plan,” Dennison said.

Dennison shared three strategies for CFOs to meet their own ESG goals:

“ESG has to be part of your overall business strategy, not a side job,” Dennison said. It should be part of board-level conversations and objectives.

Think about the long-term strategy. “Then break that down into smaller pieces in the short term,” shared Dennison. That should include identifying near-term key performance indicators.

“Use your data in the most powerful way,” she explained. Automate where you can.

Interested in learning more? Watch the full session here.

More from Workday Adaptive Planning:

FP&A’s Role in ESG Planning and Reporting

Planning for a Sustainable Future: How Organizations Can Deliver Data-Driven Results

This blog post was originally published on the Workday Adaptive Planning blog. https://blog.workday.com/en-us/2021/finance-leaders-discuss-why-esg-imperative-business-growth.html

FP&A Done Right: Approaches to Long-Term Planning

November 14, 2022 by Lee Lazarow

“If you fail to plan, you are planning to fail.” – Ben Franklin

In our collective rush to react to ever-changing marketplace dynamics and shifts in the economy, it’s easy to focus on short-term plans, to the neglect of long-term planning. Today’s leaders need to have several plans – short-term, medium-term, and long-term.

Different plans for different needs

How do these plans differ? A short-term plan is designed to show granular details for a limited time frame. This is often updated monthly, although we have some clients updating their plans on a weekly basis. One of our clients follows a process where local managers update their plans on Mondays and Tuesdays, have the regional managers review the data on Thursdays, and allow senior management to analyze and assess the data on Fridays. Each Monday they start the process over.

Most organizations utilize a medium-term plan that looks out anywhere from a few quarters to a full year. Most people will think of this as a standard monthly forecast with data at a bit more of a higher level, but still somewhat details.

A long-term plan often goes out multiple years. Many companies create a 5-year plan, although some industries such as entertainment and pharmaceutical often create 20-25 year plans. A long-term plan is a high-level view of the business. It’s not nearly as granular as short, or even medium-term plans. The plan does not get down to the level of looking at a GL account or a customer. It’s a measuring tool and a defined way of reviewing the progress of the company. In short, long-term planning helps to set the company’s direction.

The essentials of long-term planning

The long-term plan gives you guidance on how to answer several questions, including:

How can we expand the company?
How can we look into acquisitions?
What products, geographies, and verticals can we or should we add?
What products no longer make sense?
How do debt payments impact cash flow?
What type of labor, buildings, locations, and equipment do we need?

A long-term plan can be considered a proactive approach to risk mitigation, enabling companies to plan, think ahead, prepare for, and lessen the impact of potential negative effects. At Revelwood, we recommend two approaches to long-term planning: the growth percent approach and a driver-based approach.

We often see both of these methods used when performing long-term planning in IBM Planning Analytics with Watson:

Growth percent approach

The growth percent approach allows you to adjust groups of data (accounts, departments, etc.) by increasing or decreasing the values from the previous year. Some clients prefer to simply use a single percentage (example: reduce all expenses by 2% each year for the next five years) whereas some clients prefer to include more variation (example: reduce utilities expenses by 2% next year, by 3% the following year, and by 4% for the next three years). But no matter what level of detail is used, Planning Analytics’ powerful scripting tool will perform the entire long term plan in a matter of seconds.

Driver-based approach

A driver-based approach uses operational activity to calculate key variable revenues and expenses. This approach allows you to simplify the input by defining a set of drivers and creating calculations that use the drivers. For example, a single driver of “units sold” can be used to immediately calculate revenue, COGS, and some of your variable expenses using the tool’s efficient calculation engine.

Mitigate risk with long-term planning

Long-term planning is your company’s assurance against planning to fail. There’s a reason why Franklin’s quote has lasted through the years. And it should be the motto of every planning team.

Learn more about long-term planning by watching our on-demand webinar – Long-Term Planning in IBM Planning Analytics.

This post originally appeared on IBM’s Journey to AI blog.

Modern Accounting: Does Your Accounting Team Have SMART Goals?

November 3, 2022 by Revelwood

This is a guest post from our partner BlackLine, explaining SMART goals and how they can help accounting managers.

One of the most widely used—and effective—approaches to goal setting is called SMART, which stands for Specific, Measurable, Attainable, Relevant, and Time-Bound. This helps you and your teams create clear goals with defined and attainable objectives.

Many accounting managers struggle with the annual goal-setting process. It can be hard to quantify the work of the folks who do the counting—which is ironic, but quite common outside of sales teams. Accountant routines can vary significantly and tend to be driven by outside factors.

But setting smart goals for accountants is a critical part of ensuring a successful year. They help you and your teams be intentional about what you want to focus on and accomplish and create alignment across the entire F&A organization.

How to Write SMART Goals for Accountants

Make Your Goals Specific

The more specific a goal is, the more attainable it will seem, and the more likely you’ll achieve it because you’ll know exactly what you need to accomplish in this area.

It’s okay to start with a larger goal, but then break it down into smaller goals that are more specific. Determine why this goal is important—for the individual, the team, and the broader F&A organization—and then talk about the short and long-term impact of achieving it.

To get even more specific, determine the exact outcome you want.

Make Your Goals Measurable & Attainable

Next, quantify your goals so you’ll know how to measure process and when they’ve been accomplished. This is often the most difficult part of setting SMART goals for accountants, and if a goal doesn’t seem quantifiable, try rephrasing it so it’s possible to measure.

It’s equally important to ensure the goal is attainable. Stretch goals are great, but impossible goals will only lead to overwhelm and frustration. Make sure the time frame is reasonable and identify anything that could get in the way of achieving the goal.

Removing those barriers should be the first step.

Make Your Goals Relevant & Time-Bound

Every goal must have importance to the broader organization as well as the individual. Uncover the why here, so the reason this is a priority is clear. This can also increase the individual or team’s motivation to achieve this goal.

Creating a reasonable deadline for each goal is also critical, along with regular check-ins to ensure you’re on track. It’s okay to have some flexibility in adjusting this deadline as needed, especially if things come up and get in the way.

This is, however, why it’s so important to identify and remove those barriers as the first steps toward the goal.

Strategies for Effective Goal Setting

Here are three strategies to incorporate into your goal-setting process that will make your accounting and finance teams far more effective in the future.

Take a Project-Based Approach

Most of us have down time here and there that we could utilize to work on a project. Talk to each of your team members about coming up with a project that contributes to their own development, helps them connect with others, and causes them to look at their job in a new way.

For example, a Senior Staff Accountant could conduct a skills training to help accountants keep up with changes in the industry. Or, maybe you believe your AR team could be more efficient but you’re not sure. Why not give them a goal to identify areas they can improve? You may be surprised at how many great ideas surface that could save the team some time.

Create Opportunities to Strategically Develop New Skills

With the rise of new tech like robotic process automation, artificial intelligence, and blockchain, strategically developing skills to prepare for the future of finance is more essential than ever.

The exceptional accountant of tomorrow needs to begin cultivating effective communication abilities, data analysis, business acumen, and creativity — today. These capabilities will equip teams to deliver predictive insights for leadership, drive data-based decisions, and provide expert counsel.

Developing opportunities for accounting and finance professionals to problem-solve more creatively, learn new technology, and build relationships with other departments can make a massive difference on an individual and team level.

Carefully Craft Your Culture

A carefully crafted culture creates competitive advantage. It improves quality of work, boosts productivity, engagement, and retention, and reduces stress and healthcare costs.

But if this isn’t a current focus at your company, where do you even start?

A team brainstorming session can be an excellent first step. Involving your people in culture discussions can make them feel invested and as important as they really are. They’re also the ones who will be able to quickly identify what needs to change, along with the most effective approaches.

Give your leaders the responsibility of planning regular team-building events. This can be anything from fun, off-site events to a team appreciation lunch at the office. The goal should always be to bring the team closer and help them maintain better relationships, generating understanding and a deeper respect for each other.

Put Your People First

Done right, setting smart goals for your accountants can create a shared vision that makes your teams feel inspired and connected. And when the strategies within that vision meaningfully contribute to the entire organization, you can count on a higher level of buy-in.

It all comes down to putting your people first and setting them up for success. If you continually look to the future of finance and equip your teams for the new digital landscape, your F&A organization will thrive.

Creating smart goals and creating a great culture can help F&A teams retain top talent, which is a challenge in today’s business environment.

This blog post was originally published on the BlackLine blog.

IBM Planning Analytics Tips & Tricks: 21/21/21

September 20, 2022 by Lee Lazarow

Some of you may remember an Excel feature that we wrote about a while back, which explained how the DATE function will convert invalid dates into real ones. For example, DATE(2020,7,35) will be converted to August 4, 2020, since there are not 35 days in July.

Here is where the fun begins!

This week includes the 21^st day of the month of September
September is the 9^th month of this year, which is also the 21^st month of last year
Last year was 2021

Put this all together and we have another special event happening this week …

On September 21, 2022,

the resulting date will be 21/21/21

Graphical user interface, application

Description automatically generated

And if we take it a step further …

At 9:21p and 21 seconds on 9/21/2022,

the resulting time will be 21:21:21 on 21/21/21

This is why we love Excel!

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!

FP&A Done Right: Forecasting Revenue for Services-Based Businesses: A Growth Factor

August 19, 2022 by Revelwood

More than half of professional services firms can’t forecast project revenue beyond six months. Workday’s Mark David and Justin Joseph share insights into how organizations can get revenue forecasting right, even when business is anything but usual.

What is it about revenue forecasting that can be so challenging? For people-based industries such as professional services, their project revenue is based on talent supply. And people—including all 109 billion of them who have ever lived on Earth—can be difficult to predict.

“People are highly variable,” said Justin Joseph, senior director of product strategy at Workday. “They aren’t always available. They go on vacation. And, as we’ve experienced the past few years, people leave, and there can be skills shortages. So there’s a lot of variability in how professional services companies generate project revenue.”

Beyond the variability of their employees and the impact of unprecedented trends and events, professional services firms also deal with different systems in different parts of the organization. These silos can cause data to be inconsistent and inaccurate—challenges that only get worse as an organization grows.

“The larger an organization, the more complex things get,” said Mark David, vice president of solution management at Workday. “Organizations then become more reliant on processes to manage projects and people, but that requires accurate data from disparate places.”

In this episode of the Workday Podcast, we’re 100% focused on revenue forecasting for professional services, with guests Joseph and David. They share trends impacting firms, the pros and cons of different types of forecasting, and how firms can start to solve their challenges to better plan and forecast.

Here are a few highlights of our conversation, edited for clarity. Be sure to follow us wherever you listen to your favorite podcasts, and remember you can find our entire podcast catalog here.

“The reason most companies can’t forecast their revenue more accurately is because they have different systems and data across their lines of businesses and services. And all those different systems mean that you have data that’s going to be wildly inconsistent. You’ll need a lot of integrations to pull this data together to make sure that you have an end-to-end process for revenue forecasting.” —Justin Joseph
“A year ago, a customer who runs a 5,000-employee professional services firm told me the one thing he needed was a good revenue forecast more than anything else right now. As you can imagine, this was especially needed with what’s happened over the last few years, which have made forecasting where your business is going even more difficult.” —Mark David
“With unexpected scenarios, you’re following the exact same processes as expected scenarios, but you have to forecast at a much faster pace and much more frequently because your assumptions are changing so rapidly, maybe hour by hour or day by day. How quickly can you pull this data together and then model it and share it out? It may sound contradictory, but they’re similar. Speed is ultimately what’s different.” —Justin Joseph

This blog post was originally published on the Workday Adaptive Planning blog.

IBM Planning Analytics Tips & Tricks: Adding and Editing Connection URLs in Planning Analytics for Excel

August 2, 2022 by Marc Assenza Leave a Comment

Do you know how to add a new Connection URL or Edit an existing Connection URL in Planning Analytics for Excel? Here are the steps to follow:

Adding a Connection URL

Once Planning Analytics for Excel has been launched, go into the IBM Planning Analytics ribbon click on the “Connect” button. Click the “New Connection” button within the drop down menu.

The following window will appear.

Graphical user interface, text, application, email

Description automatically generated

Datasource type – This is a dropdown containing two selections: IBM Planning Analytics and IBM Cognos Analytics. For this example, we are connecting to IBM Planning Analytics.

Connection URL – The connection URL connects Planning Analytics for Excel to a Planning Analytics Workspace server. If you are connecting to a local installation of Planning Analytics Workspace, this will contain either the IP address of your Planning Analytics Workspace server or the machine name of your Planning Analytics Workspace server, examples could look as follows:

http://12.345.77.89
http://mypawserver.mycompany.com

If you are connecting to an IBM Cloud Planning Analytics Workspace Server, the connection URL would look something like this:

https://mycompanyname.planning-analytics.ibmcloud.com/

Friendly name – The friendly name is a user defined setting in the sense that you give it a name that people within the organization would recognize, and it would be something meaningful to them, an example could be:

mycompanyname DEV Server

Once the information is entered, press the Test Connection button to confirm your connection. A successful connection will result in the following message:

A picture containing graphical user interface

Description automatically generated

Once completed, click the “Save” button.

Editing a Connection URL

Occasionally you may need to edit an existing connection URL. This may entail a change to your internal “Friendly name” or a change to the IP address of the server. Here are the steps to follow:

Once Planning Analytics for Excel has been launched, go into the IBM Planning Analytics ribbon click on the “Options” button.

Graphical user interface, application, table, Word

Description automatically generated

Click on the option for “IBM” on the left-hand side of the window.

You will see a list of all your existing connections. Select the connection you want to edit and click the “Edit” button at the bottom of the screen. You can then make the necessary adjustments and retest the connection.

IBM Planning Analytics Tips & Tricks: Excel EOMONTH

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: DefineCalc

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Top 3 Blog Posts of 2022

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

2. IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via a Dynamic Report

3. IBM Planning Analytics Tips & Tricks: Pax – Rebuild Book vs Recreate Book

Read more IBM Planning Analytics Tips & Tricks: