5 Steps to Effective Financial Modeling in Corporate Finance

Financial modeling is a powerful technique that helps corporate finance professionals create a mathematical model of their business and the impact of specific decisions on their future financial results.  There are several types of corporate finance models that are used in practice and many derivatives of these can be applied across an organization.  Microsoft Excel® is the tool of choice for simple financial modeling, however, for more complex requirements, purpose-built corporate planning and forecasting software solutions are a better choice.  Read on to learn more.

Corporate Financial Modeling

Financial modeling is a common tool used by individuals and corporations to create an abstract model of a real-world financial situation.  This typically involves the gathering and analysis of historic data, which is then used to create a forward-looking projection for future time periods. Individuals may create a financial model of their monthly or annual income and expenses to help manage their finances.  For the purposes of this discussion, we’ll focus on corporate financial modeling.

Corporate financial modeling is performed by financial analysts in a corporate finance group within an enterprise, or by the line of business analysts supporting a specific functional department such as Sales, Marketing, Customer Service, or other functions. Use cases for corporate financial modeling include strategic planning, long-range financial planning, financial budgeting, mergers and acquisitions (M&A) or divestiture analysis, capital planning, project planning, or evaluating the impact of critical business decisions.  This can include new product development and launch, geographic expansion, pricing of products and services, hiring and staffing, capital investment, and other business decisions.

Of course, in the financial services industry, financial modeling is performed by investment analysts as part of their evaluation of portfolio companies or potential investment targets.

Types of Corporate Financial Models

There are a wide variety of financial models used in corporate finance, so here we’ll cover the most commonly used types of financial models.  These include the following:

• Three Statement Modeling – this is the most common approach for corporate financial modeling, especially in long-range financial planning. It includes the creation and linking of the 3 primary financial statements:  Income Statement, Balance Sheet, and Cash Flows.

• Discounted Cash Flow (DCF) – this technique is also very common and builds on the three-statement model to value a company or a potential investment based on the Net Present Value (NPV) of the future cash flows. The DCF model takes the cash flows from the three-statement model, makes some adjustments where necessary, and then uses the NPV function to discount the future cash flows back to the present using the organization’s Weighted Average Cost of Capital (WACC).

• Mergers & Acquisitions (M&A) – this is a more advanced modeling technique used to evaluate the pro forma impact of a potential merger, acquisition, or divestiture on the financials of an enterprise. The level of complexity here can vary widely based on the size of the enterprise, and it may require the use of complex accounting logic, especially when it comes to consolidating results from multiple subsidiaries and analyzing the impact on earnings per share (EPS).  This technique is most often used in corporate development work, or in investment banking

• Annual Budgeting – this technique is widely used by corporate financial planning & analysis (FP&A) professionals to create the budget or annual operating plan (AOP) for the next fiscal year. Budget models are typically based on monthly or quarterly figures and focus heavily on modeling revenue and expenses at a departmental level, then rolling up to the corporate income statement.

• Forecasting – this technique is also used by corporate FP&A professionals and line of business analysts (e.g., sales forecasting) to update the annual budget model on a periodic basis. This may include combining first quarter actuals with the budget for the next three quarters to forecast the annual financial results, or to ask managers to submit new projections for future periods based on actual results. Forecasting can also be automated through the use of predictive analytics tools that use statistical forecasting or machine learning (ML) models to predict future results based on the analysis of historic data.

• Operational Modeling – this category of financial models can vary widely based on the type of business decision being considered. As mentioned earlier, corporate or line of business analysts typically create financial models to analyze the impact of new market expansion, new product launch, hiring and staffing, capital investments, pricing options, and other scenarios. This type of modeling usually starts with a set of historic or baseline data, followed by the creation of alternative scenarios that layer in or model the impact of the decision on the financial results.

5 Steps to Effective Financial Modeling

Financial modeling in a corporate setting is a critical process whereby the results of the process will be used to support decisions that can have a major impact on future financial results.  Therefore, great care should be taken to ensure the inputs and outputs of the financial modeling process are as accurate as possible. Here are five best practices that organizations should consider when performing corporate financial modeling:

1. Ensure the accuracy of historic data – remember the adage “garbage in – garbage out?” Having accurate and up-to-date historic financial data provides a critical foundation for corporate financial modeling.
2. Identify key drivers – based on an analysis of historic financial and operational data, key business drivers can be identified and used as levers in modeling future revenue and expenses. Examples include orders, shipments, average price, new customers, customer retention rates, headcount, events, and others.
3. Create multiple scenarios – once a baseline financial model is built, alternative scenarios should be created and analyzed based on the flexing of key drivers. The traditional approach is to create a base case, high case, and low case scenarios – but many organizations generate a wide range of scenarios that are used to guide critical decisions.
4. Leverage charts and graphs – while some Finance executives prefer to review and analyze grids of numbers, many find it easier to spot trends and key financial signals through data visualization. So creating charts and graphs to present and analyze financial models is a great way to help users quickly gain insights that can be used to support critical decisions.
5. Perform stress testing – when the financial model is done, the work is not over. The next step is to start stress-testing extreme scenarios to see if the model behaves as expected and yields realistic results.

Selecting the Right Tool for the Job

If you Google the term “financial modeling” you’ll get a number of results that highlight how Microsoft Excel® can be used to support financial modeling.  And while Excel is the “go-to” tool for financial professionals, it’s more suited to personal productivity tasks and less so to supporting enterprise planning requirements. Why?  Because Excel is error-prone, has no concept of workflow, lacks controls and governance, and has very limited audit trails.  It also wasn’t designed to manage large volumes of data and is two-dimensional in nature.

In corporate financial modeling, large volumes of historic data may need to be integrated, validated, and structured across multiple dimensions to fully support the requirement at hand.  Many Finance professionals have tried to handle this in Excel, but over time they find these models and multi-tabbed workbooks become difficult to maintain, and don’t perform well.  The alternative many organizations are turning to are purpose-built corporate planning and financial forecasting software applications, such as those that are found in modern corporate performance management (CPM) software platforms.

Financial Modeling in Action

One example of an organization that outgrew the capabilities of Excel for modeling and planning and migrated to a purpose-built corporate planning application is Fibrogen. FibroGen recently transformed from a drug development company to a global multi-channel commercial business. Their transition success depended on rapidly building out sales, channel development, and marketing as well as aligning the business and operational goals of their scientists, business leaders, and the Finance team.

Realizing these goals required a more sophisticated corporate performance management (CPM) solution than their Excel®-based planning models and a 20-year-old legacy budgeting system that was fully matured and accepted within the organization. Fibrogen found that OneStream’s unified and extensible CPM software platform answered the company’s vision to gracefully accommodate their requirements to enable activity-based planning across two unique entities.

FibroGen’s China entity required a top-down model for planning and financial modeling while the United States model depended on non-finance users who are VPs and Executive Directors of their departments to provide the input that is needed for program-level and consolidated plans.

Said Alex Lee, Senior Director, Corporate FP&A, “With impending growth and transition, we sought a solution that can support a program-driven planning process and complex calculations and modeling with the ability to expand to include consolidation, reporting, accounting close automation, SEC reporting, and tax provisioning. We had a very specific vision in mind. It has been 10 months since go-live, and I’m still profoundly touched by the magic that is OneStream.”

Learn More

To learn more, download the Fibrogen case study and contact OneStream if your organization is ready to take the leap from Excel to an intelligent finance platform designed to conquer business complexity and help you lead at speed!

Download the Case Study