There are many ways to verify or analyse financial outputs. Clearly all financial outputs must be correct, but the financial analyst must be able to deliver added value via summaries, dashboard and Excel charts. 

Excel Conditional Formatting is one tool, which enables modelling professionals to improve the financial analysis and presentation of important financial and operational data. This article and video will focus primarily on Excel Traffic Lights, via Excel Conditional Formatting, in order to improve the analytical demonstration of high-level financial performance.

Strategize Financial Modelling – Excel Traffic Lights.xlsm

 

The Excel Dashboard

As the following dashboard of monthly cash receipts demonstrates, there are many ways to summarise and present high-level financial performance. It is always advisable in a best practice financial model, to integrate error checks in the dashboard, which will safeguard the integrity and accuracy of our output, summary table.

There are many ways a best practice financial model can implement Excel Conditional Formatting, which this video will present a high-level précis of.

 

1. Highlight Cells Rules

This option is highly flexible in its analytical use. In this demonstration, we are going to specify “greater than” $80,000 to highlight all monthly cash receipts above this figure. Like with all of these examples, we are going to undo these conditional format options, because we will focus on Excel Traffic Lights at the end.

 

2. Top/Bottom Rules

Here users are able to produce a statistical presentation of say the top 10 monthly cash receipt amounts, across the entire table of monthly amounts by debtors. 

Clearly, this is a fast and effective approach to better understand a certain aspect of financial performance, such as the best or worst monthly cash receipts by debtor.

 

3. Data Bars

This conditional formatting alternative will format a cell with a horizontal bar line, which is based on the cell’s value compared to the values in the other cell range.

It is a great tool because we can, for example in this summary table, visually identify smaller or large monthly cash receipt amounts by debtor. As always, we have the ability to adjust the color or the gradient of the data bar.

 

4. Color Scales

This option can color code a data array based on the respective cell’s output value. By applying conditional cell formatting to fill a cell’s color, we can compare and analyse the cell’s value, in comparison to the cells in the data range. 

This option, like the previous options, can be edited and adjusted for personal preference and analytical needs.

 

5. Icon Sets – Shapes – Excel Traffic Lights

Spreadsheet users can apply Excel Conditional Formatting, through the “Icon Sets” to produce complimentary Traffic Light objects beside key financial outputs. 

Firstly we are going to format the monthly cash receipts, which will produce either a red, amber or green traffic light beside each. This will be based on that cell’s value compared to the value in the other cells.

We will undo these and format the Year to Date Variance % by each debtor. Next, we will edit the rule by adjusting the percentile for green and amber for the data range; the red traffic light will format cells below this percentile range.

We then format the Year to Date values by debtor.

I am not happy with the distribution, which the Excel Traffic Lights is producing, thus I am going to edit the distribution parameters. Again, we will allocate 50% to the green, 20% to amber and the balance to red.

This will clearly adjust our output of Excel Traffic Lights – which we can see.

 

Final Remarks

This article and video illustrate the immense value of the Excel Traffic Lights in financial modelling, via its application in Excel Conditional Formatting. It will help to improve the aesthetic presentation and communication of key financial metrics to users of financial models.