Microsoft Office Tutorials and References
In Depth Information
Exhibit 8.14 Risk proﬁle for proﬁt/loss statement
of \$10,680.20 and a very substantial standard deviation of \$15,192.08. The coefﬁ-
cient of variation for the 500 observations is greater than one (15,192.08/10,680.20).
Thus, our model shows a very wide range of possible values with a max of
\$51,957.41 and a min of –\$2,812.43. Also, note that 71 (cell J534) of 500 obser-
vations are losses, or 14.2% of the experimental total outcomes. This is valuable
information, but the risk associated with this model is even more clearly represented
by the risk proﬁle in Exhibit 8.14. In this exhibit we see clearly a picture of the pos-
sible outcomes of the model. This is a classic risk proﬁle, in that it presents the range
of possible monetary outcomes and their relative frequency of occurrence.
Consider the calculations associated with row 33 of the worksheet in Exhibit
8.13, the ﬁrst observation. The number of units produced is 75,000 and the price
per unit is \$1.50, both resulting from VLOOKUPs involving a random sampling
of Discrete distributions. Cost of Goods Sold (COGS), \$25,601.12, is a percentage
of revenue determined by a Normal distribution outcome value divided by 100. The
variable operating expense (VOE) is determined by sampling a Uniform distribution
(\$21,084.12). Finally, the ﬁxed and interest expense (FE and IE) are constant values
of \$6,000 and \$3,000, respectively. The calculation of net income is the summation
of all expenses subtracted from revenue and results in a proﬁt of \$25,566.64.
Some of the results of the model are a bit unexpected. For example, the risk pro-
ﬁle suggests a bi-modal distribution, one that is related to low or negative proﬁts and
has a relatively tight dispersion. The second mode is associated with higher proﬁts
and is more widely dispersed. This phenomenon is probably due to the distribu-
tion of demand, 70% probability of 10,000 units of demand and 30% of 75,000. In
fact, the observations associated with the lower mode represent about 71% of the
observations ([71+224+59]/500
=
70.8%).
Search JabSto ::

Custom Search