Transcript Review of Probability and Statistics

Probability Essentials

Concept of probability is quite intuitive; however, the
rules of probability are not always intuitive or easy to
master.

Mathematically, a probability is a number between 0
and 1 that measures the likelihood that some event
will occur.
– An event with probability zero cannot occur.
– An event with probability 1 is certain to occur.
– An event with probability greater than 0 and less than 1
involves uncertainty, but the closer its probability is to 1 the
more likely it is to occur.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Rule of Complement

The simplest probability rule involves the
complement of an event.

If A is any event, then the complement of A, denoted
by Ac, is the event that A does not occur.

If the probability of A is P(A), then the probability of
its complement, P(Ac), is P(Ac)=1- P(A).

Equivalently, the probability of an event and the
probability of its complement sum to 1.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Addition Rule

We say that events are mutually exclusive if at most
one of them can occur. That is, if one of them occurs,
then none of the others can occur.

Events can also be exhaustive, which means that
they exhaust all possibilities - one of these three
events must occur.

Let A1 through An be any n events. Then the addition
rule of probability involves the probability that at least
one of these events will occur.
P(at least one of A1 through An) = P(A1) + P(A2) +  + P(An)
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conditional Probability

Probabilities are always assessed relative to the
information currently available. As new information
becomes available, probabilities often change.

A formal way to revise probabilities on the basis of
new information is to use conditional probabilities.

Let A and B be any events with probabilities P(A) and
P(B). Typically the probability P(A) is assessed
without knowledge of whether B does or does not
occur. However if we are told B has occurred, the
probability of A might change.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conditional Probability -continued

The new probability of A is called the conditional
probability of A given B. It is denoted P(A|B).
– Note that there is uncertainty involving the event to the left of
the vertical bar in this notation; we do not know whether it
will occur or not. However, there is no uncertainty involving
the event to the right of the vertical bar; we know that it has
occurred.

The following formula conditional probability
formula enables us to calculate P(A|B):
P( A and B)
P( A | B) 
P( B)
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Multiplication Rule

In the conditional probability rule the numerator is the
probability that both A and B occur. It must be known
in order to determine P(A|B).

However, in some applications P(A|B) and P(B) are
known; in these cases we can multiply both side of
the conditional probability formula by P(B) to obtain
the multiplication rule.
P(A and B) = P(A|B)P(B)

The conditional probability formula and the
multiplication rule are both valid; in fact, they are
equivalent.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assessing the Bendrix Situation

Now that we are familiar with the a number of
probability rules we can put them to work in
assessing the Bendrix situation.

To begin we will let A be the event that Bendrix meets
its end-of-July deadline, and let B be the event that
Bendrix receives the materials form its supplier by the
middle of July.

The probabilities that we are best able to be assess
on July 1 are probably P(B) and P(A|B).
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assessing -- continued

They estimate a 2 in 3 chance of getting the materials
on time; thus P(B)=2/3.

They also estimate that if they receive the materials
on time then the chances of meeting the deadline are
3 out of 4. This is a conditional probability statement
that P(A|B)=3/4.

We can use the multiplication rule to obtain:
P(A and B) = P(A|B)P(B) = (3/4)(2/3) = 0.5

There is a 50-50 chance that Bendrix will gets its
materials on time and meet its deadline.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assessing -- continued

Other probabilities of interest exist in this example.

Let Bc be the complement of B; it is the event that the
materials from the supplier do not arrive on time. We
know that P(B) = 1 - P(Bc) = 1/3 from the rule of
complements.

Bendrix estimates that the chances of meeting the
deadline are 1 out of 5 if the materials do not arrive
on time, that is, P(A| Bc) = 1/5. The multiplication rule
gives
P(A and Bc) = P(A| Bc)P(Bc) = (1/5)(1/3) = 0.0667
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assessing -- continued

In words, there is a 1 chance out of 15 that the
materials will not arrive on time and Bendrix will
meets its deadline.

