Budgeting Planning & Forecasting

IBM Planning Analytics Tips & Tricks: Errors in Non-writeable Cells

August 20, 2019 by Lee Lazarow Leave a Comment

Did you know IBM Planning Analytics is smart enough to look at the write permissions of a cell before attempting to load values into a cube? Have you ever tried to run a load process and then receive errors stating that you cannot write data to a cell? While Planning Analytics is ensuring that your data load is properly working, the errors can be disconcerting and can make the load process appear unstable.

TurboIntegrator contains a command called CellIsUpdateable that can be used to check the write permissions of a cell before attempting to load data. The command uses the same parameters of the various CellGet functions and results in a value of 1 or 0 where 1 means the cell can be written to and 0 means the cell cannot be written to.

CellIsUpdateable(Cube, Dim1, Dim2, … )

This formula is often used in conjunction with an IF statement to determine whether a CellPut command should be used. Here is an example of using the commands within a Turbo Integrator script:

# Define cube name
strCube = ‘Finance’;
# Only load data if the cell is updateable
IF ( CELLISUPDATEABLE(strCube, ‘Budget’, ‘Company1’, ‘Jan 2019’, ‘Amount’) = 1);
CELLPUTN (vValue, strCube, ‘Budget’, ‘Company1’, ‘Jan 2019’, ‘Amount’);
ENDIF;

This concept can be extended to include error messages, ItemSkip commands, or other aspects of an ELSE component. But most importantly to your end user … no error will appear when trying to load data that should not be loaded.

Error messages are great for developers and great for data reconciliation, but it’s not always what you want your users to see. This approach helps limit the messages that appear to the user.

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: Rule Concepts that are Often Forgotten

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

IBM Planning Analytics Tips & Tricks: Clearing all Data from a Cube

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

IBM Planning Analytics Tips & Tricks: Clearing All Data from a Cube

August 13, 2019 by Lee Lazarow Leave a Comment

Did you know there may be several situations during development where you may want to clear all of the data from a specific cube? This could be due to a new process to reload data, a second round of user testing, or a migration from DEV to PROD.

There are a few approaches that can be used to clear data from a cube. One approach is to create of a view which consists of the entire cube and using then performing a ViewZeroOut against that view. This can be done via the following script:

# Define cube name

strCube = ‘Finance’;

# Destroy any existing views and recreate a new one

IF (VIEWEXISTS (strCube, strView) <> 0);

VIEWDESTROY (strCube, strView);

ENDIF;

VIEWCREATE (strCube, strView);

# Perform Zero Out

VIEWZEROOUT (strCube, strView);

The benefit to this approach is that you now have flexibility to manipulate your view. You can have the view filter to specific elements within a dimension and/or set the view to ignore values such as rule-based values.

Another approach is to use the CubeClearData command. The CubeClearData command clears all data within a cube. Using this function is faster than writing a script at the expense of using an “all or nothing” approach. The same process defined above can be written using the following single line:

# Define cube name

strCube = ‘Finance’;

# Clear all data from the cube

CUBECLEARDATA (strCube);

Note that the CubeClearData command also clears any cells in the cube that are fed with feeders. If you want to restore the fed cells then you must either manually resave the rule that establishes the feeders or use the CubeProcessFeeders function.

There are multiple ways that can be used to clear data from a cube and you can define what approach is best for you.

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 – Part II

August 9, 2019 by Brian Combs Leave a Comment

How is your budget/budget prep coming along? Have you set aside time to rethink your process? In that last installment of FP&A Done Right, we started our enumeration of the problems with traditional budgeting. Before I discuss several more, here is a reminder of the last few problems:

Time consuming and costly
Quickly irrelevant and outdated
Financial process largely disconnected from specific drivers

Let me highlight more now and then we will move towards some alternative approaches.

Principled upon negotiating/gamesmanship

