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 financial
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 (profit 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 infinity, 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 infinitely extending low and high values could
lead to a foot size that is miniscule or one that fills 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 first argument. In our financial 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 (Profit)
=
Earnings before Income Tax – Income Tax
 
Search JabSto ::




Custom Search