How to Build Financial Models | A Complete Guide

What is financial modeling?”

“Do you teach discounted cash flow modeling?”

“Can you give me a sample financial model that I can fill with numbers and present in the interview?”

“How to build financial models?”

“How to get into financial modeling careers?”

“How to build discounted cash flow analysis models?”

I’m constantly bombarded with questions about best practices. And, the funny thing is—when I ask the person, “what is financial modeling?”, that person doesn’t have any clue.

So, I thought it’s apt to write on this subject today.

What Is Financial Modeling?

As per Investopedia,

The process by which a firm constructs a financial representation of some, or all, aspects of the firm or given security. The model is usually characterized by performing calculations, and makes recommendations based on that information. The model may also summarize particular events for the end user and provide direction regarding possible actions or alternatives in financial planning.

As per my experience,” financial modeling” involves building financial models, sometimes from scratch using historical data and cash flow statements or sometimes maintaining and updating the existing financial model template.

It is a skill, and you can learn it through lessons and practice.

So, what is financial modeling?

To put it as simply as I possibly can, financial modeling:

Is the idea of presenting a very real financial situation in an abstract way.

This means that you will have to create a numeric scenario, most likely in Excel, in which you have to present the current state of the financial situation you have been given as well as make a prediction, if possible, of the future of that same financial situation thus increasing the company’s performance.

• Is the task of building a model of abstract representation of a real-world financial situation.
• Is a term defined and means different things to different people, and it usually relates to either corporate finance or accounting or quantitative applications.
• A discounted cash flow statement model helps in the future operating, investing, and financial activities to determine future profitability, financial position, and possible risks that can be mitigated.
  Financial models should be exhaustive, internally dependable, externally acceptable, and analytical.
• It is desirable that the entire task should be free from error and should be easier to read and understand. By following these fundamental principles, the model will be easier to handle, direct, and check and become more reliable.

And since I’ve touched on the main tool you will be using, Excel, let’s explore this side of the story a little more.

How to learn this business skill in a step-by-step way? Are you preparing for Modeler jobs?

Then only downloading the model training free guide and templates won’t help. This will help:

• You should be good at Accounting, Finance, Valuation, and Excel VBA financial modeling skills.
• Ask yourself, “What problem I’m going to solve by creating this financial model?”
• You should know the scope, best practices, benefits, and limitations of the model.
• Remember garbage in garbage out principle. So, check your inputs.
• Your model should be simple, easy to understand, and flexible enough to accommodate future revisions.
• Time management is an essential aspect of model assumptions. Don’t get overwhelmed by numbers and calculation part in spreadsheets.
• Lastly, you should be able to make a decision based on your financial model.

What Is a Financial Model?

According to Moneyterms,

A financial model is anything that is used to calculate, forecast or estimate financial numbers. Models can therefore range from simple formulae to complex computer programs that may take hours to run.

In short, financial models are mathematical models in which variables are linked together to represent a simplified version of the performance of a financial asset or portfolio of business, project, or any other investments.

For sophisticated models such as Discounted Cash Flow (DCF) model, you need to use a computer.

Microsoft Excel skills are a must to build or update financial models.

There are computer programs built for high-end and complex financial models such as—Value-at-Risk(VAR) models used in risk management.

Define the Need:

• What is the desired goal of the financial model?
• Are you launching a new product?
• Are you integrating a potential acquisition?
• Are you refinancing debt?
• Who is the expected user? Management/ Investors/ Bankers?
• What is the expected use? Internal or External?

Who Should be Studying Financial Modeling?

Financial Modeling could be beneficial and may be explored by a vast majority of people.

• The Financial Modeling study can be done by anybody who wants to study the world of finance and who wishes to get involved in money-related decision-making.These people can be Executives, Business planners, Strategy Decision Makers, Managers working with Banks, Equity Researchers, Project Managers, Research Analysts, Investment Banking people, Portfolio Managers, Commercial Bankers, Risk Managers, Accountants, and all those who are part of the finance department in all types of the different firms.
• It’s an added advantage for those people who are pursuing a CA (Chartered Accountant), MBA (Master of Business Administration), CFA (Chartered Financial Analyst), FRM (Financial Risk Manager), and Commerce Graduates.
• Also, the candidates have a Degree, Diploma, in technical fields like B.TECH or Engineering, and want to make a career in finance.
• Any individual who just wants to gain knowledge out of passion or curiosity.

