Monte Carlo Simulation For Any Model in Excel - A Step-by-Step Guide
ฝัง
- เผยแพร่เมื่อ 17 พ.ค. 2024
- Download Excel file and eBook | store.mintyanalyst.com/l/mont...
Subscribe for FREE on Substack | mintyanalyst.substack.com/
Buy me a coffee | www.buymeacoffee.com/mintyana...
Read more on Monte Carlo Simulations and download a sample model here:
magnimetrics.com/monte-carlo-...
If you're enjoying this video, it'd be amazing if you could leave a comment to let me know your thoughts, give it a thumbs up to show your support, and if you haven't already, consider hitting that subscribe button. Your active involvement not only brightens my day but also plays a pivotal role in spreading my content to more people. Thank you for being a part of my journey! 🌟
[FREE Downloads]
Financial Modeling Excel Templates | store.mintyanalyst.com/
Monte Carlo Simulation in Excel ebook | mintyanalyst.com/monte-carlo-...
Excel Benchmark Analysis Template | mintyanalyst.com/free-excel-b...
Cloud FP&A Platform for SMEs and Solopreneurs | magnimetrics.com
Sign Up for Magnimetrics for FREE | app.magnimetrics.com/auth/inv...
Excel Add-in for FP&A professionals | magnimetrics.com/magnimetrics...
Learn with me on Udemy:
Create a Dynamic 5-Year Financial Model in Excel | www.udemy.com/course/create-a...
Create a 3-Statement Budget Model | www.udemy.com/course/create-a...
Create a Sales Dashboard in Google Data Studio [FREE] | www.udemy.com/course/create-a...
Introduction to Excel for Finance and Accounting [FREE] | www.udemy.com/course/introduc...
Read on Medium | / dobromir.dikov
AI-Powered Business Social Media Posts Generator | mintyposts.com
AI-Powered IFRS/GAAP assistant | gaaplify.com
Music | TH-cam Audio Library
#MonteCarloSimulation #Excel
Excellent description, demystifying a powerful yet simple concept
thanks, glad it was helpful :)
Nice work explaining the process and taking the appropriate time to allow people to follow and perform the work. Keep up the great work!
Glad it was helpful!
A great introduction to excel concepts for model testing using Monte Carlo Analysis.
Thanks, David :) There's also an article linked below the video, where you can download the model.
This was really well explained and I was able to modify the free download template to develop a selling strategy for stocks. By adding a column of prices to the Monte Carlo output, this can then be shown as a histogram with the number of units to sell in each price range.
Incredible example and presentation, thank you for editing this, very much appreciated helping on my first MC simulation
Very welcome! Happy you found it helpful :)
THANK YOU! This video was EXCELLENT! Much better than other examples I found. I really appreciate covering multiple distribution types!
Thanks, glad it was helpful 😊
Thank you for this amazingly simple explanation!
Thank you 😀
Fantastic, aimed just at the right level of difficulty for me. Actually, I'm relieved to find the process simpler than I feared it would be. Can't wait to play around with some ideas of my own based on this intro.
Hey, thanks for the comment! It's great you found it useful, and good luck with running your own simulations. I honestly think the 'complexity' around Monte Carlo is very hyped and overstated. Running the simulations is easy. The hard part is figuring out the distributions and probabilities for the variables ;)
Dobromir, thank you for posting this. This is the only source I could find on TH-cam on Monte Carlo Simulations based on Triangular distribution! It was extremely helpful.
hey Shawn, thanks a lot, really glad it was helpful!
@@MintyAnalyst my friend I have a question for you. Why did you NOT need a Standard Deviation to calculate Monte Carlo simulation using Triangular distribution?
@@MrChigapo it's just the way you can calculate it in Excel. I am not really great with statistics, so I tend to stick to the minimum I need to use in Excel, and not dive too deep into the statistical calculations, as I tend to get a bit lost there... maybe the Wikipedia page can provide more info on how the standard deviation fits into the whole triangular distribution thing: en.wikipedia.org/wiki/Triangular_distribution
Really good concept .
Thank you!
Really well explained, and I was able to follow along and implement this which really helped me understand it better. Great video.
Thanks a bunch, happy it was helpful :)
This is a perfect explanation. Thank you.
You're very welcome!
Dude! This made my Job today so much easier! Thank you a lot and God bless you! Keep up the good work!
thanks, glad it was helpful
Nicely explained. Learnt a lot. Thank you
Glad it was helpful! ✨
Wow! Thank you so much for this.
You're very welcome! 😊
Greetings from Varna. Thank you for the nice done video. I just found you by accident while studying CFA and I'm starting to follow you.
Awesome, hope you find the videos helpful :)
Great tutorial. I stumbled my way through some Monte Carlo simulation out of curiosity years ago but couldn’t remember the details. Thanks a ton.
Happy it was useful!
awesome, thank you for sharing this. Clearly explained and really interesting.
Glad you found it useful! If you want to take a look at the excel file, there's a link to an article in the description, where the file can be downloaded for free (no e-mail or registration needed), and happy modeling! :)
@@MintyAnalyst thanks :) I'll take a look, will check out your website too.
Thank you very much!
I thought it would be impossible for me with my limited math knowledge to create a MC simulation.
Now it only will need time. Really great!
glad it was helpful!
Thanks, that was very informative.
glad it was useful :)
3 days ahead of my exam, and i am all cleared about the concept. Thank you very much
glad it was helpful :)
This is awesome, thanks for the knowledge!
My pleasure!
Awesome stuff. I will patiently follow this and build my very first Macro.
hey, that's awesome! learning VBA and writing your own macros is quite easy and very powerful. The language has been around longer than me, so there's a ton of information and free learning resources out there! Have fun with it!
Thank a lot! Very neatly done.
Thank you Amit :)
Amazing video! Thanks for sharing, this will help me a lot in future decision makings! 👍👍
Thanks a lot! Glad it was helpful!
Very helpful, thank you for sharing this with us sir
Thanks a lot, glad it was helpful :)
Thank you very much, Very well explained, Now I will try to solve my problem.
Glad it was helpful :)
Great Video - THX buddy
Thank you, the Video was excellent.
Glad you enjoyed it!
Holly Shhhhtt Deam tht amazing.
Great work
Thanks, glad it was helpful :)
Excellent!
Thank you, I'm glad it was useful :)
Excellent
Thank you so much 😀
You have amazing teaching skills, but I have to make a special shout-out to the soundtrack that has been picked for this video. Usually, in videos like these, there is always a mid to up tempo song that, even being at a really low volume, makes the listener more "apprehensive" when watching the video and creates a difficulty to absorb the presented knowledge.
But, man, while watching this video I've been really, really relaxed with this soundtrack. It was way easier to learn. Awesome idea.
Thanks, Pedro! That was my initial idea, but due to people complaining that music is distracting, I stopped adding any background music to my videos :D
@@MintyAnalyst Oh, really? :( What a pity, because it really helped me paying more attention to the video. And, well, if it most of the people said it, you are absolutely right in attending their demand... maybe I'm just the minority, hehe. =)
@@pedrocolangelo2458 you are not alone, I also prefer the music ;)
Thank you🙏🙏
You're welcome 😊
By the way, I also have an article on Monte Carlo, which has a downloadable model that you can look at:
magnimetrics.com/monte-carlo-simulation-in-financial-modeling/
And here's another one on Probability Distributions and how to use them in modeling:
magnimetrics.com/introduction-to-probability-distributions-in-financial-modeling/
thank you thank you
You are very welcome 😊
Thanks a lot! I found the video to be very informative. I have a question - How would you define pdf of lognormal distribution in excel?
Hi there, thanks for commenting! I am not going to pretend I have a good understanding of lognormal distributions, and I want to avoid misleading you. I found two good articles on that online, you can check them if you like:
www.educba.com/lognormal-distribution-in-excel/
www.real-statistics.com/normal-distribution/log-normal-distribution/
Good luck :)
Thanks for a great video, very clear tutorial. I'm wondering if you could tell me how to model variables that would follow a lognormal distribution? .... thanks!
Hi there, you can use the LOGNORM.DIST function in Excel. Here's an article on probability distributions, it also has an example on Lognormal Distribution - magnimetrics.com/introduction-to-probability-distributions-in-financial-modeling/
Great man! Right to the point. Keep in contact!
Always!
This is great, easy to follow. Question for you, how can you run it for 02 output variables, in the same simulation ?
Thanks, Jaimie, if you want to keep track of a second output variable, just copy the code within the Iteration loop and adjust it so it saves the iterations of this variable as well. Or you can copy only the first line, change the cell reference to be on the next column to the net profit, and use the same numbering. Good luck and happy modeling! :)
Thank you for the video. It was great to test this on an excel. Could you please help for How do you reset the iteration in the macro to run it afresh several times?
thanks, Amish! Once you re-run the macro it will basically overwrite the previous one, or you can change the reference to where it saves the iterations and keep all previous ones in a separate column
Thanks a lot dude, you make it clear for me. I am wondering what if one of the model following Weibull distribution, what should we do to simulate it? is it the Alpha & Beta parameters or likewise the normal dist which use stdev and mean (weibull also have stdev and mean). Anyway, great video dude, thx again !
Hi there, glad you liked the video. Sorry, but I am not very familiar with weibull, so no idea to be honest. What I usually do when I need to figure out a specific distribution, I just google how to calculate a random number in such a distribution and go from there... sometimes you can find excel articles about it, which is even better than math articles :D
Hello Dobri, very nice and succinct presentation. I do have one question though with regards to macro button. Somehow when I click the button it doesn't populate the cells with random net profits. Your advice is appreciated.
You need to right click the button and assign the macro to it in order for it to work. Hope this helps :)
Hi, I love this video, very informative. Do you have any videos related to Monte Carlo simulations for a convertible debenture/bond?
Hi Victor, glad you liked the video! Sorry, bonds are not my strongest side 😄
@@MintyAnalyst no worries.
@@velaraj I would split it into two separate models, one containing the bond and the other containing the option. For the option you can just pull in the black scholes model for your assumptions and mechanics and then decide what distribution type you want to make each variable. For the bond I would recommend discrete for default rate and maybe triangular for interest rates. I think the value of the convertible note would then be the sum of the two components (although this could change depending on any unique additional covenants).
HI! Thank you for this excellent lesson. I found it easy to understand and follow along. Just a question tho, when I run the simulator and put a smaller number on the counter each time, (ex. from 10 iterations to 5... ) the previous info on 10 iterations does not get deleted and only the 5 iterations were refreshed? Is there a way to fix this? Appreciate your help.
Yes you can! Just add the following to the VBA code (before it runs the iterations) and it should work:
Range("L13").End(xlDown).Select
Selection.Clear
Range("M13").End(xlDown).Select
Selection.Clear
This is fantastic! Is there any way to set.seed for the iterations to make it replicable or run multiple runs of the iterations? assume this could be done by copy+pasting values from the "Net Profit" column, but wondering if there is a way to code this in the Macro?
I don't think there's any way to set a seed and make it rerunable, that's the nature of it being trully random. To be able to do so, you will have to write your own random algo that uses a seed andresults can be reproduced.
Thanks for the informative video!
Do you have any guidance on how to use Monte Carlo simulation with stock valuation?
Hi Kev, I don't have experience with stocks, but if you follow the same approach, and just apply the ranges for your valuation assumptions, you should theoretically be able to run a similar model to the one in this video. However, I can't guide you on which assumptions to use and what ranges, sorry ☹
@@MintyAnalyst No worries man and thanks for the prompt response !
Hey! Just curious about something. How did you go about choosing the distribution type for each variable? For instance, why did you pick a Triangular Distribution for sales? Could you use a normal distribution instead?
Hi Andrew, for the video I just used different ones, so that I can illustrate a few. In reality it all depends on what information you have for the value you are modeling. If historical sales data follows a normal distribution, you can use this one as well. What I usually do is plot the historical data on a chart and try to see if it looks like the chart for some distribution. To be honest, picking the distributions is the hardest part for me, still learning on that front :)
Good luck with your modeling, and if you want to read more, here's an article on probability distributions and how to model them in excel - magnimetrics.com/introduction-to-probability-distributions-in-financial-modeling/
Thank you very much! However, I would like to learn more about the Monte Carlo methods. Which books or websites do can you suggest I use to learn? I have a basic understanding of statistics and probability.
I would suggest going through the links and referenced materials in this Quora answer: qr.ae/pvKk65. It's a bit old, but you can also look on amazon/google, there's a plethora of books/tutorials, based on where you want to run the analysis (e.g., excel, python, etc.)
There's also a Wiley book on the matter. This post has a link to a PDF version of it, but you can also grab it on Amazon: www.researchgate.net/post/What_are_the_best_books_tutorials_for_learning_Monte_Carlo_simulation_for_absolute_beginners_on_their_own
@@MintyAnalyst Thank you a lot, I appreciate the feedback.
Hi. Great video but i have a couple of questions:
1. How do you define the min and max ranges for sales? I mean its possible that we go below the minimun range or above the maximun range, so what's the methodology to put the best range possible in the model?
2. How do you choose the propper distribution?
Hi there, thanks for the questions. Here's an attempt to answer :)
1. The min and max ranges are defined based on experience and historical data. In the current context I just picked the numbers, to illustrate the model concept. In reality, I would probably look at how sales performed over the last years/periods, combine this with my expectations for performance based on market knowledge and figure out the range like that. It's not a straightforward task and there's no easy answer, it requires market knowledge and knowledge of the company (or asking the sales team to help with that).
2. Choosing the distribution is also a matter of judgement. Based on the data you have for each item, you can usually see what kind of a distribution might be a good match. Some of the items can have different distributions in different circumstances. But this requires experience and knowledge of the company as well. But mostly, go with common sense... if you can have either A or B outcome, then it has to be Discrete distribution, if you have Min and Max, then it's probably uniform, if you have Min, Most likely and Max, then it's Triangular... you have to develop a feeling for it.
You can also read more on distributions in this article on our blog: magnimetrics.com/introduction-to-probability-distributions-in-financial-modeling/
Good luck and happy modeling!
@@MintyAnalyst thanks for the detailed response.
Thanks for the Video! For the triangular formula, for some reason if the random number is above 0.71 the formula outputs a number like -1000000, any ideas why this might be happening? The formula is identical
no idea, it's not happening in my file... 😞
I have come across a research paper in which they have developed regression models using past data (uptil 2016). The pdfs of variables were already known from literature review. For 2017 onwards, they utilized the forecasted values as the mean of the distribution and took 10% of that mean as standard deviation for each year, and performed MCS to calculate annual uncertainty. Is there any relation or basis for taking 10% of mean as SD for future uncertainty analysis? I have analyzed the past data as well SD is nowhere near 10% of the mean value.
Hi there, I am afraid this is a bit above over my head. I am not that knowledgeable in statistics, and I can't figure out the exact scenario from your explanation. I don't think I've seen 10% as a standard anywhere, so I would expect the research paper to state how they came up with the 10%, but I might be wrong...
great video !! can we download the excel sheet ?
You can download here: store.mintyanalyst.com/l/monte-carlo-sim
I also added to the description :)
Excellent example explained in a very lucid way. You may want to completely stop the background music which is very disturbing. Overall great stuff.
Thanks for the positive comment :) Yes, I now know the background music was too much, but sadly there's no way to remove it. However, I no longer blast music like that in my newer videos :D
Sir, users may insert a column or a row. So, the macro would use a wrong cell for iteration or any cell that we typed in VBA. Is there any solution for this issue? Thanks for the video.
Hi there, yes, the macro won't work if you are not referencing the correct cells. One way to do it is to restrict users from changing the document by protecting the sheet.
What I'd probably do is ask the user to select the cells when the macro is run. There's a RefEdit field when you build VBA forms, you should google it, it's pretty easy to set up and will allow the user to select the range to run the macro on.
I'm getting negative results on the Net Profit, using your model to practice. But I used the "What If Analysis" to simulate the results. Anything that I could have done wrong?
Hi there, unfortunately, I have no idea what you did in the What If Analysis, so no idea where things might have gone wrong. That's the whole point of running Monte Carlo. In some combination of variables you may get negative results, but as you pile on more and more random iterations, you see a pattern emerge and you can rely on the average results.
How do you know the MIN and Max price is a Uniform Distribution? Are these the only prices availible?
The idea here is that you pick the distribution based on what you know for the data you have. In this case I picked uniform distribution for the sake if including it in the example. But in reality it may be anything else. However, if I only have a min/max and no other data, I'd assume it's a uniform distribution.
@@MintyAnalyst that makes sense thanks
Thanks a lot! BUT i am currently going crazy since my outputs are the same value in the whole row. I have placed every variable in the exact same row/column as you, but it still outputs the same value... PLEASE HELP. If i update my sheet, then run the simulation again it outputs the new NPV, but only that one. So in other words, i don't think the makro works somehow...
SOLVED: If someone has this problem, it's because you don't have an automatic update in your worksheet! It then just uptade once, and outputs the same variables..
@@ByVoiLeZ hey, thanks for sharing that! I saw your comment and was trying to replicate the issue, but never thought of that! 😁
can you do a tutorial on how to choose probability distributions for variables?
hi there, I've looked into this before and it turns out it's really up to your understanding and knowledge of the specific model. Sometimes a price might have a normal distribution, sometimes you may go with a triangular one. It all really depends on the circumstances, there's no one solution that fits all cases. If you are looking to learn more about various probability distributions, you can take a look here: magnimetrics.com/introduction-to-probability-distributions-in-financial-modeling/
can anybody explain the formula in M6 (Iterations)? I understand that it is count(M13), but that does not make sense to me since it does not react to the macro. I am using german excel version, is there something different?
Hi Willi, the formula in M6 is =COUNT(M13:M1048576), so it counts all entries the macro makes all the way to the bottom. When you set up the counter in M5, the macro will iterate up until the iterations in M6 reach the same number as the counter in M5. That's why you have to clear up all the iteration below if you want to start the next model run with less iterations than before (lower counter number in M5). Hope this helps!
@@MintyAnalyst Thank you, Dobromir. I really appreciate your answer.
to calculate tax in the traditional format why is the decision tree formula used? Looking for an easy explanation, the ones available online are difficult to comprehend.
Hi there, there's no specific rule to use the decision tree logic here, it just makes more sense for me if we are trying to come with a directional model that shows the most likely scenario. Grab the most likely value for each and see what it leads to. However, if I am creating a proper financial model, I would have some assumptions and pick one or the other, I wouldn't go with a decision tree calculation. Hope this makes sense ✨
Thanks a lot very helpful. Is it possible to send the template to me?
Hi Hamed, I have an article on Monte Carlo, which has a model that you can download:
magnimetrics.com/monte-carlo-simulation-in-financial-modeling/
And here's another one on Probability Distributions and how to use them in modeling, that might be helpful with Monte Carlo simulations:
magnimetrics.com/introduction-to-probability-distributions-in-financial-modeling/
the correlation between sale prices and sale volumes is not considered
Yep, absolutely right! I did not consider any correlations between any of the variables, as it was meant to be a simple and straightforward example. Once you start adding correlation, it gets much more complex.
Nice job, but why not use the what-if function in Excel, it would do 90% of the job?
What-If analysis is indeed a great analytical tool in Excel, but you can't really perform a Monte Carlo simulation with it and find the average result over thousands of iterations.
@@MintyAnalyst Generally not true, you can do the exact same thing you did. The exact maximum number of iterations it is limited to I actually don't know.
@@Schlumpf.Meister I may be wrong, but I don't think what-if can give you a 1000 different results listed and then you can analyze them separately. Please, share a link to some resources that prove me wrong, it would be great to run monte carlo without VBA...
Why on earth do you think it’s a good idea to play music while you’re explaining something?
Hi Peter, I honestly don't know why I did that :D