Financial modeling is the process of creating imaginary financial statements of the company that can be used to calculate the impact of an event or future decision.
A financial model is a tool built in Excel or any other planning software to forecast the financial performance of a business in the future. The forecast is usually based on historical performance, assumptions about the future and requires the preparation of an income report, balance sheet, cash flow statement and investment and financing plan. From there, more advanced types of models can be built, such as reduced cash flow analysis (DCF model), effective acquisition (LBO), mergers and acquisitions (M&A), and sensitivity analysis.
What is a financial model used for?
The product of a financial model is used for decision making and financial analysis, either inside or outside the company. Within a company, directors will use financial models to make decisions about:
- Capital increase (debt and / or equity)
- Making purchases (businesses and / or assets)
- Increased activity organically (ie opening new stores, entering new markets, etc.)
- Sale or disposal of assets and business units
- Budgeting and forecasting (planning for the coming years)
- Capital allocation (priority of the projects to invest in)
- Evaluating a business
The main sections to be included in a financial model (from top to bottom) are:
- Assumptions and factors
- The income statement
- Balance sheet
- Statement of cash flows
- Support planning
- Sensitivity analysis
- Charts and graphs
How do you build a financial model?
Financial modeling is a complex process. You have to analyze many aspects of business processes and take into account the smallest details that can influence the outcome of the final calculations.
Below is a step-by-step breakdown of how you should start and how to finally connect all the dots.
- Historical results and assumptions
Each financial model starts with the historical results of a company. Start building the financial model from three past financial statements and enter the data into Excel or the planning software. Next, analyze the data and calculate indicators such as revenue growth rate, gross margins, variable costs, fixed costs, AP days (DPO), inventory days (DIO)and AR days (DSO), etc. From there, you can start with the assumptions for the forecast period and take into account the future influences of the changes in the business environment.
- Start with the income and expenditure situation
With the assumptions foreseen, you can calculate the top of the income statement, COGS, gross profit and operating expenses up to EBITDA. You will have to wait to calculate depreciation, interest and taxes.
- Start the balance sheet
With the top of the income statement, you can start filling in the balance sheet. Start by calculating debt accounts and inventory, which are both revenue and COGS functions, and AR day and inventory day assumptions. Next, fill in the paid accounts, which is a function of COGS and AP days.
- Establish the investment plan and their financing
Before you complete your income and balance sheet, you must create a program for capital assets such as Property, Plant and Equipment (PP&E), as well as debt and interest. The PP&E program will draw from the historical period and add capital expenditures and reduce depreciation. The debt program will also draw from the historical period and add debt increases and repayment of loans. The interest will be based on the average balance of the debt.
- Complete the income statement and balance sheet
The information in the investment and financing plan completes the income statement and the balance sheet. In the income statement, link the depreciation to the PP&E program and the interest to the debt program. From there you can calculate earnings before taxes, taxes and net income. On the balance sheet there is a connection between the PP&E closing balance and the closing balance in the graph. Shareholders’ equity can be supplemented by extracting the closing balance last year, adding net income and high capital and lowering dividends or redeemed shares.
- Build the cash flow statement
With the completion of the income statement and balance sheet, you can build the cash flow statement with the reconciliation method. Start with net income, add depreciation back and adjust for changes in working capital without cash, which results in cash from operations. Cash used for investments is a function of the capital expenditures of the PP&E program, and the cash from the financing is a function of the assumptions that have been made regarding debt and equity growth.
7. Add sensitivity analysis and scenarios
The purpose of this analysis is to determine how much the value of the company (or other metric) will be affected by the changes in the underlying assumptions. This is very useful for assessing the risk of an investment or for business planning purposes (i.e. should the company raise money if sales volume drops by x percent, reduce costs?).
8. Build charts and graphs
Clear communication of results is something that is really essential. The most effective way to show the results of a financial model is through graphs and diagrams. Most managers do not have the time or patience to analyze the interior workings of the model, so the graphics are much more efficient.
9. Stress test and model audit
When the model is finished, your work does not end. Next, it’s time to start extreme stress testing scenarios to see if the model is behaving as expected. It is also important to use the audit tools to ensure that the financial statements prepared are correct and that the formulas in Excel or the settings in the scheduling software work correctly.