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