Also Read: Financial Modeling in Excel: The Practical Guide for Professionals

Where Can You Use Financial Models

Financial models are used in:

Historical analysis of a company, Projecting a company’s financial performance, Data Analysis, Altman Z-Score, Cash Flow Analysis, Sensitivity Analysis, Sharpe Ratio Excel Formula, Company valuation, interest rate finance model, decision-making tools, decision trees, business decisions, merger models, LBO models, Project finance, Real estate investments, Oil & Gas modeling, Investment Banks & Financial Institutions, Personal finances, Non-profit organizations, Government, Investment banking, Pitchbook preparation, Equity research, in Corporate Finance like debt balance, interest expense, capital expenditures, working capital, balance sheet, budgeting, financial statements, capital structure, balance sheet, income statement, cash flow statement and valuation.

Users of Financial Models

There are six main groups of users that use financial models.

1. Business owners and entrepreneurs
2. Finance and Accounting professionals
3. Financial Modelers and Consultants
4. Individuals for personal finance
5. Governments
6. Universities

Let’s see the areas where you, as a financial analyst, can use financial modeling skills.

• Forecasting future raw material needs
• Valuation of a security
• Benefits of a merger
• Check the size of the market opportunity
• See the roadmap to profitability
• Check investment requirement
• Quantify and predict risk
• Portfolio performance
• Identify undervalued securities

The question comes to mind that only one financial model is useful, or do we need to use different types of financial models?

Types of Financial Models

There are different financial models that you can use as per your need.

1. The discounted Cash Flow model
2. Comparative Company Analysis model
3. Sum-of-the-parts model
4. Leveraged Buy-Out (LBO) model
5. Merger & Acquisition (M&A) model/ merger model
6. Industry-specific financial model
7. Option pricing model
8. Corporate finance models

These financial models are used to solve different problems.

As a financial analyst, you should know the time and type to use a financial model. As a managing director of a company, you should know how to use such models.

To gain expertise in this practice, you should know the fundamentals of quantitative modeling.

The Fundamentals

Here is a question that I have been asked quite a few times. Is financial modeling done in Excel? And if so, why?

Yes. It is primarily done in Excel, but this is not the only tool you can use to build the models.

Or the only tool you will be asked to use. In fact, there are other, more sophisticated software applications that could help you create the models a lot faster and with fewer errors.

Examples go from C++ to Alight, Synario, and Quantrix. If you want, you can definitely learn how to use them. In fact, I encourage you to do that if you need to create a large number of financial models on a daily basis.

It will save you a lot of time and energy.

However, the reason why most people still use good old Excel is simple. Excel is very user-friendly.

Everyone and that means your clients, has Excel on their devices and can access the models or the finance Excel spreadsheet you create.

Apart from that, Excel costs next to nothing and is very easy to use.

Should I Learn Financial Modeling?

In my opinion, you should learn as much as you can. But when it comes to financial modeling, here are some reasons why you should learn it.

• It allows you to understand a company’s fundamentals.
• In other words, the inner workings of any company or client you may have will become immediately transparent to you – their income, customer acquisitions, financial statement balance sheet, etc.
• It has become one of the most required skills in finance careers as you advance in the hierarchy.
• In fact, almost all finance careers in the corporate sector now ask you to know this skill. Not knowing it will represent a major fault on your part. But mastering it will open many doors for you.
• Not that many people are experts in it. Yes, you read this well. In reality, there are fewer people than you realize who are able to do investment analysis. And even fewer who have mastered this skill. This is part of the reason why it’s in such high demand. This skill is difficult to understand and takes years to master. As a result, if you can do it, congratulations! Start learning it right now!

