Tag Archives: Effective Management Reports

Cell-Linked Decomposition view

In displaying the key and often high level information required, many reports raise lots of additional questions in terms of why a specific number is ‘too high’ or ‘too low’, or how the number is comprised. XLCubed provides lots of ways for power users to further explore and visualise this, but in this blog we’ll take a look at one of the simplest and easiest to implement within a report – a cell-linked decomposition view, or a dynamic breakout.

The scenario below is a fairly straightforward Regional Sales report which will be deployed to management through XLCubed Web. When users click on a sales number for a specific region and month we want to provide them with a ranking to show the top 10 products which were contributing to that revenue. We provide a number of ways to achieve that but this is by far the simplest:

Select any of the values returned in the Grid and then either use the ‘Breakout’ option on the Grid Ribbon, or right click and choose XLCubed – Breakout Value. You now specify where to position the resulting breakout, which hierarchy to decompose the number by, at what level, and how many ranked items to show.

BODialog

 

In this example we’ve chosen the top 10 Products at the SubCategory level. The result is shown below. The green cell in the original grid is the cell the user has selected and is ‘tracked’, so when another cell in the grid is selected the breakout updates accordingly, meaning users can easily explore the makeup of their data.

 

BO Result1

 

The breakout result can be further decomposed by running another breakout, this time on the product subcategory. In the published example below we’ve run an additional breakout showing the top ranked products in the selected subcategory.

WebBO

 

The report user can now simply click on the values which are of interest to see the associated product sales breakdown, and it’s something which can be built in minutes by the report designer.

Rolling Forecast reporting in Excel

In monthly reporting for the current year the most pertinent numbers for closed periods are the actuals, and for open or future months the forecast. In most cubes actual and forecast (or budget) are implemented as either different measures, or as part of a scenario hierarchy. This can lead to challenges in reporting, where putting both the period and the actual/forecast on the same axis means you will get duplicated months which show the forecast for periods when the actuals are already in as in the PivotTable below.

 

 RP10

We would want the report to be asymmetric (i.e. different months returned for Actual and Budget). Various tools can handle that, and it’s always been possible to achieve that in XLCubed through formulae or in a grid. What’s more difficult is to make the report fully dynamic without the need to redesign it each time. Ideally we’d want the user to be able to change a slicer to select the last completed month (or pick this up directly from the cube), and have the appropriate months reported under Actual and Budget.  Here’s how to achieve it in one grid…..

The approach uses a combination of:

  • Excel Ranges to determine the available months, and which are for Actual and Budget
  • An XLCubed Slicer to pick the ‘Last Actuals’
  • One XLCubed grid with the ‘Exclude from display’ pointing to the relevant ranges above

Let’s work through this example which uses the Finance cube on the AdventureWorks DW 2008R2 database.

We’re going to use data from FY 2006 and report Actual and Budget figures across departments.  We will use a slicer to select the last Actual month we want reported.  For the remaining FY 2006 months we will report Budget values.

Entire year of Months in the report

There are several ways to achieve this – we added an XLCubed Excel driven slicer (XLCubed – Slicer – Excel) based on the range $AA$1:$AB$12 as shown below, containing all months in FY 2006. This lets the user pick the last month for which we want to report Actuals.

 

RP1

RP3

 

 

 

 

 

 

 

 

 

The slicer is set to output its selection into $B$1.  In $C$1 the formula =VALUE(B1) converts the text output of the slicer into a number which we’ll use to calculate which months should report Actual and which Budget, see the screenshot below:

RP2

 

 

 

 

Date Table

This is an Excel range supporting the reporting logic. Row 2 contains every month in the reporting year. Row 3 assigns an incremental number to each month, 1-12.

We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget.  An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.

At this stage we’ve used a little XLCubed, and the flexibility of Excel to match the appropriate months to Actual and Budget. We now need to add the report itself.

We’ll create our grid – initially we set it up to report Budget and Actual figures across the year, by basing the selection for Date.Fiscal on the range containing all months for the year ( $B$2:$M$2).

