Financial modeling helps visualize alternate scenarios

News

Financial modeling helps visualize alternate scenarios

Barry Wilderman

Whether it's done in specialized software or cobbled-together spreadsheets, financial modeling usually employs these basic techniques.

BusinessDictionary.com defines a financial model as "a mathematical representation of key financial and operational relationships … used in analyzing how a business will react to different economic situations or events, and in estimating the outcome of financial decisions before committing any funds."

Indeed, the ability to model financial structures, try alternatives and perform what-if and sensitivity analyses has clear value.

Varieties of financial modeling

Some financial modeling is based on financial statements, such as the profit & loss (P&L) statement, balance sheet, source and application of funds, and cash flow. To illustrate, here is a simple P&L statement that also qualifies as a financial model:

Revenues

-  Cost of goods sold (COGS)
= Gross margin
-  Selling, general and administrative expenses (SG&A)
= Net profit

Even with this simple representation, a corporation can begin to perform some important analyses. Following are two examples.

COGS is an important component of net profit. Manufacturing companies typically have numerous suppliers providing raw materials to their factories. Is the company managing these suppliers well? Is it taking all the

Continue Reading This Article

Enjoy this article as well as all of our content, including E-Guides, news, tips and more.

discounts that are available? Should other suppliers be considered?

This process is called supplier relationship management (SRM) and is supported by well-known vendors such as Ariba, which SAP bought in 2012. Extending the COGS component of the P&L to include SRM can allow a CFO and vice president of manufacturing to simulate changes in the mix of suppliers.

And what about the selling component of SG&A? With the right modeling software, questions such as whether the company can recruit and hire salespeople fast enough to meet revenue targets can be posed and analyzed. Continually evaluating the chain from job interview to productive selling can be quite instructive, and human resources (HR) systems can provide the required data for the financial modeling that's needed.

As these examples illustrate, the main goal of financial modeling is to improve the profitability of the corporation.

Optimization software provides another kind of financial modeling. It helps determine what is best for the corporation given a set of variables and constraints.

Consider the SRM example above. Suppose the corporation wants to take a more scientific approach to choosing the mix of suppliers. The relevant data on each supplier includes availability, quality, reliability and shipping costs. The company wants to minimize the cost of raw materials while ensuring that these materials can be delivered to factories on a timely basis.

From an algorithmic point of view, specialized mathematical techniques such as linear programming and integer programming can be applied here to create an optimal financial solution. By adjusting any of the supplier variables, managers can determine how a change in one might affect the optimal solution. These solutions can then be applied to the corporate strategy. Vendors such as River Logic provide comprehensive products in this space.

The next two types of financial modeling deal with forecasting future results. In the P&L above, the company could derive useful insights from the current value of COGS. Accurately forecasting its future value allows the CFO to plan SRM strategies as well.

Driver-based planning, which I described in detail in a prior article, is another common type of financial modeling.

Suppose we are preparing a budget for 2015. The old-fashioned way to budget revenue would be for the CFO to estimate revenue for each month of the year.

A more scientific approach might be to use drivers such as the following:

Revenue = Total market x available market share, where

Total market = Estimated global size of our market

Available market share = Percent of total market we can achieve

In the above model, "Total market" and "Available market share" are the drivers of "Revenue."

During the budget cycle, changes to "Revenue" are instead made at the driver level, not directly to "Revenue" itself. By disaggregating revenue, a better revenue estimate can be achieved.

But clearly, achieving the revenue number is more complicated than what goes on in the drivers the company has identified. To earn the revenue, the corporation must have superior sales and manufacturing, which leads to identifying further drivers. Numerous vendors of enterprise performance management (EPM) software provide driver-based planning.

Predictive modeling techniques, in contrast, help to forecast future behavior.

For example, mathematical algorithms can be used to "score" the buying potential of each existing customer and come up with a predictor of each one's behavior in future years. This is the micro approach to predictive modeling.

A second technique is multiple regression, where a variable (say, revenue) is a function of a number of dependent variables (say, sales maturity, sales attrition and manufacturing reliability). Regression software creates an equation that can then be used to predict revenue.

Vendors in this space include SAS and SAP's KXEN division.

Financial modeling in plain English

Anyone who has done any serious financial modeling in Microsoft Excel knows the perils of complex formulas. They are hard to read later, and most of the spreadsheets are riddled with errors. Auditing is nearly impossible.

Using an earlier example, wouldn't it be nice to simply write

Gross margin = Revenue - COGS

and have that be the formula? A class of financial modeling applications from vendors such as Quantrix and Whitebirch allows such English-like equations that can span the different subsets of a model. Financial models tend to be complex, so strong consideration must be given to anything that offers ease of use and auditability.

Finally, there are some key considerations to keep in mind when choosing financial modeling software.

Selecting and implementing the software must be approached as a collaborative project between the CFO and the CIO. Existing systems must be integrated. They include Excel, ERP, accounting software, EPM software and major data sources.

Most of all, the organization must identify quantifiable benefits from adopting financial modeling tools before buying them.

About the author:


Barry Wilderman has more than 30 years of experience as an industry analyst, researcher and consultant at such companies as Meta Group, Lawson Software, SalesOps Analytics and McKinsey and Co. He is currently president of Wilderman Associates. Contact him at Barry@WildermanAssociates.com and on Twitter @BarryWilderman.