Budgeting
Financial Modeling for Budgeting
Many 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 “whatif” experiments. The model is also needed for daytoday operational and tactical decisions for immediate planning problems. Spreadsheet software and computerbased 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.
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!
Advertisement
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 standalone 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.
Financial models fall into two types:
 Simulation, better known as “whatif” models; and
 optimization models.
“Whatif” 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 multiobjective 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
 Costvolumeprofit analysis
 New venture analysis
 Lease/purchase evaluation
 Appraisal of performance by segments
 Market analysis
 New product analysis
 Development of longterm 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 computerbased 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 longrange 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 “whatif” 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 longterm strategic considerations in their business plans, thus enabling them to investigate the possible impact of current decisions on the longterm 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 simulationtype 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, mergeracquisition 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 debtequity 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 tstatistic and Rsquared, 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 “whatif” 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 longterm debt to be floated is selected on the basis of a prespecified leverage ratio.
 The amount of equity financing to be raised is chosen on the basis of funds needed that are not financed by new longterm 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 longterm 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.

Accounting9 years ago
Check Payment Issues Letter [Email] Templates

Accounting9 years ago
How To Calculate And Record Depreciation [of Fixed Asset]

Accounting9 years ago
What is Journal Entry For Foreign Currency Transactions

Accounting5 years ago
Accounting for Business Acquisition Using Purchase Method
Anil Valdria
Jun 20, 2011 at 6:19 am
Do you have any recommendation for a good reference book on Financial Modeling?