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. 

 

 

  

 

Self-Study Financial Modeling Training – The Way of the Future

If you work in the finance industry, with:

• an unknown economic outlook;
• companies budgets stretched to the limits; and
• unemployment at all-time highs

it is important to stand out from the rest.

Learning and improving your financial modeling skills can help you to do this. At Video Financial Modelling we understand that not everyone can afford training sessions ranging from $500-$2,000 each.

To cater to this Video Financial Modelling has setup a number of self-study financial modeling training courses to cater for people who want the best possible training at a fraction of the cost.  We have training courses to suit all levels of financial modeling expertise.

What differentiates a self-study financial modeling training course from a seminar? Yes, the price, but there are also a number of other factors.

1. Flexibility – given that the training is pre-recorded you can learn at your own pace. If you don’t catch a concept you can easily just rewind the video and watch it again.
2. Quality – since we only do each training course once, we only pick the best instructors to deliver you content.  
3. Ongoing customer support – because we have lowered our cost base, we supply excellent ongoing customer support and can answer your training course questions promptly.
4. Limited risk – we are so confident in our products that we have “try before you buy options” (at a nominal cost) for many of our training courses. If you proceed to purchase the full version the nominal cost will be deducted from the full purchase price.

In addition to the above, the self-study courses are hands-on giving you the best possible practical experience.  

Check the self-study financial modeling training at Video Financial Modelling today. 

 

What are Financial Statements?

Financial statements commonly refer to formal records of the financial actions of businesses, entities and in some instances individuals. The financial statements are used by various stakeholders in making economic decisions.

Businesses which are listed are usually required to prepare and issue their financial statements to their shareholders. In most instances these financial statements are prepared in accordance with International Financial Reporting Standards (IFRS), US Generally Accepted Accounting Principles (GAAP) or UK GAAP. In recent times the accounting bodies have been working together to converge these reporting standards.

There are generally four major financial statements which are reported by businesses.

1)      Balance Sheet

2)      Income Statement

3)      Statement of Owner’s Equity

4)      Cash Flow Statement

The Balance Sheet shows an accurate representation of a business’s financial position at a certain fixed point in time. The Balance Sheet has three broad categories, assets, liabilities and owner’s equity. For example a bank loan would be shown as a liability on the Balance Sheet.

The Income Statement can be simply thought of as total revenues minus total expenses (including financing costs) over a period of time, usually a year. The Income Statement is usually based on an accrual basis – meaning that the revenues and expenses are recorded when incurred, not necessarily paid. In general an Income Statement is meant to show the performance of a company. i.e. higher Net Income generally means better performance (all else being equal).

The statement of owner’s equity shows movements in the Owner’s Equity component of the Balance Sheet. You can use a corkscrew account to calculate the movements from the start of the period to the end of the period. In general these movements are usually attributable to:  

• total comprehensive income;
• owners' investments;
• dividends;
• owners' withdrawals of capital; and
• treasury share transactions.

The Cash Flow Statement represents the flow of cash in and out of the business. The statement is usually divided into three categories:

1)      operating activities;

2)      investing activities; and

3)      financing activities.

If you liked this article, check out the financial modelling training or products at Video Financial Modelling. 

Working Capital - Debtors and Creditors

If you've been working or studying in finance you have probably heard of working capital before. If not then you might be wondering what the flip we are talking about. Well working capital is usually defined as current assets less current liabilities. If working capital is positive, the company has enough current assets to meet its current liabilities. If not then the company may have short term liquidity problems. 

In this blog tutorial we are going to look at two components of working capital, debtors or accounts receivable and creditors or accounts payable. 

What are Debtors and Creditors?

Ok, let's first start with Debtors. Debtors are current assets which arise when revenues are accrued but not paid. For example you book a sale of $100 on account, however you receive the cash 30 days from that date. The two entries you would make are:

1. when you book the sale on account - debit debtors/accounts receivable (Balance Sheet item) and credit revenue (P&L item)
2. when you receive the cash - debit cash (Balance Sheet item) and credit debtors/accounts receivable (Balance Sheet item reversing the account entry in (1))

Creditors are very similar to the above. 

Modelling Debtors and Creditors 

Ok, so you should have a good feeling of how this works now, so let's look modelling this. 

In 99% of the cases we can calculate debtor and creditor balances using the following formulae:

Debtor Balance = Revenue Accrual x Debtor Days/Days in Period