Best Financial Modeling Training Courses

Speaking of learning, let’s take a look at the best financial modeling courses you can find. But before that, here are some questions that I get asked most of the time and which I think will help you as well.

Can I learn it on my own?

You can start by brushing up on your advanced Excel finance skills and take it from there.

You can also learn all your formulas and spreadsheets. However, I wouldn’t recommend making this your only source of learning.

What’s the best way to learn financial modeling?

If you’re planning on building a career in finance and applying for a job where this skill is required, you won’t be able to tell your interviewer that you learned financial modeling on your own off the internet.

Remember what I said earlier? There are not that many people who know how to do this, so these positions are in high demand. Only the best make it through!

Do I have to get certified if I learn financial modeling?

Not necessarily. It would be good if you could also get your certification, but it is not mandatory. Your level of expertise is what matters when it comes to this job.

What is the best financial modeling certification?

There are 10 Financial Certifications that are considered to be the best at the current moment:

1. CPA – Certified Public Accountant
2. CFP – Certified Financial Planner
3. ChFC – Chartered Financial Consultant
4. CFA – Chartered Financial Analyst
5. CIC – Chartered Investment Counselor
6. FRM – Financial Risk Manager
7. CLU – Chartered Life Underwriter
8. CAIA – Chartered Alternative Investment Analyst
9. CMFC – Chartered Mutual Fund Counselor
10. CMA – Certified Management Accountant

In case you want to learn more about financial modeling certification, please check out another interesting piece I wrote about the BIWS Certification Review: Features, Benefits, and Courses.

This Certification equips you for careers in equity research and investment banking.

Where can I get financial modeling training?

You can and should be trained in this skill in one of several ways.

Either by joining a training program offered by a financial institution or a bank, by taking an online course like the one offered by a university or by us right here, or by simply getting trained on the job once you are hired.

What is the best financial modeling course free of charge?

If you’re interested in learning this skill and want to hear more from me, of course, you can check out our YouTube channel.

FinanceWalk offers full-length, free videos in a crash course format as well as more than one financial modeling online course that teaches you how to create financial models in Excel.

Financial Modeling Skills: Do You Have Them?

Alright, so up until now, we’ve discussed a series of questions that I get quite often. Now let’s switch roles.

I will list the skills you need and you tick them off the list one by one.

• Advanced Excel skills – you saw this one coming. Since I have already talked so much about Excel, you knew this had to be on the list. There is no way you can practice this skill without mastering a very advanced form of Excel.
• There is a book by K. Scott Proctor called Building Financial Models with Microsoft Excel. A Guide for Professionals. Take the time to go through this PDF and learn the basics of financial modeling in Excel before you learn anything else.
• A great alternative is the classic Financial Modeling in Excel for Dummies by Danielle Stein Fairhurst. You can find it for free at the archive.org library. Also, read financial analysis for dummies.
• Accounting – you either need a degree in accounting or at least a basis in it. Whichever way you choose, you need a basic understanding of accounting for a career as a Modeler.
• Knowing how to build a forecast – I will get into this in more detail below but this is another essential tool of the trade.
• Problem-solving
• Knowing how to build and give a presentation.
• Attention to detail – working in Excel is not easy. The smallest mistake can have the direst consequences.
• Being able to process massive amounts of data and presenting them in a very distilled format.
• The ability to think about high-level strategies.

Financial Modeling Careers – Where Will I Work?

This is one topic where I can tell you that you won’t have any problems finding a job. As long as you’re good at what you’re doing, of course!

But where exactly should you look for a job when you are skilled in company valuation? In other words, who builds financial models?

1. Investment banking

This is the original career where spreadsheet models were used. Investment bankers have always used financial models built in Excel to value any given company when it comes to mergers and acquisitions or capital raising.

2. Equity research

Equity researchers need to come up with reports that they send to their clients on whether or not they should invest. Those reports often include financial models.

3. Corporate Development

