Visualise this - Financial modelling and forecasting

Showing high-level financial results for management purposes in a quick and meaningful way is a challenge. The standard comparisons to budget and to last year for both value and proportionate change requires six columns of figures in addition to the actual result. Each column needs to be studied across the page and the impact of each variance mentally held. Simple graphical techniques such as bar graphs can convey the information, but each element (actual, budget and prior year) have the same visual shape that needs to be distinguished by colour or shading. Close attention is required to interpret the result properly.

To date, I have not seen a method which conveys the relative management performance in a simple but readily interpretable way. The Visual Analytics course run by ICAEW caught my eye and I attended, looking for a solution. The horizontal bullet graphs drew my interest (in basic form in figure 1).

This was something to build on. The main drawback of the presentation is the light grey shaded peripheral areas. In practice, the boundaries between poor, satisfactory and good are subjective and they are another way of showing tolerances around the target performance. It was obvious that the light grey area should be used to report last year’s performance. This is a figure that is known and immutable. The symbol marker noted in figure 1 as the comparative measure is the clear candidate for the budget figure.

A limitation of the graph templates given on the course for the horizontal presentation of a series of figures is that it requires each set to be reported within its own independent graph. Adjacent graphs need to have a common scale so that comparison between them will be quantitative as well as qualitative. I initially solved this by setting the axis parameters for each graph manually. A macro re-adjusting of the axis to a common standard was an improvement, but had to be invoked every time the figures changed. This was prone to generating unwanted results.

The use of individual graphs for each reported element needed to be overcome and the format of the input data simplified. Using other resources, I was able to adapt and perfect the graphing so that all the reported items could be included in one Excel graph. This ensures a common scale and allows the graph to re-size automatically with fresh figures. The figure shown below (figure 2) shows the arrangement of the input data and resultant graph in a simplified form.

The content of the input data is largely self-explanatory, except for the placeholder element. This is required to control the positioning of the budget measure, which is graphed on a secondary axis. As each figure is against a different heading on the profit and loss, spaces have been inserted between the bars within the graph data area to deliver a normal spatial layout. The placeholder value needs to be adjusted to position the budget amount correctly against the respective headings. The graph has been snapped to the grid to appear alongside the description and value, so it can be moved and sized along with them. Most normal Excel graphing elements, such as borders, scales, grid lines and axis descriptions, are suppressed so that the main visual message is the performance and its comparison.

For each measure it is necessary only to show the actual figure, as thereafter the relative position of the comparative figures of budget and last year give the information normally derived from a value and percentage variance reported as numbers. The figure 2 example clearly displays that each type of performance yields its own unique shape.

An excess result over last year and budget (overheads in this example) looks different from where the result is better than last year but below budget (turnover). The user quickly gets used to the shapes that indicate each type of performance outcome.

Where the graph gives an analysis of like figures, such as turnover, the spaces can be removed, as each reported item is directly comparable with all others. I use the graphs to report on high-level performance at the front of the management information (MI) pack. The principles explained are evident and the reader is cued to the main trading issues (see figure 3).

Costs are graphed as positive figures so that they fall on the same side of the axis as revenue. Clearly the shape for excess costs is the same as excess revenue and yet the impact on profit is adverse. The reader will get used to this, but the design demands this to avoid confusion; the reported measures are few in number and kept at a high level.

The source profit and loss for the top part of the above figure has some other minor figures that were too small to report meaningfully, so they have been excluded and the figures displayed do not vertically cast. This fits with the aim of producing a high-level visual summary. Where identically-sourced measures are reported (turnover or margin), as many items as needed can be displayed.

The remainder of the MI pack consists of an in-depth commentary and supporting financial schedules. Extra detail can then be found where required, and as prompted by the graphs. They fulfil a dashboard purpose and are my first place to look when assessing new period figures.

I have received a good reaction to these visual P&Ls and think that they are a significant aid to high-level conditioning of the reader to the entity’s financial performance. The graphs are also a good way to display high-level sales figures.

• Visualise this, Finance & Management magazine, Issue 211, June 2015

The ICAEW Library & Information Service provides access to a selection of key business and reference eBooks from leading publishers.

Further reading on displaying financial data is available through the eBooks below.