Financial modeling is the process by which a firm constructs a financial representation of some, or all, aspects of the firm or given security. The model is usually characterized by performing calculations and makes recommendations based on that information. The model may also summarize particular events for the end user such as investment management returns or the Sortino ratio, or it may help estimate market direction, such as the Fed model.
Typically, financial modeling is understood to mean an exercise in either asset pricing or corporate finance, of a quantitative nature. In other words, financial modelling is about translating a set of hypotheses about the behavior of markets or agents into numerical predictions; for example, a firm’s decisions about investments (the firm will invest 20% of assets), or investment returns (returns on “stock A” will, on average, be 10% higher than the market’s returns).
The output of a financial model is used for decision making and performing financial analysis, whether inside or outside of the company. Inside a company, executives will use financial models to make decisions about:
- Raising capital (debt and/or equity)
- Making acquisitions (businesses and/or assets)
- Growing the business (i.e. opening new stores, entering new markets, etc.)
- Selling or divesting assets and business units
- Budgeting and forecasting (planning for the years ahead)
- Capital allocation (priority of which projects to invest in)
- Valuing a business
Financial modeling is an iterative process. You have to chip away at different sections until you’re finally able to tie it all together.
Below is a step-by-step breakdown of where you should start and how to eventually connect all the dots. Detailed instruction on How to create and work through your own Excel model will be taken in the class.
- Historical results and assumptions: Every financial model either starts with a company’s historical results or if the company is new, then it is based on the assumptions and industry ratios / trends. You begin building the financial model by pulling three years of financial statements and inputting them into Excel. Next you reverse engineer the assumptions for the historical period by calculating things like revenue growth rate, gross margins, variable costs, fixed costs, AP days, inventory days, and AP days, to name a few. From there you can fill in the assumptions for the forecast period as hard-codes.
- 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.
- 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 are functions of revenue and COGS as well as the AR days and inventory day’s assumptions. Next fill in accounts payable which is a function of COGS and AP days.
- Build the supporting schedules: Before completing the income statement and balance sheet you have to create a schedule for capital assets like 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 schedule will also pull from the historical period and add increases in debt and subtract repayments. Interest will be based on the average debt balance.
- Complete the income statement and balance sheet: The information from the supporting schedules completes the income statement and balance sheet. On the income statement, link depreciation to the PP&E schedule and interest to the debt schedule. From there you can calculate earnings before tax, taxes and net income. On the balance sheet link the closing PP&E balance and closing debt balance from the schedules. Shareholder’s equity can be completed by pulling forward last year’s closing balance, adding net income and capital raised and subtracting dividends or shares repurchased.
- Build the cash flow statement: With the income statement and balance sheet complete you can build the cash flow statement with the reconciliation 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.
- Perform the DCF analysis: When the three statement model is completed it’s time to calculate free cash flow and performs the business valuation. The free cash flow of the business is discounted back to today at the firms cost of capital (its opportunity cost, or required rate of return). We offer a full suite of courses that teach all of the above steps with examples, templates, and step-by-step instruction.
- Add sensitivity analysis and scenarios: Once the DCF analysis and valuation section is 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 assessing the risk of an investment or for business planning purposes (i.e. does the company need to raise money if sales volume drops by x percent?)
- Build charts and graphs: Clear communication of results is something that really separates good from great financial analysts. The most effective way to show the results of a financial model are thought charts and graphs, which we cover in detail in our advanced Excel course, as well as many of the individual financial modeling courses. Most executives don’t have the time or patience to look at the inner workings of the model, so charts are much more effective.
- Stress test and audit the model: When the model is done your work is not over. Next it’s time to start stress testing extreme scenarios to see if the model behaves as expected. It’s also important to use the auditing tools covered in our financial modeling fundamentals course to make sure it’s accurate and the Excel formulas are all working properly.