• 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

Analytics

IBM Planning Analytics Tips & Tricks: Reviewing Chores

January 28, 2020 by Lisa Minneci Leave a Comment

Video

In today’s IBM Planning Analytics video tutorial, our FP&A Technology Director, Lee Larazow, demonstrates PAW v 2.0.43’s new approach that allows you to easily review the chores that are associated with a specific process. Once you have mastered the techniques Lee explains in this video, you’ll have a key skill to making it much easier to maintain your Planning Analytics environment.

Watch this video and you’ll learn how to:

  • Go into a list of processes
  • View all the individual chores associated with a process
  • Define the processes within the chore
  • Completely remove a process from the chore

This new approach by PAW makes Planning Analytics maintenance much easier!

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!

Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip! And don’t forget to subscribe to our YouTube channel for more Planning Analytics videos.

Watch more Planning Analytics video tutorials:

Creating Buttons in Planning Analytics

Planning Analytics Admin

Dimension Maintenance in IBM Planning Analytics

Home » Analytics » Page 8

Filed Under: IBM Planning Analytics Tips & Tricks, Videos Tagged With: Analytics, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, lee lazarow, Planning Analytics video, Planning Analytics Workspace, Revelwood video, TM1

IBM Planning Analytics Tips & Tricks: Excel’s IFS Function

January 21, 2020 by Nina Inverso Leave a Comment

Tips & Tricks

While the IF function has been around for a while now, IFS is new to Excel. This function checks whether one or more conditions are met and then returns the value associated with the first TRUE condition. The main objective of IFS is to simplify an expression that would have previously required multiple nested IF functions.

The general format of the IFS function is

=IFS(Condition 1, Value if Condition 1 = TRUE, Condition 2, Value if Condition 2 = TRUE, Condition 3, Value if Condition 3 = TRUE)

The function can handle up to 127 different conditions, and only the first two parameters are required.

Here is an example using a sample Excel grid:

IBM Planning Analytics Tips & Tricks: Excel's IFS function
=IFS(A1>250,A2,B1>250,B2,C1>250,C2,D1>250,D2)

will return “Green” (the value of cell C2).

In older versions of Excel, this would be written as

=IF(A1>250,A2,IF(B1>250,B2,IF(C1>250,C2,IF(D1>250,D2))))

Multiple nested IF functions would previously be used to accomplish the same result.

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!

Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip!

Read related posts with Excel Tips & Tricks and using Excel with Planning Analytics:

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

IBM Planning Analytics Tips & Tricks: Recalculating Excel Worksheets

Home » Analytics » Page 8

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Analytics, Excel tips & tricks, Financial Performance Management, FP&A, IBM Cognos TM1, IBM Planning Analytics, Nina Gordy, Revelwood, TM1

IBM Planning Analytics Tips & Tricks: Creating Buttons for TurboIntegrator Scripts

January 14, 2020 by Lisa Minneci Leave a Comment

Videos

In today’s IBM Planning Analytics video tutorial, our FP&A Technical Director, Lee Lazarow, shows you how to easily create buttons for TurboIntegrator (TI) scripts. When you create buttons for your TI scripts, you simplify tasks for your end users and create a much better Planning Analytics experience for them.

In many situations you’ll have a TurboIntegrator script that you’ll want your end users to run. But you should have an easy way for them to run that script. The solution is to create a button in the Planning Analytics Workspace (PAW) environment.

Watch our video Creating Buttons for TurboIntegrator Scripts in IBM Planning Analytics to learn the steps to create a button. These include:

  • Going into properties
  • Turning the properties for the button target into a process
  • Choosing if you want a prompt, and how you want that prompt to appear

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!

Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip!

Watch more IBM Planning Analytics Tips & Tricks videos:

Planning Analytics Administration

Dimension Maintenance in IBM Planning Analytics

Regions with Rules in IBM Planning Analytics

Subscribe to Revelwood’s YouTube channel to watch all of our IBM Planning Analytics Tips & Tricks videos.