Corporate development groups are the counterparts of your more well-known investment bankers. They need to work together on transactions. But to do said transactions, all corporate developers must be excellent when it comes to financial development. In other words, if this is the job you have in mind, you have to be at the top of your game!

4. Real estate financial modeling

As a real estate developer, it will be your job to create detailed financial models that determine just how profitable the real estate project you want to invest in really is. The most important thing you need to understand is that you will need to learn real estate statement models both as a junior and as a senior real estate developer.

5. Financial projections for startups

It doesn’t matter if you’re in finance or not for this one. Every single businessman who owns a startup has to create projections (startup financial model) of how his or her company will behave in the future.

Cash flow valuation, in this case, will be the job of the CFO, the founder of the company, or the person in charge of the finance team.

You can also use a financial projections template that will help you calculate payroll costs, startup expenses, cash flow, balance sheets, and more.

Learn more about How to Build Your Practice in Freelance Financial Modeling?

The Most Common Financial Modeling Techniques

I have laid out for you below some general methods which you can use in almost every financial situation if you are dealing with business models.

They are the most common ways in which to approach a problem.

The three-statement model

Use this model to help a company understand how their three statements, income, balance, and cash flow are tied to each other. This is also where the name comes from.

1. The LBO or leverage buyout model

Use this one when the company is trying to buy out another one, hence the name, and needs to use a considerable fund to complete this transaction.

Your financial model will help the company see if it can make a profit after buying the asset or after selling it.

You should know that this is one of the most complicated and difficult types of financial models and it is most commonly used in investment and private equity firms.

Learn more about how to value a private company.

2. The consolidation model

It’s a model that adds several business units to a larger model. Every individual unit comes with its own set of data that you must consolidate and create a large worksheet using all of them.

If you want a financial projections example, think of it this way. Create a financial consolidation model for Fashion Inc.

It examines all the data for all its units such as Chanel, Dior, Gucci, Prada, etc.

3. IPO or Initial Public Offering model

If you want to get a job as an investment banker or a developer, you will have to use this model. They use it to find out how valuable a business is before it goes public. Within this model, you will compare companies and decide how much an investor might want to pay for shares within that company before it starts selling said shares.

4. The budget model

Just as the name suggests, you will need to use this model to plan a budget for the years to come. The basis of this model is quarterly or sometimes monthly figures. They focus on a business’s income statement.

5. The forecast model

If you work in financing activities, you will have to perform this one and the budget model above around the same time.

Sometimes you might even have to combine them so as to understand how the company sits financially overall.

Please understand that the list I have presented to you above does not include all the best practices that exist.

The others are used in rare situations. The ones above are the most common examples that will be able to help you do your job on a day-to-day basis.

How to Build Financial Models

I have outlined below all the steps you need to follow to create a basic financial model. Once again, please be aware that it takes a lot of practice to learn how to excel at this job.

Also, for a more detailed look into how to create financial models, please take a look at our YouTube channel or take one of our courses!

1. Start the process with the historical results

The first thing you need to do is pull the financial statements of the company from the last three years.

Once you have them, input all of the data into Excel.

Reverse engineer your assumptions for this data. Calculate gross margins, revenue growth rate, fixed costs, inventory days, and everything else.

Fill in your assumptions when it comes to the forecast period in the shape of hard codes.

2. Calculate the income statement

Continue by calculating the income statement. This means gross profit, revenue, COGS, and operating expenses, as well as EBITDA.

Learn more about How to Build a Revenue Projection Model?

3. Begin the balance sheet

Start to fill in your balance sheet. Calculate inventory and accounts receivable. Next step – fill in your accounts payable.

4. Create the supporting schedules

Keep in mind that before you can finish with your balance sheet and income statement, you will have to build a supporting schedule debt, interest, and Property, Plant, and Equipment. These are your capital assets.

If you have done your historical period correctly, the Property, Plant, and Equipment can pull from there. Subtract depreciation and add capital expenditures.

5. Finish the balance sheet and income statement

Once you are done with your supporting schedules, the information gathered there would also finish your balance sheet and income statement.