I can still remember my first visit to Corporate to review (or as I soon learned, to defend) our annual budget. Back then, I was fresh off my MBA and I had landed a job at one of our Region offices. We had just spent months building a plan from the lowest level, capturing input and feedback from every location manager and painstakingly describing every variance to the penny. We were ready. This was a done deal. Boy was I naïve. We were escorted into a nice room with a large table. Around the table I could see the president of our division and the heads of every major functional area ready to discuss our plan. Game on! My controller and I didn’t even get a chance to pull out most of the backup schedules we had created. He spent his time trying to negotiate fewer expense reductions and less revenue while I was busy taking notes on all the “savings” and “initiatives” the team had just found for us during the review. Great news. Thanks for the assist. I learned my lesson that day. After that, I knew that I had to pad my expenses and sandbag my revenue. They knew we did it too which is why they had us take a 5-10% cut in expenses as soon as we walked in the door. That is a difficult game to stop playing and, in the end, no one wins. Yet, many of us continue to play.

Triggers Unnecessary Spending

Since our budget numbers are frequently tied to prior year spend rather than being based on needs (a zero-based budgeting approach), we feel the need to spend money just so we have the same amount available to us next year. This is crazy, but I still see it today. We should be creating an environment where our front-line managers are rewarded for being fiscally conservative, not penalized. If you find a way to save money this year, we should be analyzing what you did so we can replicate it with your peers rather than giving you a hard time next year since you now have a large YoY increase.

Creates an inflexible performance contract

This is a big one as it impacts your managers directly in their bank accounts. This is especially true when incentives are tied to performance against the annual business plan. Once my budget was completed, I knew I would spend the rest of the year running actuals vs budget reports so we could determine what our bonus would be. If you remember from the first part of this blog, almost two-thirds of budgets are outdated between 4-6 months into the plan. If that’s the case, why are we using that number to determine the bonus for our managers? I want to reward my managers for changing course if they see something that is in the way of them achieving their goals. Compensate them based on what is occurring now, not what you thought was going to happen 12 months ago. When we focus on an inflexible budget number, we begin to manage to that number. Don’t fall in to that trap.

Drives Wrong Behavior

It doesn’t take long before you know roughly where the year will pan out vs the budget. You know fairly quickly whether it is attainable or a long shot. Since compensation is tied to the budget, it tends to drive the wrong behavior. You should expect your managers to do what is in their best interest. It is your job to ensure that by doing so, the company gains as well. If I am in the back half of the year and I already know I can’t achieve my annual budget numbers, where is the incentive for me to continue to find cost savings and improve my processes. I might as well give up on trying to get better this year because I won’t reach my bonus threshold anyway. Right? Maybe I’ll push off a cost savings initiative until next year. Or I’ll try that new revenue generating idea at the start of next year. The same is true if you have already maxed your bonus for the year. Why continue to do better? Save some of that goodness for next year. You need to make sure that the company goals are aligned with the individual goals. A budget can create a false sense of security and it may be holding the organization back from achieving its true potential.

It’s often easier to ‘see’ a problem when someone else describes it. My hope is that while reading this, you took some time to compare and contrast these issues with your methodology and approach to the budget. Does anything look familiar to you? If so, perhaps it is time to make a change. Please reach out and share your stories with me. In my next blog, we’ll discuss some alternatives to these problems that you can begin using immediately. Happy 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!

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.

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

Budgeting Planning & Forecasting

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.

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.

Principled upon negotiating/gamesmanship

Triggers Unnecessary Spending

Creates an inflexible performance contract

Drives Wrong Behavior

Read more posts in Brian’s FP&A Done Right Series:

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.

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.

Time Consuming and Costly

Quickly Irrelevant and Outdated

Financial Process Largely Disconnected from Specific Drivers

Read more posts in Brian’s FP&A Done Right Series:

Division

/

\

Mathematical Comparisons

And, Or, Not

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.

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.

Learn more about Revelwood and Adaptive Insights:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT