How do you build a financial model? (10 steps guide)

Financial modeling is an iterative process. You need to delete the different sections before you can finally connect them together.

Here's a detailed breakdown of where to start and how to connect all the dots at the end.

1. Past Results and Assumptions: Any financial model starts with a company's past performance. To start building your financial model, get three financial statements and enter them into Excel. It then reconstructs assumptions from the past period by calculating sales growth, tuning, variable costs, fixed costs, AP days, inventory days, AP days, and more. From there, you can hardcode the assumptions of the forecast period.

2. Start the income statement: With the forecast assumptions in place, you can calculate the top of the income statement with revenue, COGS, gross profit, and operating expenses down to EBITDA. You will have to wait to calculate depreciation, amortization, interest, and taxes.

3. Start the balance sheet: With the top of the income statement in place, you can start to fill in the balance sheet. Begin by calculating accounts receivable and inventory, which is both functions of revenue and COGS, as well as the AR days and inventory days assumptions. Next, fill in accounts payable, which is a function of COGS and AP days.
   

4. Build the supporting schedules: Before completing the income statement and balance sheet, you have to create a schedule for capital assets like Property, Plant & Equipment (PP&E), as well as for debt and interest. The PP&E schedule will pull from the historical period and add capital expenditures and subtract depreciation. The debt program will also track the historical period, adding up debt increases and subtracting repayments. The interest is based on the average debt balance.

5. Complete the income statement and balance sheet: The information from the receipts completes the profit and loss account and the balance sheet. The income statement link depreciation to the PP&E program and interest to the debt program. From there, you can calculate profit before taxes, taxes, and net income. Carry forward the closing balance of property, plant, and equipment and the closing balance of debt from the schedules to the balance sheet. Equity can be completed by preparing last year's closing balance sheet, adding net income and capital raised, and paying dividends or deducting treasury shares.

6. Create a cash flow statement: With the full income statement and balance sheet, you can build a cash flow statement using the settlement method. Start with net income, add back depreciation, and adjust for changes in non-cash working capital, which results in cash from operations. Cash used in investing is a function of capital expenditures in the PP&E schedule, and cash from financing is a function of the assumptions that were laid out about raising debt and equity.

7. Perform the DCF analysis: When the 3-statement model is completed, it’s time to calculate free cash flow and perform the business valuation. The free cash flow of the business is discounted back to today at the firm’s cost of capital (its opportunity cost or required rate of return).

8. Add sensitivity analysis and scenarios: Once the DCF analysis and valuation sections are complete, it’s time to incorporate sensitivity analysis and scenarios into the model. The point of this analysis is to determine how much the value of the company (or some other metric) will be impacted by changes in underlying assumptions. This is very useful for investment risk assessment and business planning (e.g. if sales decrease by x percent, should the company raise funds?

9. Create charts and graphs: Clear communication of results is what really separates good financial analysts. The most effective way to show the results of a financial model is through charts and graphs. Most managers don't have the time or patience to consider the inner workings of a model, so maps are much more effective.

10. Stress test and model review: Once the form is ready, you're not done yet. Next, it’s time to start stress-testing extreme scenarios to see if the model behaves as expected.