Mar 4, 2019
The Excel Hell Menace
In the first Episode of the epic webinar saga – MAY TM1 BE WITH YOU, we explore how you can solve some of the issues that can make a great tool like Excel an absolute hell.
The road to Excel Hell is normally paved with good intentions. Almost every firm use Excel for financial reporting but let’s be honest, we have all been there or seen it happening, when a new Excel report is created, your boss loves it and few “easy” suggestions are added to the report.
The report becomes more complex and hence error prone specially if you take into consideration that 9 in 10 Spreadsheets contain errors. Then many people in the company is using the report and the “users” send updates and tweaks, eventually macros and other linked spreadsheets are incorporated but they don’t always work and not everyone is a VBA expert.
In 2013 JP Morgan had a $6 billion USD loss from a copy and paste error.
Several checks and balance reconciliations are also added to avoid errors. Now the report needs twice as long to be produced and it takes every month a whole team to consolidate, update, check, reconcile and distribute. Sounds familiar to you?
You maybe ahead of the curve by already using TM1 and taking the concept of Excel to a robust database level also known as the Functional Database. Yet, you could benefit even more from learning handy tips and best practices on how to effectively leverage TM1’s power through Planning Analytics for Excel (PAx). By using dynamic reports in conjunction with MDX you can solve some of the issues that can make a great tool like Excel an absolute hell.
Tips and best practices
“TM1User” identifies which instance is connected.
Excel defined ranges allow for more dynamic formulas.
Organize your layout for easier maintainability.
Centralized parameter cubes to reduce monthly updates (e.g., date).
“SUBNM” for selections reduces the reports required.
“Format ranges” with Excel “IF” formulas allow for many visual design options.
MDX statements for dynamic rows without creating multiple subsets.
Action buttons can enhance user experience by triggering refreshing or TI scripts.
Debugging check list
TM1RPTTITLE needs to reference existing elements.
No error returned in TM1RPTTITLE formula cell does not indicate mistake-free formula.
Only use TM1RPTTITLE for “fixed” dimensions, otherwise could still work but performance may suffer.
Ensure MDX statement returns values.
DBRW formulas should be right of TM1RPTROW cell and needs to reference element in every dimension.
Watch Episode 1 now:
All the above and much more is explained in details in the recording video.
Next episode:
In this exciting second Episode of this webinar saga, you will learn how you can implement Best Practices for the new revolutionary functionality of Planning Analytics known as Hierarchies.