• Skip to main content
  • Skip to footer
Revelwood Logo

Revelwood

Your SUPER-powered WP Engine Site

  • Who We Are
    • About Us
      • Our Company
      • Our Team
      • Partners
    • Careers
      • Join Our Team
  • What We Do
    • Solutions
      • Workday Adaptive Planning
      • IBM Planning Analytics
      • BlackLine
    • Services
      • Implementation Services
      • Customer Care
        • Help Desk
        • System Administration as a Service
      • Training
        • Workday Adaptive Planning Training
        • IBM Planning Analytics / TM1 Training
    • Products
      • DataMaestro
      • LightSpeed
      • IBM Planning Analytics Utilities
  • How We Help
    • Use Cases
    • Client Success Stories
  • How We Think
    • Knowledge Center
    • Events
    • News
  • Contact Us

Planning & Forecasting

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.
IBM Planning Analytics Tips: DefineCalc
  • 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:

IBM Planning Analytics Tips & Tricks: DefineCalc

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.”

IBM Planning Analytics Tips & Tricks: DefineCalc

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

IBM Planning Analytics Tips & Tricks: DefineCalc

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

IBM Planning Analytics Tips & Tricks: DefineCalc

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

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!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: MDX Syntax Explained

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

IBM Planning Analytics Tips & Tricks: TM1Ellist

Home » Planning & Forecasting » Page 4

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Budgeting Planning & Forecasting, IBM Cognos TM1, IBM Planning Analytics, IBM Planning Analytics for Excel, PAx, Planning & Forecasting

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.

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!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: 2022’s Top 3 Tips & Tricks Videos

IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

Home » Planning & Forecasting » Page 4

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Budgeting Planning & Forecasting, IBM Cognos TM1, IBM Planning Analytics, Planning & Forecasting, TM1

Modern Accounting: Adjusting Journal Entries

December 8, 2022 by Revelwood

This is a guest post from David Brightman at our partner BlackLine, explaining why it’s necessary to adjust journal entries. 

What Are Adjusting Journal Entries?

Adjusting journal entries are used to adjust a company’s financial statements and bring them into compliance with relevant accounting standards, such as generally accepted accounting principles (GAAP) or International Financial Reporting Standards (IFRS). The activity of adjusting journal entries is routinely performed by accountants to allocate income and expenses to the actual period in which the income or expense occurred or earned—a feature of accrual accounting.

Five Common Types of Adjusting Journal Entries

There are many different types of adjusting journal entries, but the five most common types are:

1)    Accrued revenue is revenue that has been recognized by the business, but the customer has not yet been billed. This type of revenue is common in service-related businesses, as services can be performed several months before a customer is invoiced. Revenue must be accrued, otherwise revenue totals would be understated, especially compared to expenses for the period. 

2)    Accrued expenses are those that have been incurred before they have been paid. For example, a company purchases supplies from a vendor but has not yet received an invoice for the purchase. Other examples of accrued expenses include interest payments on loans, warranties on products or services, and taxes.

3)    Deferred revenues indicate when a company receives payment in advance of work that has not yet been completed. This is common for professional firms that work on a retainer—such as a law or CPA firm. A client may pay in advance for work that is to be done over a period of time. When the revenue is later earned, the journal entry is reversed.

4)    Prepaid expenses need to be recorded as an adjusting entry. Many companies prepay rent for an entire year. The company will record the expense each month for the next 12 months to account for the rental payment properly. Without this, financial statements will reflect an unusually high rental expense in one month, followed by no rental expenses at all for the following months.

5)    Depreciation expenses, including depreciation expense and accumulated depreciation, need to be posted to properly expense the useful life of a fixed asset. Depreciation is a fixed cost and does not negatively affect cash flow, but the balance sheet would show accumulated depreciation as a contra account under fixed assets.

Given the nature of adjusting entries, they often impact both the balance sheet and the income statement. Adjusting entries are also used to correct financial errors and must be completed before a company’s financial statements can be issued. For example, something is capitalized and booked to a Fixed Asset account that, under company policy, should be booked to an expense account like Supplies Expense, or vice versa.

Where Do Adjusting Journal Entries Fit into the Financial Close Process?

At the end of each financial period, accountants go through all the prepaid and accrued expenses as well as unearned and accrued revenue and identify necessary adjusting entries.

