Free Financial Modeling Guide
A free step-by-step guide on financial modeling in Excel
This page is written to provide our readers with a complete guide on financial modeling while thoroughly explaining all its aspects. By the end of this guide, you should be on your way to building financial models like a seasoned professional.
In this practical guide, we have followed a step-by-step approach to walk you through all the key aspects of financial modeling. First, we discuss what financial models are. Then, we describe the four main approaches to forecasting. Finally, we dive into the steps involved in financial modeling.
We strongly believe that a more hands-on approach always helps gain a much better understanding of a topic. Therefore, before we start, we would like you to pick out one of the FREE modeling templates that our finance experts have created for you to use as you go along reading this page. The templates are plug-and-play, so you can enter your numbers or formulas to auto-populate output numbers.
What is a financial model?
A financial model is a structured representation of finance settings from the real world. It is a powerful analytical tool that helps decision-makers forecast the consequences of a decision. By helping them make informed decisions, it gives the organization direction. It is usually built using spreadsheet software such as Excel. It also gives numerical context to risk and returns.
It is like a machine that uses historical financial performance and assumptions (such as growth rates, profit margins, customer acquisition costs, userbase size, etc.) as raw materials and produces projections of future performance. A range of assumptions is mostly used to gauge the variety of possible outcomes. Although choosing the right assumptions is one of the hardest parts when building financial models, its benefits outweigh the costs (time and effort) as they can be crucial for decision-making.
Financial modeling often starts with building a three-statement model as the base upon which other models are built, like the Discounted Cash Flow (DCF) models, mergers and acquisition (M&A) models, leveraged buyout (LBO) models, and sensitivity and scenario analyses. Is financial modeling an art or a science? That's moot. However, the consensus is that it has gained significant momentum and has developed plenty since the advent of the digital age.
There are four primary approaches to establishing assumptions when financial modeling. These approaches may be used on a standalone basis or with other approaches.
- Top-down: Analysts using this approach start working from the bigger picture towards smaller (yet important) details. It is common to start at the total market size and narrow it to market share and smaller segments based on geography, customers, products, etc. until it is narrowed down to the company's revenue. For example, a company may start with an assumption of the potential number of users for its service or the market size for its product. Then, they may work towards their company-specific details, like the customer acquisition cost (CAC) or the cost per unit.
- Bottom-up: This approach is the mirror opposite of the top-down approach. It starts with the foundational assumptions specific to the company related to its value drivers. An example of a value driver could be the number of salespeople needed and the related costs incurred to achieve a certain sales target. Then, users may use scenario analysis to analyze a range of numbers for the assumptions and assess the likelihood of meeting the objectives under various scenarios.
- Regression analysis: It refers to the use of regression to forecast vital assumptions about the drivers that fuel a company's performance. This kind of forecast enables users to analyze the relationship between a company's income and other factors, like sales costs, direct costs, price of a product, etc. This functionality can be used by clicking on the Data tab, then clicking on Data Analysis, and finally choosing Regression Analysis.
- YoY growth: Using the year-on-year (YoY) growth rates as the basis for forecasting the future financial health of a company is the most basic approach. In this approach, we simply calculate the historical YoY growth rates and use them to calculate future sales, net earnings, etc.
How to build a financial model?
It is of utmost importance to structure the model and the modeling process logically. Structuring makes the modeling process easier, and it also helps new users understand the model. Furthermore, it is certainly beneficial when auditing the model as well.
Depending on what type of financial model is desired, different steps may follow. For example, a standard financial model may link the financial statements and forecast them based on assumptions, also known as a three-statement model. The key is to start with understanding what you want your result to look like. The first step in this direction can be asking the right questions.
Often, building a financial model starts with the three-statement model. Steps 1 to 8 teach you about building a three-statement model which is used as a base for other models. Steps 9 to 12 are relevant when conducting further analyses.
- Collect historical data
- Analyze historical data
- Determine model drivers
- Project income statement
- Forecast investment in capital assets and funds from financing activity
- Project balance sheet
- Build cash flow statement
- Link the statements
- Further analyses (DCF models, LBO models, M&A models, etc.)
- Stress-test and audit
- Present the results
Please ensure that iterative calculations are disabled in Excel (we will later enable it). It is done to handle circularity which is inherent in the three-statement model. A computation is circular when its output is also one of its required inputs directly or indirectly.
Iterative calculations can be disabled on the Windows version of Excel by going to File > Options > Formulas and deselecting the "Enable iterative calculation" checkbox. On the Mac version of Excel, users can go to Preferences > Calculation and then disable "Use iterative calculation." Please check out this web page from Microsoft on removing or allowing a circular reference for more details.
The steps to build financial models are elaborated below.
Input historical data
Historical numbers are used as the base upon which financial models are built. This data is particularly needed for the next two steps. Input the numbers for the income statement and the balance sheet. There are fewer chances of human error if the data can be downloaded or copied from a reliable source instead of manually entering it in the spreadsheet. Please see the tips below, which are part of the best practices in financial modeling.
- Format data differently. Data input manually needs to be formatted differently from calculations and links to other workbooks.
- Use comments where necessary.
- Shortcuts, especially the most used ones, make the modeling process efficient.
- Incorporate balance checks to ensure that the data is entered correctly.
Analyze historical data
Analyze the data by looking for trends and computing ratios, and statistical data. Model drivers (discussed in the next step) are based on the results of these analyses. YoY growth rates, margins, and ratios are among the commonly used metrics.
Determine model drivers
Also known as assumptions, model drivers guide the forecasts. They are established based on the previous two steps. Users may either use the same metrics as the analyses in the previous step or adjust them with a forward-looking perspective. Key assumptions include revenue growth rate, expenditure, and margins. It is important to ensure that the assumptions are reasonable and rational before proceeding, as this is the foundation of the entire financial model.
Project income statement
Once the assumptions are established, users can begin forecasting, starting with the income statement. Even though we have many of the inputs necessary for forecasting the income statement, it still requires supporting schedules for line items such as depreciation, taxes, and interest expense. Please note that net financing costs (interest expense) are not linked to the income statement at this point (it will be linked once the debt schedule is completed). This is to deal with circularity produced by financing costs. A computation is circular when its output is also one of its required inputs.
Forecast capital assets and financing activity
This step involves building supporting schedules. A depreciation schedule is prepared to account for depreciation for the coming years. There are multiple ways to account for depreciation, i.e., straight-line method, declining balance method, etc.
Similarly, the interest costs may be calculated in a variety of ways, i.e., based on the closing balance of debt, the opening balance, or an average of the two. Using an average is ideal, as there might be principal repayments throughout the year. For a more intensive calculation, users may build a separate supporting schedule for financing costs altogether. Usually, supporting debt schedules are prepared to calculate interest payments, principal repayments, and debt issuances.
Project balance sheet
The balance sheet is slightly tougher to build compared to the income statement. With the numbers available at this stage, it can be built without the figures for cash, equity, and debt. We need to build the cash flow statement to derive the numbers for those line items. We use averages of the ratios from recent years (with or without adjustments depending on the assumptions) to determine closing balances of the other line items.
Build cash flow statement
There are three parts to the cash flow statement. Each part must be completed by linking the line items to the ones in the other statements and the supporting schedules. We make all the adjustments necessary to convert net earnings from the accrual system to the cash system of accounting to determine the cash flows from operating activities. We also adjust for items that might rather belong in investing activities or financing activities.
Linking the statements
A few items which were previously left out in the balance sheet can now be filled. After filling in the line items for debt, equity, and cash in the balance sheet, the balance sheet should be complete, and interest expense should be the only item left to be filled in the income statement.
As mentioned earlier, financing costs have an impact on all three statements, which produces circularity. Plugging in the net finance costs in the income statement will change the net earnings, which will further impact retained earnings (balance sheet) and cash flows from operating activities (cash flow statement).
Excel solves iterative calculations by trial and error every time there is a small change in the dependencies. This becomes resource-intensive and slows down the computer. Since we only want these calculations at the end when they can be solved because the other numbers are in place, we disable the setting in the beginning and then enable it at the end.
The final step is to enable iterative calculations in Excel. On the Windows version of Excel, users can go to File > Options > Formulas and select the "Enable iterative calculation" checkbox. Users with the Mac version of Excel can go to Preferences > Calculation and enable "Use iterative calculation." The net earnings figure should get updated in the income statement now, which should flow into the other two statements as well.
At the end of these steps, what we have is a three-statement model.
Now that we have the three-statement model, we can add other layers of analyses depending on the objective, such as discounted cash flow (DCF) analysis, mergers and acquisitions (M&A) analyses, leveraged buyout (LBO) analysis, and more.
- DCF analysis is used to find the intrinsic value of a business.
- M&A analysis is used to assess the outcome of potential corporate events like mergers, divestitures, etc.
- Capital raising models are used to evaluate the impact of raising funds through debt, equity, or mezzanine financing.
- LBO models are often used to ascertain the ideal amount of debt for a buyout.
- Sensitivity analysis tells the users about how sensitive the outcome is to various values for a given assumption.
- Scenario analysis illustrates the various possible outcomes under different scenarios. There is often a best-case scenario, a worst-case scenario, and some in between.
Stress-testing and audit
Once the model is built, it is necessary to test it with extreme inputs and assumptions to see whether it behaves as expected under those assumptions. Certain audit tools and techniques can be used to confirm whether the model is accurate and that the formulae are working correctly.
Presenting the results
A model can lose half its potential value addition if the results are not communicated effectively. Financial models are only useful if analysts can communicate the results and findings to executives who are the decision-makers. Effective communication can be achieved through visual tools like charts and graphs. These tools are more effective primarily because executives do not have the time to look at every granular detail in the model.
Often, building a financial model starts with the three-statement model.
To read more about three-statement models, please check out WSO's complete guide to building three-statement models. To learn more about financial modeling and the various kinds of models, please check out WSO's Elite Modeling Package, in which our experts cover everything from the three-statement model and the DCF model to Excel tips and tricks so you can model more efficiently than ever.
Everything You Need To Master Financial Modeling
To Help you Thrive in the Most Prestigious Jobs on Wall Street.
More on Excel
To continue your journey towards becoming an Excel wizard, check out these additional helpful WSO resources.
or Want to Sign up with your social account?