presentation

Representing a corporate graphical profile in Excel is not always easy and can sometimes take a bit of work to get right. We have recently experimented a bit with the use of different fonts to improve the presentation of some recent financial models.

Using Webdings to insert new characters

To re-create the Corality ‘check’-symbol in the logo we created a list based on the Character ‘a’ and formatted as Webdings.

To insert a new symbol from an unusual font click Insert -> Symbol.

By choosing Webdings and the selecting the ‘check’ symbol we can achieve the a symbol that is similar to the Corality check.

Make sure to format the cells as Webdings to see the ‘a’ represented as a ‘check symbol’.

This is unlikely to revolutionize your financial modelling, but could be a nice effect for a financial model that needs to be prepared for professional printing, like an Information Memorandum of a pitch document.

EDIT: Enhanced functionality with built in colour formatting (Thanks Denver McCann at Numeritas for inspriration)

The image below should be read after you have read the discussion below (the comment from Denver McCann at Numeritas and my reply)

Custom Format with 'custom conditional formatting

The majority of the time at Corality we audit financial models or develop user friendly financial models for our clients. However, in a field that develops as quickly as financial modelling we need to make sure that we stay ahead of the competition - even in the long term.

Our method to constantly re-invent and develop our skill set is to experiment with Excel to develop new methods and applications that we had not earlier thought of. Every now and then we come up with something useful that can be integrated into our day to day practice of financial modelling. This time, I am not sure if that is what happened…

Using VBA to generate a cross-cursor

I presented a VBA training course in Sydney last week and one of the applications in that course is a dynamic cross-cursor for highlighting relevant data in a matrix format. (Here is an introduction to basic conditional formatting if you need one of those, and a bit more advanced here)

VBA cross-cursor with conditional formatting

The VBA exercise shows the power of the worksheet functions and how it can be used to improve model presentation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Generate a circle around the selected cell

After the VBA course I kept experimenting with the same concept, i.e. updating the worksheet based on the properties of the selected cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Description: Returns column and row of the selected cell
'Author: Rickard Warnelid - Corality
'When to run: Automatically returns column and row of selected cell

    'Return selected column
    [CursorColumn] = Target.Column

    'Return selected row
    [CursorRow] = Target.Row

End Sub

Convert the VBA trick into a ‘Find the hidden treasure’

This time I thought I would expand the conditional formatting to generate a circle around the selected cell by back-solving the equation c^2 = a^2 + b^2. This gives a circle with a fixed radius. The second step was to apply a scaling factor inversely related to the distance of the active cell and a defined Target Cell. The scaling factor was then used to determine the size of the highlighted circle around the active cell.

Excel conditional formatting calculations

With “O17″ as the location of the ‘hidden treasure, the conditional formatting looks like this:

=SQRT((COLUMN(F20)-CursorColumn)^2+(ROW(F20)-CursorRow)^2)<$D$14

Graphical representation of combination of VBA and conditional formatting trick

Not even close...

Getting closer...

Even closer....

Found it!

I have been scratching my brain, but just can’t come up with a useful commercial application for this rather neat VBA trick. I suppose it could be used to really prove to your friends and colleagues that there is a real geek inside you, but that’s probably about it. None the less, it is a pretty cool feature given that it is generated with only two lines of VBA code.

Download the conditional formatting and VBA example here

If you can think of a useful application for this, please let me know!