Your Basic Guide to Creating a Financial Model

Here’s a basic guide to creating a financial model. I will cut through the jargon to help you understand how to prepare a financial model for your business.

Let’s begin!

Historical results

Pull 3 years of financial statements and put them into Excel. Now, calculate items like AP days, inventory days, AR days, fixed costs, variable costs, gross margins, revenue growth rate, etc., to reverse engineer the assumptions for the historic period.

Prepare the income statement

Compute the income statement with gross profit, COGS, revenue, and operating expenses, including EBITDA. Don’t calculate the taxes, interest, amortization, and depreciation now.

Prepare the balance sheet

Calculate inventory day assumptions, AR days, inventory, and accounts receivable. Now, input accounts payable.

Create the supporting schedules

Creating a schedule for interest, debt, and capital assets such as PP&E is important before completing the balance sheet and the income statement. The PP&E schedule will subtract depreciation and add capital expenditures by pulling from the historical period. Similarly, the debt schedule will subtract repayments and add debt increases by pulling from the historical period. The debt balance on average, will decide the interest.

Finalize the balance sheet and the income statement

Complete the balance sheet and income statement using the data from the supporting schedules. The balance sheet links the closing debit balance from the schedules and the closing PP&E balance. The income statement links the debt schedule to the interest and the PP&E schedule to the depreciation. Then, calculate net income, taxes, and earnings before tax. Look at the closing balance of last year to complete shareholder’s equity. Subtract shares repurchased or dividends and add capital raised and net income.

Create the cash flow statement

Use the reconciliation method to create the cash flow statement. To obtain cash from operations, begin with net income, adding back depreciation and adjusting the non-cash working capital accordingly.

Do the DCF analysis

With the completion of the 3-statement model, you can start performing the business valuation and calculating free cash flow. The business’ free cash flow is discounted back to today at the firm’s capital cost.