Home » Analytics » Page 8

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

IBM Planning Analytics Tips & Tricks: Regions within Regions

January 7, 2020 by Lee Lazarow Leave a Comment

Tips & Tricks

A while back I wrote a blog post about using Regions within Planning Analytics rules. Regions allow you to make your rules easier to navigate and read when scrolling through many lines of code. But did you know that you can have regions within your regions?

Here is an example of a region, both opened and closed:

IBM Planning Analytics Tips: Regions within Regions
IBM Planning Analytics Tips: Regions within Regions

But what if you wanted to differentiate the components within the region?  You can use the exact same concept to create sub-regions:

IBM Planning Analytics Tricks: Regions within Regions
IBM Planning Analytics: Understanding Regions within Regions

Planning Analytics is smart enough to know how to group the start/end components of the region and allow the sub-regions to work within a larger region.  This approach allows us to create sub-groups to make your code even easier to navigate.

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! You can also sign up to get our Planning Analytics Tips & Tricks sent directly to your inbox!

Learn more about Planning Analytics Rules and working with Regions:

IBM Planning Analytics Tips & Tricks: Regions with Planning Analytics Rules

IBM Planning Analytics Tips & Tricks: Writing Rules

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

Home » Analytics » Page 8

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

FP&A Done Right: The Victors of the Decade Combine Agility and Resilience

December 20, 2019 by Ken Wolf Leave a Comment

FP&A Done Right

As we near the end of the decade, it’s a good time to think back about what businesses have learned from an FP&A perspective, and how they can fortify and position themselves for the next decade and for future decades.

The beginning of this decade saw the evolution of online analytical processing (OLAP) systems, such as our beloved TM1, grow into comprehensive and sophisticated platforms for holistic financial and operational performance management. In theory we had the tools to empower Finance to reveal the secrets of business success locked in our systems and in our data.

The last few years of the decade have seen our imaginations captured by the disruptors, the unicorns and those who have seemingly mastered the elusive “digital transformation.” But as we’ve learned over the last quarter, some of those “darlings” of the business world may not be the successes they first appeared to be. Take WeWork for example: the company has not managed growth effectively. They are at a point (or possibly past it) where Finance could step in, do some serious analysis and revisit the company’s business model. Other headline catching companies are growing exponentially, but struggle with delivering profits. This too, points to where Finance can be playing a larger role.

Business Resilience? Or Agility?

These musings were prompted by a recent article by McKinsey on business resilience. When we think about disruptors and unicorns, we might associate them more with the popular FP&A theme of business agility. One of our business partners, Adaptive Insights, frequently talks about business agility in the context of needing to make faster and more informed decisions. The backbone of this is continuous planning, which is spearheaded by the Office of Finance.

In one sense, perhaps, business agility is the young business, the quick, rookie running back on the football field – dodging and weaving and making stellar plays, with end zone celebrations when the offense is in control of the game.

But where does resiliency come in? To me, the resilient business is the more established, mature defensive linebacker, whose job is to thwart the competition, and who is less likely to be celebrating in the end zone, but just as important to winning the game.

The question for all of us, on the precipice of a new decade is, “Will we be playing a mostly offensive game in the next few years, or a mostly defensive game? Perhaps both.” And furthermore, how do we, the CFOs and the leaders in the Office of Finance, best prepare, plan and enable our organizations for what’s to come?

Facing the Future

McKinsey mentions that while we are still in “the largest global economic expansion in history, the outlook is uncertain.” Isn’t it always? The article states that in the company’s latest survey on economic conditions, “executives’ views on the current global economy and expectations of future global growth are less favorable than they have been in years.” I’d posit a good executive is outwardly optimistic and inwardly financially, cautiously pessimistic.