We then use XLCubed’s right-click menu (Keep – Exclude From Display) to exclude members.  It doesn’t matter which slices are excluded at this point. To do this highlight ‘Actual’, followed by the first available month and then choose Exclude From Display. Repeat this for ‘Budget’ and the first available month.  We will exclude July 2005 for Actuals and August 2005 for Budget.  You will notice the red comment marker on the grid. Right-click on this cell and choose Axis – Edit, and go to the Excluded Slices tab.

RP6 RP7

 

 

 

 

 

 

 

 

The above screenshots show that we are currently excluding July 2005 from Actual as well as August 2005 from Budget.

We can then easily change the Date Fiscal selection for each scenario.  For Budget we will exclude Budget values for months in cell locations $B$4:$M$4 (as we want to report Actuals for those entries) and Actuals values for the months in cell locations $B$5:$M$5 (as we want to report Budgets for those entries).

RP8

The grid report now looks like this with Actuals being reported up to November 2005 and Budgeted for the remainder of FY 2006:

RP5

We have also set Merge Repeating Cells on (in Grid Properties).  You could do further formatting – for example by colour filling all Budget values and hiding the grid headers.

RP9

The end result is a one grid report giving the Actual:Budget month mix that we want with the monthly maintenance process a case of simply changing a combo box selection.

 

 

Streamlining writeback with XL3DoWriteback

We were recently asked by one of our customers to help them improve their forecasting process. They had originally been using a solution developed using XLCubed Excel Edition v6.0 and our XL3LookupRW formula. The system had been working, but because of a combination of the intricacy of the data model and the slowness of the cube server when performing a writeback, the process was taking much longer than necessary.

As an example, one of the workbooks that was being used contained nearly 7,000 XL3LookupRW formulae, and another contained over 1,000. Many of these lookups could actually have been replaced by a simple Excel formula, such as a sum or a product of other values, but built as it was, the customer was having to type these values into the cells individually: a tedious, time-consuming and error-prone task.

The process before XL3DoWriteback

In the screenshot above, the price, percentage and production figures would be typed in, then a calculation made to calculate their product (in the white cells). This would then be individually copied and pasted into the corresponding cell in the revenue row.

What the customer wanted was a couple of changes to streamline the process:
* the ability to use Excel formulae in the workbook to obtain the final values – without the subsequent copying of values,
* they wanted to be able to get all the calculations lined up, then submit them all at once – this would make the poor server performance a much less important issue, since instead of having to wait to enter the next value, that period could be usefully spent doing other tasks.

What we offered was a different writeback method, which has been available in its current form since XLCubed v6.5: the XL3DoWriteback formula.

Unlike XL3LookupRW, XL3DoWriteback is geared towards the kind of batch writeback approach that the customer had envisioned. Once set up, Excel formulae can be used to do the actual work of calculating the numbers, and the XL3DoWriteback formulae remain dormant until all the values are ready, then are activated in one transaction.

If this sounds useful for you, here’s how to set it up.

The XL3DoWriteback Formula

In addition to the member list required by the XL3LookupRW formula, the XL3DoWriteback formula requires two extra parameters:

  • PerformWriteback: this parameter tells the formula whether it should be in active writeback mode, or should remain dormant
  • Value: this parameter gives the new value that should be written back to the tuple

Following these two parameters are the connection number, and the hierarchy-member pairs that will be familiar to you from the XL3Lookup and XL3LookupRW formulae.

The PerformWriteback parameter is a bit special. If it refers to a cell that contains only a boolean value of TRUE, then when it has finished sending the value, it will set that cell back to FALSE. This means that periods of writing and non-writing are very easy to define. In order to maximise the power of this, we usually point all the XL3DoWriteback formulae at a single PerformWriteback cell, which we can switch using an XL3Link formula. For example:

A1: =XL3Link(XL3Address($A$1),"Write changes",,XL3Address($B$1),TRUE)
B1: FALSE
C3: 1,000
C4: 0.85
C5: 20,132
C6 =C3*C4*C5
D3: =XL3DoWriteback($B$1,C3,1,"[Account]","[Account].[Production]",
     "[Date]","[Date].[Calendar].[January 2011]")