This is often a time-consuming process that involves spreadsheets to track expenses and payments made against those expenses as well as revenue earned and payments received against that revenue.

These adjustments are often a result of the account reconciliation process during the financial close. They may also be detected by doing variance analysis of account balances to detect any unusual balance fluctuations.

How to Record Adjusting Journal Entries

When the need for an adjusting journal entry is identified, accountants prepare the journal entry to credit and debit appropriate accounts. In theory, the process for recording an adjusting journal entry can be broken into 3 steps:

1)    Determine the current account balance

2)    Determine what the current balance should be

3)    Record an adjusting entry

This is likely oversimplifying, since companies may have hundreds or thousands of adjusting journal entries to make each period, but it gives an overview of the process needed for each entry. In addition, adjusting journal entries should include supporting documentation, links to applicable policies and procedures, and be properly reviewed and approved before being posted.

Examples of Adjusting Journal Entries

One example is to accrue for unpaid wages at month-end. A potentially more intricate example may be rebate accruals. Rebates are payments made back to you from a supplier (or from you to a customer) retrospectively, reducing the overall cost of a product or service.

In this case, you may have an arrangement with a supplier to earn a quarterly rebate based on your overall spend with that supplier. Imagine the supplier’s policy is to pay the rebate at the end of the year. Then, from an accounting perspective, this may need to be accrued for when the rebate is earned, not when it is received.

When preparing the entry, it’s helpful to reference your company’s policy and procedure to ensure compliance, and it is best practice to attach supporting documents to the journal entry, like the contract and terms. This will help speed up the approval process, as well as any audit work later.

This blog post was originally published on the BlackLine blog.

Read more about Modern Accounting:

Modern Accounting: Highlights from Beyond the Black 2022

Modern Accounting: Does Your Accounting Team Have SMART Goals?

Modern Accounting: The Impact of Investing in Accounts Receivable

Home » Planning & Forecasting » Page 4

Filed Under: Financial Close & Consolidation Tagged With: BlackLine, Financial Performance Management, modern accounting, Planning & Forecasting

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

December 6, 2022 by Dillon Rossman

Revelwood’s latest IBM Planning Analytics Tips & Tricks video features Dillon Rossman, a consultant with our IBM Planning Analytics practice, demonstrating how to set up action buttons in IBM Planning Analytics for Excel (Pax).

In this video, Dillon shows your three approaches: 

  • Default approach
  • Creating the action button in a different workbook or sheet
  • Run a process and then go to another worksheet

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!

Watch more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: How to Set Up Synchronizations in IBM Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Filtering in IBM Planning Analytics Editor

IBM Planning Analytics Tips & Tricks: Global Settings in Overview Area of Exploration

Home » Planning & Forecasting » Page 4

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Planning & Forecasting, TM1

IBM Planning Analytics Tips & Tricks: How to Set Up Synchronizations in IBM Planning Analytics Workspace

November 29, 2022 by Dillon Rossman

Revelwood’s latest IBM Planning Analytics Tips & Tricks video features Dillon Rossman, a consultant with our IBM Planning Analytics practice, demonstrating how to set up synchronizations in Planning Analytics Workspace (PAW).

In this video, Dillon walks you through how to:

  • Change an exploration to a pie chart
  • Edit the synchronization properties
  • Select the dimensions you want to be synchronized
  • Perform synchronizations on both a sheet and a book

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!

Watch more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Filtering in Planning Analytics

IBM Planning Analytics Tips & Tricks: Global Settings in Overview

IBM Planning Analytics Tips & Tricks: Color in Planning Analytics

Home » Planning & Forecasting » Page 4

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: IBM Cognos TM1, IBM Planning Analytics, Planning & Forecasting, TM1

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Borders in Preview

November 1, 2022 by Lee Lazarow

Tips & Tricks

IBM Planning Analytics Workspace (PAW) allows you to easily create selector widgets that can be used for updating the data in your PAW book. This is done by adding a dimension as a selector widget.

You can edit the list of elements (e.g., the set) and the way the list appears. The selector widget allows you to easily select an element that is synchronized to your charts and explorations. When selecting the element, a border appears around the widget.

Diagram

Description automatically generated with low confidence

While this border offers features such as the ability to expand the screen, it clearly makes the list into a separate section of the page. You can remove the border by using the property called “Show selected border in preview” (within the area called General, Appearance). Here is the same list without the border enabled:

