How to build Financial Models? (10 Step Guide)
- 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
- 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.
- 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.
- 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)
- 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
- 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
- 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.