The bottom line for Bendrix is whether it will meet its
end-of-July deadline. After the middle of July the
probability is either 3/4 or 1/5 because by this time
they will know whether the materials have arrived on
time.

But since it is July 1 the probability is P(A) - there is
still uncertainty about whether B or Bc will occur.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assessing -- continued

We can calculate P(A) from the probabilities we
already know. Using the additive rule for mutually
exclusive events we obtain
P(A) =P(A and B) + P(A and Bc) = (1/2)+(1/15) = 0.5667

In words, the chances are 17 out of 30 that Bendrix
will meet its end-of-July deadline, given the
information it has at the beginning of July.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Probabilistic Independence

A concept that is closely tied to conditional probability
is probabilistic independence.

There are situations unlike Bendix when P(A), P(A|B)
and P(A| Bc) are not all different. They are situations
where these probabilities are all equal. In this case
we can say that events A and B are independent.

This does not mean they are mutually exclusive; it
means that knowledge of one of the events is of no
value when assessing the probability of the other
event.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Probabilistic Independence -continued

The main advantage of knowing that two events are
independent is that the multiplication rule simplifies to
P(A and B) = P(A)P(B)

In order to determine if events are probabilistically
independent we usually cannot use mathematical
arguments; we must use empirical data to decide
whether independence is reasonable.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Distribution of a Single
Random Variable
Background Information

An investor is concerned with the market return for
the coming year, where the market return is defined
as the percentage gain (or loss, if negative) over the
year.

The investor believes there are five possible
scenarios for the national economy in the coming
year: rapid expansion, moderate expansion, no
growth, moderate contraction, or serious contraction.

She estimates that the market returns for these
scenarios are, respectively, 0.23, 0.18, 0.15, 0.09,
and 0.03.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Background Information -continued

Also, she has assessed that the probabilities of these
outcomes are 0.12, 0.40, 0.25, 0.15, and 0.08.

We must use this information to describe the
probability distribution of the market return.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Type of Random Variables

A discrete random variable has only a finite number
of possible values.

A continuous random variable has a continuum of
possible values.

Mathematically, there is an important difference
between discrete and continuos random variables. A
proper treatment of continuos variables requires
calculus. In this book we will only be dealing with
discrete random variables.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Discrete Random Variables

The properties of discrete random variables and their
associated probability distributions are as follows:

Let X be a random variable and to specify the
probability distribution of X we need to specify its
possible values and their probabilities. This list of
their probabilities sum to 1.

It is sometimes useful to calculate cumulative
probabilities. A cumulative probability is the
probability that the random variable is less than or
equal to some particular values.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Summarizing a Probability
Distribution

A probability distribution can be summarized with two
or three well-chosen numbers:
– The mean, often called the expected value, is a weighted
sum of the possibilities. It indicates the center of the
probability distribution.
– To measure the variability in a distribution, we calculate its
variance or standard deviation. The variance is a weighted
sum of the squared deviations of the possible values from
the mean. As in the previous chapter the variance is
represented in the squared units of X so a more natural
measure of variability is the standard deviation.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
MRETURN.XLS

This file contains the values and probabilities
estimated by the investor in this example.

Mean, Probs, Returns, Var and Sqdevs have been
specified as range names.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Summary Measures

The summary measures for the probability
distribution of the outcomes can be calculated as
follows:
– Mean return: =SUMPRODUCT(Returns,Probs)
– Squared Deviations: =(C4-Mean)^2
– Variance: =SUMPRODUCT(SqDevs,Probs)
– Standard Deviation: =SQRT(Var)

We see that the mean return is 15.3% and the
standard deviation is 5.3%. What do these mean?
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Analyzing the Summary
Measures

First, the mean or expected return does not imply that
the most likely return is 15.3%, nor is this the value
that the investor “expects” to occur. The value 15.3%
is not even a possible market return.

We can understand these measures better in terms
of long-run averages.

If we can see the coming year repeated many times,
using the same probability distribution, then the
average of these times would be close to 15.3% and
their standard deviation would be 5.3%.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Derived Probability Distributions
Background Information

