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
Units Unit Price
Sales Revenue
Sales Revenue
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
Cost of Goods Sold Expense
Normal Distribution—Mean–30 ; Std Dev–5
Gross Margin
Sales Revenue-COGS
Sales Revenue Percentage
Variable Operating Expense
Continuous Uniform Distribution—10%–20%
Contribution Margin
Gross Margin – VOE
Fixed Expenses (FE)
A constant value of $6000
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% >
Net Income (Profit)
Earnings before Income Tax – Income Tax
Search JabSto ::

Custom Search