D4: =XL3DoWriteback($B$1,C4,1,"[Account]","[Account].[Our %age]",
     "[Date]","[Date].[Calendar].[January 2011]")
D5: =XL3DoWriteback($B$1,C5,1,"[Account]","[Account].[Price]",
     "[Date]","[Date].[Calendar].[January 2011]")
D6: =XL3DoWriteback($B$1,C6,1,"[Account]","[Account].[Forecast Revenue]",
     "[Date]","[Date].[Calendar].[January 2011]")

In this example, C3, C4 and C5 are cells containing the raw values. Since we know that the forecast revenue is a product of the production, the percentage and the price per unit, C6 is just the product over those three cells. The four XL3DoWriteback formulae in column D refer to these value cells, but because the value in cell B1 is FALSE, nothing is written back yet.

In cell A1 is a XL3Link formula that, when clicked, will change B1 to TRUE. This immediately signals the XL3DoWriteback formulae that they should gather and write back their values. Once that transaction has been sent to the cube, the XL3DoWritebacks set cell B1 back to FALSE, and the workbook is back to the ready state.

The Setup

To make it as easy and efficient as possible, we used:

  • one section for values. These were a mix of XL3Lookup formulae, typed-in values and standard Excel formulae
  • one section for XL3DoWriteback formulae. We pared away any excess XL3DoWriteback formulae, leaving only those cells that we were sure we wanted to be writeable
  • a single cell with the boolean value, set to FALSE
  • an XL3Link in a highly visible place, to switch the boolean cell. In this case, the cell containing the boolean value was B1:
=XL3Link(XL3Address($A$1),"Write changes",,XL3Address($B$1),TRUE)

The final workbook looked a little like this (except, of course, much larger!):

A section from the finished workbook

The customer would then enter all the necessary values on the left section, using whatever combination of Excel formulae, cube lookups and typed-in values he needed, without any wait between entries. A single click of the XL3Link then wrote the values back in a single batch, leaving the customer to do other jobs.

The revised model allows the user to update entries quickly and efficiently, without any ‘write’ delay. The numbers to be written back can be calculated using Excel formulae as needed based on the raw input numbers. When the input process is done and checked in Excel, everything can be committed to the cube with one button press. The end result – a happy customer, with more time to plan and analyse the budget, rather than just input it.

Further reading

XL3DoWriteback formula reference

“Prev” and “Next” in XLCubed Slicers

We’ve been asked a few times in the last couple of months if we can build a ‘Previous / Next’ selector for date hierarchies, which allows the user to quickly navigate sequentially through months or days. The answer is of course ‘yes’,  otherwise it would be a very short blog..

One of the key strengths of XLCubed is it’s tight integration with Excel, and it means that with some creative thinking the answer is very rarely  ‘no you can’t’. Here we use a combination of our slicers, the xl3membernavigate function, and standard Excel formulae to produce a very effective selector for just this scenario.

A working example of this which connects to the sample bicycle sales local cube which we  ship with the product is available here or you can view the online demo here.

There are a couple of key things to note with this approach:

1) Slicers are typically populated direct from the cube, which makes them very flexible and dynamic. However a less well known aspect is that slicers can be driven from an excel range, and in this case that’s what we’ll be doing.

2) XL3MemberNavigate(). A fairly new formula which allows you to traverse a hierarchy dynamically in a multitude of different ways. Here we just scratch the surface.

To begin with we need to prepare a range of cells in Excel to base the slicer on, in this case the months, and we also need to ensure it’s dynamic and can change with the underlying data structure.  We need to prepare a table of similar structure to the below.

Cell B2 is the selection made by the user in the slicer, which we’ll come back to. The other columns in the table show:

Description:

Logical description of what the row is

Month:

The month available for selection, determined by whatever the user chooses in the slicer, and the Xl3MemberNavigate formula (Insert Formula – Member Navigate) .