A bookstore is planning on ordering a shipment of
special edition Christmas calendars that they will sell
for $15 a piece.

There will be only one order, so
– if demand is less than the quantity ordered the excess
calendars will be donated to a paper recycling company
– if demand is greater than the quantity ordered, the excess
demand will be lost and customers will take their business
elsewhere

The bookstore estimates that the demand for
calendars will be between 250 and 400.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
DERIVED.XLS

This file contains the probability distribution that the
demand for calendars will follow. These estimates
have been derived from subjective estimates and
historical data.

If the bookstore decides to order 350 calendars, what
is the probability distribution of units sold? What is
the probability distribution of revenue?
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Derived Distributions of Units
Sold and Revenue
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution

Let D, S,and R denote demand, units sold, and
revenue.

The key to the solution is that each value of D directly
determines the value of S, which in turn determines
the value of R.

S is the smaller of D and the number ordered, 350,
and R is $15 multiplied by the value of S.

Therefore we can derive the probability distributions
of S and R with the following steps:
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution -- continued
– Calculate Units sold : =MIN(B10,OnHand)
– Calculate Revenue for each value of units sold:
=UnitPrice*B20
– Transfer the Derived Probabilities for demand: =C10
– Calculate Means of demand, units sold, and revenue:
=SUMPRODUCT(Revenues, DerivedProbs)
– Calculate the Variances and Standard Deviations of
demand, units sold and revenue.
• First, calculate the squared deviations of revenues from their
mean in Column F, then calculate the sum of the products of
these squared deviations and the revenue probabilities to
obtain the variance of revenue. Finally, calculate the standard
deviation as the square root of the variance.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Summary Measures for Linear
Functions

When one random variable is a linear function of
another random variable X, there is a particularly
simple way to calculate the summary measures of Y
from the Summary measures of X.
Y = a + bX
for some constant a and b then:
» mean: E(Y) = a + bE(X)
» variance: Var(Y) = b2 Var(X)
» standard deviation: bStdev(X)

If Y is a constant multiple of X, that is a=0 then the
mean and standard deviation of Y are this same
multiple of the mean and standard deviation of X.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Distribution of Two Random Variables:
Scenario Approach
Background Information

An investor plans to invest in General Motors (GM)
stock and gold.

He assumes that the returns on these investments
over the next year depend on the general state of the
economy during the year.

He identifies four possible states of the economy:
depression, recession, normal and boom. These four
states have the following probabilities: 0.05, 0.30,
0.50, and 0.15.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Background Information -continued

The investor wants to analyze the joint distribution of
returns on these two investments.

He also wants to analyze the distribution of a portfolio
of investments in GM stock and gold.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
GMGOLD.XLS

This file contains the probabilities and estimated
returns of the GM stock and the gold.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Relating Two Random Variables

There are two methods for relating two random
variables, the scenario approach and the joint
probability approach.

The methods differ slightly in the way they assign
probabilities to different outcomes.

Two summary measures, covariance and
correlation, are used to measure the relationship
between two variables in both methods.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
The Summary Measures

We have discussed summary measures with the
same names, covariance and correlation, earlier. The
summary measures we are looking at now go by the
same name but are conceptually different.

In the past we have calculated them from data; here
they are calculated from a probability distribution.

The random variables are X and Y and the probability
that X and Y equal xi and yi is p(xi, yi) is called a joint
probability.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Summary Measures -- continued

Although they are calculated differently , the
interpretation is essentially the same as we
previously discussed.

Each indicates the strength of a linear relationship
between X and Y. If X and Y vary in the same
direction then both measures are positive. If they vary
in opposite directions then both measures are
negative.

Covariance is more difficult to interpret because it
depends on the units of measurement of X and Y.
Correlation is always between -1 and +1.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
The Scenario Approach

The essence of the scenario approach in this
example is that a given state of the economy
determines both GM and gold returns, so that only
four pairs of returns are possible.

