The Beginner's Guide: Financial Modeling Using Excel and VBA

Financial Modeling Using Excel and VBA: Automation is the buzz word in today’s corporate world.

Whether it is the manufacturing industry or the service industry, all businesses are aiming to reduce the human element for critical processes and tasks to improve efficiency and output.

As a finance aspirant, you will see this trend in finance companies as well.

Calculators are replaced by laptops, ledgers are replaced by spreadsheets and hardbound documented financial models are replaced by dashboards.

If you want to pursue a career in finance, it is of utmost importance for you to know the latest trends in your domain, and more importantly how to use automation in your day to day activities as a finance professional.

In this article, I will tell you about VBA financial modeling (Visual Basic Application) in Excel.

Introduction to Financial Modeling

Financial modeling in very simple terms is…

“systematic creation of a logical structure, to process and analyze a data set, so as to arrive at conclusive financial decisions.”

It can be done on the back of an envelope or using state of the art applications.

The end objective is always to derive logical conclusions.

In the current age of information and technology, financial modeling has become exhaustive and speedy at the same time.

With usage of technology and automation, long complex calculations can be done within seconds just by click of a button on your computer screen.

But to be able to build one such model, it takes a lot of time and efforts. You need to be thorough with the basic concepts of finance as well as be well-versed with the usage of functions and algorithms in Excel.

Where Is Financial Modeling Used?

Financial modeling is used in every sphere of the financial world.

In fact it is one of the most important aspects of any corporate set up.

Following is the list of entities that use financial modeling, either for themselves or their clients:

1. Investment banks

There are many branches within investment banking where financial modeling is used.

In the case of mergers and acquisitions, investment bankers use financial models to understand and comprehend the viability of any deal.

In corporate finance, it is used to identify the financial health of a company in the long run.

In project finance, it is used to estimate and forecast the capital outflow and so on.

I personally believe that financial modeling is the lifeline of any investment bank. So if you are aspiring to be an investment banker, you better get a grip on financial modeling.

2. Private equity/Venture capital (PE/VC) firms

Since these firms deal in large amounts of money for any project, it becomes imperative for them to use financial modeling.

It is typically used to determine the return on investment for a certain amount, and for a certain period of time.

There are various functions in Excel that help these firms identify the return in various scenarios.

(Scenario analysis is one of the most popular functions used in financial modeling)

3. Equity research firms

It doesn't matter if you are on the buy-side or the sell side of the horizon, financial modeling is present on both sides with equal importance.

Equity research firms use multiple financial models to predict and anticipate the stock movements and the financial performance of a company.

Equity research analysts have to be conversant with financial modeling and should also be comfortable with using advanced Excel functions.

4. Corporates

All companies, either big or small, have their finance departments.

Financial modeling using Excel and VBAs are typically used in large corporations where they have a separate finance department and financial analysts.

Companies usually use financial modeling for their internal growth projections and forecasts.

It is also an important tool used for making long-term financial decisions like determining the capital structure, cost of capital, raising funds and issuance of shares and debentures.

Advantages of Financial Modeling using VBA

1. Excellent output with minimum input

You must have used Excel at some point in your student life.

But how much do you actually know about the power of Excel?

Excel comes with the most astonishing tools when it comes to financial modeling.

Explore the options in order to fully utilize the potential offering in Excel.

There are so many commands and functions which, not many people know, but are embedded in Excel and can yield excellent results if utilized properly.

2. Speedy operations

One of the biggest advantages of using Excel and VBA in financial modeling is the speed at which your data set is processed.

Once the logic is defined and variables are set, all you have to do is feed in the required data in the variables’ cells and with the click of a button, you can arrive at data points which otherwise are very time consuming to arrive at.

3. Accuracy

If you input the correct data and maintain a sound logic in designing your algorithms, there is no way on earth you can get the wrong analysis output.

Humans are bound to make mistakes, but Excel doesn't have that flaw.

And accuracy is of utmost importance while analyzing large sets of data, especially when the numbers (input or output) represent a huge sum of money.

4. Ease of comprehension

Excel enables you to represent your data in a visually appealing as well as easy to understand way.

There are many chart options that help you in representing analysis in a very simplified manner.

Features like trend lines, bubble charts, pie-in-pie charts, and 3D charts are very useful and save a lot of comprehension time on the part of stakeholders and decision-makers.

How to Create a VBA Financial Modeling

According to me, the key to creating a good financial model is having a systematic approach.

Before even starting on creating a model, take a minute out and systematically design a broad structure of your model on a piece of paper.

It will immensely help you in the actual creation of your model.

Following are the seven steps you should follow while creating a financial model using Excel and VBA:

1. Define the problem

The first step here should be establishing the need behind creating a financial model.

Try to ask yourself this question – “what problem statement does this model aim to address?’’.

Depending on the answer, you will be able to determine what all insights you need from the model.

I sincerely suggest that please discuss this with as many stakeholders of the model as possible.

It will give you clarity and the third person perspective.

2. Structure the logic

You should know that VBA is nothing but a programming language that enables logic and a defined outcome using a string of characters.

This is the most time-consuming part of creating a financial model using VBA in Excel.

Please be very careful while structuring the logic and syntax, as even a small error on your part can lead to huge discrepancies in the model.

You need not be an expert in VBA to do so, just stick to the basics and try to simplify the logic as much as possible.

3. Identify the input variables

This is one of the trickiest parts of financial modeling using Excel.

There are variables – independent and dependent.

Independent variables are usually the scattered numbers that you feed in without using any formulae.

Their value or function doesn’t change unless you manually change those numbers.

While on the other hand dependent variables will vary depending on the independent variables.

To give you a very simple example – if you want to calculate a service tax of 10% that you pay in a restaurant, you will need a bill amount.

That bill amount becomes the independent variable, whereas the tax that you would end up paying becomes the dependent variable as it depends on the bill amount.

Make sure that the input variables are correctly entered into the spreadsheet and correct function is applied to arrive at the output.

4. Define the output

The output is the reason why you are making this model in the first place.

Make sure that it gets calculated in the right manner and format the output cells appropriately.

For example – if your output that you want is in percentage terms, and if the output cell is not formatted in the right manner, there is high probability that you might get misleading numbers.

Let’s say the output value is 10%, but if the cell is not formatted in percentage it might show the figure as 0.1.

5. Pilot run

After you have done the above mentioned things, try out the model with some dummy numbers.

I suggest, use simple and smaller numbers to do this.

Use multiples of hundreds or thousands for independent variables since it becomes easier for you to manually check the desired output.

Another thing that you should keep in mind here is that do not use large sets of data initially. Start by using small data sets, which makes identifying bugs and errors easier when compared to large sets of data.

If your model is very big, or in other words has a lot of formulae and algorithms, you can test run at every step and at regular intervals before running the pilot at the end of completion.

6. Record/document the model

Once you have done the pilot test of your model, the next step is to record all your logic, syntax, functions and formulae, preferably in a word document or notepad.

Even better if you spend a little more time and prepare a process document with screenshots.

It has two advantages – one – it would be easier for you or someone else to replicate a few common functions at a later stage for the same model or some other model that you might work on.

And two, if there are any changes that are to be made at a later stage; you would exactly know where to do the edits.

It would save a lot of time for you if you have it readily documented at someplace rather than playing around with the model worksheet.

7. Monitor and update

Your work doesn’t end with the completion of the model…

You should ideally go back to the model every once in a while to look for bugs, redundancies and errors.

It is highly unlikely that you would be able to come up with an excellent model at the first go.

Keep on trying to simplify it and monitor the output at regular intervals.

Here are a few things that you should keep in mind while you are working on a model in Excel.

Apart from the functions and formulae that you will use, the following things will help you in smooth operations and ultimately a better and faster output:

1. Use of short cuts

Since we have established that speed carries a lot of importance, using keyboard short cuts will enable faster work and improved efficiency.

There are short cuts right from opening up toolbars to creating formulae to running an entire macro.

Please start using these short cuts at a very early stage, because with a little bit of practice you will know how much time and effort is saved by using those.

2. Using Excel Help

You cannot possibly know everything about Excel whatever your level of expertise is.

Please do not shy away from using the inbuilt Help option in your Excel.

Everything you need to know about functions and formulae will be there.

If the language and explanation there isn’t enough for you, then you can always go online and lookup for help.

The point is, please make sure that you use this option, it will save up a lot of your time.

3. Conditional formatting

The use of various colors, fonts, sizes, and signs can give a whole new look and feel to the dashboard of your financial model.

It has been proved very helpful to the modelers around the globe to make their models user-friendly and efficient.

4. Nomenclature

Naming your columns and rows appropriately carry a lot of weight in financial modeling.

Avoid using ambiguous titles.

Be precise.

It would be helpful for other people who might use this model without your presence.

Now You Try It

I hope you can see the potential of financial modeling using Excel and VBA.

Yes, it takes hard work to create something great.

But with this guide, you already know ahead of time that your hard work is going to pay off (unlike downloading a free template and then crunching the data).

I want you to start working on your financial model and let me know how it works for you.

If you have a question or thought, leave a comment below and I’ll get right to it.