Budgeting

IBM Planning Analytics Tips & Tricks: Financial Rules

August 6, 2019 by Lee Lazarow Leave a Comment

Did you know one of the great things about Excel is that there are many built-in functions that calculate values associated with specific industries? One of these industries is the financial industry where Excel is sometimes used to calculate values associated with annuities. But did you know that some of these formulas are also built into Planning Analytics for use within rules?

Here are three annuity formulas that can be used in Planning Analytics rules:

The FV formula returns the value of an annuity at the time of the last payment.

FV(payment, interest, periods)

Payment = The amount of the payment made per period.

Interest = The interest rate paid per period.

Periods = The number of periods in the annuity.

The PV formula returns the initial or principal value of an annuity.

PV(payment, interest, periods)

Payment = The amount of the payment made.

Interest = The interest rate paid per period.

Periods = The number of periods in the annuity.

The PAYMT formula returns the payment amount of an annuity based on a given initial value or principal, an interest rate, and a number of periods.

PAYMT(principal, interest, periods)

Principal = The present value (total amount) that a series of future payments is worth now.

Interest = The interest rate paid per period.

Periods = The number of periods in the annuity.

All of these formulas assume that payments are made at equal intervals of time (when applicable) and that they are made at the end of each period.

Excel has a lot of great built in functions, so why can’t Planning Analytics? Well, as you can see … it does!

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!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

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

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

IBM Planning Analytics Tips & Tricks: IFERROR

July 30, 2019 by Lee Lazarow Leave a Comment

Did you know one of the great things about creating TM1 Web sheets is that you simply need to create an Excel sheet and then press a button called Publish? This will convert your Planning Analytics formulas into a web-based interface that your users can easily access.

In addition to the Planning Analytics formulas, IBM is continuing to expand the use of Excel’s native formulas. Planning Analytics version 2.0.7 introduced the use of the IFERROR formula into TM1 Web sheets.

The IFERROR formula determines if an error exists and then returns an alternative value, such as a text message, in place of the error message. The error checks include any of the following results: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

=IFERROR (value, value_if_error)

Here is an example of the IFERROR formula within a spreadsheet:

=IFERROR ( A1/B1, “There is an error in the calculation”)

The result of this formula will eliminate hard-to-read results such as #N/A or other results that could make your Web sheet less user friendly.

IBM continues to expand the Excel formulas that can be used in TM1 Web and Revelwood will continue to keep you updated with these additions.

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

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

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

FP&A Done Right: Beware of Budgeting

July 26, 2019 by Brian Combs Leave a Comment

“Not to beat around the bush, but the budgeting process at most companies has to be the most ineffective practice in management. It sucks the energy, time, fun, and big dreams out of an organization. It hides opportunity and stunts growth. It brings out the most unproductive behaviors in an organization, from sandbagging to settling for mediocrity. In fact, when companies win, in most cases it is despite their budgets, not because of them.” – Jack Welch, former Chairman and CEO of General Electric

That is a pretty strong statement, but I bet many of you are smiling. You know, that uncomfortable smile you make when someone says something that hits a little too close to home. As an FP&A guy who has spent plenty of time building those budgets he’s talking about, that quote certainly speaks to me. He makes some great points though. Despite that, budgeting is still deeply embedded in our corporate culture. As you embark on the 2020 planning season, this is a good opportunity to rethink your process. Let’s examine a few of the problems with traditional budgeting.

Time Consuming and Costly

I’m preaching to the choir on this one. You know how much time and effort is spent on the budgeting process. There are typically multiple passes that include all levels of the organization, presentations to senior management where we “defend” our budget, and the thought that more is somehow better. More schedules, more pages in the deck, more passes, more reviews. It’s maddening. We capture more detail than anyone could possibly know in the future and many of us still compile it using Excel (don’t get me started on that one…). It is difficult to get timely information that you can use to help build a reasonable budget.

Quickly Irrelevant and Outdated

Do you make it through half of the plan year with a relevant business plan still? If so, you are in the minority. I used to feel as if I was just going through the motions knowing that as soon as it was finalized, it was useless. It simply became a method to determine my bonus, not a method for driving the business forward.

Business Finance conducted a survey several years ago and they asked respondents to tell them when their current year’s budget became outdated. Based on my experience, these numbers still hold true. Take a look at the responses:

28%: Before the plan year begins

48%: 1-3 months in

67%: 4-6 months in

70-75%: Before 2^nd half of year

What are doing? Why do we continue this process?

Financial Process Largely Disconnected from Specific Drivers

How often are you building your plan with driver-based accounts? Are you starting with your line/operation managers and asking them what they can actually achieve next year? If you are, great! What I often see, however, is a disconnect between the P&L, oftentimes created in a vacuum, and operations. We talk about our plan in terms of YoY growth rather than focusing on the macro and micro indicators that surround us today. We build plans with months and quarters in mind while the business may be run by weeks or days or cycles. If you aren’t focusing on the specific drivers of your business, you risk creating an unattainable plan and you will spend the entire year making up variance analysis comments.

There are several other challenges with traditional budgeting that I’ll discuss in my next blog. Then, we will talk about alternatives to this and what our next steps can be. For now, just know that we can help show you another way. Decide right now that this will be the last traditional budget you do. 2020 is it! Give us a call. We’re here to help.

IBM Planning Analytics Tips & Tricks: Rule Concepts that are often Forgotten

July 23, 2019 by Lee Lazarow Leave a Comment

Did you know that rules are used in TM1 to perform live calculations without the need for any manual intervention or on-demand scripts? This is a very powerful aspect of Planning Analytics and, when used properly, increase the value of the tool for your users.

There are some aspects of rules that I often see people forgetting. Here are some friendly reminders about how to use these components properly:

Division

TM1 offers two different approaches to write a division formula via the “slash”. Not only do I often forget the difference between the two slashes, but I also sometimes forget the names of each slash.

/

The forward slash; goes bottom left to top right

Division by zero using this operator returns an undefined value

\

The back slash; goes top left to bottom right

Division by zero using this operator returns zero

Mathematical Comparisons

TM1 allows for mathematical comparison of numbers using standard nomenclature such as =, >, < and <>. However, TM1 also offers the ability to compare strings via the same functionality. You can compare strings by simply adding an @ symbol to the nomenclature.

Numeric check: IF ( numValue = numCounter )

String check: IF ( strValue @= strCounter )

And, Or, Not

When writing comparisons, we often use the AND concept and the OR concept. AND is written by using the ampersand symbol ( & ) and OR is written by using the percent symbol ( % ). But did you know that you can also write NOT by using the tilde ( ~ ).

~( x = 5 ) is the same as writing ( x <> 5 )

Rules are a very powerful component of Planning Analytics. Hopefully these components help you continue creating efficient code.

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

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

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

Learn how to Create Reports in Adaptive Insights

July 17, 2019 by Lisa Minneci Leave a Comment

HTML reports are the “bread and butter” of the finance professional. We’ve created a short demo video of how to easily create reports in Adaptive Insights.

In this video, part of our series The Capabilities of Adaptive Insights, Ken DiSessa shows you how to create a variance report looking at a budget versus actuals. He discusses the color coding and icons in Adaptive Insights, how to drill down into details, and the powerful explore cell feature.

Watch Reporting in Adaptive Insights and you’ll learn how to create a new report using filters and parameters.

Did you miss the first video in our series?

Check out Dashboarding in Adaptive Insights.

Learn more about Revelwood and Adaptive Insights:

Revelwood Named Adaptive Insights Partner Rising Star of the Year

Revelwood’s Insights on Adaptive Live 2019

Adaptive Insights Earned Gartner Peer Insights Customer Choice Award for Best Cloud Financial Planning and Analysis Solutions of 2019

IBM Planning Analytics Tips & Tricks: How to Manipulate Case Used for Text

July 16, 2019 by Lee Lazarow Leave a Comment

Did you know IBM Planning Analytics rules can be used to manipulate the case used for text? There are rules to change values into both uppercase and lowercase.

UPPER

Converts an entire string to upper case

UPPER(‘Lee Lazarow wrote this’) becomes LEE LAZAROW WROTE THIS

LOWER

Converts entire string to lower case

LOWER(‘Lee Lazarow wrote this’) becomes lee lazarow wrote this

CAPIT

Converts the first letter of each work to upper case

CAPIT(‘Lee Lazarow wrote this’) becomes Lee Lazarow Wrote This