These pairs are -0.20 and 0.05, 0.10 and 0.20, 0.30
and -0.12, and 0.50 and 0.09. Each pair has a joint
probability.

To calculate means, variances and standard
deviations, we treat GM and gold returns separately.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Covariance and
Correlation

We also need to calculate the covariance and
correlation between the variables. To obtain these we
use the following steps:
1 Deviations between means: To calculate the covariance we
need the sum of deviations from means, so we need to
calculate these deviations with the formula =C4-GMMean in
B14 and copy it down through B17. We also calculate this for
gold.
2 Covariance: Calculate the covariance between GM and gold
returns in cell B23 with the formula
=SUMPRODUCT(GMDevs,GoldDevs,Probs)
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Covariance and
Correlation -- continued
3 Correlation: Calculate the correlation between GM and gold
returns in cell B24 with the formula
=Covar/(GMStdv*GoldStedev)

The negative covariance indicates that GM and gold
returns tend to vary in opposite directions, although it
is difficult to judge the strength by the magnitude of
the covariance.

The correlation of -0.410 is also negative and
indicates a moderately strong relationship. We
cannot infer too much from this correlation though
because the variables are not linear.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Simulation

A simulation of GM and gold returns help explain the
covariance and correlation.

There are two keys to this simulation:

First we must, simulate the states of the economy,
not - at least not directly - the GM and gold returns.
– We simulate this be entering a RAND function in A1 and
then by entering the formulas VLOOKUP(A21,LTable,2) in
B21 and VLOOKUP(A21,LTable,3) in C21.
– This way uses the same random number, hence the same
scenario, to generate both returns in a given row, and the
effect is that only four pairs of returns are possible.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Simulation -- continued

Second, once we have the simulated returns we can
calculate the covariance and correlation of these
numbers.
– We calculate these in cells B8 and B9 with the formulas
COVAR(SimGM,SimGOLD) and
CORREL(SimGM,SimGold). These are built-in Excel
functions.
– A comparison of these summary measures with the
previously calculated summary measures shows that there is
reasonably good agreement between the covariance and
correlation of the probability distribution and the measures
based on the simulated values. The agreement is not perfect
but will improve as more pairs are simulated.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Simulation of GM and Gold
Returns
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Portfolio Analysis

The final part of this example is to analyze a portfolio
consisting of GM stock and gold.

We assume that the investor has $10,000 and puts
some fraction of this in GM stock and the rest in gold.

The key to the analysis is that there are only four
possible scenarios -- that is, there are only four
possible portfolio returns.

In this case we calculate the entire portfolio return
distribution and summary measures in the usual way.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Portfolio Analysis -- continued

One thing of interest is to see how the expected
portfolio return and standard deviation of portfolio
return change as the amount the investor puts into
GM stock changes.
– To do this we use a data table or mean and stdev of portfolio
return as a function of GM investment.

A graph of these measures show that the expected
portfolio return steadily increases as more and more
is put into GM.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Portfolio Analysis -- continued

However, we must note that the standard deviation,
often used as a measure of risk, first decreases, then
increases.

This means there is trade-off between expected
return and risk (as measured by the standard
deviation).

The investor could obtain a higher expected return by
putting more of his money into GM; but past a
fraction of approximately 0.4, the risk also increases.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Distribution of Portfolio Return
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Distribution of Two Random
Variables: Joint Probability
Approach
SUBS.XLS

A company sells two products, product 1 and 2, that
tend to be substitutes for each one another.The
company has assessed the joint probability
distribution of demand for the two products during the
coming months.

This joint distribution appears in the Demand sheet of
this file.

The left and top margins of
the table show the possible
values of demand for the
products.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
SUBS.XLS -- continued

Demand for product 1 (D1) can range from 100 to 400
(in increments of 100) and demand for product 2(D2)
can range from 50-250 (in increments of 50).

Each possible value of D1 can occur for each
possible value of D2 with the joint probability given in
the table.

Given this joint probability distribution, describe more
fully the probabilistic structure of demands for the two
products.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Joint Probability Approach

In this example we use an alternative method for
specifying probability distribution.

A joint probability distribution, specified by all
probabilities of the form p(x, y), indicates that X and Y
are related and also how each of X and Y is
distributed in its own right.

The joint probability of X and Y determines the
marginal distributions of both X and Y, where each
marginal distribution is the probability distribution of a
single random variable.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Joint Probability Approach -continued

The joint distribution also determines the conditional
distributions of X given Y, and of Y given X.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Marginal Distributions

We begin by finding the marginal distributions of
demands for each product.

These are the row and column sums of the joint
probabilities.

The marginal distributions indicate that “in-between”
values of the demands for each product are most
likely, whereas extreme values in either direction are
less likely.

These distributions tell us nothing of the relationship
between the demands for the products.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conditional Distributions

A better way to do learn about this relationship is to
calculate the conditional distributions of the demands.

We begin with with the conditional distribution for D1
given D2.

To calculate we create a new table. In each row of
the table we fix the value of D2 at the value given in
column B. We can then calculate the conditional
probabilities of the values of D1.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conditional Distributions -continued

This is the joint probability divided by the marginal
probability of the D2. They can be calculated all at
once by entering the formula =C5/$G5.

We also check that each row of the table is a
probability in its own right by summing across the
rows. These sums should equal one.

Similarly the conditional distributions of the D2 given
the D1 can be calculated in another table by entering
the formula =C5/C$10. Each column sum should
equal one.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Summary Measures

Various summary measures can now be calculated.
– Expected values: The expected demands follow from the
marginal distributions and are calculated in cells B32 and
C32 by these formulas =SUMPRODUCT(Demands1,Prob1)
and =SUMPRODUCT(Demands2,Prob2).
– Variances and standard deviations:These are also
calculated from the marginal distributions in the usual way.
We first find squared deviations from the means and
calculate the weighted sum of these squared deviations.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Summary Measures -- continued
– Covariance and correlation: The formulas are the same as
before but we proceed differently.
• We now form a complete table of products of deviations from
the means by using the formula =(C$4-MeanDem1)*($B5MeanDem2) in C37 and copying it to C37:F41.
• Then we calculate the covariance in cell B47 with the formula
=SUMPRODUCT(ProdDevsDem,JtProbs).
• Finally, we calculate the correlation in B48 with the formula
=CovarDem/(StdevDem1*StDevDem2).
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Analysis

The best way to see the joint behavior of D2 and D1 is
to look in the conditional probability tables.

For example: Compare the probabilities in the
conditional distribution table of D1, given D2. The
value of D2 increases, while the probabilities for D1
tend to shift to the left. In other words, as the demand
for product two increases the demand for product 1
tends to decrease.

This can be seen more clearly in the following graph.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conditional Distributions of
Demand 1 Given Demand 2
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Analysis -- continued

The graph shows that when D2 is large D1 tends to be
small, although again this is a tendency not a perfect
relationship.

When we say that two products are substitutes for
one another, this is the type of behavior we imply.

By symmetry, the conditional distribution of D2 given
D1 shows the same type of behavior.

This is shown in the next graph.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conditional Distributions of
Demand 2 Given Demand 1
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conclusions

The information in these graphs is confirmed - to
some extent - by the covariance and correlation
between the demands for the products.

In particular, their negative values indicate that the
demands for the products move in opposite
directions.

Also the small correlation indicates that the
relationship between these demands is far from
perfect. There is still a reasonably good chance that
when D1 is large D2 will be large, and when D1 is
small D2 will be small.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assessing Joint Probability
Distributions

Using the joint probability approach can often times
be quite difficult especially when there are many
possible values for each of the random variables

One approach is to proceed backwards from the way
we proceeded in this example.

Instead of specifying the joint probabilities and then
deriving the marginal and conditional distributions, we
can specify either the marginal or conditional
probabilities and use theses to calculate the joint
possibilities.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assessing Joint Probability
Distributions -- continued

The advantage of this procedure is that it is probably
easier and more intuitive for a business manager.

He gets ore control over the relationship between the
two random variables, as determined by the
conditional probabilities he assesses.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
JTPROBS.XLS

An file shows an example of the indirect method of
assessing joint probabilities.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
JTPROBS.XLS

The shaded regions of the spreadsheet represent
probabilities assessed directly, and the joint
probabilities are calculated from these.

The formula used in C20 is =C11*C$6 and then this
is copied to C20:F24.

The associated graph on the next slide appears to
be consistent with the meaning of substitute
products.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Conditional Distributions of
Demand 2 Given Demand 1
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Independent Random Variables
Background Information

A distributor of parts keeps track of the inventory of
each part type at the end of every week.

If the inventory of a given part is at or below a certain
value called the reorder point, the distributor places
an order for the part.

The amount ordered is a constant called the order
quantity.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Assumptions

The ordering lead time is negligible.

Sales are lost if customer demand during any week is
greater than that week’s beginning inventory; that is,
there is no backlogging of demand.

Customer demands for a given part type in different
weeks are independent random variables.

The marginal distribution of weekly demand for a
given part type is the same each week.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
INVNTORY.XLS

This file contains the plant manager’s estimated data
in the shaded area for a particular part type.

She wants to calculate the mean revenue in each of
the first weeks, given that the initial inventory at the
beginning of week 1 is 250, the value shown in cell
B12.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Independent & Dependent

When random variables are independent, any
information about the values of any of the random
variables is worthless in predicting any of the others.

Random variables in real world applications are not
usually independent; they are usually related in some
way, in which case they are dependent.

However, we often make an assumption of
independence in mathematical models to simplify the
analysis.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Joint Distribution of Demands

Due to the assumption that weekly demands are
independent and have the same distribution, all the
manager needs to assess is a single weekly
distribution of demand.

To obtain these joint probabilities, we calculate
products of marginals by entering the formula
=VLOOKUP(C$20,DistTable,2)*VLOOKUP($B21,DistTable,2)
in cell C21 and copying it into range C21:G25.

This formula simply multiplies the two marginal
probabilities corresponding to the demands in the
top and left margins of the joint probability table.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Joint Distribution of Demand -continued

To check we calculate the row and column sums. The
column sums agree with the probabilities in the
distribution of demand in each week, as they should.

The calculations are shown on the next slide.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculations
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Mean Revenue Week 1

The mean revenue calculation portion of this example
is more complex. This is particularly true for the
revenue in week 2 because it depends on the
demands of both periods.

Beginning with the revenue in week 1, the revenue is
the unit price multiplied by the smaller of the on-hand
inventory and demand in week 1.

We use this formula to calculate revenue for each
value of demand in week 1: =UnitPrice*MIN(C29,InitInv)
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Mean Revenue Week 1 -- continued

Given these values we can calculate the mean
revenue in week 1 by entering the formula
=SUMPRODUCT(Revenues1,Probs1).
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Mean Revenue Week 2

The revenue in week 2 is the unit price multiplied by
the number of units sold in week 2, and this latter
quantity is the smaller of the beginning inventory in
week 2 and the demand in week 2.

The complex portion is that the inventory in week 2
depends on the demand in week1, because this
demand determines how much (if any) is left at the
end of week 1 and whether an order was placed.

The analysis breaks down into three cases in which
I0, D1 and RP denote beginning inventory in week 1,
the demand in week 1, and the reorder point.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Mean Revenue Week 2 -- continued

One of following occurs:
– If I0 -D1 < 0, the ending inventory in week 1 is 0, and an order
is placed. This brings the beginning inventory in week 2 up
to the 400 units (the order quantity).
– If 0< I0 -D1 < = RP, then positive inventory is on hand at the
end of week 1, but demand in week 1 is large enough to
trigger an order. Therefore, beginning inventory in week 2 is
I0 -D1 plus the order quantity.
– If I0 -D1 > RP, no order is triggered, so the beginning
inventory in week 2 equals the ending inventory in week 1, I0
-D1.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Mean Revenue Week 2 -- continued

