The triangular distribution is something like the risk modeller’s first bicycle. When someone develops a risk model for the first time, there is a good chance that the triangular distribution plays an important role in it. It is generally perceived as very intuitive and easy to understand, and it is therefore also easy to explain. Both are important for gaining acceptance for new methods and introducing them.
Below we use an example to show how the triangular distribution is defined, what properties it has and when it fits. We simulate the triangular distribution step by step in Excel with Risk Kit and then apply it to several uncertain quantities in one model. Along the way we also see where the limits of the triangular distribution lie.
What is a triangular distribution?
A triangular distribution is defined by three points. The minimum a and the maximum c form the range in which the values lie. Inside this range lies the most likely value b, which is also called the mode. It marks the area in which we most expect the value to be. So a ≤ b ≤ c.
The three points are the corners of a triangle: corner A lies at the minimum, corner B at the most likely value and corner C at the maximum. The density of the distribution rises in a straight line from the minimum to the peak at the most likely value and from there falls in a straight line to the maximum:
f(x) = 2(x − a) / ((c − a)(b − a)) for a ≤ x ≤ b
f(x) = 2(c − x) / ((c − a)(c − b)) for b < x ≤ c
Outside the range from a to c the density is zero. Values below the minimum or above the maximum therefore do not occur.
What does an example of a triangular distribution look like?
Let us look at an example in which a triangular distribution can be used sensibly. It is about the decision whether a new product should be manufactured. The decision is to be based on the profit that this product promises.
Figure 1 shows the calculation in Excel. Manufacturing the product incurs costs. They are divided into development costs, test costs, marketing costs and manufacturing costs in cells C5 to C8 and add up in cell C9 to total costs of 10.25 million euros. They are offset by income: the number of products sold in cell C11, here 450,000 units, is multiplied by the unit price of 28 euros in cell C12. This results in revenue of 12.6 million euros in cell C13. Revenue minus costs gives a profit of 2.35 million euros in cell C15.
Figure 1: The calculation for the new product with a profit of 2.35 million euros in cell C15. Cells E11 to G11 contain the minimum, most likely value and maximum of the products sold (German interface).
This raises the question of how certain this figure is. With what probability do I make a profit at all? With what probability am I in the order of 2.35 million euros? And what value do I expect overall?
The number of products sold, for example, is quite certainly uncertain. But perhaps I can state a lower limit, because some customers have already signalled great interest. They have said that they would order at least 150,000 units if the new product is actually manufactured. That is the minimum. Conversely, there is also an upper limit, the maximum, which is set by our production capacity. Perhaps we cannot produce more than 550,000 units, because then all machines are running at full throttle.
Inside, this range is further structured by a most likely value, for example 450,000 units. This figure may come from comparable projects we have done before. So we have three points we can name: the minimum and the maximum for the range and, inside it, the most likely value. In Figure 1 they are to the right of the calculation in row 11: the minimum of 150,000 in cell E11, the most likely value of 450,000 in F11 and the maximum of 550,000 in G11.
The most likely value is not the expected value
With these three points we can specify a triangular distribution. Figure 2 shows it. The horizontal axis shows the number of products sold from 150,000 to 550,000. The vertical axis shows the density: the higher the orange area at a point, the more often values occur in that area. The triangle rises slowly from the minimum at 150,000 to the peak at the most likely value of 450,000 and then falls steeply to the maximum at 550,000. So the left side of the triangle is long and the right side is short.
Figure 2: The triangular distribution for the number of products sold, with its peak at 450,000. The dashed line shows the expected value of 383,333, which lies to the left of the peak (German interface).
We can see straight away that the most likely value is not the expected value. The dashed line labelled “Erwartung” (expectation) lies slightly to the left of the peak, on the long side of the triangle. The asymmetry of the triangle pulls the centre of gravity to the left. The table on the right gives the value: the expectation is 383,333.33. So here the expected value is smaller than the most likely value of 450,000.
This is an important side condition that you need to know about the triangular distribution so that you do not confuse these two quantities. The most likely value is not the expected value, unless the triangle is symmetric. Then the two coincide.
When does a triangular distribution fit?
Figure 2 also shows clearly what properties the triangular distribution has. We need to know them when we consider when to use it. There are two.
First, the triangular distribution is always bounded. There is always a minimum and a maximum, and no values are taken outside this range. It is therefore suitable for quantities that actually have a minimum and a maximum. But we also have to know these limits. If we do not know the minimum and maximum, it becomes much more difficult. And if they do not exist at all, because the distribution is open at the sides, then the triangular distribution is not suitable as a model.
Second, the frequency falls off towards the extreme values only in a straight line. Compared with the PERT distribution, for example, there is therefore still a relatively large amount of probability mass near the minimum and the maximum. If we use the triangular distribution, we must therefore assume that in the simulation we will actually see results near the minimum and likewise near the maximum. These are not hypothetical values but values that will actually occur in the simulation. The table in Figure 2 also shows this: 5% of the values lie above 505,279, which is not far below the maximum of 550,000.
These are the important considerations for choosing the triangular distribution. Further below we check them for several cost items and see where the triangular distribution reaches its limits.
How do you simulate a triangular distribution in Excel?
In short: Risk Kit is an add-in for Microsoft Excel that lets you build and analyse Monte Carlo simulations directly in the spreadsheet. What we show here applies in general, though. Whether you simulate with Risk Kit, with Python or with any other tool: the triangular distribution has the same three parameters everywhere and delivers the same results.
We stay with the example from Figure 1. The three parameters are in row 11: the minimum in cell E11, the most likely value in F11 and the maximum in G11. The triangular distribution is to be created in cell C11, which so far contains the fixed value 450,000 for the number of products sold. The profit in cell C15 is calculated with the formula =C13-C9 as revenue minus costs. We proceed in five steps:
- Select cell C11, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose “Triangular”, the English name of the distribution.
- In the input dialog, enter the three corners of the triangle: A is the minimum, B the most likely value and C the maximum. We enter references to E11, F11 and G11. To the right of the fields, Risk Kit shows the values the references point to: 150000, 450000 and 550000. The “Name” field contains a reference to the label in cell B11. It appears as the title above the chart.
- Use the small arrow next to “Insert” to choose the option “Insert & mark as input cell” (“Einfügen & als Eingabezelle markieren” in the German interface). Risk Kit writes the triangular distribution into cell C11 and marks it as an input at the same time. In the dialog that follows, the field for the name (“Ausgabename”) contains a reference to B11, that is, the label “Anzahl der verkauften Produkte” (number of products sold). We confirm with “Update” (“Aktualisieren”).
- Select cell C15 with the profit and mark it as an output using the output button (“Ausgabe”). As the name we use the label in cell B15, and as the unit EUR. Risk Kit extends the formula to =C13-C9+OutputName(B15)+OutputUnit("EUR") and highlights the cell in yellow.
- Click “Simulation” on the toolbar.
Figure 3 shows the input dialog from step 2. On the left of the sheet you can see the three parameters in cells E11, F11 and G11. In the dialog on the right, the fields A, B and C refer to these cells. The orange wedge connects the dialog to its target cell C11. The chart in the dialog immediately shows the triangle from Figure 2, and the table next to it lists statistics such as the expectation, the minimum and the maximum.
Figure 3: The input dialog of the function Triangular_variate for cell C11. The parameters A, B and C refer to cells E11, F11 and G11 with the minimum, most likely value and maximum (German interface).
Figure 4 shows step 3. Cell C11 is highlighted in green, which marks it as an input. It now contains a random number from the triangular distribution, here 535,585, and revenue and profit change accordingly. The formula bar shows the formula =Triangular_variate(E11;F11;G11)+OutputName(B11). In the dialog on the right, the name field (“Ausgabename”) contains the reference to B11. Despite its name, which means output name, this field names every model variable, including inputs.
Figure 4: Cell C11 is marked as an input and highlighted in green. The formula bar shows the formula =Triangular_variate(E11;F11;G11)+OutputName(B11) (German interface).
After the simulation we see how the triangular distribution affects the profit. Figure 5 shows the result. In the list on the left, the profit (“Gewinn”) is listed under “Ausgabe” (output) and the number of products sold under “Eingabe” (input). In the middle are the statistics of the simulated profits, on the right their histogram. Its horizontal axis shows the profit in euros, and the height of the bars shows how often a profit in that area occurred.
Figure 5: Simulation result for the profit. In the middle the statistics with a mean of 447,818 euros and a 75% quantile of 2,336,892 euros, on the right the histogram (German interface).
The profit is also triangularly distributed. The mean is 447,818 euros, which is the dashed line in the histogram. The range extends from a loss of about 6 million euros to a maximum profit of about 5.1 million euros.
So, just because of the uncertainty in the number of products sold, the profit is anything but fixed.
What does this mean for the 2.35 million euros from the spreadsheet calculation? In the table, the 75% quantile is 2,336,892 euros: 75% of the simulated profits lie below it and 25% above it. So the 2.35 million euros are not reached with a probability of about 75% and exceeded with a probability of about 25%.
How do you model several uncertain quantities with triangular distributions?
That was a first application of the triangular distribution, which we can generalise further. So far the profit depended on just one uncertain quantity, the number of products sold. This made sales, and therefore revenue and profit, uncertain. But revenue is not the only thing that is uncertain. Uncertain costs can be added. We therefore apply the same idea and the same use of the triangular distribution to further risk factors. This gives us a more differentiated picture of the uncertainties the profit is exposed to overall.
The second video on the triangular distribution shows this step (in German). We also go through it below.
Development costs
As an example, let us look at the development costs. They are forecast at 1.5 million euros. That is the most likely value, or mode. The minimum is 750,000 euros. One might say: the whole thing requires a minimum effort, and with less than 750,000 euros we will not be able to manufacture the product at all. But we also assume that we will need at most 1.75 million euros. So once again we have the three points that are relevant for the triangular distribution.
In Figure 6, the calculation is on the left and next to it is a table with the columns minimum, mode and maximum, with one row per cost item. The values for the development costs are in row 5. We insert the triangular distribution into cell C5 just as before: parameter A is the minimum in E5, the middle corner B is the mode in F5 and corner C is the maximum in G5. The chart in the dialog shows the shape for the development costs from 750,000 euros to 1.75 million euros, with its peak at the most likely value of 1.5 million euros. We then mark the cell as an input, with the label as its name and EUR as its unit.
Figure 6: The triangular distribution for the development costs in cell C5. The parameters refer to cells E5, F5 and G5 with the minimum, mode and maximum (German interface).
Whether the triangular distribution is really suitable here is an open question, though, which we would have to discuss. That development costs are bounded below is understandable. Whether they are also bounded above is another matter. If serious problems really do occur, the maximum of 1.75 million euros could turn out to be an illusion. Here we are at the limit of what the triangular distribution can sensibly represent, and we are potentially making compromises.
This is also why we called the triangular distribution the risk modeller’s first bicycle at the beginning. It is the first bicycle, perhaps still with training wheels. It gives you a way in and you can work with it. But you may also be making assumptions that can be very painful and are not necessarily met. With the development costs, we can be anything but sure that a maximum exists at all or that we know it.
Test costs
The test costs have similar properties to the development costs. After all, the two are closely related. The lower limit is 200,000 euros, the most likely value 250,000 euros and the maximum 350,000 euros. Here too, the maximum is questionable for the same reasons as with the development costs. If there are problems in development and the costs get out of hand, the same will probably happen to the test costs, because whenever we have redeveloped something, we have to test it again. So here too, assuming that the test costs are triangularly distributed is a compromise. We adopt the triangular distribution nonetheless and mark the cell as an input as before.
Marketing costs
With the marketing costs, the situation is somewhat more relaxed. Here we have assumed a most likely value of 5 million euros and a minimum of 4.9 million euros. This may be because, for example, we have a fixed marketing plan that requires us to spend at least this amount. We can also sensibly assume a maximum here, because it is essentially based on a decision we can simply make: we will under no circumstances spend more than 7.5 million euros. So here the triangular distribution potentially fits better.
Manufacturing costs
For the manufacturing costs, the most likely value is 3.5 million euros and the minimum 3 million euros. The minimum can result from working hours that we know and that we need, for example, for the preparation so that production can start at all. We set the maximum of 3.9 million euros if we know that we will definitely manage to produce within this range.
This is a realistic model above all when the variable costs that arise from the number of products sold are negligible. If you make software, for instance, the variable costs are very low. Then you can certainly set up a calculation like this, in which the manufacturing costs are independent of the number of products sold.
Figure 7 shows the finished model. The five uncertain quantities in cells C5 to C8 and C11 are marked in green as inputs, and the profit in cell C15 is marked in yellow as the output. On the right is the table with the minimum, mode and maximum for all five quantities. The formula bar shows the formula for the development costs in cell C5: =Triangular_variate(E5;F5;G5)+OutputName(B5)+OutputUnit("EUR").
Figure 7: The finished model with five triangularly distributed inputs (green) and the profit as the output (yellow). The formula bar shows the formula for cell C5 (German interface).
What assumptions are built into such a model?
Once we have built a more comprehensive model like this, it is important to be clear about which assumptions we have actually made.
First, we assumed that a maximum exists for the development costs and that we know it. The same applies to the test costs. We have already seen above that this assumption stands on somewhat shaky ground.
Second, we assumed that the frequency distribution really has the shape of a triangle. The frequency therefore runs in a straight line towards the minimum and towards the maximum, so that values close to these extremes occur regularly in the simulation and are not just hypothetical. If, on the other hand, extreme values occur only in very rare cases, the distribution may still have a minimum and a maximum, but it does not have the shape of a triangle. This is a consideration you should not neglect, because here a limit of the triangular distribution is reached.
Third, we assumed that all the individual uncertainties are independent of each other. This may not quite hold. Development costs and test costs are related, as we saw with the test costs. If I have to redevelop something, I also have to test it again. If I was fast in development, I will also be fast in testing. This could be improved in a next step by adding a dependency between these two uncertainties. Something similar applies to the marketing costs and the number of products sold, which are hopefully related. After all, that is exactly the purpose of marketing: to increase sales.
We have to be clear about the assumptions we make and put them to the test. This lets us recognise the limits of what we are analysing here. The results are always subject to the assumptions we have made, and these should be correspondingly realistic. Developing a model therefore means making ever better assumptions that fit the case at hand better.
The triangular distribution certainly fits much better here than simply assuming a fixed value, which is completely unrealistic. It is much more realistic, but still not perfect.
How does the result change with all uncertainties?
Let us look at what the result of the simulation looks like under these assumptions. We click “Simulation” again. Figure 8 is laid out like Figure 5: on the left, all five uncertain quantities are now listed under “Eingabe” (input), in the middle are the statistics and on the right the histogram of the profit.
Figure 8: Simulation result for the profit with all five uncertain quantities. The mean is −135,943 euros, the minimum −7,844,994 euros and the maximum 5,216,463 euros (German interface).
Taking the uncertainty in the costs into account, the mean of the profits is now negative: it is −135,943 euros. The loss potential has also increased, and the minimum is now about −7.8 million euros. The profit potential has also risen somewhat, to a maximum of about 5.2 million euros. This is because in some very favourable cases we have low costs with high sales.
Under the assumptions we have made, this gives us a differentiated picture of how these uncertainties affect the profit.
Which uncertain quantity has the strongest effect on the profit?
We can now look at how the individual uncertainty factors affect our profit. To do this, we tick them in the list on the left under “Eingabe” (input) and click “Calculate sensitivities” (“Sensitivitäten berechnen”). Risk Kit then relates the selected output cell with the profit to these input cells.
The result is the tornado chart in Figure 9. Each bar stands for one uncertain quantity, and the horizontal axis shows the effect on the profit in euros. The longer a bar, the greater the influence of that quantity on the result. At the top is the most important influencing factor, sales, that is, the number of products sold. Then come the marketing costs, the development costs, the manufacturing costs and, at the very bottom as the least important factor, the test costs. The sensitivity analysis thus immediately gives a ranking of the influencing factors by importance.
Figure 9: Tornado chart of the sensitivity analysis. The number of products sold has the greatest influence on the profit, the test costs have the smallest (German interface).
What do we take away from the example?
With this example we have taken an important first step into modelling. We have seen that the assumptions we make actually form the model.
The triangular distribution has some very fundamental assumptions that must be met for it to be a sensible model. The quantity must be bounded. And the shape of the triangle towards the extreme values must be more or less given. This means that there is also still an appreciable amount of probability mass near the extreme values, so that they actually occur in practice. In addition, we assumed that the factors are independent of each other. This assumption has nothing to do with the triangular distribution, but it is also important for a model like this.
From this point on we can improve the model step by step and critically question our assumptions.
More distributions
For an overview of all distributions, see our guide “When Do I Use Which Distribution?”.
- When do I use a Bernoulli distribution in a Monte Carlo simulation?Definition, formula and examples: when the Bernoulli distribution fits and what role it plays as a switch in a simulation.
- When do I use a binomial distribution in a Monte Carlo simulation?Definition, formula and examples: how the binomial distribution describes the number of successes in n independent trials and how to calculate it in Excel.
- When do I use a Poisson distribution in a Monte Carlo simulation?Definition, formula and examples: how the Poisson distribution describes the frequency of independent events and when it fits.
- When do I use a normal distribution in a Monte Carlo simulation?Definition, formula and examples: when the normal distribution fits, where its limits lie and how to truncate it.
- Quantifying expert estimates in a Monte Carlo simulation: the Expert distributionQuantifying expert estimates better: how the Expert distribution captures estimates through bounds and quantiles, even without a fixed minimum or maximum.
- When do I use a PERT distribution in a Monte Carlo simulation?Definition, formula and example: how the PERT distribution describes a bounded duration with minimum, most likely value and maximum, and where its limits lie.
- When do I use a lognormal distribution in a Monte Carlo simulation?Definition, properties and limits: when the lognormal distribution correctly describes proportional growth processes and how to parametrise it in Excel.
- Custom distributions in a Monte Carlo simulation: the Flex distributionCustom distributions: how the Flex distribution is built from values and probabilities and when it fits, from expert estimates to transferring simulation results.