(Click On Image To See a Larger Version)

This is one of the following six articles on Solving Problems With Other Distributions in Excel

Overview of the Beta

Distribution

The beta distribution is a family of continuous probability distributions. This is evidenced by the smooth shape of the above graph of a beta distribution’s PDF (Probability Density Function) curve. The beta distribution is defined on the interval [0,1] and is defined by two positive shape parameters α and β. The Excel formulas that calculate the PDF and CDF of the beta distribution have optional inputs that enable the [0, 1] interval to be expanded to an arbitrary width.

The beta distribution is most often used to model events which are constrained to take place between a minimum and maximum time limit. In this case the beta distribution is used to calculate the probability that a project will be completed within a given period of time. For this reason, the beta distribution is often used for modeling project planning and control systems such as PERT (Project Evaluation and Review Technique) and CPM (Critical Path Method).

The beta distribution is used in general to model the variation of a variable across samples, such as the percentage of the day people spend watching television. The beta distribution can be applied to a wide variety of disciplines to model the behavior of a random variable limited to intervals of finite length. Examples include the modeling of the variability of soil properties and the proportions of the minerals in rocks in stratigraphy. One common use of the beta distribution is to model a defect rate. The first problem in this section will perform this type of analysis.

Beta Distribution’s PDF –

Probability Density Function

The following Excel-generated graph shows the beta distribution’s PDF (Probability Density Function) for as the X value (the completion time) goes from 0 to 1 with α = 8 and β = 10.

The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value.

(Click On Image To See a Larger Version)

Beta Distribution’s CDF –

Cumulative Distribution Function

The following Excel-generated graph shows the Beta distribution’s CDF (Cumulative Distribution Function) for 10 degrees of freedom as the X value (the completion time) goes from a completion time of 0 to 1.

The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value.

(Click On Image To See a Larger Version)

Beta CDF Problem 1 Solved in Excel

The defect rate of observed for units coming off of a production line can be described by the beta distribution with shape parameters α = 3 and β = 6. What is the probability that UP TO 30 percent of the units are observed to be defective during an inspection of a large lot of units produced on that production line?

The problem asks to calculate the probability of UP TO 30 percent so the beta’s CDF (Cumulative Distribution Function) will be used to solve this problem.

Shape parameters have already been given as follows: α = 3 and β = 6

Min and Max Times will be set at 0 and 1 because the range of possible percentages of defective units is 0 percent to 100 percent, which is 0 to 1.

The X value to be evaluated is X = k = 30 percent = 0.3

The Excel equation to solve the problem is as follows:

F(X=0.3;α=3,β=6) = BETA.DIST(k,α,β,TRUE,MIN Time, Max Time)

F(X=0.3;α=3,β=6) = BETA.DIST(0.3,3,6,TRUE,0,1) = 0.4482

There is a 44.82 percent chance that at least 30 percent of all units inspected from one large batch are defective. This agrees with the Excel-generated CDF graph which X = 0.3 corresponds with Y = 0.4438 as follows:

(Click On Image To See a Larger Version)

Beta CDF Problem 2 Solved in Excel

The completion time for a project can be described by the beta distribution with shape parameters α = 3 and β = 6. What is the probability that completion of the project will take UP TO 27 hours if the min and max times for project completion are 15 hours and 45 hours?

The problem asks to calculate the probability of UP TO 27 hours so the beta’s CDF (Cumulative Distribution Function) will be used to solve this problem.

Shape parameters have already been given as follows: α = 3 and β = 6

Min and Max Times are given in the problem to be 15 hours and 45 hours.

The X value to be evaluated is X = k = 27 hours

The Excel equation to solve the problem is as follows:

F(X=27;α=3,β=6) = BETA.DIST(k,α,β,TRUE,MIN Time, Max Time)

F(X=27;α=3,β=6) = BETA.DIST(27,3,6,TRUE,15,45) = 0.6846

There is a 68.46 percent chance that the project will take up to 27 hours to complete. This agrees with the Excel-generated CDF graph which X = 27 hours corresponds with Y = 0.6846 as follows:

(Click On Image To See a Larger Version)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Technorati Tags: beta,beta distribution,statistics,excel,excel 2010,excel 2013

Become an Excel Statistical Master

Excel Master Series - MBA-level statistics - Over 1,100+ Pages of Easy-To-Follow Instructions in Excel

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

Step-By-Step Optimization With Excel Solver

What's In It?

For anyone who wants to be operating at a high level with the Excel Solver quickly, this is the book for you. Step-By-Step Optimization With Excel Solver is a 200+ page .pdf e-manual of simple yet thorough explanations on how to use the Excel Solver to solve today’s most widely known optimization problems. Loaded with screen shots that are coupled with easy-to-follow instructions, this book will simplify many difficult optimization problems and make you a master of the Excel Solver almost immediately.

Here are just some of the Solver optimization problems that are solved completely with simple-to-understand instructions and screen shots in this e-manual:

• The famous “Traveling Salesman” problem using Solver’s Alldifferent constraint and the Solver’s Evolutionary method to find the shortest path to reach all customers. This also provides an advanced use of the Excel INDEX function.

• The well-known “Knapsack Problem” which shows how optimize the use of limited space while satisfying numerous other criteria.

• How to perform nonlinear regression and curve-fitting on the Solver using the Solver’s GRG Nonlinear solving method.

• How to solve the “Cutting Stock Problem” faced by many manufacturing companies who are trying to determine the optimal way to cut sheets of material to minimize waste while satisfying customer orders.

• Portfolio optimization to maximize return or minimize risk.

• Venture capital investment selection using the Solver’s Binary constraint to maximize Net Present Value of selected cash flows at year 0. Clever use of the If-Then-Else statements makes this a simple problem.

• How use Solver to minimize the total cost of purchasing and shipping goods from multiple suppliers to multiple locations.

• How to optimize the selection of different production machine to minimize cost while fulfilling an order.

• How to optimally allocate a marketing budget to generate the greatest reach and frequency or number of inbound leads at the lowest cost.

Step-By-Step Optimization With Excel Solver has complete instructions and numerous tips on every aspect of operating the Excel Solver. You’ll fully understand the reports and know exactly how to tweek all of the Solver’s settings for total custom use. This e-manual also provides lots of inside advice and guidance on setting up the model in Excel so that it will be as simple and intuitive as possible to work with.

All of the optimization problems in this book are solved step-by-step using a 6-step process that works every time. In addition to detailed screen shots and easy-to-follow explanations on how to solve every optimization problem in the book, a link is provided to download an Excel workbook that has all problems completed exactly as they are in this e-manual.

Step-By-Step Optimization With Excel Solver is exactly the e-manual you need if you want to be optimizing at an advanced level with the Excel Solver quickly.

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

Immediate, Absolute, No-Questions-Asked, Money-Back Guarantee If Not TOTALLY, 100% Satisfied. In Other Words, If Any Excel Master Series eManual That You've Purchased Here Does Not Provide Instructions That Are CRYSTAL CLEAR and EASY TO UNDERSTAND, You Get All Of Your Money Back Immediately and Keep the eManual. Guaranteed!

Meet The Author