Creditor Balance = Expense Accrual x Creditor Days/Days in Period

For a yearly timescale the Days in Period would be equal to 365 days (we'll ignore leap years). 

From here you can find the total cash received or paid from revenue and expenses respectively. Let's look at this from a debtor perspective and utilising a corkscrew account (if you don't know what this is see our Corkscrew Account. What the? blog).

Debtor Account

Opening Balance          50

Add: Revenue Accrual  50 

Less: Cash Received    [x]

Closing Balance             [y]

Firstly let's find y. If we are looking at a year timescale and 30 day debtor days, then our closing balance (y) would be 50 x 30/365 = 4.1. 

Now we need to find x. Rearranging the formula we get: 

Cash Received (x) = Opening Balance + Revenue Accrual - Closing Balance (y)

 = 50 +50 - 4.1

 = 95.9

Ok, that might be a bit of brain dump.... so let's look at some examples. 

Debtor and Creditor Examples

You can follow along with the examples by downloading the Working Capital - Debtors and Creditors YouTube video and the Excel spreadsheet.  

Example 1

You forecast $2,000,000 of sales on credit each year from 2011-2015. Your credit terms are 30 days. What is the size of your debtor/accounts receivable in each of 2011-2015?

Ok, firstly and as always you need to put in a timescale as below:

Next let's put in the Revenue - Accrual. 

Now using the formula Revenue Accrual x 30/365 calculate the debtor balance.

Now you could calculate the cash received, but we will put in one more step. We will find the movements in debtors (i.e. debtor opening balance - debtor closing balance).

Now if we add the above debtor balance with the revenue - accruals we should get the cash received. 

Example 2 - Homework

Now we are not going to go through this example, but we run through the solution on YouTube and you can find the answers in the Excel spreadsheet. So why don't you give the problem a go?

Question: You buy services worth $1,000 every year from 2008 to 2011. You have credit terms of 90 days and you start with a credit balance of $250 at the start of 2008. What is your creditor/account payable balance at the end of 2011?

Take your Excel and financial modelling skills to the next level. Try our Excel and Financial Modelling training courses today. 

 

 

 

Financial Modelling - Sensitivity and Scenario Analysis

If a financial model is setup using best practice modelling techniques then putting sensitivities and scenarios should be simple. What is the difference between a sensitivity and scenario you may be asking. Well a sensitivity only flexes of one input, whilst a scenario flexes multiple inputs.

About the Inputs/Assumptions

At Video Financial Modelling we usually have one assumption sheet for a simple financial model and two assumptions sheets for complex financial models. The two assumptions sheets for a complex financial model are:

1)      A static assumptions sheet – inputs that don’t change over time. For example a scheduled construction end date.

2)      A time-series assumptions sheet – inputs that can change over time. For example inflation indicies.

Setting up a sensitivity or scenario analysis

Below are brief instructions to setup a sensitivity or scenario analysis manager.  

Step 1: The first step in setting up a sensitivity or scenario analysis is to incorporate, what we like to call a scenario selector. The scenario selector is simply a number between 1 and the “maximum number of scenarios you want to run”. For example say you want to run ten sensitivities/scenarios, then the maximum number you’d put in your scenario selector would be ten.

A simple scenario selector would just look like this.

Not much to it is there? Bear with us though. 

Step 2: Now that you’ve got a scenario selector let’s move onto incorporating a simple offset formula. The basis of this formula is to grab the relevant data from the selected scenario. The form of the formula we usually use for this is:

= offset(current cell reference, ,scenario selector) for static pages

= offset(current cell reference, scenario selector,) for time-series pages

Obviously if you have rows/columns in between the actual assumption you use in your model and your input, then you may need to adjust the above formula.

Step 3: So what happens if you want to use scenario 1’s input if you don’t have any data in the selected scenario? Well that is easy, we just add an IF statement in as shown below.

=if(offset(current cell reference, ,scenario selector)=””,scenario 1 cell, offset(current cell reference, ,scenario selector)) – for a static sheet.

Obviously it is nearly the same for a time-series sheet.

The logic behind this is that we don’t have to put all inputs in for every different scenario we do. We just put in the inputs we want to change from the scenario 1 (usually called the base case).

Sensitivity and Scenario Example

You can follow along with the below example by downloading the YouTube video and spreadsheet.

Ok, this might be confusing right now, but once we look at the below example our methodology should become apparent.

