Financial Modeling for BudgetingMany companies are increasingly using financial modeling to develop their budgets. Budgeting and financial models comprise a functional branch of a general corporate planning model. They are essentially used to generate pro forma financial statements and financial ratios. These are the basic tools for budgeting and profit planning. The financial model is a technique for risk analysis and “what-if” experiments. The model is also needed for day-to-day operational and tactical decisions for immediate planning problems. Spreadsheet software and computer-based financial modeling software are being widely utilized for budgeting and planning in an effort to speed up the budgeting process and allow budget planners and nonfinancial managers to investigate the effects of changes in budget assumptions and scenarios.

Advertisement

This post try to answer the following questions: What is a financial model? What are some typical uses of financial models? What are the types of financial modeling? How widespread is the use of financial modeling in practice? How do we go about building a financial model? Enjoy!

 

 

What is A Financial Model?

A financial model, narrowly called a budgeting model, is a system of mathematical equations, logic, and data that describes the relationships among financial and operating variables. A financial model can be viewed as a subset of broadly defined corporate planning models or a stand-alone functional system that attempts to answer a certain financial planning problem.

A financial model is one in which:

  • One or more financial variables appear (expenses, revenues, investment, cash flow, taxes, and earnings).
  • The model user can manipulate (set and alter) the value of one or more financial variables.
  • The purpose of the model is to influence strategic decisions by revealing to the decision maker the implications of alternative values of these financial variables.

Below Exhibit shows a flowchart of a simplified financial planning model.

Flowchart of a Simplified Financial Planning
Financial models fall into two types:

  • Simulation, better known as “what-if” models; and
  • optimization models.

What-if” models attempt to simulate the effects of alternative management policies and assumptions about the firm’s external environment. They are basically a tool for management’s laboratory.

Optimization models” are ones in which the goal is to maximize or minimize an objective such as present value of profit or cost. Experiments are being made on multi-objective techniques, such as goal programming.

Models can be deterministic or probabilistic. Deterministic models do not include any random or probabilistic variables, whereas probabilistic models incorporate random numbers and/or one or more probability distributions for variables such as sales and costs.

Financial models can be solved and manipulated computationally to derive from them the current and projected future implications and consequences. Due to technological advances in computers (i.e., spreadsheets, financial modeling languages, graphics, database management systems, and networking), more companies are using modeling.

 

Budgeting and Financial Modeling

Basically, a financial model is used to build a comprehensive budget (i.e., projected financial statements, such as the income statement, balance sheet, and cash flow statement). Such a model can be called a budgeting model, since we are essentially developing a master budget with it. Applications and uses of the model, however, go beyond developing a budget. They include:

  • Financial forecasting and analysis
  • Capital expenditure analysis
  • Tax planning
  • Exchange rate analysis
  • Analysis for mergers and acquisitions
  • Labor contract negotiations
  • Capacity planning
  • Cost-volume-profit analysis
  • New venture analysis
  • Lease/purchase evaluation
  • Appraisal of performance by segments
  • Market analysis
  • New product analysis
  • Development of long-term strategy
  • Planning financial requirements
  • Risk analysis
  • Cash flow analysis
  • Cost and price projections

 

Use of Financial Modeling in Practice

The use of financial modeling, especially a computer-based financial modeling system, is in wide use. The simple reason is the growing need for improved and quicker support as a management decision support system (DSS) and wide and easy availability of computer hardware and software.

Some of the functions currently served by financial models, as described by the users, are:

  • Projecting financial results under any given set of assumptions, evaluating the financial impact of various assumptions and alternative strategies, and preparing long-range forecasts
  • Computing income, cash flow, and ratios for five years by months, as well as energy sales, revenue, power generation requirements, operating and manufacturing expenses, manual or automatic financing, and rate structure analysis
  • Providing answers and insights into financial “what-if” questions and providing scheduling information, such as production planning
  • Forecasting the balance sheet and income statement with emphasis on alternatives for the investment securities portfolio
  • Projecting operating results and various financing needs, such as plant and property levels and financing requirements
  • Computing manufacturing profit, any desired processing sequence through the manufacturing facilities, and simulating effect on profits of inventory policies
  • Generating profitability reports of various responsibility centers
  • Projecting financial implications of capital investment programs
  • Showing the effect of various volume and activity levels on budget and cash flow
  • Forecasting corporate sales, costs, and income by division and by month
  • Providing sales revenue for budget, a basis for evaluating actual sales department performance, and other statistical comparisons
  • Determining pro forma cash flow for alternative development plans for real estate projects
  • Analyzing the impact of an acquisition on company earnings
  • Determining economic attractiveness of new ventures, such as products, facilities, and acquisitions
  • Evaluating alternatives of leasing or buying computer equipment
  • Determining corporate taxes as a function of changes in price
  • Evaluating investments in additional capacity at each major refinery
  • Generating income statements, cash flow, present value, and discounted rate of return for potential mining ventures, based on production and sales forecasts
  • Supported by the expanded capabilities provided by models, many companies are increasingly successful in including long-term strategic considerations in their business plans, thus enabling them to investigate the possible impact of current decisions on the long-term welfare of the organization.

 

