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()
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