Excel Monte Carlo Simulation Triangular Distribution Calculator

[@driftviews]

Thanks! I fully agree with a certain Dr Wright "I think that is pretty good. And all done using basic Excel - no expensive add-ins."

[@andressaid6410]

Thank you!

[@TheStatsFilesDawnWrightPhD]

Thanks for the positive feedback!

[@MagicCPA1]

Thank you very much for sharing! I've been looking for something like this for a long time----and really didn't want to use an add-in!

[@Kig_Ama]

ok in this example we estimated for three different scenarios (low, most likely, high) cash flows and asumed a triangle distribution. its a common situation when a bunch of analysts estimate free cash flows of a listed company at the stock echange. as we all know analysts can make mistakes and we can derive standard deviation by looking at the estimation of each analyst. my question is whether its possible to use this standard deviation as an addon to the triangular distribution in order to consider forecast uncertainties.

[@TheStatsFilesDawnWrightPhD]

I think you could model each of the values (low, most likely, high) with a normal distribution or other probability distribution.

[@Kig_Ama]

@@TheStatsFilesDawnWrightPhD ty, but what if i don't know what each analyst estimated, if i only know the three estimate values best, base and worse of the analysts in average and the standard deviation of the forecast predictions? if it's not possible to calculate an expected cash flow for a normal distribution? for instance s&p provides the estimated low, medium and high averages of free cash flows of a stock company for the next 10 years and the standard deviation based on analyst predictions but doesn't provide the forecast of each analyst separetly as data. so under these circumstances the triangular distribution might be still a good fit. isn't it better to use the triangular distribution and then to use the provided standard deviation as a forecast error for a shift of the triangular distribution in order to consider the forecast error. if yes my question would be then how to shift the triangular distribution appropriately? may be the shift logic has to be normal distributed?

[@Kig_Ama]

@@TheStatsFilesDawnWrightPhD What I've done now is as follows. In the first step for each simulation scenario output I assumed a triangular distribution like u've done in the video and in the second step I've multiplied the outcome with what I call a shift factor while I've defined the shift factor as normally distributed with a mean of 1 and a standard deviation that was provided as the forecast differences of the analysts. In the third step applying this method I've simulated 50,000 outcomes for the cash flow. How does it sound to you? Does it make sense? Looking forward to your reactions.

[@Kig_Ama]

can u tell me how u highlight your mouse pointer in excel wit that yellow transparent circle form?

[@TheStatsFilesDawnWrightPhD]

I use Camtasia to edit my videos. That allows me to highlight my cursor.

[@Kig_Ama]

@@TheStatsFilesDawnWrightPhD thank you very much.

[@JalanJalanAjah]

Thank you so much ❤️

[@YvesAustin]

Great video and very dense series of videos; congrats! Would it be possible to share the link to the original formula for the triangular distribution model used in MC simulations which you reference to here? I am trying to replicate it; Thank you so much :)

[@TheStatsFilesDawnWrightPhD]

Yes, I just updated the link in the "Show More" section to the blog post where you can download the Excel workbook.

[@YvesAustin]

Thank you - straight forward algebra - a little rusty :)

[@Kig_Ama]

Why do we even havet to use the tirangular distribution for estimating cash flows and not the normal distribution? I don't get it.

[@TheStatsFilesDawnWrightPhD]

It's used for a different situation - Best case, Most likely case, Worst case.

[@Kig_Ama]

@@TheStatsFilesDawnWrightPhD I understand, but why don't u use instead of a triangle distribution a normal distribution for instance or let me ask you this way: What distribution would you choose for modelling the estimated free cash flows of a company like Microsoft, Apple, etc. that is listed on the stock exchange and why? Would u still choose a triangular distribution for this purpose?

[@timianalytics7150]

@@Kig_Ama Well I believe it depends on 2 things. 1. The available parameters. you can't be forced to use a normal distribution if you don't have the standard deviation statistics. 2. This is used based on the assumption that the data follows a triangular distribution. And really, it may be the conventional distribution that this scenario follows. For instance, stock volatility is assumed to follow a log-normal distribution