Developing Financial Models

Development of financial models essentially involves definition of variables, input parameter values, and model specification. As far as model specification goes, we will concentrate only on the simulation-type model specification in this section.

Generally speaking, the model consists of three important ingredients:

  • Variables
  • Input parameter values
  • Definitional and/or functional relationships

 

Definition of Variables

Fundamental to the specification of a financial model is the definition of the variables to be included. Basically, the three types of variables are:

  • policy variables (Z);
  • external variables (X); and
  • performance variables (Y).

 

Policy variables (often called “control variables“) are those over which management can exert some degree of control. Examples of financial variables are: cash management, working capital, debt management, depreciation, tax, merger-acquisition decisions, the rate and direction of the firm’s capital investment programs, the extent of its equity and external debt financing and the financial leverage represented thereby, and the size of its cash balances and liquid asset position.

External variables are the environmental variables that are external to the company and that influence the firm’s decisions from outside. Generally speaking, the firm is embedded in an industry environment. This environment, in turn, is influenced by overall general business conditions. General business conditions exert influences on particular industries in several ways. Total volume of demand, product prices, labor costs, material costs, money rates, and general expectations are among the industry variables affected by the general business conditions.

Performance variables, which measure the firm’s economic and financial performance, usually are produced internally. We use the symbol Y. The Y’s often are called “output variables“. The output variables of a financial model would be the line items of the balance sheet, cash budget, income statement, or statement of cash flows. How the output variables of the firm are defined will depend on the goals and objectives of management. They basically indicate how management measures the performance of the organization or some segments of it. Management is likely to be concerned with the firm’s level of earnings, growth in earnings, projected earnings, growth in sales, and cash flow.

Frequently, when we attempt to set up a financial model, we face risk or uncertainty associated with particular projections. In a case such as this, we treat some of these variables, such as sales, as random variables with given probability distributions. The inclusion of random variables in the model transforms it from a deterministic model to a risk analysis model. However, the use of the risk analysis model in practice is rare because of the difficulty involved in modeling and computation.

 

Input Parameter Values

The model includes various input parameter values. For example, in order to generate the balance sheet, the model needs to input beginning balances of various asset, liability, and equity accounts. These input and parameter values are supplied by management. The ratio between accounts receivable and financial decision variables, such as the maximum desired debt-equity ratio, would be good examples of parameters.

 

Model Specification

Once we define various variables and input parameters for our financial model, we must then specify a set of mathematical and logical relationships linking the input variables to the performance variables. The relationships usually involve either: (1) definitional equations or (2) behavioral equations:

  • Definitional equations take the form of accounting identities.
  • Behavioral equations involve theories or hypotheses about the behavior of certain economic and financial events.

They must be tested and validated before they are incorporated into the financial model. Let’s discuss both equations further. Read on…

 

Definitional Equations

Definitional equations are exactly what the term implies—mathematical or accounting definitions. For example:

Assets = Liabilities + Equity

Net Income = Revenues – Expenses

These definitional equations are fundamental definitions in accounting for the balance sheet and income statement, respectively.

Another example is:

CASH = CASH(–1) + CC + OCR + DEBT – CD – LP

This equation is a typical cash equation in a financial model. It states that ending cash balance (CASH) is equal to the beginning cash balance (CASH(–1)) plus cash collections from customers (CC) plus other cash receipts (OCR) plus borrowings (DEBT) minus cash disbursements (CD) minus loan payments (LP).

Another example is:

INV = INV(–1) + MAT + DL + MO – CGS

 

This equation states that ending inventory (INV) is equal to the beginning inventory (INV(–1)) plus cost of materials used (MAT) plus cost of direct labor (DL) plus manufacturing overhead (MO) minus the cost of goods sold (CGS).

 

Behavioral Equations

Behavioral equations describe the behavior of the firm regarding the specific activities that are subject to testing and validation. The classical demand function in economics is:

Q = f (P) or more specifically Q = a – bP

It simply says that the quantity demanded is negatively related to the price. That is to say, the higher the price, the lower the demand. However, the firm’s sales are more realistically described in this way:

SALES = f (P, ADV., I, GNP, Pc, etc.)

or,

assuming linear relationship among these variables, we can specify the model as:

SALES = a + bP + cADV + dI + eGNP + fPc

which says that the sales are affected by such factors as price (P), advertising expenditures (ADV), consumer income (I), gross national product (GNP), and prices of competitive goods (Pc).

