MM4XL Home > Decision Making > Risk Analyst > Probability Distribution Functions for Monte Carlo Simulation

# Probability distribution functions Monte-Carlo simulation software

mmBETA
 The Beta distribution returns the probability of an event occurring. This helps, for instance, when we need to estimate the probability that the next client will buy. For instance, if for every 100 calls 15 would buy, we could use the formula below to estimate the probability of the next purchase occurring =mmBETA(15, 86) Click to enlarge

mmBETAGEN
 The Beta General distribution function returns the probability of an event occurring. It helps when we need an estimate of the probability that, for instance, the next client will buy. Replacing the mmBETA shown previously with the formula below we can estimate the probability of the next purchase occurring, exactly as we would do with mmBETA(16, 85): =mmBETA(16, 85, 0, 1) Click to enlarge

mmBINOMIAL
 The Binomial distribution returns the number of events that occur. This helps, for instance, when we need to estimate the entrance of a new competitor in the market. Say that we are launching a new product in a new market category and we need a model to estimate the profitability over the next 5 years. We expect competition to enter the category, and based on previous knowledge we estimate a maximum number of 5 competitors to enter the category in 5 years with a probability of 40% for these events to occur. The formula below estimates the number of competitors entering the market in one year: =mmBINOMIAL(5, 0.4) Click to enlarge

mmCHI2
 The Chi Square distribution returns the amount of mutually exclusive events. Say that we are modeling the employment of a new professor for an MBA course and we need an estimate of the experience, in years, of use of the PC. We expect it to range between zero and 15 years. The formula below may be used to estimate the years of experience for each candidate: =mmCHI(2) Click to enlarge

mmDISCRETE
The Discrete distribution is used to simulate the occurrence of the given number of events only. For instance, it could be the lights of a semaphore, the winners of a horse race, which counter will host the next client, or any other process that returns a limited number of events only.

mmERF
 The Erf distribution returns extreme values. Say we are working on short-term sales levels and we are modeling forecast errors as computed with the Forecast Manager tool of MM4XL software (refer to the example file Forecast Manager.xls, sheet MM4XL Forecast, range L33:L90). The formula below may be used to simulate the errors produced with the times series of the forecast example: =mmERF(0.1276). The Mean level for the formula above has been found using the Fitting tool with the data in range L33:L90, yet this is the same mean value that one can find in cell H17. Click to enlarge

mmERLANG
 The Erlang distribution returns the amount of time between events. Say we are modeling the client flow to our fast-food restaurant. As a result of regular observation, we know that it takes 3 minutes for an employee to serve a client. The formula below may be used to simulate the amount of time between 0 and 5 minutes needed to serve a client: =mmERLANG(3, 0.373) Click to enlarge

mmEXPON
 The Exponential distribution returns the amount of time between events. This helps, for instance, when we need to estimate how long it takes between client arrival and departure. Say that we are modeling the client flow at a fast food restaurant and we need an estimate of the time between arrivals, which is useful information to determine how much production capacity is available at any time. Our records show that on average in a given day, we serve one client every 12 minutes. The formula below estimates the time in minutes between client arrivals: =mmEXPON(12) Click to enlarge

mmEXTVAL
 The Extreme Values distribution simulates extreme values. Say we are modeling the production capacity of a fast food restaurant. During the weekend, when the employee at the window requires more than 4 minutes to serve a client, the risk of losing a client in line is high. The formula below may be used to simulate the maximum time taken to serve a client: =mmEXTVAL(4, 0.525) Click to enlarge

mmGAMMA
 The Gamma distribution returns the amount of time between events. Say, we are modeling the time to issue an order to restock the inventory of sodas at a retail store. The order can be issued only when all 3 brands of soda that the store carries have one or zero units in stock. From historical data we know this happens every 28 days plus or minus 7 days. The formula below may be used to simulate the time in days to reorder sodas: =mmGAMMA(7, 4) Click to enlarge

mmGAUSSINV
 The Gauss Inverse distribution models response time in sequential patterns. Say we are modeling the web surfing behavior of car buyers searching for information. From past experience we know that the number of links (pages) a visitor follows in a website before jumping to the next website is about 3 (although 1 page is the modal value) while the maximum number of pages visited is about 100. This surfing behavior can be modeled with: =mmGAUSSINV(3, 6) Click to enlarge

mmGEO
 The Geometric distribution returns the number of trials before a positive event. This helps, for instance, to estimate the number of cold calls before we reach a potential buyer. Say we run a telemarketing call center, and we need to estimate the number of telephone lines needed to complete the action within a given timeframe. From past experience we know the conversion rate from call to purchase is 0.65%. The formula below could help to estimate how many calls before we have a successful one, which is a good hint to estimate the overall result of the action: =mmGEO(0.0065) Click to enlarge

mmHYPERGEO
 The Hypergeometric distribution returns the number of expected defects in a sample of a given size according to the number of defects expected in the whole batch. This helps, for instance, to estimate the number of defective computers in lot of 5000 units. Say that 1% defects in a batch is the limit within we accept incoming goods. The following formula estimates the number of expected defectives in a batch of 5000 using an inspection sample of 100 units: =mmHYPERGEO(100, 50, 5000) Click to enlarge

