Excel Traffic Lights.
This video will illustrate the value of Excel Traffic Lights in a financial model. 0:00:07.066,0:00:07.033
There are many ways to verify or analyse financial outputs. 0:00:11.066,0:00:11.000
Clearly all financial outputs must be correct, but the financial analyst must be able to deliver
value-added summaries, dashboards or Excel charts.
As the following dashboard of monthly cash receipts demonstrates,
there are many ways to summarise and present high-level financial performance.
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.
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 receipt above this figure.
Like with all of these example, we are going to undo these conditional format options,
because we will focus on Excel Traffic Lights at the end.
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.
Data Bars
This conditional formatting alternative will format a cell with a horizontal bar line, 0:01:45.033,0:01:45.000
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 larger monthly cash receipt amounts by debtors.
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.
Icon Sets – 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, 0:03:02.066,0:03:02.000
which will produce either a red, amber or green traffic light beside each.
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.
This illustrates the immense value of the Excel Traffic Lights in financial modelling.