VBA

In project finance transactions it is common practice to use debt sculpting when sizing the debt - i.e. finding an appropriate level of debt from the lenders point of view. In our VBA training courses I have found that one of the most appreciated methods is what we refer to as the ‘sequential goal seek’ so I would like to share this method today.

Debt-sculpting in Excel/VBA - alternative methods.

Debt sculpting can be done in a number of ways - often even using simple algebra. In other cases where there are circular logic due to look-back covenants or sweep mechanisms one is required to use a VBA macro. The example below outlines to core principles in how to use Goal Seek in combination with VBA to solve a range of cells to an optimal value - cell by cell.

Automate manual tasks in Excel with VBA

The example is an illustrative example of how simple it can be to automate a manual task without having to introduce any particularly complex VBA code. The focus in our example is on the VBA and Goal Seek rather than the actual Debt Sculpting, but hopefully both messages are clear.

VBA and named ranges

Before looking at the code we need to set up the named ranges in Excel. It is critical to ALWAYS work with range names in Excel even though it may cause confusion to novice users, as there is a great risk that your vba code will stop working over time otherwise.

DebtService: The highlighted yellow range in the image
DSCRDebtDelta: The DSCR Delta row
CountDeltas: The cell with the value ‘6′. This could easily be coded in VBA but for simplicity I have done it in Excel.

VBA Goal Seek example

Sub SeqGoalseek()

Dim i
 Dim NumberOfGoalseeks
 Dim rngDebtService As Range
 Dim rngDSCRDebtDelta As Range

 'Pick up the number of Deltas to goal seek
 NumberOfGoalseeks = [CountDeltas]

'Pick up DebtService range
 Set rngDebtService = Range("DebtService")

'Pick up DSCRDebtDelta
 Set rngDSCRDebtDelta = Range("DSCRDebtDelta")

For i = 1 To NumberOfGoalseeks

'Goalseek DSCRDebtDelta(i) by changing DebtService(i)
 rngDSCRDebtDelta(i).GoalSeek Goal:=0, ChangingCell:=rngDebtService(i)

'End Loop
 Next i

End Sub

How does the VBA goal seek example code work?

The VBA code will run a loop for 6 iteration (=the number of cells to optimise) and for each iteration optimise Debt Service (rngDebtService(i)) as a function of the DSRC Delta (rngDSCRDebtDelta(i)).

If you want to use this in a ‘live’ project finance model I would recommend you to refine the errorhandling and make sure that it is really stable. There is nothing worse than a VBA macro that only works sometimes….

Would you like to get a copy of the VBA goal seek workbook?

You could simply copy the code above and set up the range names and it will work. Alternatively, drop me an email and I can send you my version which may save you a bit of time.

It is not always easy to ensure that a financial model presents well on all screen types and resolution settings. I notice frequently while working on spreadsheets on four different platforms

• Office desktop (3 x 19 inch screens)
• Laptop 13.3 inch laptop
• Blackberry (2 inch screen)
• Home desktop (24 inch widescreen)

There is not one simple way of making sure that your financial model looks great on all platforms but one trick that often helps is the ‘zoom to selection’ functionality. This Excel functionality gives the user the possibility to zoom in on a selected range to fill the screen which is quite useful for disclaimers and cover sheets of financial models. Clearly not even this trick can improve the presentation on the blackberry, but I am sure you can live with that.

Manual ‘Zoom to Selection’ in Excel

To understand the functionality, select a range in Excel (say A1:N44) and then click ‘Selection’ in the Zoom-menu.

Zoom to selection in Excel - dropdown

Excel will then set the zoom-level to whatever is necessary to make the specified range fill the screen.

Automated ‘Zoom to Selection’ using VBA in Excel

The manual Zoom to Selection has limited value as it will only set the zoom level to be right just for your computer and since your clients and colleagues may have different settings they would have to redo it.

By using VBA code this process can be automated and it is quite simple.

Sub ZoomToSelectionDemo()

    'Select the range you want to zoom to
    Range("A1:N44").Select

    'Zoom to the selected range
    ActiveWindow.Zoom = True

    'Select range A1
    Range("A1").Select

End Sub

VBA code for zoom to selection

To see a working example of this, feel free to download one of our Excel Tutorials and have a look at the VBA code in the Disclaimer section.

The data security of Excel spreadsheets is a joke. Anyone with half a talent can crack the passwords at most levels using either simple VBA code or one of the Russian password crackers on Google. This is a well-known security issue for most people having a technical perspective on Excel, but in the banking / transaction space this is certainly not the case. It is not uncommon that people responsible for multi-billion dollar investments protect parts of their Excel models by hiding and password protecting individual sheets without being aware that this ’security’ can be circumvented in seconds using simple VBA code available on most Excel forums.

How secure is the password on ‘Protect Sheet’?

Below is a typical VBA example of the code demonstrating the weaknesses of the terrible security in the ‘Protect sheet’ functionality. Password-protect a sheet and run the code below to generate another(!) password for that sheet.

Sub PasswordBreaker()

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _

Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If ActiveSheet.ProtectContents = False Then

MsgBox “One usable password is ” & Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

ActiveWorkbook.Sheets(1).Select

Range(”a1″).FormulaR1C1 = Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

End Sub

How to send secure Excel information to external parties?

The day-to-day impact of the security flaws in Excel is that many bankers have to extract key information from Excel into PowerPoint which is a waste of everyone’s time but at least it can get distributing all of the data in the Excel workbook. Funnily enough you often see the Excel workbook pasted as an Excel object into PowerPoint which means that you can simply copy-past the whole workbook back into Excel…

Can you send safe Excel Workbooks?

There are a number of different providers working on security solutions for these problems. Many individuals have built ‘time-bombs’ etc in VBA which disables or destroys the workbook after a set date, however these often rely upon the user enabling VBA code when opening the workbook… A simple ‘No’ to the question ‘Enable Macros?’ therefore disables the time-bomb and you get full access to the workbook. One classic example of this is Chip Pearson’s  ’Timebombing a Workbook‘  which is an excellent article outlining a pragmatic view of Excel Security and should be read by all Excel users.

How to improve the security of Excel workbooks and VBA?

It would be great if there was one standard trick that solved all these issues. Unfortunately that is not yet the case and the industry of Excel users are still searching for a simple-to-use and still secure solution. One new up-and-coming software development company that appears to have overcome the biggest hurdles of secure Excel workbooks is DataSafeExcel. DataSafeXl was started by Andreas Ashiotis who has a background in KPMG financial modelling in London. I have only tested an early prototype of DataSafeXL but it looks very interesting and Andreas swears that the security really is at the level needed for institutional transactions. In a rather clever way the software can be used to set things like expiry dates and it then disables all the usual ways of disabling functionality in the normal ways. If someone’s used this in real applications it would be great to hear more about it.

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!