Complete financial modeling guide

A free step by step guide on financial modeling in Excel.

Complete guide to financial modeling

This is designed to be the best free guide on financial modeling!  In this guide, we will break down all the most important aspects of financial modeling, and provide tips and tricks about industry leading best practices.

CFI’s mission is to help anyone in the world become a world class financial analyst.  With that goal in mind, we we’ve designed this guide to be extremely practical with specific takeaways that can help you improve your financial modeling skills.

Download our financial modeling templates.

What will this guide cover?

This financial modeling guide will cover several important topics designed to sharpen your financial analysis.  Topics in this guide include:

  • Why build a financial model
  • Excel tips and tricks
  • Modeling best practices
  • Building the forecast
  • Linking the statements
  • Further analysis (DCF, sensitivity, M&A and more)
  • Presenting results

All of these topics are covered in more detail in our online financial modeling courses.

Why build a financial model?

For anyone pursuing or advancing a career in corporate development, investment banking, financial planning and analysis FP&A, equity research, commercial banking, or other areas of corporate finance, building financial models is part of the daily routine.

Financial models are essential just tools to help make decisions.  These decisions often include: whether or not to invest in a company, asset, or security; whether or not to invest in a project (project finance); whether or not to do a merger or acquisitions, and whether or not to raise money; and other corporate finance transactions.

The financial model allows decisions makers to test scenarios, observe potential outcomes, and hopefully make an informed decision.  There is a lot of talk about software programs that can be used, but the truth is the vast majority of financial modeling takes place in Excel.

Excel tips and tricks

Excel is the main tool used by banks, corporations, and institutions to perform financial modeling.  The main reason for this is Excel’s phenomenal versatility.  Every company or investment opportunity is unique, and Excel is a blank canvas that can be totally customized and tailor to the situation.  The flip side of this is that there are no controls or rules in place to ensure the model is accurate or error free.

Here are some of the most important Excel tips for financial modeling:

  1. Use as many keyboard shortcuts as possible
  2. Keep formulas and calculations simple – break them down into smaller steps
  3. Use the grouping function to organize sections of the financial model
  4. Use F5 (go to special) to quickly locate all hardcoded numbers or formulas
  5. Use Trace Precedents and Trace Dependents to audit the model
  6. Use XNPV and XIRR to apply specific dates to cash flows
  7. Use INDEX MATCH over VLOOKUP for looking up information
  8. Use a combination of date functions (EOMONTH) and IF statements to make dates dynamic
  9. Remove gridlines when presenting or sharing the financial model
  10. Memorize all the most important Excel formulas for financial modeling

For a refresher on basic Excel functions check out our free Excel Crash Course.

When you’re ready to take your skills to the next level, our Advanced Excel Formulas course will help you stand out from the pack.

Modeling best practices

Over and above good Excel skills, analysts who really stand out at financial modeling are great at structuring and organizing their spreadsheets.

Here are our top 10 best practices for structuring a model:

  1. Use color coding to distinguish between inputs and formulas (i.e. blue and black)
  2. Build a standalone 3 statement model on one worksheet (don’t separate the statements onto different sheets)
  3. Clearly separate the assumptions or drivers from the rest of the model (one section at the top)
  4. Use clear headers and subheads (with bold shading) to clearly distinguish sections
  5. Use the cell comments function (shift + F2) to describe calculations or assumptions that need explaining
  6. Build in error checks such as ensuring the balance sheet balances (without a plug)
  7. Pull forward (or repeat) information where it helps users follow the logic of the model (i.e. pull forward EBITDA from the income statement to the cash flow valuation section)
  8. Avoid linking to other Excel workbooks unless absolutely necessary (and if so, clearly indicate those links exist)
  9. Avoid circular references unless necessary (and use iterative calculation to solve them)
  10. Use tables, charts and graphs to summarize important information

Download this financial model template.

Building the forecast

The “art” of financial modeling mostly relates to making assumptions about the future performance of the business being modeled.  This is the most subjective and important part in the valuation of a company.

This guide will outline various approaches to forecasting, which include:

  • Top down analysis. In this approach, you start with the total addressable market TAM and then work down from there based on market share and segments such as geography, products, customers, etc unit you arrive at revenue.
  • Bottom up analysis.  In this method, you start with the most basic drivers of the business such as website traffic, then conversion rate, then order value, and finally revenue, in the case of an e-commerce business.
  • Regression analysis.  With this type of forecast you analyze the relationship between the revenue of the business and other factors such as marketing spend and product price by performing a regression analysis in Excel.
  • Year over year growth rate.  This is the most basic form of forecasting,

Linking the statements

We’ve created another detailed guide on how to link the 3 financial statements, but we will also provide a quick recap of it here.  Once the forecast assumptions are in place, it’s just a bunch of basic mathematical operations to fill in the three financial statements in the model.

From a financial modeling perspective, this is the least subjective part of the process.  With the assumptions clearly stated, an analyst more/less multiplies, divides, add or subtracts to produce the statements.

Step #1 – begin by calculating revenue, based on the forecasting approach used form the above section.  From there fill in cost of goods sold, gross profit, operating expenses, and arrive at earnings before interest taxes depreciation and amortization EBITDA.

Step #2 – create supporting schedules for (i) capital assets (PP&E, depreciation, and capital expenditures), (ii) working capital balances (accounts receivable, accounts payable and inventory), and (iii) financing schedules to equity capital, debt balances and interest expense.

Step #3 – finish the income statement (depreciation, interest, taxes, net income) and fill in the balance sheet items except for cash, which will be the last part of the financial model to be completed.

Step #4 – build the cash flow statement, consisting of cash from operating activities, cash used in investing activities, and cash from financing activities.  Combined these three sections will determine the closing cash balance, which links to the balance sheet to complete the financial model.

This is a simplified overview of the financial modeling process, or linking of the three statement, so please watch our video based courses on finical modeling if you’d like more detailed instruction.

Launch financial modeling courses.

Further analysis

With the baseline financial model in place it’s time to layer on whatever type of financial modeling exercise suits the situation.

We’ve published an overview of the various types of financial models, but to recap, the most common ones include:

  • DCF analysis – discounted cash flow analysis (DCF model) to value a business
  • M&A analysis – evaluate the attractiveness of a potential merger, acquisition or divestiture
  • Capital raising – analyze the pro forma impact of raising debt or equity, or other capital events
  • LBO analysis – determining how much leverage (debt) can be used to purchase the company
  • Sensitivity analysis – layering on a section that evaluates how sensitive the business or the investment is to changes in assumptions or drivers (sensitivity analysis course)

Download all our financial modeling templates.

Presenting the results

When all of the above analysis is done, the work still not over.  The last step is to develop charts, graphs, and other outputs that can be used to easily communicate the information from the model.  This is where the best analysts really get to shine.

It’s one thing to build a complex model that only you understand, but it’s another thing to effective communicate the risks, rewards, and critical factors to all audiences.

As the capstone for your financial modeling training we recommend either an advanced Excel course to learn how to build all the best charts and graphs for a presentation, dashboard, or any other document you’re producing.

More financial modeling guides

We hope this has been a helping guide to financial modeling in Excel, and has helped you advance your career as a financial analyst.  At CFI, we pride ourselves on create the best free guides to help you get an edge.

Please check out these other resources to continue developing your skills: