Microsoft Office Tutorials and References
In Depth Information
Exhibit 8.14 Risk profile for profit/loss statement
of $10,680.20 and a very substantial standard deviation of $15,192.08. The coeffi-
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 profile 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 profile, 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 first 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 fixed 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 profit of $25,566.64.
Some of the results of the model are a bit unexpected. For example, the risk pro-
file suggests a bi-modal distribution, one that is related to low or negative profits and
has a relatively tight dispersion. The second mode is associated with higher profits
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