Simon Thorneycroft and Jonathan Hodgson

KPIs and Excel 12

Excel '12' is being greatly enhanced in the business intelligence space, which is great news as alot of users would never leave Excel if they didn't have too.

I've briefly mentioned KPIs before and support for PivotTables is looking excellent.

A KPI has four main components:

• Value. The current value of the business metric – this could be a physical measure like Sales, a calculated measure like Profit, or a custom calculation defined specifically in the KPI.
• Goal. The target for the business metric – this is usually an MDX expression that resolves to a value.
• Status. A number defining the current status of the Value, normalized in the range -1 (very bad) to +1 (very good) – this is also an MDX expression.
• Trend. An indication defining how the business metric is developing over time – getting better or worse relative to its goal.  Trend is also normalized between -1 and 1, and also an MDX expression.

Using simple graphics makes figures and KPIs much easier to quickly glance.

The quick formatting with data bars and icon sets should keep many a manager happy with RAG (Red, Amber, Green) status reports.