At its core, a model is designed to provide quantitative analysis and insights into a company’s performance and value. Financial models allow analysts to test assumptions and scenarios to predict future revenues, costs, asset values, debt levels, and profitability.
Models come in many forms and levels of complexity, but most share some common elements:
- Inputs: Assumptions about future financial performance, such as revenue growth, profit margins, capex, etc.
- Formulas: Mathematical relationships linking the inputs to outputs. Formulas calculate revenue, costs, taxes, etc., based on the inputs.
- Outputs: The projected results, such as income statement, balance sheet, cash flow statement, ratios, etc.
- Scenarios: The ability to quickly test different versions of models by changing inputs and assumptions.
While models rely heavily on math and Excel formulas, their real power comes from the insights they provide into a business. Building models requires critical thinking about how the different pieces of a company work together.
Why Do Financial Models Matter?
Financial models have become indispensable tools for nearly all key finance roles today. Here are some of the job titles that rely on Excel modeling today:
Investment Banking & Financial Advisory
Investment bankers use models to analyze the valuation, financing, and financial feasibility of mergers, acquisitions, IPOs, debt/equity issuances, and other deals. The outputs help bankers advise clients on deal terms, structure, and strategy.
Private Equity & Venture Capital
PE and VC firms use models to evaluate potential investments in target companies. Models estimate equity value, returns to investors, exit multiples, and more. They also monitor portfolio company performance post-investment.
Corporate Finance & FP&A
FP&A teams in operating companies use models for budgeting, forecasting, managing working capital, analyzing capital projects and acquisitions, and making other financial decisions. Models provide metrics to steer strategy.
Equity Research & Asset Management
Equity researchers and asset managers use models to project revenues, costs, profitability, cash flows, and other metrics for companies under coverage. The models support valuation and investment recommendations.
Credit Analysis & Risk Management
Banks use models to assess the creditworthiness of institutional clients and set credit limits. Models estimate downside risk, debt service coverage, collateral coverage, and more.
Management Consulting
Consultants build models to evaluate growth opportunities, potential cost savings, operational improvements, and other strategic initiatives for clients.
Financial modeling is mission critical in virtually all roles in the finance and banking industry. Having strong modelling skills signals to employers that you can think strategically and quantitatively analyze real business problems.
Types of Popular Financial Models
Discounted Cash Flow Model
Using the weighted average cost of capital (WACC), a discounted cash flow (DCF) model actively projects a company’s future free cash flows and discounts them back to their present value. This method allows analysts to calculate the net present value (NPV) of expected future cash flows. DCF models are common tools for valuation and capital budgeting, which are critical factors in any business venture.
Comparable Company Analysis
This approach values a company by using multiples, such as the price-to-earnings (P/E) ratio, from similar publicly traded companies. It operates on the theory that comparable companies will exhibit similar valuation multiples.
Leveraged Buyout Model
LBO models calculate returns on private equity acquisitions. They factor in multiple parameters, including the ratio of debt-to-equity used for financing a deal and projected returns on the invested equity.
Merger Model
Merger models quantify the financial profiles of companies involved in a merger or acquisition deal. They objectively determine potential synergies, accretion, and dilution and provide a tangible measure of combined valuation.
Startup Model
Start-up models forecast revenues, expenses, and cash flows for new companies. They assist in estimating critical factors, including capital requirements, burn rates, and exit valuations.
How to Start Learning Financial Modeling
While modeling can seem intimidating initially, there are simple steps you can take to gradually start developing your skills:
Step 1: Understand the 3 Financial Statements
Before you can model a business effectively, you need a solid grasp of how the three key financial statements – income statement, balance sheet, and cash flow statement – work together to represent the overall financial health of a company. Know the major line items, how they interact, and the key accounting equations that link them. It’s also helpful to know that when a number looks “off,” what decision should be made?
Step 2: Learn Excel Shortcuts
Excel is the primary tool for building models. Become a proficient Excel user by mastering keyboard shortcuts, functions, and key modeling features like data tables, goal seek, and scenarios. Skilled modelers can navigate Excel quickly without using a mouse.
Step 3: Study Public Examples
The best way to learn modeling is to examine existing models. Search online to find sample models for industries you’re interested in. Study the structure, logic flow, formulas, and format. Recreate parts of the models yourself in Excel.
Step 4: Simplicity is Key
When first getting started, the goal shouldn’t be to build complex models with thousands of lines. Begin with straightforward examples that calculate revenue, costs, profitability, and other metrics based on a few manual inputs. Gradually add layers of complexity over time.
Step 5: Learn From Your Mistakes
You’ll make plenty of errors as you learn modeling – incorrect formulas, broken links, miscalculated totals – and that’s okay. The key is reviewing each model carefully to understand what went wrong and why. This will help you sharpen your skills with each subsequent model.
Step 6: Expand Your Library
Build up a library of sample models across various industries and transaction types. Include models you’ve built yourself, found online, or used in job interviews and internships. They’ll become handy reference points as you advance in your career. As you move through different positions, take the best of a certain template and continue to add to your library.
Key Modeling Skills to Master
As you gain more modeling experience, focus on developing these core skills.
Structuring with a Top-Down Approach
A model’s layout should be logical, clear, and efficient. Use a top-down approach, where high-level inputs flow into more detailed supporting calculations. Group similar sections together effectively.
For example, start your model with overarching revenue assumptions at the top. Then, it will be broken down into detailed cost calculations, with separate sections for fixed and variable expenses, all organized cohesively for easy navigation and understanding.
Maintaining Dynamic Formulas
Avoid static numbers. Build dynamic formulas that update outputs as the inputs change. Make as many cells as possible reference other cells for inputs rather than being hard-coded numbers. We can use tracing tools later to check the formulas. This will make updating the template’s numbers much more efficient than creating another model from scratch.
Performing Reality Checks
Build in sanity checks to ensure your model remains reasonable at all times. Check for errors in formulas, unrealistic growth rates, outsized variations between periods, and values that exceed typical industry ranges. Be sure to use your network in this process as well. Check with your peers on the accuracy based on their experience and what the end-result outcome could look like if those numbers are accurate.
Incorporating Flexibility
Allow for flexibility to test different scenarios by making key drivers into variables that users can easily change. Build data tables to show outputs under different assumption sets.
Linking Across Statements
The income statement, balance sheet, and cash flow statement should be integrated cleanly. Ensure changes flow through the model consistently across all three statements. Double-check that your template continues to work. The more accurate your template, the easier it will be later on in your career as you continue to use the same template.
Designing Clear Outputs
Model outputs should provide users with clear takeaways. Structure output pages to highlight key metrics, ratios, and trends. Summarize data appropriately using summary tables, charts, and graphs.
Template Examples
To help tie these concepts together, let’s walk through two common modeling examples.
Building a DCF Model
A discounted cash flow (DCF) model forecasts a company’s free cash flows into the future and discounts them back to the present to estimate equity value. Key steps
- Projecting revenue growth: Research the company’s products, markets, and historical growth to project realistic revenue growth rates for the forecast period. Consider industry outlook, competition, and macro trends.
- Estimating operating expenses: Project margins and expense ratios based on historical performance and future expectations. Drivers include economies of scale, input costs, SG&A leverage, and efficiency initiatives.
- Modeling working capital: Use historical data to create formulas for changes in working capital accounts like inventory, receivables, and payables based on revenue growth.
- Calculating free cash flow: Subtract capital expenditures from operating cash flow to determine free cash flow available to equity investors. Incorporate expected CapEx for maintenance, growth, etc.
- Discounting cash flows: Discount future free cash flows back to the present using the company’s weighted average cost of capital (WACC) to derive a net present value.
- Determining terminal value: Estimate value beyond the explicit forecast period using a perpetuity growth formula or exit EBITDA multiple.
The end result is an equity valuation that can be compared to the current market price. Scenario analysis on key drivers provides insights into upside and downside risks.
Building an LBO Model
A leveraged buyout (LBO) model assesses the viability of a private equity investment. Here are the high-level steps.
- Identifying debt financing: Model the principal and terms for senior debt, junior debt, and other funding sources based on typical market standards.
- Modeling returns: Project EBITDA, interest expense, debt paydown, and equity cash flows to determine IRR and multiple of invested capital for the PE firm.
- Structuring ownership: Determine the PE firm’s ownership stake based on the equity contribution needed to fund the purchase price and transaction fees.
- Analyzing debt capacity: Test debt coverage ratios like DSCR to ensure adequate cash flow to service debt obligations throughout the holding period.
- Modeling exit and sale proceeds: Estimate exit EBITDA multiple and transaction fees. Calculate net sale proceeds available to the PE firm upon exit.
- Performing sensitivities: Run scenarios on key value drivers like revenue growth, profit margins, debt terms, and exit multiple to assess potential upside and downside cases.
Given the capital structure and forecast operating performance, this model determines if the proposed LBO provides acceptable risk-adjusted returns to the PE firm.
How to Get Modeling Practice
Getting modeling practice is critical for rapidly improving your skills. Completing modeling tests, case studies, and exercises from online learning platforms and modeling bootcamps allows you to work through detailed examples across various industries and situations.
Participating in internships and training programs at banks and other financial firms provides the opportunity to receive direct modeling training and get staffed on live projects to develop real models. You can also search the internet for publicly available modeling resources from financial institutions as many make sample models or modeling guidelines available for practice.
Offering to build or improve models for classes, student organizations, or startups for free is a great chance to apply your modeling skills to real business needs. Participating in modeling competitions and case competitions via student clubs or modeling platforms allows you to compete against peers and improve your skills.
Ultimately, nothing develops modeling proficiency like hands-on learning in the work environment. You should seize every opportunity – both in and out of work – to practice modeling actual business problems and get constructive feedback on your models. This will accelerate your progress tremendously.
Next Steps in Your Modeling Journey
This guide covers the fundamentals of financial modeling and how to start developing your skills. But modeling is a journey that takes continual practice. To take your expertise to the next level:
- Become a pro in Excel: Become a power user by mastering pivot tables, VLOOKUPs, macros, solvers, and other advanced functions. They’ll help you model faster and more efficiently.
- Understand accounting: Take accounting courses and strengthen your grasp of financial statement reporting, revenue recognition, accruals vs. cash flow, and other concepts critical for modeling.
- Become knowledgeable in capital markets: Learn how instruments like debt, equity, and derivatives are valued and used in corporate finance. It will expand your modeling toolkit.
- Specialize in industries: Tailor your modeling knowledge to specific industries, such as energy, healthcare, real estate, and others. Each has unique modeling nuances and industry averages.
- Stay up to date: Continuously expand your modeling skills as new data sources, technologies like machine learning, and industry best practices emerge.
The best modelers have an insatiable appetite for learning. Embrace modeling as a lifelong journey, stay curious, and you’ll excel in any finance role.
Try Macabacus for free to help accelerate your financial modeling career with 100s of shortcuts, formula auditing tools, and links to presentations.