Have you ever needed to create a TM1 report that uses Traffic Lights? You know, those little Red, Green or Orange (Amber) indicators that everything is good in your world, that it’s ok, or that it needs to be investigated?

How to Create Traffic Lights in TM1

Well, here is how to do it and it is sooo simple! Just do the following:

• Create your report. Usually, this will be a variance report of some nature. Below I have a simple sales report with 4 highly original product names and then Actual and Planned sales.
• Include in the report a calculation for what you want to judge good bad or ok on. In the example below I have created a Variance % column, based on the Variance/Plan.
• I want traffic lights on the Variance % column, where if it’s a positive variance by more than 10%, I want it green, if lower than -10%, red, and in between, amber.

• Select the cells for the data to be assessed.
• Go to Conditional Formatting in Excel and select Icon Sets and then the set you want to use.

• This will put Excel’s version of the conditional format on the cells you selected. For me, these were not what I wanted.

• So go to Conditional Formatting/Manage Rules and Edit the rule.
• In here Excel also shows that you are using “Per cent”
• Change the Type to Number (rather than Percent) and the values to be assessed, in my case, to >= 0.1 and < 0.1.

• Click Ok twice and you’re done!

Other Conditional Formatting Ideas for use with TM1

Of course, there are many other examples of conditional formatting when using TM1 data. You could, for example:

• make the conditional formatting much more complex. You could, for example, assess the materiality of the base values before assessing the percentage (after all, who cares about a 10% positive variance on an inconsequential planned sales number!).
• assess the dollar variance rather than/in addition to the percentage variance.
• compare this year to last year.
• have your Versions selectable in the report, so one comparator could be Actual the other Budget, or with a picklist, change it to be Forecast v Budget, or Actual v Forecast, or Current Forecast vs July Forecast.
• have periods selectable via a picklist to work the same way as Versions above. So you could choose September and 2019 from picklists and the report automatically pick up September (as the primary analysis month), August (as the prior month), 2019 (as the primary year) and 2018 (as the prior year).