Say we had the following inputs and we wanted to find the NPV of equity distributions. Let's assume there are no taxes and our profit is fully distributed to shareholders as there is no debt in the company. 

Now let’s put in a scenario selector (Step 1 above). Obviously this scenario selector will not work on its own, so let’s now put in a an offset formula as per Step 2 above. The result is shown below. Now all our inputs will be moved to the 1st scenario and the formula will be placed where we previously had inputs.

Ok let’s now run a scenario, say scenario 2. Let’s put in rev of 55 and ops of 40. What happens? Because we don’t have inflation (and haven’t incorporated Step 3 above) in we won’t have any inflation.

Let’s change the formula as per Step 3 above to get:

Now we have an active scenario manager which we can change and run scenarios on. Putting it all together you should get NPV of equity distributions of 135 for the base case and 101 for scenario 2. See the below picture or download the spreadsheet to see the workings. 

Now for all of you out there who think you’ve got it why don’t you try this one. Find the NPV of the equity distributions if the inflation is 2.5% for the first five years and 7% for the next five years. Hint: Your scenarios will need to go down the page as this is now a time-series assumption (i.e. changes over time). The NPV of the equity distributions you should come out to be 137. 

If you liked this tutorial, try one of our Financial Modelling or Excel training courses today.  

MOD Function - Bridging the Time Gap in Excel

Say you had quarterly data and you want to collect this data onto a yearly page? How would you go about it? Probably a hundred different ideas came to your head when I raised that question. Video Financial Modelling has seen all of these. One sticks out ahead of the rest, the MOD function. 

Using the MOD function

The MOD function is quite simply awesome. In simple terms it takes a number and a divisor and spits out a remainder. The equation is shown below.

= MOD(number,divisor)

Does that bring back memories of school maths? Well for all of those people who can't remember, here is a couple of examples to help the mental juices:

• mod(11,3)   - 11/3 is 3, with a remainder of 2. So MOD gives us 2. 
• mod(12,3) - yes you guessed it. 0.
• mod(-11,3) - ok so this is getting a bit harder. The answer is 1. Why? Well because the MOD function gives the amount by which a number exceeds the largest integer multiple of the divisor that is not greater than that number. In this case the largest integer multiple that doesn't exceed -11 is -12. Still confused. Have a play around with the function. 

MOD function Example

Ok, so I promised you that I would show you how to grab data from a quarterly page and aggregate it on a yearly page so let's get started. Download the associated YouTube video and Excel spreadsheet. 

We have the following quarterly dates and associated revenue numbers. 

We want to aggregate these numbers on an annual page. Let's firstly put in annual dates in. You can put these on a seperate page or keep it simple and have it on the same page. This is shown below.

Now the magic. Below the quartlery dates, let's put in the following formula. Don't worry we will explain the components soon. 

= EOMONTH(quarter end date, MOD(month(first period ending date)-month(quarter end date),12)

This should look something like this:

Copy this fomrula across. Wow magic... hopefully this formula gives you the year ending for each of the dates? It sure does, now how does it work. Let's break it down:

= EOMONTH(Component 1,Component 2)

• Component 1: quarter end date
• Component 2: MOD(month(first period ending date)-month(quarter end date),12)

The first component should be pretty self explanatory, so let's look at the second component by looking at some brief examples.

Ok given that the first period ending date is always 31 Dec 2012, the month(first period ending date) will always equal 12. Simplifying the formula we get:

= MOD(12-month(quarter end date),12)

There are 4 possible options for the month(quarter end date). 3, 6, 9 and 12. i.e. corresponding to March, June, September and December. Let's look at each in turn.

• March - MOD(12-3,12)=9 
• June - MOD(12-6,12)=6
• Sep - MOD(12-9,12)=3
• Dec - MOD(12-12,12)=0

If you're switched on you should see a pattern above. The result is equivalent to the number of months to the year end. So if we combine this with an EOMONTH formula we will get to the year end. 

Now if we do a sumif formula (as below) we can aggregate the revenue numbers into yearly results.

We should get the following:

 

Now you may be wondering why we didn't just use a Year formula and then a sumif. Well that is definitely another option, but say you now had a monthly construction page and you want to aggregate or lookup this data in a quarterly page. Trust me this happens all the time in project finance deals. Well the answer.... use the above methodology and you can't go wrong. 

If you liked this blog tutorial check out our new advanced training course, Advanced Toll Road Model Training Course. 

For more free blog tutorials go to Video Financial Modelling's Blog.