Why you don't have to be a VBA macro master to build a financial model - Part 2

 

In part 2 of our “Why you don’t need to be a macro master to build a Financial Model” we are going to look at the main types of macros you’ll need to produce an advanced financial model. The three types of macros we are going to look at are:

 

1)      A goal seek macro – can be used to break a circularity

2)      A single cell copy and paste macro – we covered this in part 1 of this series

3)      A multiple cell copy and paste macro

Goal Seek Macro

Ok, this is probably the easiest macro to implement. It i

s very similar to the single cell copy and paste macro. If you are not aware of what a goal seek is we will try and sum it up in a line or two.

A goal seek will try and solve a certain cell to a specified value by changing another. If you look at the below figure we have 50 sales, each with a profit margin of $3.5/unit giving a total profit of $175. We want to know what the profit margin would have to be to get a total profit of $200. Obviously the profit margin has to be $4 per unit. But look how we found it. We goal seeked it by setting the total profit cell to 200 by changing the profit margin per unit.  

 

Ok, now let’s cheat by going back to our spreadsheet in part 1. As per part 1 we are going to record a macro. Go to View, Macros, Record Macro. Let’s call the macro GoalSeek. Go to the DebtCheck value and select Data, What-if Analysis and then Goal Seek. We want to select to value 0, by changing cell DebtHard value as per the below figure. 

  

Stop the recording by pressing the stop button in the bottom left hand corner.

Now let’s edit the macro. Go to View then Macros, View Macros. Select the GoalSeek macro and press edit.

We are going to delete the Range("D13").Select at the top. And we are going to replace the cells with their actual names as per below.

 

Now go back to the spreadsheet. Put 12,500 into the DebtHard cell (G12). Go to Data, Macro and View Macro. Select GoalSeek and press Run. This should solve the macro and set the DebtCheck to 0.  

Single Cell Copy and Paste Macro

Now we looked at a single cell copy and paste macro in the first part of this series so we are not going to cover it here. If you want to check out that article, click here. 

Multiple Cell Copy and Paste Macro

Ok, we’re going to leave you to do a bit of homework on this one. Depending on how you go we might look at doing a third part to this course. Go to the spreadsheet and scroll down to question two.  See how you go with it. If you’d like us to do a third part to this macros series, leave us a comment or send us an email. Hint: In this question you might find that the countif function will come in handy.

We cover macros in detail in our advanced toll road training course. Click here to check it out.

 

 

 

 

Why you don't need to be a macro master to build a financial model - Part 1

Normal 0 false false false false EN-GB X-NONE X-NONE

Many self-proclaimed financial modeling experts will tell you that you need to have a good grasp of Visual Basic for Applications (VBA) and macros. Well at Video Financial Modelling we are here to tell you that you don’t. Well not if you’ve read this article.

Resources

YouTube Video

Starter Spreadsheet

Final Spreadsheet

Why do we need macros?

In simplistic terms a macro is a program that implements a task. VBA macros are often used to automate tedious financial modeling tasks or to break circularities.

Why don’t you need to be a macro master?

Well there are a number of reasons why you don’t need to be a VBA macro master.

1)      Firstly, we’re going to give you some common macros that you can simply copy and paste into VBA in your financial model

2)      Secondly, it is really easy to record your own macros and then manipulate them to suit your particular situation. We’ll look at an example a bit later

3)      Thirdly and finally in all the financial models we have seen (and we’ve seen a lot) there are only a few situations that require a macro. We will run through each below.

Firstly let’s look at an example on how to record a macro.

Example – Manually recording a macro and then manipulating it

In this example we are going to look at Firstly open the accompanying blog workbook. Also remember you can watch the blog youtube video to follow this example.

To start recording a macro, press View on the menu and then select Macros. Select Record Macro on the popdown menu. Let’s call the macro DebtSolve.

Select the Debt Amount – Calculated value and then copy the cell (CTRL+c) and paste special values (Home, Paste, Values) in the Debt Amount – Hardcoded cell. Press enter.

 

 

Now press the stop button in the bottom left hand corner.

 

 

Go to Data, Macro and push View Macro. Then select the DebtSolve macro and press edit. We are now going to put the named cells into the macro and add in a loop.

Ok, let’s firstly put the relevant named ranges in. Just copy both the DebtCalc and DebtHard and put them in place of the relevant cells in the VBA file.

Now let’s add the loop.

Simply copy and paste the following code:

Do While Range("DebtCheck") <> 0.

We’ll also have to put in:

Range(“DebtCalc”) to replace the Selection before copy.

Place it at the start of the code after the Sub Debt Solve() and the green commentary.

And now put a Loop before the End Sub. The final macro should look something like this.

This loop will keep copying and pasting the calculated debt amount into the hardcoded debt amount, until these values converge and the debt check becomes zero.

Now let’s go and select Insert on the menu, select Shape and then a rectangle. Draw it on the sheet. Then right click on the rectangle and select Assign Macro and press DebtSolve.

Now press the button you just created and voila you’ve created a macro that solves a common financial modeling circularity. Change the hardcoded value or the arrangement fee and press the button again. It should solve.

In the next part to this macro series we are going to look at the main types of macros you’re going to need when you’re financial modeling.