Knowing how to create financial models has become a required skill for nearly all analyst and management positions in business and finance. This has led to a rise in financial modeling governance practices to ensure the integrity of models through development, implementation, and monitoring.
Formal financial modeling experience reduces a firm’s liability due to improper or misleading modeling practices. Part of the effort to formalize the financial modeling process is the effort to instill best practices in model development. The following are the six steps in the process of creating and using financial models:
Step 1: Gather Historical Data
Financial models require at least three years of a company’s financial data. The less historical the data available to the model, the less accurate the model results will be. By reverse-engineering the data, analysts can calculate revenue growth rate, gross margins, and variable and fixed costs. The results are used to complete assumptions for the forecast period.
Step 2: Calculate Ratios and Metrics
The historical data also serves as the foundation for determining historical ratios and metrics, including growth margins and rates, inventory changes, and asset turnover ratios. Four types of ratios that support business and financial decisions are profitability ratios; liquidity ratios; solvency ratios; and valuation ratios, which are also called multiples. To avoid cherry-picking ratios that present a company’s finances in the best light, ratios are typically used in combination to provide a comprehensive view from many different angles.
Step 3: Make Informed Assumptions
Historical data, ratios, and metrics serve as the basis for formulating assumptions about a company’s financial future. These assumptions help analysts calculate future growth margins and growth rates, potential asset turnover, and possible changes in inventory. Among the assumptions created are those relating to revenues and expenses, as well as working days in a year and seasonal variance.
Step 4: Create a Forecast
Combining historical data, ratios, metrics, and assumptions leads to the formulation of a specific forecast of a company’s future cash flow, income, and balance sheets. The forecasts usually attempt to answer a single question, such as the financial feasibility of an acquisition or a capital expense, or whether to shut down or outsource certain operations. They’re also used to gauge the level of risk involved in a specific transaction.
Step 5: Determine a Value for the Company or Asset
Once the financial forecast is complete, various models can be applied to find the value of a firm, a division, or an asset, typically using the DCF method or the present value of free cash flows.
- The DCF model assumes the company being analyzed will continue to sell its products with no drop in demand, thus guaranteeing a steady cash flow.
- The present value of free cash flows assumes the firm’s management will spend its capital in ways that preserve its cash flow and that the value of the present cash flows exceeds the company’s stock value.
Step 6: Review and Compare Various Resulting Scenarios
The more scenarios the model constructs, the clearer the picture of the future that the financial model generates. It may seem counterintuitive that a model that creates a range of possible future outcomes is considered more accurate than a model that generates a single forecast, but scenario modeling allows analysts to compare many potential eventualities rather than only the one the model identifies as most likely or the best case.
Resources on How to Build Financial Models
- Sturppy, The Best Resources for Startup Financing and Modeling — Resources include scenario planning and analysis, use of artificial intelligence (AI) in financial models, and creating a financial model for a mobile app.
- Verizon, 18 Ways to Make Your Financial Models Stand Out to Investors — Tips include targeting the model to its intended audience, color-coding fonts and other layout elements, and creating an area for assumptions and main drivers.
Common Financial Modeling Techniques
The financial modeling techniques and tools used in business range from the long-time stalwart Microsoft Excel to the latest AI-powered modeling tools. Financial analysts continue to use Excel to create basic financial models despite the growth in financial modeling tools designed to improve business decisions and help companies adapt to rapidly changing industry and economic conditions.
Examples of Excel-based financial model techniques include DCF models and valuation models such as the dividend discount model (DDM) and the comparables model. The standard approach to financial modeling in Excel is to use the x-axis to measure a time period (quarters or years, for example) and the y-axis to show revenue, cost of goods sold (COGS), gross margin, and other financial results. Revenue estimates may be generated on a separate sheet.
The following are among the financial modeling tools that are widely used in business:
Planful
Planful is a financial planning and analysis (FP&A) program based on the concept of continuous planning in all areas of the organization, including budgeting, capital expenses, workforce, and operating expenses. The program gives business managers access to complete financial information updated daily and the ability to generate reports quickly and simply.
Prophix
Prophix is intended to serve as a “virtual financial analyst” that automates many aspects of budgeting and planning, reporting and analytics, financial management, and workflow automation. The system puts all of a company’s financial information in a single location to avoid managers having to open several different Excel spreadsheets to access it. However, some users report that it doesn’t integrate well with Excel.
DataRails
Data Rails is a corporate performance management (CPM) solution that generates profit and loss (P&L) statements, balance sheets, and cash flows from figures pulled from the company’s databases in real time. The program is used primarily by midsize firms for budgeting and planning, tracking capital and operational expenses, and automating appreciation and amortization calculations.
Anaplan
Anaplan bills itself as a platform for building hyperscale models that give managers a 360-degree view of their operations to the transaction level. The platform is intended primarily for enterprises to help managers create forecasts quickly and accurately. Among the platform’s popular features are forecasting, scenario modeling, personalized dashboards, and management reporting.
Workday Adaptive Planning
Workday Adaptive Planning is another enterprise planning tool that automates financial modeling via a dashboard interface. The program integrates with many core business systems, such as Salesforce, Oracle, SAP, and Sage Intacct. Customers report that their favorite Workday Adaptive Planning features are forecasting, financial budgeting, long-term financial planning, and management reporting.
Centage Planning Maestro
Cengage Planning Maestro financial planning and analysis software supports scenario planning, budgeting, financial forecasting, reporting, and analytics via an intuitive dashboard interface. The cloud-based service is intended to provide enterprise-level modeling features for small and midsize businesses. Among its features are 40 prebuilt reports that are compliant with generally accepted accounting principles (GAAP) and International Financial Reporting Standards (IFRS) that can be exported to Excel and general ledger systems.
OnPlan
On Plan is designed primarily for sales forecasting and budgeting. It combines the usability of spreadsheets with the speed of custom financial planning and analysis software. Managers can use Excel syntax to create their models while also benefiting from visualizations and scenario generation. Among the application’s features are rolling financial forecasts, template management, budgeting, performance tracking, and management of roles and permissions.
Oracle Essbase and Hyperion
The Oracle Essbase database development system works with Oracle’s Hyperion financial management solution to provide enterprises with business intelligence (BI) tools integrated with financial modeling of data pulled from Oracle databases. One of the strongest features of the Oracle approach to financial modeling, according to users, is the ability to perform multidimensional analyses much more simply than when using a relational database.
Resources on Financial Modeling Techniques
- Layer, “The Guide to Financial Modeling and Forecasting” — A description of several types of financial models and the differences between financial modeling and financial forecasting.
- Modano, Forecasts — An examination of the process of creating an historical and a financial forecast model that maps historical data to forecasts directly, so financial performance can be tracked more accurately over time.
How to Learn Financial Modeling
The two most common ways that business managers learn financial modeling are by taking online courses and by enrolling in a business or a finance degree program.
- The options available for online financial modeling courses range from those that can be completed in a matter of hours to programs that require months to complete. Subjects covered in these courses include Excel skills, business statistics and analysis, and financial modeling for startups and small businesses.
- While traditional colleges and universities rarely offer degree programs in financial modeling, financial analysts and business managers commonly have at least a bachelor’s degree in financial services or a related subject. Courses in such programs typically cover business statistics, securities analysis and investment techniques, financial planning, and portfolio management.
- Among the certifications for financial modeling are the Corporate Finance Institute’s Financial Modeling and Valuation Analyst (FMVA) credential and the International Financial Modeling Institute’s Professional Financial Modeler (PFM) certification.
Financial Modeling Skills Required for Financial Analyst Positions
The duties of financial analysts include determining the value of a company by examining its financial statements, according to the U.S. Bureau of Labor Statistics (BLS). Among the skills necessary to qualify for financial analyst jobs are math, financial analysis software, analytics, decision-making, and communication.
Most financial analysts work for one of three different types of finance and investment firms:
- Buy-side firms are investment companies that manage their own funds.
- Sell-side firms employ analysts to prepare research reports that influence the buy and sell decisions of investors.
- Investment banks and equity firms rely on analysts to model potential mergers and acquisitions, IPOs, and other financial deals between companies.
Resources on How to Learn Financial Modeling
- Finance Walk, “The Best Way to Learn Financial Modeling in Excel” — Methods include video tutorials, virtual classroom training, use of case studies, and distance learning.
- The Balance, “Best Online Financial Management Courses” — Features online courses focusing on investment banking, modeling in Excel, and modeling for small businesses and startups.
Financial Modeling: A Fundamental Skill for Financial Analysts
As firms of all types and sizes rely increasingly on sophisticated financial models to support their business decisions, the value of financial modeling skills skyrockets. Anyone interested in pursuing a career as a financial analyst or financial planner will find that possessing the ability to construct, run, and analyze financial models can help prepare them for a range of financial and management positions.