Microsoft Office Tutorials and References

In Depth Information

8.4 A Financial Example—Income Statement

Not all simulations are related to discrete events. Let us consider a simple ﬁnancial

example that simulates the behavior of an income statement. I emphasize simple,

because income statements can be quite complex. Our purpose in this exercise is to

demonstrate the variety of simulation that is possible with the MCS method.

Table 8.1 shows the components of a typical income (proﬁt or loss) statement and

commentary on the assumptions that will be used to model each component. I have

selected a variety of distributions to use in this example, including the
Normal dis-

tribution
, often referred to as the
Bell Curve
due to its shape. It is a very commonly

used and convenient distribution that has a remarkable range of applications. It is

distinguished by its symmetry, a central tendency (“peaked-ness”), and probabilities

for lower and higher values that extend to inﬁnity, but with very, very low proba-

bility of occurrence. The symmetry and central tendency of the Normal probability

distribution (density function) is particularly useful to modelers. Such observable

variables as individual’s weight, shoe size, and many others, are often modeled with

Normal distributions, although the inﬁnitely extending low and high values could

lead to a foot size that is miniscule or one that ﬁlls a soccer stadium. Fortunately,

these extreme values have very, very low probability of occurrence.

A Normal distribution is described by two parameters—mean and standard devi-

ation (or variance). The formula for generating a value from a Normal distribution

in Excel is
NORMINV
(RAND(), mean, standard deviation). Note that the function

uses the familiar RAND() function as its ﬁrst argument. In our ﬁnancial example,

Table 8.1

Income statement example data

Component

Assumptions

Units
∗
Unit Price

=

Sales Revenue

Sales Revenue

Distributions

Discrete Units: 30%-75,000 units ; 70%–10,000 units

Discrete Unit Price: 25%–$1.50 ; 50%–$2.00; 25%–$2.50

Percentage
∗
Sales Revenue

Distribution

Percentage

Cost of Goods Sold Expense

(COGS)

COGS

=

=

Normal Distribution—Mean–30 ; Std Dev–5

Gross Margin

=

Sales Revenue-COGS

Sales Revenue
∗
Percentage

Distribution

Percentage

Variable Operating Expense

(VOE)

VOE

=

=

Continuous Uniform Distribution—10%–20%

Contribution Margin

=

Gross Margin – VOE

Fixed Expenses (FE)

A constant value of $6000

Operating

Earnings (EBIT)

=

Contribution Margin – Fixed Expenses

Interest Expense (IE)

A constant value of $3000

Earnings before

Income Tax

=

EBIT-Interest Expense

Income Tax expense

Conditional Percentage of [EBIT-Interest Expense]

35% < $20,000; 55% >

=

$20,000

Net Income (Proﬁt)

=

Earnings before Income Tax – Income Tax

Search JabSto ::

Custom Search