Work on the income statement – connect depreciation to the Property, Plant, and Equipment schedule. Connect the debt schedule to the interest. This will allow you to calculate income, taxes, and earnings before tax.

6. Create the cash flow statement

Use the reconciliation method to create the cash flow statement. Add back depreciation to the net income. Adjust for changes when it comes to the non-cash working capital. This will result in cash from operations.

7. Do the DCF analysis

When you have reached this stage, the three-statement model should now be complete.

It’s time to start the business valuation as well as to calculate free cash flow. You can also find DCF under the name Discounted Cash Flow Models.

Training guide: Speaking of valuation, here is a PDF of a great resource book you can read while you learn your basics in interview prep. I’m talking about Financial Modeling and Valuation A Practical Guide to Investment Banking and Private Equity by Paul Pignataro. You can find the full PDF right here.

8. Add scenarios and sensitivity analysis

The point of this step is to see how risky an investment would be. But it will also prove useful from the point of view of business planning.

* Don’t forget to add charts and graphs to your financial model!

This is what will turn a generic model into a great one. The best part of your clients will never have the time to read the whole thing through or understand how your model works from the inside.

Therefore, a chart or a graph placed right at the end that sums it all up can work wonders!

*Always stress test your model!

This concept means subjecting your financial model to extreme scenarios to see if it behaves exactly as you intended it to. Use auditing tools that will help you see if all the formulas you used in Excel hold up and if they were correct, to begin with.

Tell Me About Financial Forecasting Models

Just as the name suggests, financial forecasting uses the data you have collected during financial analysis to create predictions about what the costs will be in the future, as well as the profits and the direction of the growth. With that in mind, here are some financial forecasting models that will help you.

1. The top-down model

Use this model when you are evaluating a business opportunity that is quite new and for which you do not yet have private sales data. It will allow you to make predictions about this opportunity taking into account the new market and how big it is, as well as how much of it you will be able to cover.

2. The bottom-up model

As opposed to the one above, use this model when you have all the data you need. Seeing as all the information is already there, this model, in particular, will be able to produce very accurate results. Still, please be aware that it will also take up much more of your time, and you might need to get other people involved to help you finish it.

3. The correlation model

This model is all about the relationship between two given variables. You are looking for ways in which variables in one are affecting the other and the other way around. This is the most common predictive model when it comes to businesses.

4. The quantitative model

You will also find it under the statistical name model. Use it to establish the connection between other disciplines and their equations in the sense of financial forecasting. The most popular method of doing it is the Gaussian distribution analysis.

Tips for the Perfect Financial Model

1. Always plan and outline – before you start by putting in the historical numbers and getting the whole show on the road, outline your project. Draw a timeline, decide on the number of years, and read about the company you are working on.
2. Maintain quality throughout – it’s easy enough at the beginning. But remember what I said above. Merger Models are one of the hardest things to do. Therefore, the process is long and strenuous. Make sure you keep up the same quality of work you began with.
3. Create a simple presentation – remember this. It doesn’t matter how good your financial model is if no one else can understand it. Colour code the information and use pictures, graphs, tables, and sectioning. Make it easy for people to read and follow!
4. Make the correct assumptions – this is where you get to see how good you really are at this job. Because the financial model and its projections will only be as good as the initial assumptions you made when starting. If the assumptions you made don’t have any reliable base, then the outcome will be useless.
5. Check it to see if it’s accurate – do this at all times throughout the process not just at the end! A financial model is usually very long and intricate. The longer and more complex it gets, the easier it is for errors to slip in. Checking it for accuracy as you go will save you from redoing the whole thing at the end.

What About Financial Risk Modeling?

This is the concept that deals with financial risk when it comes to portfolios or individual securities. Here are some of the risks when investing in securities that you need to be aware.

• Market risks – any downturn happening in the market may cause stocks and bonds to fall price-wise.
• Risks in interest rates – when the market interest rate rises, the prices of the bonds fall. And the other way around, of course.
• Inflation risks – this will chip away at the value of an investor’s portfolio.
• The risk with credit – annual repayments with bonds and interests might suffer because the seller’s financial stability is declining.

