I started building and working on Excel-based models in 2008 when I was an economics PhD candidate and working as a trainer for the executive training program of Queen’s University. At the time, the models were mostly designed for training purposes. My focus was mainly the cost and benefit analysis of investment projects, rather than their financial analysis. In 2010, Professor Glenn Jenkins selected me to be part of his team of consultants at the African Development Bank (AfDB). That’s when I had to face and model complex issues like debt sizing, debt service reserve accounts, different tranches of debt and equity, shareholder loans, cash sweeps, guarantee support programs and other complications. At the same time, I also had to deal with circularity in spreadsheets.
The first financial model that I built for the AfDB was the lenders’ financial model for Abidjan’s ‘Third Bridge’ project. This project had all manner of complications, including circular references as you might imagine. I knew that I could not let the circularity run freely in my model, so that was when I wrote my first VBA code (a copy and paste code with a loop). I remember I was so excited and proud of the end result and I even added a flashy button and named it ‘optimization’ in a summary sheet I named ‘dashboard’!
This method worked for some time and I was happy with my copy-and-paste macro. However, as I started working on more complex issues like bid models and revenue-guarantee structures, I noticed that the copy-and-paste macro was limiting my models. Trust me: when every time you change an input, your model copies and pastes multiple rows repeatedly (and runs a goal seek simultaneously), it will take quite a while. If you are patient, you might be fine working with this model yourself, but we usually use these models during contract negotiations and have to run scenarios live during meetings. While the model is running, you have either to endure the awkward silence in the room or to engage in small talk about the weather!
When facing a problem, you can seek a solution yourself or find someone who has found a solution. For me that someone was Edward Bodmer. I had been following Edward’s work for years. While everyone else was watching Game of Thrones, I was watching Edward’s video tutorials on YouTube. I was desperate to adopt his method and get rid of the copy and paste macro I was once so proud of. Having tried several times and failed, it was only last year that I had the chance of one-to-one training with Edward Bodmer. Finally, I could learn some of his tricks.
There have been two turning points in my life. First was having Lasik eye surgery that let me see without my thick glasses. Second was meeting Edward Bodmer. He opened my eyes to a new horizon in financial Excel modelling.
Bodmer’s technique applies user-defined functions to solve any circular reference problem in Excel. In this method you need to reprogram in VBA the equations causing circularity in the financial model, and then use this function in the Excel model. This method not only makes your model more flexible and faster; it also helps you to debug your models. This is because you are basically programming the same calculations twice: once in Excel and once in VBA. Most times the mistakes are in your Excel formulae rather than in the VBA code!
For more on that subject, I refer you to the creator himself and strongly recommend that you take a course with him to learn his techniques and to be introduced to the future of financial modelling.
www.financeenergyinstitute.com
