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

 

Labelling data points in charts is generally more reader-friendly than using a vertical axis. The axis is automatically displayed in Excel, and therefore it is often found in reports and presentations. However, it forces the chart reader to move their eyes back and forth between the data points and the axis.

If data points are labelled directly, on the other hand, the reader’s eye can rest steadily on the data points. That way, readers of your charts can concentrate on the content (!). Unfortunately, standard labelling options in Excel are often too inflexible, and the results are quite ugly. In line charts, for example, there are often overlaps between label and line, and in the worst case, they are no longer legible.

Excel does offer different label positions (top, bottom, right, left, centred). But these are always applied to all data points at once, although an individual alignment per data point would make more sense. Of course, you can also move the labels manually. But this can also lead to problems, as the manual position also needs to be changed by hand after data updates.

A little trick provides the solution: an invisible auxiliary data range can be used to generate data labels that always position themselves correctly. In the case of a line chart, you can have it calculate whether it ‘bends’ up or down for each point. The formula then compares each data point with the median value of its two neighbours. Of course, you may have to use completely different formulas for different chart types.

Use the following steps to create clever labels for line charts:

  • Create a formula to move the label up (+1) or down (-1)
  • Calculate auxiliary data range “Label” for data labelling (at the points level)

  • Add data range “Label” to the diagram, make points and connecting lines invisible and insert centred data labels

  • Click the first label field of the new data range twice (don’t double click), so that the individual label field is selected
  • Type “=” and click on the table cell showing the value to be displayed (e.g. the value for January), then confirm with enter (this creates a so-called linked label)
  • Link the other data points with the corresponding table cells

Done 🙂

Tip: Excel Version 2013 introduced the labelling option “Value From Cells”, with which you can achieve the desired result much quicker.

 

“Small multiples” are charts that display a range of data in several smaller charts of the same type. This is often easier to read than mixing the same data in a single, more complex chart. The informational value and overview are the result of the aggregate charts in this case.

The specific application scenario determines whether to use a small multiple or a single chart with several data ranges. Stacked column or bar charts visually highlight the sum. Small multiple charts place the visual focus on the individual data ranges.

If the company revenue is displayed as a stacked bar chart (1 chart with 4 data ranges), for example, then total revenue is easy to see, but less so the precise development of individual product groups.

On the other hand, if 4 single charts (with one data range each) are displayed for the product groups, then revenue performance for each product group can easily be gauged. Additional details such as deviation indicators can easily be implemented at the product group level.

To maintain a good overview, however, you need to standardise the individual charts making up the small multiple. A consistent axis scale is especially important. Highlighting improves the informational value, e.g. using the signal colours green and red to mark positive (desired) or negative (undesired) deviations.

Unfortunately, this type of chart isn’t part of Excel’s native functionality. Nevertheless, you can easily implement custom small multiples in just a few steps.

Create Chart

  • Chart Type: Cluster Bar
  • Data ranges: main data range and min/max data range for a consistent axis scale

Format chart

  • Add Data Labels
  • Format Data Series… Series Overlap: 100% / Gap Width: 50% / bar colour: Solid Fill = green, Invert if negative, second colour = red / min/max colour: no fill

  • Format Axis… Axis Options: Values in reverse order / Line: Solid Line, black / Label Position: Low
  • Format Chart Area… Fill: No fill / Border: No line / Font size: 8 pt

Position chart

  • Align chart area (outside) and drawing area (inside) to the cell using the Alt button

Copy charts

Adjust the data source for each chart

Done 🙂

Tip: In one of our next posts, we’ll be presenting a second way of realising small multiples even quicker for simple application scenarios.