In my previous post, I answered the frequently asked questions about the UDF parallel model.
In this post, I will take you through a typical project finance model and show you how things can get ugly and difficult when you are dealing with a model with multiple copy and paste codes.
First of all, I am working with a standard model which is in compliance with the fundamental rules of financial modeling.
- Circular reference in sources and uses of funds:
- Circular Reference in Taxes
- Circular reference in debt sizing using CFADS
- Circular reference in debt sculpting
In order to solve the circular reference problems above, I have created 6 copy and paste modules.
I then created a global copy and paste macro code that runs all the copy and paste codes until there’s no difference between the calculated lines and the pasted as values lines.
When I run the global copy and paste macro it takes almost 15 seconds for the macro to run.
When I am in meeting and use the model to run scenarios live with other people, I disactivate the debt sculpting and debt sizing copy and paste macros so that the model runs faster. If however, I need to run a scenario using debt sizing then I need to ask my audience to either be patient or tell them that I will do it later and give them the results after the meeting.
For me this is a problem. Also this is not the most complex model, if I include re-financing, Bridge loan, shareholder loan, cash sweep, stand by debt and other calculation blocks then there will be a need for more copy and paste macros.
For every problem, there’s a solution and if you can’t find a solution yourself, you should find someone who has found the solution. In this case, when we all were happily pressing our copy and paste buttons. professor Edward Bodmer back in the early 2000 realized that there’s a problem and started developing a solution. His solution is to create a User Defined function that can do the calculation in VBA with a loop and we use the function in our excel model.
In the next post, I will show you how to insert the UDF parallel model in the same financial model that we went through together in this post.
