Probability Essentials

1
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.

2
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.

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

4
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
Conditional Probability -- continued

• The new probability of A is called the
  conditional probability of A given B. It is
  denoted P(AB).
• 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(AB)

6
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(AB).
• However, in some applications P(AB) 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(AB)P(B)
• The conditional probability formula and the
  multiplication rule are both valid in fact, they
  are equivalent.

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

8
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(AB)3/4.
• We can use the multiplication rule to obtain
• P(A and B) P(AB)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.

9
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

10
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.

11
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.

12
Probabilistic Independence

• A concept that is closely tied to conditional
  probability is probabilistic independence.
• There are situations unlike Bendix when P(A),
  P(AB) 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.

13
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.

14

Distribution of a Single Random Variable
15
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.

16
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.

17
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.

18
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.

19
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.

20
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.

21
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?

22
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.

23

Derived Probability Distributions
24
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.

25
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?

26
Derived Distributions of Units Sold and Revenue
27
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

28
Solution -- continued

• Calculate Units sold MIN(B10,OnHand)
• Calculate Revenue for each value of units sold
  UnitPriceB20
• 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.

29
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 a0
  then the mean and standard deviation of Y are
  this same multiple of the mean and standard
  deviation of X.

30

Distribution of Two Random Variables Scenario
Approach
31
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.

32
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.

33
GMGOLD.XLS

• This file contains the probabilities and
  estimated returns of the GM stock and the gold.

34
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.

35
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.

36
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.

37
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.

38
Calculating Covariance and Correlation

• We also need to calculate the covariance and
  correlation between the variables. To obtain
  these we use the following steps
• 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.
• Covariance Calculate the covariance between GM
  and gold returns in cell B23 with the
  formulaSUMPRODUCT(GMDevs,GoldDevs,Probs)

39
Calculating Covariance and Correlation --
continued

• Correlation Calculate the correlation between GM
  and gold returns in cell B24 with the formula
  Covar/(GMStdvGoldStedev)
• 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.

40
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.

41
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.

42
Simulation of GM and Gold Returns
43
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.

44
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.

45
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.

46
Distribution of Portfolio Return
47
Distribution of Two Random Variables Joint
Probability Approach
48
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.

49
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.

50
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.

51
Joint Probability Approach -- continued

• The joint distribution also determines the
  conditional distributions of X given Y, and of Y
  given X.

52
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.

53
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.

54
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/C10. Each column sum
  should equal one.

55
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 deviationsThese 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.

56
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
  (C4-MeanDem1)(B5-MeanDem2) in C37 and copying
  it to C37F41.
• 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/(StdevDem1StDevDem2).

57
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.

58
Conditional Distributions of Demand 1 Given
Demand 2
59
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.

60
Conditional Distributions of Demand 2 Given
Demand 1
61
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.

62
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.

63
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.

64
JTPROBS.XLS

• An file shows an example of the indirect method
  of assessing joint probabilities.

65
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 C11C6 and then this
  is copied to C20F24.
• The associated graph on the next slide appears to
  be consistent with the meaning of substitute
  products.

66
Conditional Distributions of Demand 2 Given
Demand 1
67
Independent Random Variables
68
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.

69
Assumptions

• The ordering lead time is negligible.
• Sales are lost if customer demand during any week
  is greater than that weeks 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.

70
INVNTORY.XLS

• This file contains the plant managers 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.

71
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.

72
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(C20,DistTable,2)VLOOKUP(B21,DistTable,
  2) in cell C21 and copying it into range C21G25.
• This formula simply multiplies the two marginal
  probabilities corresponding to the demands in the
  top and left margins of the joint probability
  table.

73
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.

74
Calculations
75
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 UnitPriceMIN(C29,Init
  Inv)

76
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).

77
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.

78
Calculating Mean Revenue - Week 2 -- continued

• One of following occurs
• If I0 -D1 lt 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 0lt I0 -D1 lt 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 gt RP, no order is triggered, so the
  beginning inventory in week 2 equals the ending
  inventory in week 1, I0 -D1.

79
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 C37G41 UnitPriceMIN(B37,IF(InitInv-C36lt
  0,OrderQuan, IF(IntiInv-C36ltReorderPt,InitInv-C
  36OrderQuan,InitInv-C36)))
• This formula is complex but it simply implements
  the logic on the previous slide with nested IF
  functions.

80
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.

81

Weighted Sums of Random Variables
82
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.

83
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?

84
Input Data
85
Solution

• This is a typical weighted sum model.
• The random variables, the Xs are the annual
  returns from the stocks the weights, the as are
  the dollar amounts invested in the stocks and
  the summary measures of the Xs are given in rows
  12,13 and 17-24 of the input data.
• We can obtain the mean return from the portfolio
  in cell B49 by using the formula
  SUMPRODUCT(Weights, Means)

86
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))2Cov(Xi, Xj) StDev(Xi) X
  Stdev(Xj) X Corr(Xi ,Xj)

87
Solution -- continued

• To calculate these in Excel, it is useful to
  create a column of standard deviations in Column
  L by using Excels TRANSPOSE function.
• To do this highlight the range L12L19 and type
  the formula TRANSPOSE(Stedevs) and press
  Ctrl-Shift-Enter.
• Next we form a table of variances and covariances
  of the Xs i the range B28I35, using the formula
  L12B13B17 in cell B28 and copying it to the
  range.

88
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 B38I18 and typing the
  formula Weights and pressing Ctrl-Enter.
• Column of weights Enter these same weights as a
  column in the range A39A46 by highlighting the
  range, typing the formula TRANSPOSE(Weights) and
  pressing Ctrl-Shift-Enter.

89
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 A39B28B38 in cell B39 and
  copy it to the range B39I46.
• 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.

90
(No Transcript)
91
Solution -- continued

• The standard deviation of approximately 11,200
  is sizable. The standard deviation is th a
  measure of the portfolios 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.