It is in this context that McKinsey examines what makes a company resilient. The article defines resilient organizations as those that exhibit a “willingness to take decisive action to strengthen their balance sheets and improve cash flow before the [previous] downturn hit, often by divesting non-core assets, reducing debt, and improving the efficiency of working capital.” To become a resilient business, McKinsey recommends the following three steps:

  1. Enhance the role of the finance team. They recommend doing this in strategic planning, business analytics and decision-making at all levels of the organization. As the article states, “The best way to do this is to embed finance managers alongside business unit leaders and empower them to be partners in running the business.” Think about that for a moment – take the traditional “bean counter” out of Finance and put her or him with the business unit leader. Imagine the possibilities: the finance professional knows where the data is, how to get answers from that data, and how to slice and dice that data in different ways. The business leader knows what questions to ask – questions urgent for today’s business challenges and vital for tomorrow’s business opportunities and threats.
  2. Pressure test capital structure and scenario plan. McKinsey recommends doing this with both capital structure and cash flow, and using a range of scenarios, “from an economic crisis to other disruptive events.” You might feel somewhat certain your industry will not have a massive disruption like that of Uber on the taxi industry. But what if you are a sports arena? How much overall revenue could, for example, MetLife Stadium lose should there be an NFL strike? Over how many games? While that is not a global crisis, it is an economic crisis with impact far beyond ticket sales. On a global level, are companies pressure testing and scenario planning for the potential impact of Brexit, of various international tariffs and trade disputes that, significantly increase the price of French cheeses and wines served at the high-end luxury suites at a stadium?
  3. Take immediate action to harvest hidden value from their balance sheet. McKinsey research shows “that working capital management is surprisingly variable, even among companies in the same industry.” The organization has found that “large companies that make a focused effort can typically free up more than $100 million from working capital and redeploy it to priority projects.” This argues for going beyond traditional budgeting and embracing more flexible planning methodologies, such as rolling forecasting or active planning. For example, McKinsey revealed that they saw “upside realized by companies that consistently track cash returns on an asset level and that make an ongoing effort to reevaluate and mitigate their liabilities.” With traditional budgeting and planning, you are assessing your balancing sheet in the past. By adopting rolling forecasting or active planning – where you have the tools and skill sets to assess and adjust your balance sheet proactively – you have the power to gain this upside.

As McKinsey states, “While most CFOs have a role in setting company strategy, the rest of the finance organization are sometimes viewed as passive scorekeepers. Best-in-class organizations, in contrast, expect their finance professionals to play a substantial role with business-unit leaders to set strategic priorities.”

Your Game Plan: Find Your Enabling Technology

So, what’s your best game plan for the coming years? McKinsey specifically mentions these best-in-class organizations have finance teams that “utilize innovative performance management tools to help determine how the business is actually performing and suggest steps to optimize results.” At Revelwood we’ve been consulting on and delivering solutions for financial and operational performance management for 25 years. One would think most mid-sized businesses have moved off of spreadsheets for their budgeting, planning and reporting activities. But, day-after-day, our team here speaks with not just new upstarts, but established, even large, publicly traded companies that rely on spreadsheets as the backbone of their core activities in the Office of Finance. Spreadsheets have a role in the Office of Finance and always will. But any organization that uses only spreadsheets simply can’t achieve true resiliency. And they can’t embrace agility.

Your Game Plan: Think Differently About the Office of Finance

How can you unlock the potential hidden within your finance team to add true value to the business? Think differently about how to enhance your team members’ roles. Maybe it’s even a matter of breaking up some aspects of the physical office and having finance team members sit among their associated business units. Separate their function from their strategic role. Be agile about how you think about your people and what they can do for the business.

The End Game: Resilience and Agility

As I mentioned, we think the victors of the next decade will strike a good balance between resilience and agility. Or, offense and defense. Invest in the right enabling technology, rethink the role of the Finance team, and build the skillsets and mindsets for both. That’s your best game plan.

Home » Analytics » Page 8

Filed Under: FP&A Done Right Tagged With: Adaptive Insights, agile planning, Analytics, business agility, business resilience, continuous planning, Financial Performance Management, FP&A, FP&A done right, IBM Planning Analytics, ken wolf, Rolling Forecasts, TM1

