How to Build a Financial Model | The Complete Guide
Originally published: 08/11/2018 11:37
Publication number: ELQ-37929-1
View all versions & Certificate
certified

How to Build a Financial Model | The Complete Guide

This is a fantastic guide offering tips and tricks to help you improve your financial modeling ability.

cfiexcelfinancefinancial modelingmodeling

Introduction

What will this financial modeling 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


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.

  • Step n°1 |

    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 and error-free.


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


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

    How to Build a Financial Model | The Complete Guide image
  • Step n°2 |

    Financial 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:


    - Use color coding to distinguish between inputs and formulas (i.e. blue and black)
    - Build a standalone 3 statement model on one worksheet (don’t separate the statements onto different sheets)
    - Clearly separate the assumptions or drivers from the rest of the model (one section at the top)
    - Use clear headers and subheads (with bold shading) to clearly distinguish sections
    - Use the cell comments function (shift + F2) to describe calculations or assumptions that need explaining
    - Build in error checks such as ensuring the balance sheet balances (without a plug)
    - 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)
    - Avoid linking to other Excel workbooks unless absolutely necessary (and if so, clearly indicate those links exist)
    - Avoid circular references unless necessary (and use iterative calculation to solve them)
    - Use tables, charts and graphs to summarize important information

    How to Build a Financial Model | The Complete Guide image
add_shopping_cartContinue reading for free (70% left)

Reviews

  • No review yet!

keyboard_arrow_leftkeyboard_arrow_right

Any questions on How To Build A Financial Model | The Complete Guide?

The user community and author are here to help. Go ahead!

please wait...