Checked Month:

Validation checks on the month to cater for when the first and last available months are selected.

Slicer Display:

what will be displayed in the slicer dialog for user selection.

The first month uses MemberNavigate to get the first available month. This is very straightforward in the MemberNavigate dialog, and will insert a formula in this syntax: XL3MemberNavigate(1,”[Time]”,”[Time].[Month]”,”FirstMember”). Last month is achieved the same way, but using ‘lastmember’.

Previous and Next are again achieved using MemberNavigate, this time the syntax will be:  XL3MemberNavigate(1,”[Time]”,SlicerData!$B$7,”Previous”).

Displayed month is simply what the user has chosen in the slicer.

 Adding the slicer:

Add a slicer from the XLCubed ribbon (or insert slicer menu in 2003). On the selection tab, choose ‘slicer range’ and select C5:D9 on the table shown above. Then set the slicer Type to be buttons. Lastly, on the settings tab, set the slicer to update cell B2 on the SlicerData sheet.

Optionally, you can also name the slicer and choose to show a title bar, as we have in this example.

On inserting the slicer, you’ll need to resize the control itself, and possibly also the size of the buttons if the data member names are long.

You should now have a slicer which enables Prev/Next selections, along with first and last.

Using the slicer in a report

The slicer isn’t currently connecting to anything, or changing filters within a report. To do that, as it’s not directly connected to a hierarchy in the same way as a standard slicer, we need to go via the excel cell which it updates. So any XLCubed grids or formulae need to reference the cell which the slicer outputs its selection to, in this case in this case SlicerData!$B$2.

In our example we’ve just connected one grid, but there can be as many as required. Our example also gives some sales and costing detail for the main product categories. We also use in-grid sparklines to give a feel for the trend, and these can be drilled or sliced and diced in the same way as a standard grid.

The working example can be downloaded here, or a similar version published to XLCubedWeb used online here.

 

 

Effective Management Reports? Interview with Rolf Hichert

Professor Rolf Hichert is the foremost specialist for information design for financial professionals in the German speaking world. His seminars have been attended by thousands of CFO’s, financial controllers in Germany, Austria, Switzerland and the UK. Recently my friend Martin from INTALIGN had the pleasure to interview Professor Dr. Rolf Hichert about his view on what makes an effective management report:

Professor Hichert, your extensive research claims management reports are often ineffective and largely misunderstood, mainly because they are simply never read. Yet we all continue to create these complex documents. Why is that?

Hichert: Reading management reports is enormously time-consuming, in a time when our corporate culture is particularly time-poor. Concise messages are buried, or missing altogether, phraseologies can be confusing, and notation is often not uniform. Frequently, those who do understand the reports have had prior knowledge of its contents, so they are simply reinforcing what they already know. Financial controllers in particular are frequently frustrated by what they perceive to be a lack of understanding, and interest in their reports, despite the tremendous amount of work they may have put into creating a very comprehensive document. I liken the experience to a newspaper editor, who writes a long, in-depth story and then complains about lack of interest by his readers.

What is the main objective of a management report?

Hichert: Reports need to convey a comprehensive message, otherwise they function merely as a statistic or a reference book, comparable to a telephone directory. “To report” means that the creator of the report has taken a certain position and has something of value or novel to say. This may be in the form of statements, explanations, conclusions or recommendations. So, according to this definition, many management reports are not actually reports at all, but merely an exercise in pontification.

Who should be recipients of reports?

Hichert: Structured reports are usually directed to the executive level, the managing directors, and board members. We’re all contributing to information overload however, and there is a considerable increase in the tendency to now supply these reports to middle management, and even trickle them down to all the company’s employees. Other business partners such as banks and investors also have access or are supplied with reports on a regular basis.

We hear a lot of managers complain about the volume of management reports – is that a common problem?