IBM Planning Analytics Tips & Tricks: The New Planning Analytics Admin Approach

December 17, 2019 by Lisa Minneci Leave a Comment

Video

In our latest IBM Planning Analytics Tips & Tricks video, Lee Lazarow, Revelwood’s FP&A Technology Director, shows you a new, single location in Planning Analytics where you can perform all your administrative tasks. This saves Planning Analytics admins time and simplifies your maintenance activities by enabling you to do all the tasks you would normally do through Windows services.

With this new approach to administration, Planning Analytics now combines server maintenance, database configuration and user definitions all into a single dashboard.

Watch this video to learn how to:

  • Get the status of your servers
  • Look at log files
  • Examine processes
  • Look at and control individual threads
  • Manipulate configuration settings, including your MTQ – multi-threaded querying setting

Planning Analytics new approach to administration saves you time and simplifies maintenance. This video will show you how to take advantage of this new feature.

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!

Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip!

Check out our IBM Planning Analytics Tips & Tricks video series:

Dimension Maintenance in IBM Planning Analytics

Regions with Rules in IBM Planning Analytics

Bookmarking in IBM Planning Analytics

Snap Commands in IBM Planning Analytics

Pivoting & Selecting Shortcut in IBM Planning Analytics

Home » Analytics » Page 8

Filed Under: IBM Planning Analytics Tips & Tricks, Videos Tagged With: Analytics, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, lee lazarow, Planning Analytics admin, Planning Analytics maintenance, TM1, video

IBM Planning Analytics Tips & Tricks: Rule Timestamps

December 10, 2019 by Lee Lazarow Leave a Comment

Tips & Tricks

TM1’s print report wizard still exists in Perspectives and many clients continue to use this approach. I recently came across an issue where a client wanted to “burst” a report and include a time stamp on a report that is being snapshot (e.g., set the cells to contain actual values and not TM1 functions that retrieve values).  Easy peasy … just use the =NOW function to generate a time stamp. Right?

Wrong. =NOW is not a TM1 formula. This means that the cell will not get converted and the timestamp would therefore refresh each time the report was opened/refreshed.

Ok, no problem. We can create a numeric value in a control cube that uses TM1’s NOW function. The time stamp will refresh each time the cell is referenced and we can then zap that value. Again, easy peasy. Right?

Wrong again! TM1 only refreshes numeric rules when the rule is re-processed. In order to keep updating the result (e.g., show the current time and date), the user would need to reprocess the rule each time.

Again, no problem. TM1 may not re-evaluate the numeric value, but it will re-evaluate a string rule like this each time the cell is queried. So we can just switch this into a string value and simply use a STR formula against the NOW command. Right?

Wait for it … wait for it …

Wrong once more! Remember that date stamps within TM1 and Excel vary by 21,916 days (well, maybe). As a result, we need to add these days to the formula. The end result gives us a formula that uses a combination of STR, NOW, 21916, lots of digits and decimals, and a trim of the wasted space.

IBM Planning Analytics Tips & Tricks: Rule Timestamps

… and this end result gives the client the ability to have a time stamp using a TM1 formula that can be snapshot and then converted into a date format!

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! Or, sign up to get our Planning Analytics Tips & Tricks delivered directly to your inbox!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: New Configuration Settings for Dates

IBM Planning Analytics Tips & Tricks: Writing Rules

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

Home » Analytics » Page 8

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

FP&A Done Right: What Prevents Business Agility

December 6, 2019 by Revelwood Leave a Comment

FP&A Done Right

This is a guest blog post from our partner Workday Adaptive Planning, written by Gary Cokins. Cokins examines what business agility really means for the Office of Finance.  

There’s a lot of talk these days about business agility. Yet it’s much more than just a trendy new business term to throw around at a networking lunch. Ask any C-suite executive if their organization could be more agile, and they will likely answer yes. But what does agility mean, and why does it matter?

An agile company can respond quickly to change as it happens, opening the door to new opportunities and minimizing the risks of threats and challenges from competitors, changing market conditions, digital technology disruptions, etc. If your company can think fast, be nimble, and move first, it’s positioned to gain a significant competitive advantage in today’s data-driven, fast-paced world.

A problem is many companies over-plan and under-execute. To be competitive today, companies must embrace a “speed to results” culture. This does not mean a “ready-fire-aim” approach to decision-making and actions. But they must move fast with a constant sense of urgency.

“Any company designed for the 20th century is doomed to failure in the 21st,” says David Rose, CEO of Gust. In other words, agility is no longer a nice-to-have. It’s the cornerstone of modern business success. But achieving a responsive, dynamic organization is easier said than done.

Barriers to agility are often so entrenched that overcoming them can seem incredibly challenging, especially when compounded by static, manual planning. Relying on racked-and-stacked table reports is insufficient. Robust modeling software, enhanced with visualization features, is needed.

Ultimately, business agility is about having the right tools to efficiently manage and measure change quickly, accurately, and comprehensively. And most of that comes down to how you plan. What is needed can be referred to as “active planning.”

Here are the three primary barriers to business agility:

Barrier #1: Manual processes and ad hoc reporting

Most finance teams use Excel to create reports, track financial projections and budgets, and synthesize numbers across departments. And it’s no wonder. Excel is an effective tool for building custom formulas, scenarios, and look-ups. As a stand-alone budget and reporting tool, however, it has some significant drawbacks, including the slow, cumbersome processes Excel perpetuates. Using Excel’s columns-to-rows math is restrictive. Greater flexibility is needed with modeling software.

Relying on Excel to reconcile budget numbers and bring business unit projections into alignment with corporate forecasts is a herculean task—rife with errors, broken formulas, and missed deadlines. By the time finance gets the numbers to match, they’re usually out-of-date.

Then there are time-consuming ad hoc requests. Who hasn’t had a CFO request detailed reports about revenue fluctuations or variances of planned-to-actuals by region, or expense increases due to higher healthcare premiums? In fact, 60% of CFOs say ad hoc analysis can take up to five days. Ultimately, ad hoc reporting is used to fill a gap in a company’s reporting process—a gap that can be filled with automated planning and reporting.

Excel doesn’t need to be replaced, however. Excel can augment automated planning. Used in conjunction with a cloud planning solution, it becomes one piece of a continuous, comprehensive, and collaborative planning process.

Barrier #2: Lack of alignment and collaboration

World-class companies know that organizational alignment on KPIs is a predictor of business success. Tracking performance against goals, ideally with targets set for the KPIs, and then flagging under- or over-performing business units monopolizes finance team resources. Finance is so busy with low-value but time-consuming tasks like balancing spreadsheets, fixing broken formulas, and nudging managers to submit budget requests, that they’re usually too swamped to steer overall financial strategy, let alone help facilitate and build collaboration.

Manual tasks hold finance hostage to mundane (albeit critical) processes, keeping data siloed and business decision-makers in the dark. Lining up behind KPIs is extremely difficult under these circumstances. These pockets of disconnected information keep decision-makers from effectively planning for what’s next.

And alignment around KPIs or collaboration under these circumstances? Not likely.

It’s not surprising that a majority of CFOs report lack of time as the biggest barrier to collaboration. Continuous firefighting and pursuing short-term priorities get in the way. When business processes become more efficient, however, collaboration is achievable. Productivity increases. Without alignment with KPIs, the disconnects between sales and operations, or production and management, or marketing and sales, make true agility impossible.

Barrier #3: Disjointed planning

By their very nature, resource allocation decisions need to reflect current circumstances—not the supply and demand challenges from last year and not financial reports that are three months old. Whether or not to hire more people, alter supplier relationships, invest in skills training, or accelerate capital investment plans largely depend on whether an organization plans effectively and agilely. And to plan effectively requires far more than a series of budget meetings and annual reports. It gets worse when different departments have their own set of numbers, revealing the need for a single version of the truth.