mmINTUNI
 The Integer Uniform distribution returns numbers with equal probability within a Lower and an Upper bound. Say that we sell three different kinds of pizza, and every week we sell roughly an equal quantity of each, 300 for instance. The formula below could help to estimate the preference of clients ordering one of three kinds of pizza: =mmINTUNI(1, 3) Click to enlarge

mmLOGISTIC
 The Logistic distribution returns values more spread in the tails of the distribution. Say that we are modeling the response of demand to advertising investments. We estimate that next year sales could be in the range \$1750 plus or minus \$250, given that we invest the planned advertising budget. The formula below could help to estimate the expected demand levels: =mmLOGISTIC(1750, 45) Click to enlarge

mmLOGNORMAL
 The Lognormal distribution can model the product of several independent events, such as monthly sales. Assume that each customer's purchase is the product of many factors, such as salary times a weather factor times a mobility factor times several other independent factors. If there are not too many customers with a lognormal sales shape, the company sales will also tend to be lognormal. Otherwise, with many lognormal customers, company sales will tend to be normally distributed due to the central limit theorem. The formula below can help to model the sales of a not too large pool of customers with average sales of \$50000 and standard deviation \$10000: =mmLOGNORMAL(50000, 10000) Click to enlarge

mmNEGBIN
 The Negative Binomial distribution returns the number of trials before reaching a certain number of successes. Say that we are planning to use outdoor advertising for our store and we are wondering about the visits to the store that billboards can generate, which impacts on sales. From a survey study we know that 30% of the 25,000 pedestrians exposed to the billboard each day notice it, and 6.5% of the 30% enter the store. The formula below can help to model the required number of pedestrians exposed to the billboard in order to obtain 10 visits: =mmNEGBIN(10, 0.0195) Click to enlarge

mmNORMAL
 The Normal distribution returns a normally distributed value around the mean. This helps, for instance, to model the growth of a given market for successive years. Say that we are looking into the profitability of a new product launch and we need to estimate the market size for 5 consecutive years. The value of the market at year zero is estimated in 1 million and will grow at a rate of around 5% a year. The formula below helps to model this case: =mmNORMAL(1.05, 0.01) * 1000000 Click to enlarge

mmPARETO
 The Pareto distribution returns a normally distributed value around the mean. This helps, for instance, to model the growth of a given market for successive years. Say that we are looking into the profitability of a new product launch and we need to estimate the market size for 5 consecutive years. The value of the market at year zero is estimated in 1 million and will grow at a rate of around 5% a year. The formula below helps to model this case: =mmPARETO(0.2, 1) Click to enlarge

mmPARETO2
 The Pareto2 distribution can return extreme values starting from zero. Say we are modeling the mean number of active sessions at our website. From internal data we know that the average number of open sessions is 14. The formula below simulates the number of open sessions at a given time: =mmPARETO2(2, 14) Click to enlarge

mmPERT
 The PERT distribution works like the triangular distribution. Say we are modeling the long-term profitability of a new product and we need an estimate of our market share. We assume that future market share will be in the range 2%-14% with the most likely value being 4%. The formula below helps to model this instance: =mmPERT(2, 4, 14) Click to enlarge

mmPOISSON
 We can use the Poisson distribution, for instance, to model the purchase cycle of a washing powder market of 20 million potential buyers. The mmPOISSON distribution can be used to estimate the population at different points in time. When the parameter of the mmPOISSON is very large, however, the result is approximated and the difference between runs may become negligible. =mmPOISSON(20000000) Click to enlarge

mmRANDBETWEEN
Say we are modeling the pedestrian flow of a sidewalk that our business display window faces onto. According to internal data we know that every hour between 700 and 1000 people walk past our window. The Random Between distribution may be used to simulate the next hour pedestrian flow: =mmRANDBETWEEN(700, 1000)

mmRAYLEIGH
 The Rayleigh distribution can simulate time to perform. Say we are modeling wind speed over a year in order to estimate the energy recovery from a wind turbine. The formula below simulates wind speed in miles with a modal value equal to 8.5 miles per hour: =mmRAYLEIGH(8.5) Click to enlarge

mmSTUDENT
 Say we are modeling the weight of biscuit boxes. In order to measure the accuracy of production, we select 15 boxes from each production lot and measure an average weight of 720 grams with standard deviation equal to 25 grams. The Student distribution below produces t values that can be used to simulate box weights. It is a two-step process. Step 1: the formula below produces a t value: =mmSTUDENT(14) Step 2: the obtained t value is used within the TDIST function, built in to MS Excel, in order to simulate the average box weight. If this falls outside certain limits it could require the production supervision to reject the box, and rejections produce costs that management dislikes. The following formula returns weight in the range 720 plus or minus 25, according to the t distribution: =720-((25*mmSTUDENT(14))/Sqrt(15)) Click to enlarge

mmTRI
 The Triangular distribution returns values within a minimum and maximum boundary accounting for a most likely value. Say we are modeling the long-term profitability of a new product and we need an estimate of our market share. We assume that future market share will be in the range 20%-40% with the most likely value being 25%. The formula below helps to model this instance: =mmTRI(0.2, 0.25, 0.4) Click to enlarge

mmUNIFORM
 The Uniform distribution returns values with an equal probability of occurrence. Say we are modeling the financial sheet of a new store, and we are wondering a