Microsoft Office Tutorials and References

In Depth Information

one, Obs. 7, results in a loss (–$1,348.50), and the others result in a proﬁt. The

advantages of this structure are: (1) there are many more rows visible on a work-

sheet than columns, and (2) since there are many more rows on a worksheet than

columns, many more observations are possible. In this model, I will generate 500

observations of the Proﬁt or Loss statement.

The calculations for this model are relatively straightforward. The calculations

for
Units
and
Price
, which determine
Revenue
, are VLOOKUPs that sample from

the
Sales Revenue
(cell A2) section of the
Brain
and are shown in Exhibit 8.12. The

calculation of the
COGS
uses the NORMINV function discussed above, to deter-

mine a randomly sampled percentage. Variable Operating Expense,
VOE
,usesthe

values for lower and upper limits (10% and 20%) through a linear transformation to

return continuous uniformly distributed values:

(
upper limit-lower limit)
∗
RAND()

lower limit

+

The formula leads to continuous values between the lower and upper limit, since

RAND() takes on values from 0 to 1. For example, if RAND( ) is equal to the

extreme upper value 1, then the value of the expression is simply the
upperlimit
.

Conversely, if RAND() is equal to the extreme lower value 0, then the expression is

equal to the
lower limit
.

In Exhibit 8.13 we see summary statistics (cell range I530:J535) for the simula-

tion of 500 observations. The average proﬁt or loss is positive, leading to a proﬁt

Exhibit 8.13
Summary statistics for ﬁnancial model

Search JabSto ::

Custom Search