But what if you only want to capitalize the very first character? You can do this by using these commands in conjunction with the LONG and the SUBST formulas.

Here is an example of a set of rules that show various ways to manipulate a text string.

IBM Planning Analytics Tips & Tricks: Rules for Capitalization

By combining formulas together in Planning Analytics, text can easily be manipulated in a variety of ways.

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Ways to Stop a TI Process

IBM Planning Analytics Tips & Tricks: Ways to Skip a Record Using a TI Process

IBM Planning Analytics Tips & Tricks: How to Maintain a Hierarchy via a TurboIntegrator Process

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

Revelwood Launches “The Capabilities of Adaptive Insights” Video Demo Series

July 11, 2019 by Lisa Minneci Leave a Comment

We’ve exciting news for those who follow our award-winning Adaptive Insights practice. This week we’re launching a new four-part YouTube series, The Capabilities of Adaptive Insights.

In these short videos Revelwood’s Ken DiSessa walks you through how quickly and easily you can do specific activities and tasks in Adaptive Insights cloud-based business planning software. The first video, Dashboarding in Adaptive Insights features Ken demonstrating how easy it is to use Adaptive Insight’s dashboard capability. He’ll show you how to build some of the 13 different types of charts, including KPI charts, bar charts, pie charts and more.

You’ll also be able to see how some things that are extremely difficult and hard in Excel are easy in Adaptive – specifically, waterfall charts.

Subscribe to Revelwood’s YouTube channel to watch all the videos in our series on The Capabilities of Adaptive Insights.

Learn more about Revelwood and Adaptive Insights:

Revelwood Named Adaptive Insights Partner Rising Star of the Year

Revelwood’s Insights on Adaptive Live 2019

Adaptive Insights Earned Gartner Peer Insights Customer Choice Award for Best Cloud Financial Planning and Analysis Solutions of 2019

IBM Planning Analytics Tips & Tricks: Ways to Stop a TI Process

July 9, 2019 by Lee Lazarow Leave a Comment

Did you know there are two different ways to stop a TurboIntegrator script?

The ProcessBreak command stops processing source data and proceeds directly to the Epilog. This approach allows you still run code in the Epilog that can perform tasks such as deleting subsets, writing to a process tracking cube, or running other processes.

Here is an example of skipping the data and metadata section if a parameter called pVersion does not exist:

            IF ( DIMIX ( ‘Version’, pVersion) = 0 );
                 PROCESSBREAK;
            ENDIF;

As you can see, there are no parameters to this command. You use this by simply telling Planning Analytics to go to the Epilog.

If, however, you want to completely end the process then you can use the ProcessQuit command. This command completely terminates the process without running anything in the Epilog. Here is an example of doing the same check with ProcessQuit.

IF ( DIMIX ( ‘Version’, pVersion) = 0 );
PROCESSQUIT;
ENDIF;

It’s up to you to determine how you want to stop a TI script from processing.

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Ways to Skip a Record Using a TI Process

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

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

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

IBM Planning Analytics Tips & Tricks: Ways to Skip a Record Using a TI Process

July 2, 2019 by Lee Lazarow Leave a Comment

Did you know there are two different ways to skip specific records when you are running TurboIntegrator scripts?

ItemSkip is used to skip a specific record. This is often nested within an IF statement and used when you want to simply ignore a record. Here is an example of skipping a record if a variable called vValue is negative:

            IF ( vValue < 0 );
                 ITEMSKIP;
            ENDIF;

As you can see, there are no parameters to this command. You use this by simply telling Planning Analytics to skip the record.

If, however, you want to skip the record and create a record in the error log then you can use the ItemReject command. This command allows you to customize the message that appears in the log. Here is an example of rejecting the same record using ItemReject.

            IF ( vValue < 0 );
                 ITEMREJECT (‘The value is less than zero.’);
            ENDIF;

It’s up to you to determine how you want to skip records.

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

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

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Financial Rules

The FV formula returns the value of an annuity at the time of the last payment.

The PV formula returns the initial or principal value of an annuity.

The PAYMT formula returns the payment amount of an annuity based on a given initial value or principal, an interest rate, and a number of periods.

Learn more IBM Planning Analytics Tips & Tricks:

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

IBM Planning Analytics Tips & Tricks: IFERROR

Learn more IBM Planning Analytics Tips & Tricks:

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.