How to build Financial Models? (10 Step Guide)

  1. Historical assumptions and results

Each financial model begins with historical results for the company. For instance, we may calculate the financial data of 3 three years in an excel sheet, and make assumptions for the historical period by calculating the revenue growth rate, profit margins, fixed and variable costs and inventory. Through these, it is possible to fill in the assumptions for the forecast period

 

  1. Income statements

The income statement is calculated with the income, collection of customs fees, total profit and operating expenses, up to profits before interest, taxes, consumption, and debt payment.

  1. The balance sheet

With the presence of the income statement, the balance sheet is filled in, in addition to the accounts due, inventory, revenue, COGS, account receivable days, stock days. Then, the accounts payable are filled in, and this is the function of the COGS and account payable days

  • Building the supporting schedules

Before completing the income statement and balance sheet, we must create a schedule of the capital assets such as: (PP&E) property, plant and equipment and debt and interest.

The PP&E schedule is for the historical period in addition to capital expenditures and subtraction of depreciation

The debt schedule also consists of the historical period, in addition to the increase in debt and subtraction of payments

The interest is based on the average debt stock

  • Completing the income and balance sheet data

Data from supporting tables is a supplement of the income statement and balance sheet

Depreciation is linked to the PP&E table and the debt interest table. Hence, it is possible to calculate profits before taxes, taxes and net income.

In the balance sheet, the closing balance is linked to the PP&E and the closing balance is linked to debt from the tables

The shareholders ’rights can be achieved by withdrawing the closing balance for the past year, and adding the net income and the capital raised, and subtracting the profits or the shares bought

  • Building a cash flow statement

After finalizing the income statement and the balance sheet, it is possible to create the statement of cash flows by starting with the net income and adding the depreciation again, and controlling the changes in capital, that is not cash, which produces cash from operations

The cash used in the investment is the function of capital expenditures in the PP&E table and the transferred cash is the function of the assumptions that were used to increase debt and ownership.

  1. Conducting (DCF) discounted cash flow analysis

After completing the 3 models, we calculate the free cash flow, evaluate the business, and deduct the free cash flow of the company of the day, considering the company’s capital cost (Opportunity cost or required return rate)

  1. Sensitivity and scenario analysis

Once the DCF analysis and the section evaluation are completed, we turn to the sensitivity and scenarios analysis of the model

The aim of this is to determine to what extent the company’s value is affected by changes in the basic assumptions, and this is beneficial for evaluating investment risks or for business plans

  1. Building charts

A good financial analyst is one whose results are clear and the best way to show the results of the financial model is through graphs, professional presentation

 

  1. Test pressure and model accuracy

When we finish the model, we should test it to identify its proficiency. It is also important to use the audit tools.