Hichert: Criticism about the extent and thickness of management reports probably dates back to the first ever management report itself. I come across many companies in which senior management are buried under monthly reports containing over 100 pages, an unsurmountable monthly feat to read. And then there are organizations where reports contain only 10 pages or less. The reasons for the extent of management reports are varied; if a report is targeted at a large diverse group for example, it inevitably becomes more extensive as it has to cover a wide variety of needs. In addition, volume may vary depending upon the objective of the report – wether it is to provide an overview, or to give full and complete details. I believe that the question of validity centres less around the extent of a report, and more around the structure itself – is it easy to read and does it follow clear, consistent structures?

Do you then recommend using more charts in a management report?

Hichert: We live in a visual world, where a picture is worth 1,000 words. Pictures are much quicker and clearer to describe complex facts, which might otherwise require substantial wording. It is important to note though, that we can over-use charts as well. Many management reports use charts to visualize numbers that could easily be described in two brief sentences or less. If I want to refer to an export portion of 50% for example, I can easily do this in one sentence, I really don’t need to waste space on a pie chart that depicts only two halves. Such ‘business charts’ serve primarily for ‘optical loosening up’ reports that otherwise might only contain tables and texts. Financial analysts typically complain to me that “my boss is a numbers man, he doesn’t like charts, he prefers tables.” If you look at the quality of charts produced, you can understand this notion. Typically these charts have very low ‘information density’ and are weak illustrations, with no clear message, ‘cut off’ axes, and lack a consistent concept of notation and design structures.

How important is the inclusion of strategic aspects into management reports?

Hichert: Naturally, the structure and contents of management reports should be as aligned as possible to the company’s overall targets. The now popular introduction of a Balanced Scorecard into an organization, with the objective of aligning operations with corporate strategies, offers the ideal opportunity to rethink and improve corporate reporting systems.

What are your thoughts on packaging reports  ‘decoratively’?

Hichert: We now have easy access to creative programs which render all of us amateur graphic designers. Too often, though the necessary knowledge about basic information design principles is missing. CD (Corporate Design) guidelines, that are in principle important to unify content typically  don’t address those either. From our research, we know that the simpler the structure, the easier the report is to understand. Our work shows that such graphic elements as colored backgrounds, decorative pictures, pseudo-3-D-display, shades, frames or other design facets which may be inserted without meaning, should be considered as noise. Rather than add to a report, these features ultimately reduce the quality and the message of a report. Decorations that are unnecessary additions can dilute and crowd out the message. The over-use of color is the most common source of error. Color should only be used if it has an assigned meaning. One shouldn’t for example, expect that the reader will understand the use of red and green as traffic light colors, indicating stop and go on projects, if these colors are also used in other areas of the report for purely decorative purposes.

What is your recommendation in regards to how to deal with the display of variances between actuals and targets or plans?

Hichert: Typically, the major portion of a management report should demonstrate substantial deviations between targets and current actual values. This includes different forms of deviations, such as, for example, between previous years or even more importantly, corporate numbers versus industry benchmarks. If deviations are important, then they should also be concisely represented and emphasized through colors, arrows or frames. The more important the deviation, the more the emphasis must be marked. Professional report guidelines should ensure that equally relevant deviations are equally marked and represented. And it should apply not only to charts, but to tables and texts as well. This principle should always be applied to any reports in an organization, in a consistent and uniform manner.

So, the format of reports should be standardized?

Hichert: We strongly recommend employing a consistent uniform design concept which can be easily understood and interpreted without confusion. The key sign of a quality report is its ability to convey a message and explain facts in a clear and simple manner. Its objective is not to be an object of beauty. Today, we rarely see organizations that have mapped out clear guidelines and rules for scaling, usage of color, when to use what chart types, tables or texts. But consider the road map which universally utilizes a single color scheme; a river is always blue, the scale is always on each side, and north is always at the top. Whether in Australia, or Africa, the rules of the map remain the same. With management reports, it‘s usually left to the creator whether turnover figures are shown in blue columns or green lines – rendering it difficult guesswork for the executive who is forced to interpret and understand it. To be fair, cartographers needed many hundred years to develop visualization of roads, cities and to unite standards – so the financial controllers still have some time…