7/30/2019 3-4 Constructing a Probability Mass Function With EXCEL

1/2

/16/12Chapters 3-4 Constructing a Probabil ity Mass Function with EXCEL

ww.indiana.edu/~iustats/query/chap3.htm

Chapters 3-4 Constructing a Probability Mass

Function with EXCEL

From William E. Becker, Statistics for Business and Economics Using Microsoft Excel 97, S.R.B. Publishing,

1997, p. 142-143. Reproduced with permission of S.R.B. Publishing.

QUERY 4.1: (p. 142-143) In the early 1990s,Newsweekreported that "average take" for bank robberies was $3,244

but 85 percent of the robbers were caught. Assuming 60 percent of those caught lose their entire take and 40 percent

lose half, graph the probability mass function using EXCEL. Calculate the expected take from a bank robbery. Does it

pay to be a bank robber?

ANSWER: Construction of the probability mass function for bank robberies first requires the defining of the random

variablex, bank robbery take. If the robber is not caughtx = 3,244. If the robber is caught and manages to keep half

x = 1,622. If the robber is caught and loses it all, thenx = 0. The associated probabilities for thesex values are 0.15

= 1- 0.85), 0.34 ( = 0.850.4), and 0.51 ( = 0.850.6) After entering thex values in cells A1, A2 and A3 and the

associated probabilities in B1, B2, and B3, the following steps lead to the probability mass function:

1) Click on the ChartWizard. The "ChartWizard Step 1 of 4" screen will appear.

2) Highlight "Column" at "ChartWizard Step 1 of 4" and push "Next."

3) At "ChartWizard Step 2 of 4 Chart Source Data," enter "=B1:B3" for "Data range," and push

"column" button for "Series in." A graph will appear. Click on "series" toward the top of the screen to ge

a new page.

4) At the bottom of the "Series" page is a rectangle for "Category (x) axis labels:" Click on this rectangle

and then highlight A1:A3.

5) At "Step 3 of 4" move on by clicking on "Next," and at "Step 4 of 4" click on "Finish." Your screen should now

look like that in Chart 4.1.

CHART 4.1 Probability Mass Function for Bank Robberies Within the EXCEL SpreadSheet

7/30/2019 3-4 Constructing a Probability Mass Function With EXCEL

2/2

/16/12Chapters 3-4 Constructing a Probabil ity Mass Function with EXCEL

The expected value of a robbery is $1,038.08.

E(x) =xP(x) = (0)(0.51) + (1622)(0.34) + (3244)(0.15)

= 0 + 551.48 + 486.60 = 1038.08

The expected return to bank robbery is positive - on average, bank robbers get $1,038.08 per heist. If criminals

make their decisions strictly on this expected value, then it pays to rob banks. A decision rule based only on an

expected value, however, ignores the risks or variability in the returns. In addition, our expected value calculations do

not include the cost of jail time, which could be viewed by criminals as substantial.

Go to:

Examples of EXCEL Use

Statistics for Business and Economics WWW Page

http://www.indiana.edu/~iustats/stats.htmhttp://www.indiana.edu/~iustats/excel.htm