A Complete Guide to Financial Modeling

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 essentially just tools to help people make business 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 acquisition (M&A), and whether or not to raise money (e.g., do an IPO); and other corporate finance transactions.

The financial model allows decision-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 that 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 is Excel’s phenomenal versatility. Every company or investment opportunity is unique, and Excel is a blank canvas that can be customized and tailored to the situation. The flip side of this, however, 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:

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 to look 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 CFI’s free Excel Crash Course.

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

Financial Modeling Best Practices

Over and above good Excel skills, analysts who stand out in 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 (e.g., blue and black)

2. Build a standalone 3-statement model on one worksheet (don’t separate the statements onto different sheets)

3. 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 (e.g., pull forward EBITDA from the income statement to the cash flow valuation section)

8. Avoid linking to other Excel workbooks unless necessary (and if so, clearly indicate those links exist)

9. Avoid circular references unless necessary (and use an iterative calculation to solve them)

10. Use tables, charts, and graphs to summarize important information

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 of 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., until 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, 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 regression analysis in Excel.

• Year-over-year growth rate.  This is the most basic form of forecasting. Simply use a year-over-year (YoY) percentage growth rate.

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-or-less multiplies, divides, adds, or subtracts to produce the statements. This is the step-by-step part of the financial modeling guide.

Step #1 – Begin by calculating revenue, based on the forecasting approach used from the above section. From there, fill in the cost of goods sold (COGS), gross profit, and 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 for 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 statements, so please watch our video-based courses on financial modeling if you’d like more detailed instructions.

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 (M&A model course)

• Capital raising – analyze the pro forma impact of raising debt or equity, or other capital events

• LBO analysis – determine how much leverage (debt) can be used to purchase the company (LBO model course)

• 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)

Presenting the Results

When all of the above analysis is done, the work is 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 get to shine.

It’s one thing to build a complex model that only you understand, but it’s another thing to effectively 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.

Rayvat Accounting is an accounting outsourcing firm that provides online accounting services for small businesses, CPAs, CFOs, non-profit organizations, entrepreneurs, and individuals across the globe.