Text

Description automatically generated

This property will allow you to create the perception of a single page and not a set of independent objects.

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: Rounded Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: MDX Syntax Explained

IBM Planning Analytics Tips & Tricks: Global Settings in Overview Area of Exploration

Home » Planning & Forecasting » Page 4

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: IBM Cognos TM1, IBM Planning Analytics, IBM Planning Analytics Tips & Tricks, Planning & Forecasting, TM1

IBM Planning Analytics Tips &Tricks: Rounded Buttons in Planning Analytics Workspace

October 25, 2022 by Lee Lazarow

IBM Planning Analytics Workspace (PAW) allows you to easily create buttons that can be used for navigation and to run TurboIntegrator (TI) scripts. This is done by dragging the Action Button widget onto the sheet.

By default, the button appears as a rectangle.  

Shape

Description automatically generated

You can make the button more rounded by using the property called “Corner Radius” (within the area called General, Appearance).  Here is the same button with a corner radius setting of 90:

Shape, rectangle

Description automatically generated

You can also update the height of the button to make it appear even more rounded:

Shape, circle

Description automatically generated

From there, you can further customize the formatting associated with colors, fonts, and text size.

This property will allow you to define various ways to incorporate simple “press this button to…” approaches within your PAW books.

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: Working with Two Time Zones in Google Calendar

IBM Planning Analytics Tips & Tricks: New Filter Option in Set Editor

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

Home » Planning & Forecasting » Page 4

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Planning & Forecasting, TM1

Three Reasons You Need IBM Planning Analytics / TM1 Training

October 21, 2022 by Revelwood

Whether you are new to IBM Planning Analytics / TM1 or you consider yourself an all-star, you could still benefit from training. Over our 27+ years of designing, implementing and maintaining IBM Planning Analytics / TM1 applications and environments, we’ve found there are three primary reasons for investing in training. 

1. Staff Turnover in the Office of Finance

According to Gartner, the U.S. total annual turnover rate will jump by nearly 20% from the pre-pandemic average. Maybe your Planning Analytics administrator left your organization. Maybe your organization is growing rapidly and needs to add more TM1 developers and administrators. Regardless of the reasons, training is a smart move when you have new team members joining the organization or taking on new roles in your company.

People often give their employers two weeks’ notice that they are leaving their job. That does not leave enough time for a company to hire a new employee for the position. Even if you are temporarily assigning the role to a current employee, two weeks is still not enough time to get up and running on your Planning Analytics / TM1 implementation. 

There’s also an additional complication – how accurate and up-to-date is your Planning Analytics documentation? Old and out-of-date documentation makes it even more difficult for new TM1 administrators and developers to get up to speed. 

2. Expanding use of Planning Analytics / TM1 to Extended Planning & Analysis (xP&A)

xP&A is an enterprise planning strategy that combines and extends financial and operational planning. Gartner describes it as “taking the F out of FP&A).” xP&A offers “cross-organization leaders an ‘extended,’ holistic view of their company’s operations so that they can pivot the business with greater agility and more rapidly model future business scenarios.” [Gartner].

This can mean using IBM Planning Analytics / TM1 for:

  • Human resources
  • Sales
  • Marketing
  • Operations
  • Supply Chain
  • IT
  • Research and development

There are many benefits to xP&A. The business unit leaders in these functional areas may not be the Excel experts that the Office of Finance are. Before rolling out Planning Analytics applications to other areas, you should make sure new developers and end users receive training. It will make a significant positive impact on your roll out and eventual user adoption and satisfaction. 

3. Upgrading from TM1 to Planning Analytics

Migrating from TM1 to Planning Analytics is a big opportunity to modernize your planning environment. It’s not just a standard update or migration. You’ll learn a new, rich data exploration and visualization experience in Planning Analytics Workspace. You’ll have a new Excel add-in, Planning Analytics for Excel (PAx). And you’ll leverage hierarchies – the ability to turn attributes into dimensions for faster and more flexible data analysis.

Migrating to Planning Analytics requires training. Planning Analytics is powered by TM1 but delivers a whole new environment for your planning activities.

Revelwood’s Planning Analytics / TM1 Training Options

Revelwood has offered TM1 and Planning Analytics training for decades. We’ve refined our approach, expanded our best practices, and have the answers to all your TM1 and Planning Analytics questions. 

We offer several options for training, including: 

  • IBM Planning Analytics Fundamentals
  • IBM Planning Analytics Custom Report Building
  • Personalized Training Courses, working with your data and environment. Example courses include:
    • IBM Planning Analytics / TM1 Administrator Training
    • IBM Planning Analytics / TM1 Developer Training
    • TM1 10.2.2 and earlier versions User Training
    • Customized IBM Planning Analytics User Fundamentals Training
    • Customized IBM Planning Analytics Custom Report Building Training

All training options are virtual, enabling your team members worldwide to participate in training. Take advantage of our Planning Analytics experts! Contact us about training options.

Home » Planning & Forecasting » Page 4

Filed Under: FP&A Done Right Tagged With: Financial Performance Management, IBM Planning Analytics, Planning & Forecasting, TM1

The Cost of Spreadsheet Errors

October 20, 2022 by Revelwood

News & Events

Using disconnected spreadsheets for forecasting, budgeting, planning and reporting creates a host of issues, including:

  • Wasting weeks every year manually consolidating a mass of individual spreadsheets
  • Inability to easily model potential future scenarios or answer what-if questions
  • Measuring actual spend against plan is a major chore
  • Talented finance staff spends too much time on low-level, non-value add activities.

These challenges are ongoing and exponential. The longer you rely on spreadsheets for “collaborative” planning, the higher the costs are. Most of these costs are “soft” costs – time, manpower, delays. But there are hard costs too – in the form of errors. Some of these errors might be small. Some might have a significant impact on your company. Here are a few examples:

Famous Spreadsheet Errors

J.P. Morgan’s “White Whale” debacle was a result of a spreadsheet user error. The firm was using Excel spreadsheets to model Value at Risk (VaR) for the Chief Investment Office. The model was built by copying and pasting data from one spreadsheet to another. Several cells in this model contained faulty equations due to a failed copy-and-paste process. This led the firm to severely underestimate the downside risk of one of its credit portfolios, which led to approximately $6.5 billion in losses and fines. 

The municipality of West Baraboo, Wisconsin, relied on spreadsheets to calculate how much its borrowing would cost. The spreadsheet had a sum that was missing one cell. This resulted in West Baraboo underestimating the total cost of a 10-year bond, meaning the village had to pay $400,000 more interest on the bond than it originally thought.

Lazard, Ltd. Is an investment bank that advised SolarCity Corp. The bank had a computational error in a spreadsheet. This error led Lazard to discount the value of SolarCity Corp by $400 million. This happened when Tesla Motors Inc. was purchasing SolarCity Corp.

When Vista Equity Partners purchased Tibco Software, Tibco shareholders received $100 million less than originally anticipated. This was a result of a spreadsheet error that overstated Tibco’s equity value.

The chances might be slim of your company suffering one of these financial disasters. But if you are still relying on spreadsheets for forecasting, budgeting, planning and reporting, you are likely to be experiencing one – if not many – of the nine circles of spreadsheet hell. 

What are they? Download this eBook, The Nine Circles of Spreadsheet Hell, to learn them – and the hidden costs of spreadsheets. 

Home » Planning & Forecasting » Page 4

Filed Under: News & Events Tagged With: Financial Performance Management, Planning & Forecasting, Workday Adaptive Planning, Workday Adaptive Planning Tips & Tricks

  • « Go to Previous Page
  • Page 1
  • Page 2
  • Page 3
  • Page 4
  • Page 5
  • Page 6
  • Interim pages omitted …
  • Page 17
  • Go to Next Page »

Footer

Revelwood Overview

Revelwood helps finance organizations close, consolidate, plan, monitor and analyze business performance. As experts in solutions for the Office of Finance, we partner with best-in-breed software companies by applying best practices guidance and our pre-configured applications to help businesses achieve their full potential.

EXPERTISE

  • Workday Adaptive Planning
  • IBM Planning Analytics
  • BlackLine

ABOUT

  • Who We Are
  • What We Do
  • How We Help
  • How We Think
  • Privacy

CONNECT

World Headquarters

Florham Park, NJ | 201 984 3030

European Headquarters

London & Edinburgh | +44 (0)131 240 3866

Latin America Office

Miami, FL | 201 987 4198

Email
info@revelwood.com

Copyright © 2025 · Revelwood Inc. All rights reserved. Revelwood® and the Revelwood logo are registered marks of Revelwood Inc.