Disjointed and static planning flows from ad hoc information, missing data, and siloed decision-making. Active planning, on the other hand, helps organizations predict and respond quickly to potential gaps in performance and course-correct swiftly and agilely to changing market conditions.

But to do that requires a comprehensive and collaborative approach to planning that incorporates the latest information in near real time. In short, it requires active planning.

A way forward

The sought-after capabilities of agility—to see change coming, rapidly adapt to it, and turn uncertainty into business opportunities—can only be achieved by changing fundamental processes. Automating reporting so that it flows from multiple coordinated systems (ERP, CRM, HCM, etc.), generating reports in real time, and giving managers access to self-service reporting are all critical to an active planning process. Equally critical is to avoid digitally cementing existing processes that need to be redesigned.

While static planning produces monolithic plans that aren’t a true reflection of the business environment, active planning is different. It’s about listening to what your data is telling you about your goals, resources, suppliers, customers, competitors, and the wider market. Where static planning is top-down, siloed, slow, and limited, active planning is collaborative, continuous, and comprehensive. In other words, active planning allows you to plan and forecast at the speed of modern business.

By deploying a modern planning solution that enables active planning, your company can streamline FP&A processes, gain insights more quickly, and make better decisions faster. And be able to respond to change as it happens.

Don’t get stuck in the back office reconciling numbers and fixing broken links. Become a strategic partner by giving decision-makers access to the information they need with easy-to-use self-service reports, up-to-date data, and strategic insights.

Barriers to agility? With active planning, they’re easy to overcome.

Gary Cokins is an internationally recognized expert, speaker, and author in enterprise and corporate performance management (EPM/CPM) systems. He is the founder of Analytics-Based Performance Management LLC. Gary can be reached at gcokins@garycokins.com

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

Sign up for our FP&A Done Right series

Get our FP&A Done Right series sent directly to your inbox. Sign up here. 

Home » Analytics » Page 8

Filed Under: FP&A Done Right Tagged With: Adaptive Insights, Analytics, Financial Performance Management, FP&A, FP&A done right

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

December 3, 2019 by Lee Lazarow Leave a Comment

Tips & Tricks

Excel is, and always will be, a powerful tool for the financial team. With so much power, there are many great Excel formulas that rarely get used. I recently had a situation where I wanted to easily replicate a sheet and have each sheet use an MDX expression that referenced a different dimension name. I wanted to use a formula to determine the sheet name and used the CELL function.

The CELL function returns information about a specific cell within an Excel file. The syntax of the function is

CELL( info_type, reference ) 

The info_type defines the type of information to be returned and reference is associated with a specific cell. Some examples of info_type include “col” to define the column, “row” to define the row, “address” to define the cell refence and “filename” to return the full path and filename of the workbook and the worksheet.

The resulting format of the info_type called filename is

path[workbook.xlsm]sheetname

I took the approach of naming the sheet with the same name as the dimension. For example, one sheet was called “Account” and another sheet was called “Company.” By using a combination of the FIND function, the MID function and the TRIM function I was able to isolate the sheet name.

=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename"))+1,99))

I created this formula in cell B10 and then used the result within my MDX expression.

="{ TM1DRILLDOWNMEMBER( { ["&B10&"].[Orphans] }, ALL, RECURSIVE ) }"

This approach allows me to replicate an Active Form report by simply copying the entire sheet and then renaming the new sheet to be the same as the dimension name.

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 Excel-related tips & tricks:

IBM Planning Analytics Tips & Tricks: Recalculating Excel Worksheets

IBM Planning Analytics Tips & Tricks: Workarounds for Unsupported Excel Functions in TM1 Web

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP

Home » Analytics » Page 8

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Analytics, Budgeting, Budgeting Planning & Forecasting, Excel, Excel tips & tricks, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Revelwood, TM1

  • « Go to Previous Page
  • Page 1
  • Interim pages omitted …
  • Page 6
  • Page 7
  • Page 8
  • Page 9
  • Page 10
  • Interim pages omitted …
  • Page 26
  • 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.