Posts

Management accounting analyzes, plans and informs. This requires a reliable database, but also flexibility in designing reports, presentations and dashboards. These requirements often can´t be combined in a software system. The discussion about the authorization of local reporting solutions, that complement central standard systems, is currently up-to-date. The article reports from practice and conveys the advantages and disadvantages of local solutions.

Central versus local reports

When I wrote ‘local report solutions’, I could already hear the prompt “think big or go home”. It seems that the management accounting is focusing on big data and the big BI solution. But what is the true situation? What experiences we make in our projects?

The utopia of the one system into which all the data flows and from which rapidly different reports can be generated to steer the entire enterprise, is the driving force of many positive, important developments. But the resource expenditure behind this idea ist enormous. This type of reporting is made practical by a high degree of standardization and continuity. Data collection and storage benefits from the general conditions.

However, the reports and presentations that provide centralized systems are limited by these conditions. They are not very variable and can only help partially in answering non-standardized questions.

Automated, local reporting tools can overcome these communication hurdles.

Therefore they are a flexible and cost-effective alternative. From our projects we know that a mix of central and local reporting solutions is the best course of action.

The local solution isn´t so local

At this point it is important to split the management accounting IT. The topics of data collection and storage are to be separated from the communication of the results, the reporting. Collecting data in isolated applications must be called into question for many reasons. Each BI vendor can name these reasons. However, automated local tools solve many issues when creating reports and presentations.

Strictly speaking, local reporting tools are hybrids. The data they access is usually from centralized systems. The reporting is done in front-end tools that are individually designed and managed locally. The distribution of reports can also be automated and is not particularly limited.

Sind lokale Reporting-Lösungen sinnvoll? Blogbeitrag auf chartisan.com

The right time for individual reporting tools

Use custom reporting tools when management often needs to change report variants. They make sense if structured data from the DataWarehouse are to be supplemented with unstructured, individual comments and explanations.

In strategic management, variable reporting and presentation tools make sense in every case. But operational management also raises questions that can only partially be answered by centralized reports.

Resource requirements

There is a catch. As a rule, locally generated reports can´t created quickly. The efficient creation of local reporting tools usually requires a very long learning period.

The design and planning process requires fewer reconciliations and compromises between the report creator and the report recipient. That’s positive. The technical and creative implementation requires a lot of know-how. Specialized knowledge is needed that has little to do with management accounting in a business sense.

Knowledge of the data connection, automation and programming are essential. If the visual presentation and presentation should be efficient and at high quality standards, an above-average user knowledge is necessary.

An idea, if own resources are missing

But even without a resource pool of its own, the advantages of local reporting tools are easy to exploit. The development of such tools is an individual service for which there are different solution providers on the market. These are not software providers, but experienced service providers with special management accounting know-how and relevant experience in the field of reporting.

Good service providers provide a very high quality standard that meets your individual conceptual and technical requirements.

They work directly with the specialist departments in manageable project periods and deliver immediately ready-to-use solutions.

chartisan is one of these solution providers. Our focus is the Management Information Design with IBCS® and the technical implementation with the possibilities of Microsoft Office (Excel, PowerPivot, Power Query, PowerPoint) and Power BI.

Advantages and disadvantages at a glance

You have little time? Therefore is here a clear plus-minus list:

+ Local reporting tools allow variable reporting and presentations.

+ These reporting tools can efficiently display structured and unstructured data.

+ Local reporting tools are ready for use in short time.

+ Through the high degree of automation, the tools support the operative controlling routine.

+ You can buy suitable solutions externally as a targeted supplement to the standard systems.

 

– Through internal development you tie up important resources, time and financially.

– When using external service providers, you buy foreign competences.

– External service also costs money.

 

Are there any questions left? Need a hint? You are welcome to leave a comment here or to contact me confidentially by e-mail.

Happy reporting,
Yours Silja Wolff

 

It´s mostly about variations when presenting data to decision makers, such as planned, target or empirical values. Only these variations allow us to reasonably evaluate the actual numbers.

Variations should play a major role during the visualisation as well. Today´s trick shows us that this is feasible within Excel only with a few clicks in specific cases:

  • Compile a diagram with two curve streams of data
Abweichungen Schritt-für-Schritt erklärt
  • Mark the diagram and add the variation columns by choosing the menu diagram draft
  • Format the variation columns red or green respectively. The column width can be adjusted to one of the curve stream of data by right click. (parameter “space width”)
Abweichungen mit Excel darstellen
  • Add data caption and legend if applicable (see our trick “captions”)

Finished 🙂

Excel Template Abweichungsdiagramm

 

 

Data captions and legends are important to understand the content of diagrams. Unfortunately the provided alternatives to caption are partially not legible. For example, sums on top of piled columns are not provided. You search in vain for standard functions such as legends which support the viewer not to look back and forth as they are placed directly next to the stream of data.

With our today´s trick it is possible to implement legible and at the same time dynamic captions:

Part 1: Sums on top of piled columns

  • Calculate sum values and insert as new stream of data (right click on diagram, “choose data”, add stream of data)
  • Change stream of data type to “XYZ” of the new stream of data (right click on new column, choose “Change stream of data type”)
  • Invisibly format items and fade in the data caption on top
Summe in Excel Diagramm

Part 2: Legend next to piled column

  • Fade out the standardised legend
  • Extend the column stream of data to an area with empty segments, so that there is enough space for the legend next to the last column
  • Calculate the X- and Y- coordinates for the legend text, depending on the data of the last column (see sample data file)
  • Insert a stream of data of the type “XYZ” (as in part 1)
Steuerung von Excel Legenden
  • (The X-values for the horizontal alignment of the points, can be divided into the data source capture only after the type “point XY” is selected)
  • Formatting points invisible and show data label right
  • First data label of the new data series, click twice (not double), is so marked a single label area
  • “=” type and click on the table cell in the desired legend text is, then Enter to confirm
Excel Template
  • Connect the remaining legend texts with the equivalent chart segments

Finished 🙂

Template Beschriftung direkt an der Säule

In many cases it is important to allocate data to multiple diagrams so the presentation is clear. It results in so-called Small-Multiple illustrations.

If each single diagram uses its own scale, the informative value of the entire page will get lost. In terms of a high quality visualisation, unitary scales are very important. At the same time the diagrams should be of low-maintenance and dynamic.

Negativbeispiel

Skalierung mehrerer Diagramme ohne Excel Trick

This challenge can be managed in Excel with our today´s small trick:

  • Calculate the minimum and maximum of all (!) single values (function MIN and MAX)
  • Insert into each single diagram the minimum and maximum values as an extra data stream
  • Invisibly format the minimum and maximum data stream (no filling, no frame line)
  • Align diagrams of same size next to each other

(keep holding the alt-key when pulling the mouse; then your diagrams lock with the segments)

Excel einfügen einer min-/max-Datenreihe

Done 🙂

Excel to go Skalierung

 

What are Bullet Graphs and where are they used?

Key data overviews on dashboards are easier to understand by visualisation. The realisation is quite difficult, because standardised diagrams are uneligible for this function. Bullet Graphs, a special type of bar diagrams, have been especially developed by Stephen Few. Basically they are very suitable for  those purposes, but they hold some disadvantages in its common form (see illustration 1 and 2), which we have solved in our projects due to a modified, optimised form of visualisation.

 

Illustration 1: The components of a Bullet Graph as it is regularly used in dashboards

  • Bullet Graphs are a special form of bar diagrams
  • They compare a presented key figure with a target value
  • By using background colours, additonal information of quality will be allocated
  • Bullet Graphs are often used in dashboards. They save space and replace tachometer
  • Bullet Graphs can be aligned horizontally or vertically

 

Illustration2: Dashboards with Bullet Graphs as they are often recommended and used

 

 5 disadvantages of this Bullet Graph illustration

  • The graphics become confusing very quickly, because many detailed information is shared, which is not necessarily needed or partly not available at all. For example the quality statement “bad, okay, good”. This disadvantage mainly occurs when many Bullet Graphs are displayed on one page (list of key data).
  • Variations of the goal are not visualised and therefore difficult to see. The only part easy to read is, if the bar of the just value is overstepping the target value. For the viewer it still would be important if the overstepping is good or bad and how large the variation is.
  • Coloured background areas distract the viewer from the main information, which is the bar of the just value and the target value. The coloured areas always look the same, no matter if the goal was reached or not. They rather disrupt the perception than to support it.
  • Background areas decrease the contrast towards the bar of the just value and target value. Thus, these essential graphical elements are difficult to see in the front.
  • Target areas are difficult to illustrate. In practice there are not only set target values (single values) for key data but often also for target areas. With this key data (KPI) the goal is considered achieved as long as the just value is between the minimum and maximum level of the target area.

 

How to ideally use the idea of Bullet Graphs in dashboards

For the use in dashboards we use a specially optimised form of Bullet Graphs (Illustration 3), which avoid the named disadvantages and bring additional visual features.

 

Illustration 3: Dashboard with Bullet Graphs recommended by chartisan

 

  • The simplified illustration without unnecessary background areas allows a quick overview
  • Indicated are the target values (single values) or target value areas (minimum/maximum level)
  • Variations will be highlighted in colours: positive (desired)= green, negative (undesired) = red. The extend of the variation is easily captured. Viewers immediately see where there is “a lot” red or green flashing.
  • The value caption is space-saving and clearly stored within the columns of the chart.
  • This optimised graphic form uses the strengths of the original Bullet Graphs and complements them with a visual drift indicator. The detail level has been reduced as much as possible, whereby these optimised Bullet Graphs are ideal for lists of key data.

 

By the way, we have managed and automised the displayed example with some tricks in Excel. As often the idea was key not the tool. Having said this, we hopefully could inspire you. Should you look for further good ideas for your reporting, contact us!