Putting all this together we can calculate the revenue
in week 2 for each combination of week 1 and week 2
demands.

The following formula should be copied into the range
C37:G41 =UnitPrice*MIN($B37,IF(InitInvC$36<=0,OrderQuan, IF(IntiInv-C$36<=ReorderPt,InitInvC$36+OrderQuan,InitInv-C$36)))

This formula is complex but it simply implements the
logic on the previous slide with nested IF functions.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Calculating Mean Revenue Week 2 -- continued

Next, we calculate the mean revenue in week 2 in the
usual way, as a sum of products of possible revenues
and their probabilities with the formula:
=SUMPRODUCT(Revenues2,JtProbs)

The one advantage to doing all this work is that now
we can change any of the inputs in the shaded cells
and the mean revenues will be recalculated
automatically.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Weighted Sums of Random Variables
INVEST.XLS

An investor has $100,000 to invest, and she would
like to invest it in a portfolio of eight stocks.

She has gathered historical data on the returns of
these stocks and has used the data to estimate
means, standard deviations and correlations for the
stock returns.

This file contains summary measures obtained from
historical data. She believes they are also relevant for
future returns.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
INVEST.XLS -- continued

The investor would like to analyze a portfolio of these
stocks using certain investment amounts.

What is the mean annual return from this portfolio?
What are its variance and standard deviation?
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Input Data
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution

This is a typical weighted sum model.

The random variables, the X’s are the annual returns
from the stocks; the weights, the a’s are the dollar
amounts invested in the stocks; and the summary
measures of the X’s are given in rows 12,13 and 1724 of the input data.

We can obtain the mean return from the portfolio in
cell B49 by using the formula
=SUMPRODUCT(Weights, Means)
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution -- continued

We are not quite ready to calculate the variance of
the portfolio return.

The reason why is because we do not currently
know the Var(Y). But these are related to standard
deviations and correlation by
Var(Xi) = (Stdev(Xi))2
Cov(Xi, Xj) = StDev(Xi) X Stdev(Xj) X Corr(Xi ,Xj)
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution -- continued

To calculate these in Excel, it is useful to create a
column of standard deviations in Column L by using
Excel’s TRANSPOSE function.

To do this highlight the range L12:L19 and type the
formula =TRANSPOSE(Stedevs) and press CtrlShift-Enter.

Next we form a table of variances and covariances of
the X’s i the range B28:I35, using the formula
=$L12*B$13*B17 in cell B28 and copying it to the
range.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution -- continued

Finally, we need to calculate the portfolio variance in
cell B50.

To do this we form a table of terms needed and then
sum these terms as in the following steps.
– Row of weights Enter the weights in row 38 by highlighting
the range B38:I18 and typing the formula =Weights and
pressing Ctrl-Enter.
– Column of weights Enter these same weights as a column
in the range A39:A46 by highlighting the range, typing the
formula =TRANSPOSE(Weights) and pressing Ctrl-ShiftEnter.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution -- continued
– Table of terms Now use these weights and the covariances
to fill in the table of terms required for the portfolio variance.
To do so, enter the formula =$A39*B28*B$38 in cell B39
and copy it to the range B39:I46.
– Portfolio variance and standard deviation Calculate the
portfolio variance in cell B50 with the formula
=SUM(PortVarTerms). Then calculate the standard
deviation of the portfolio return in cell B51 as the square root
of the varince.

The results are shown in the calculation on the next
slide.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7
Solution -- continued

The standard deviation of approximately $11,200 is
sizable. The standard deviation is th a measure of the
portfolio’s risk.

Investors always want a large mean return, but they
also want low risk.

They realize though that often time the only way to
obtain high men returns is to assume more risk.
5.2 | 5.3 | 5.4 | 5.5 | 5.6 | 5.7