Visual performance signals I
- Apr. 06, 2010
Introduction
A few months ago, I was looking for an easy way to visually assess a company‘s performance against target or budget in TM1. I made a number of custom templates for KPI data entry. Targeted or budgeted monthly KPI data are set at the higher levels within the organization. Actual KPI data are entered in many areas of the company. Ultimately, the ultimate purpose is to compare and assess actual performance against target, plan or budget.
To simplify matters, let us simply take 3 cases for the Rate:
Our scenarios
Actual, Target, Plan, Budget, Rate … might all be elements of a Scenario dimension in a TM1 cube. The aim is to calculate the Rate using a TM1 rule. The result will be 1, -1 or 0. The SIGN function is well suited for this task: it avoids a more complicated IF statement:
# Wim Gielis # https://www.wimgielis.com##### # Rule for a visual performance signalling system # 06/04/10 #####Skipcheck; ['Rate']=N:SIGN(['Actual']-['Target']); Feeders; ['Actual']=>['Rate']; ['Target']=>['Rate'];
The rule could be extended to incorporate the notion that for some KPI‘s or accounts, Actuals being greater than Target is in fact worse (like Costs). A straightforward IF test could reverse the result of the SIGN function.
The SIGN function has… 3 outcomes: 1, -1, and 0! Now, how do we apply this in our Excel sheets or TM1 websheets? It is possible, but you need a bit of advanced Excel number formatting trickery! Here are 3 steps to get the trick working:
- Choose the Webdings font for your Rate DBRW cell.
- Convert the values of 1, 0 and -1 into another symbol:
Perhaps you might not know it, but you can easily do this with a custom number formatting: =;\r;\ëValue New symbol 1 = -1 r 0 ë - Lastly, apply a nice color for your new symbols:
Here is the upshot: even this can be done using custom number formatting: [Green]=;[Red]\r;[Yellow]\ëValue Color 1 Green -1 Red 0 Yellow
This custom formatting can be copied to other Rate cells. An example file in Excel can be downloaded on the next page.