Let’s Talk About Financial Modeling and Analysis

To understand the relationship between financial modeling and analysis, first, I have to define the two concepts for you.

Financial analysis can be understood as assessing a project or a business with the goal of investing in said business or lending money to the completion of said project.

You need the analysis because it is the only way to make a final, informed decision.

Here’s the complete guide on How to Determine the Financial Health of a Company?

The Pro forma model template, on the other hand, as we’ve already seen, asks you to sum up a real financial situation and create a financial model based on it.

So, what is the relationship between the two?

Modelling, in this way, is the perfect tool at your disposal to perform financial analysis.

If this is the career path you have chosen, you will obviously need to have experience in both financial modeling and financial analysis.

We have already seen how you go about financial modeling. How about the analysis side?

If you want to gain experience, you will need at least a bachelor’s degree in a field that is related to finance, such as accounting, economics, or statistics.

However, your chances of getting a better-paying job will increase if you have a master’s degree or an MBA in the same field.

Financial Formulas in Excel

So you want to learn Excel to work in finance. This is definitely the right place to do it, and I have for you a list of the top Excel functions and formulas that you will be using on a daily basis.

XNPV

Here is the formula – =XNPV(discount_rate, cash_flows, dates)

This is, without a doubt, the original formula all finance professionals use and love. Using it helps you determine the Net Present Value or NPV when it comes to a multitude of cash flows.

However, adding the X in front will help to make the formula more precise by telling it only to take into account the dates for cash flow you want it to.

XIRR

The formula is =XIRR(cash flow, dates)

As you can see, it’s a sister formula to the one above. IRR stands for internal rate of return when it comes to cash flow at specific dates. This is what you can use it for.

MIRR

The formula is =MIRR(cash flows, cost of borrowing, reinvestment rate)

This is a variation on the IRR one above. The exception here is the M, which in this case stands for Modified. Use this particular formula when you are dealing with cash from one investment that is being directed into another, new investment.

PMT

The formula is =PMT(rate, number of periods, present value)

It will help you, especially if you’re working in real estate financial modeling, which we have already discussed above. You can think of it as your personal mortgage payment calculator.

Choose your interest rate, the duration of the loan in months or years as well as its value, and you can find out quickly enough how much a single payment needs to be if you use this formula.

IPMT

The formula is = IPMT(rate, current period #, total # of periods, present value)

As you can already tell, it’s a variation of the formula above. Use it to calculate how much the interest will be if you have a fixed payment. The added I in this formula stands for interest.

EFFECT

The formula is =EFFECT(interest rate, # of periods per year)

If you work in finance, and you are involved in borrowing or lending, this formula will be your bread and butter so to say. Therefore, make sure you learn how to use it properly before anything else.

DB

The formula is =DB(cost, salvage value, life/# of periods, the current period)

Simple, but very effective. This is the Excel function that all accountants and professionals adore. The reason is that as small as it is, it will save you from constructing a gigantic Declining Balance, hence DB.

If you use the function, Excel can work out the depreciation expense for every given period on your behalf using this little gem of a formula.

RATE

The formula is =RATE(# of periods, coupon payment per period, price of a bond, the face value of a bond, type)

Use it if what you need is to work the Yield to Maturity for security. It will come in handy, especially when figuring out the rate of return that comes from buying a bond.

FV

The formula is =FV(rate, # of periods, payments, starting value, type)

Another small but very valuable one, the FV function generally shows you how much money you will get at a point in the future. You have to start with a balance, a compounding interest rate, and regular payments.

As I said at different times during this extensive guide on financial modeling, it takes years to learn how to master this craft. However, you do have to start somewhere.

I suggest you read this guide as many times as you need to take in all the information.

Browse our YouTube channel for great, educational videos, and don’t forget to subscribe to an online course if you want to learn in more detail how to get ready for your future career!

• Download BIWS Course sample videos here.
• Read Students’ Testimonials here.