With the data on SALES, P, ADV, I, GNP, and Pc, we will be able to estimate parameter values a, b, c, d, e, and f, using linear regression. We can test the statistical significance of each of the parameter estimates and evaluate the overall explanatory power of the model, measured by the t-statistic and R-squared, respectively.

This way we will be able to identify the most influential factors that affect the sales of a particular product. With the best model chosen, management can simulate the effects on sales of alternative pricing and advertising strategies. We can also experiment with alternative assumptions regarding the external economic factors such as GNP, consumer income, and prices of competitive goods.

 

Model Structure

A majority of financial models that have been in use are: recursive and/or simultaneous models. Let’s discuss both models further. Read on…

 

Recursive Models

Recursive models are the ones in which each equation can be solved one at a time by substituting the solution values of the preceding equations into the right hand side of each equation.

An example of a recursive model is:

(1) SALES = A – B*PRICE + C*ADV
(2) REVENUE = SALES*PRICE
(3) CGS = 0.70*REVENUE
(4) GM = SALES – CGS
(5) OE = ~10,000 + 0.2*SALES
(6) EBT = GM – OE
(7) TAX = 0.46*EBT
(8) EAT = EBT – TAX

 

In this example, the selling price (PRICE) and advertising expenses (ADV) are given. A, B, and C are parameters to be estimated and;

  • SALES = sales volume in units
  • REVENUE = sales revenue
  • CGS = cost of goods sold
  • GM = gross margin
  • OE = operating expenses
  • EBT = earnings before taxes
  • TAX = income taxes
  • EAT = earnings after taxes

 

Simultaneous Models

Simultaneous models frequently are found in econometric models that require a higher level of computational methods, such as matrix inversion. An example of a simultaneous model is:

(1) INT = 0.10*DEBT
(2) EARN = REVENUE – CGS – OE – INT – TAX – DIV
(3) DEBT = DEBT(–1) + BOW
(4) CASH = CASH(–1) + CC + BOW + EARN – CD – LP
(5) BOW = MBAL – CASH
Notes:

  • Earnings (EARN) in equation (2) is defined as sales revenue minus CGS, OE, interest expense (INT), TAX, and dividend payment (DIV). But INT is a percentage interest rate on total debt in equation (1).
  • Total debt in equation (3) is equal to the previous period’s debt (DEBT(–1)) plus new borrowings (BOW).
  • New debt is the difference between a minimum cash balance (MBAL) minus cash.
  • Finally, the ending cash balance in equation (5) is defined as the sum of the beginning balance (CASH(–1)), cash collection, new borrowings and earnings minus cash disbursements and loan payments of the existing debt (LP).

Even though the model presented here is a simple variety, it is still simultaneous in nature, which requires the use of a method capable of solving simultaneous equations. Very few of the financial modeling languages have the capability to solve this kind of system.

 

Decision Rules

In addition to definitional equations and behavioral equations, the financial model may include basic decision rules specified in a very general form.

The decision rules are not written in the form of conventional equations. They are described algebraically using conditional operators, consisting of statements of the type: “IF… THEN… ELSE.”

For example: suppose that we wish to express this decision rule: “If X is greater than 0, then Y is set equal to X multiplied by 5. Otherwise, Y is set equal to 0”. Then we can express the rule in this way:

Y = IF X GT 0 THEN X*5 ELSE 0

Suppose the company wishes to develop a financing decision problem based on alternative sales scenarios. To determine an optimal financing alternative, managers might want to incorporate some decision rules into the model for a “what-if” or sensitivity analysis.

Some examples of these decision rules are:

  • The amount of dividends paid is determined on the basis of targeted earnings available to common stockholders and a maximum dividend payout ratio specified by management.
  • After calculating the external funds needed to meet changes in assets as a result of increased sales, dividends, and maturing debt, the amount of long-term debt to be floated is selected on the basis of a pre-specified leverage ratio.
  • The amount of equity financing to be raised is chosen on the basis of funds needed that are not financed by new long-term debt, but is constrained by the responsibility to meet minimum dividend payments.
  • In the model we have just described, simultaneity is quite evident. A sales figure is used to generate earnings, and this in turn leads to, among other items, the level of long-term debt required. Yet the level of debt affects the interest expense incurred within the current period and, therefore, earnings.
  • Furthermore, as earnings are affected, so are the price at which new shares are issued, the number of shares to be sold, and earnings per share. Earnings per share then feed back into the stock price calculation.

 

Lagged Model Structure

Lagged model structure is common in financial modeling. Virtually all balance sheet equations or identities are of this type. For example:

Capital = capital(–1) + net income + contributions – cash dividends

More interestingly;

CC = a*SALES + b*SALES(–1) + C*SALES(–2)

Where:

CC = cash collections from customers
a = percent received in the month of sale
b = percent received in the month of following sale
c = percent received in the second month following sale

This indicates that the realization of cash lags behind credit sales.