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

 

1x1.trans The Beginners 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 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 hard bound documented financial models are replaced by dashboards.

If you want to pursue a career in finance, it is of utmost important 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 financial modeling using advanced functions like VBA (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 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 life line 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 the sides with equal importance.

Equity research firms use multiple financial models to predict and anticipate stock movements and 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 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 Financial Model Using Excel and VBA

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 stake holders 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 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 you 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 screen shots.

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 some place 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 tool bars 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 look up 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

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 you columns and rows appropriately carries 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.

Special Offer from FinanceWalk

All FinanceWalk readers will get FREE $97 Bonus – FinanceWalk’s Equity Research Program.

If you want to build a long-term career in Financial Modeling, Investment Banking, and Private Equity, I’m confident these are the only courses you’ll need. ( Because Brian (BIWS) has created world-class online financial modeling training programs that will be with you FOREVER).

Sign Up for Excel and Modeling Fundamentals program. In this course, you’ll master Microsoft Excel via two extended case study exercises. Here are the program modules:

You Get In The Excel & Financial Modeling Fundamentals Course

Here’s what you receive in the 43 hours of modeling content (178 videos + full transcripts):

  • Financial Modeling Lessons cover Accounting 101 up through 3-Statement Models, Valuation, DCF Analysis, Merger Models, and LBO Models.
  • Introductory Excel Lessons include how to set up Excel properly, navigation, data manipulation, formatting, printing, and key formulas used in finance.
  • Time-Saving Excel Shortcuts. Includes Excel 2003, Excel 2007 / 2010 / 2013, and Mac Excel shortcuts.
  • Accounting Fundamentals Lessons teach you how to how to answer interview questions, build a 3-statement model, and balance a balance sheet.
  • Equity Value, Enterprise Value & Multiple Lessons cover how to calculate diluted shares, move from Equity Value to Enterprise Value, and calculate the most relevant valuation metrics and multiples.
  • Valuation Lessons teach you how to pick comparable public companies and precedent transactions and how to build a DCF analysis (projections, terminal value, discount rates, and WACC).
  • Merger Model Lessons cover accretion / dilution, synergies, pro-forma balance sheet adjustments, and sensitivity tables.
  • LBO Model Lessons teach you both a basic model (no balance sheet) and a more advanced model with all 3 statements, pro-forma balance sheet, and debt schedules with 5 tranches of debt from Revolver to PIK.
  • Instant Access when you sign up because everything is delivered online – no shipping charges or trips to the post office.
  • Free Lifetime Updates and Support.
  • And much, much more.

If you purchase BIWS courses through FinanceWalk links, I’ll give you a FREE Bonus of FinanceWalk’s online Equity Research Program ($97 Value).

I see FinanceWalk’s Equity Research Program as a pretty perfect compliment to BIWS courses – BIWS helps you build financial modeling and investment banking skills and then I will help you build equity research and report writing skills.

To get the FREE $97 Bonus, please purchase from the following links.

Excel and Financial Modeling Fundamentals – Free $97 Bonus

Advanced Financial Modeling – Free $97 Bonus

Watch Sample Excel Video

In order to get your FREE Bonus, you must:

  1. Purchase the course through FinanceWalk links.
  2. Send me an email along-with your full name and best email address to avadhut@financewalk.com so I can send you the course login details.

Click Here to Check All BIWS Programs – Free $97 Bonus

 

4 Ways to Learn Financial Modeling in Excel

 

1x1.trans 4 Ways to Learn Financial Modeling in Excel

Learn Financial Modeling

 

After all these years in the finance domain, I have come across a very frequent question from finance aspirants: “How to do financial modeling?”

Since understanding of financial model is one of the key tasks involved in any finance profile, every aspiring investment banker, financial analyst or any finance professional, should know how these models work.

In the current age of information & technology, learning these models and their functionality has become much easier than ever before.

I have listed down a few very easy ways of getting to learn financial modeling.

How to learn financial modeling?

1. Learn by watching tutorial videos

There are a bunch of websites available these days that offer financial modeling free tutorial videos for aspiring finance professionals.

If your understanding of financial models is very basic, tutorial videos are recommended, as you can repeatedly go and watch them if you do not understand anything at the first go.

These videos are uploaded by experts in the finance field and by various educational institutes.

Most of these videos on the Internet are free, but there are also websites that offer subscription to these videos at a certain fee.

2. Learn by attending virtual classroom training sessions

There are virtual classrooms wherein you can login either collectively with peers or individually (depending on the format) to a webinar/remote access platform monitored by a trainer.

These sessions are interactive in nature and also offer an opportunity of peer learning.

These sessions offer a deeper understanding when compared to videos, as you can ask questions if you are stuck at any point during the session.

These sessions are conducted by various educational institutions and online learning portals, usually at a certain price.

But you can also find these sessions for free as a result of promotional campaigns by these institutes.

Usually they offer the first module as a free bee, and if you like the training model you can subscribe for the entire course.

3. Learn by practicing and using case studies

This approach is less normative in nature and requires a lot of your time in practicing.

As Excel is a very commonly used tool for analysis, there is a plethora of case studies available on the Internet.

There are solutions available for cases as well so that you can compare your work with the actual model in use and understand the difference.

These case studies and practice material is widely available on the Internet and mostly does not require any subscription.

But there are paid subscriptions by a few institutions and portals which offer these case studies and also offer a detailed evaluation of your exercise.

4. Distance learning courses

This is the most result oriented way of learning financial modeling in excel.

There are institutes and portals offering these courses over a period of time at a certain fees.

The biggest advantage of taking this course is that typically at the end of the course you will be awarded a certificate stating you are a professionally trained professional in financial modeling.

One of the most popular courses for this is Wall Street Modeling Course amongst various others.

I hope this post gives you enough clarity on options in choosing your own method of learning financial modeling using excel.

Now You Try It

I hope you can see the potential of financial modeling for your career.

Yes, it takes hard work to create something great.

But with this skill you are ahead of rest of the crowd and your hard work is going to pay off.

I want you to start watching the videos. Do 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.

How to Analyze Historical Data for Forecasting – Part 6

Analyze historical data

  In my previous video posts in the Forecasting series, I covered importance of historical analysis and how to project financial statements. See the previous videos in this series here: 1. How to Analyze Historical Data for Forecasting – Part 1 2. How to Analyze Historical Data for Forecasting – Part 2 3. How to […] Read more »