A First Monte Carlo Simulation Example in Excel: Planning Production with Uncertain Demand
Вставка
- Опубліковано 9 вер 2024
- Enjoyed this content & want to support my channel? You can get the spreadsheet I build in the video or buy me a coffee! Links below:
Buy me a coffee: www.buymeacoff...
Buy complete spreadsheet (must buy simulation add-in first; see below): www.buymeacoff...
Buy me an item from my wishlist: www.buymeacoff...
If you're not already familiar with Monte Carlo simulation, I recommend you watch my conceptual video first: • Introduction to Monte ... .
The simulation add-in I am using in this video: treeplan.com/s... (I'm not sponsored by this company).
This is the first example of a Monte Carlo simulation I show to my students. Given future uncertain demand for a product, and assuming you need to commit to a production quantity in advance, how to choose a production quantity that will generate a high expected profit and keep risks in check?
Thank you, I appreciate the time you dedicate to explain every little detail.
My pleasure!
these are some of the finest contents I found on UA-cam. Really appreciate your hard work !
Better than my actual college courses. Thank You!
You’re welcome! Thank you for watching!
Thanks again! I enjoyed a lot watching your video on MC simulation!
Glad you enjoyed it!
Thank you so much for your amazing contents !
Thanks for sharing. I hope you will post more business solution with monte carlo simulations technique
I have 3 videos with Monte Carlo simulation examples. Did you watch all of them? If not, take a look inside my Excel Models playlist.
@@TallysYunes I just watched 1. I'm using simulation for my business
WoW!!
This is fantastic, Professor! I'd like your thoughts and advice on how to do an MCSim with triangular distribution where the lower bound is negative? Thanks in advance!
What are the 3 parameters of this triangular distribution?
Essentially I’m trying to model future growth in an industrial company that has historically (15 years data) varied from -2% to +8% with median at 3%.
If the triangular function doesn't allow you to enter a negative for the low end, you can shift everything up 2% points and subtract afterward. For example: Cell A = randtriangular(0, 0.05, 0.1), and B = A - 0.02. This will make cell B behave as if it were a randtriangular(-0.02, 0.03, 0.08).
@@TallysYunes Thanks, Prof! Will try this.🙏
Thanks so much for uploading this video. Could you please advise 1) what is the substitute formula for RANDTRUCNORMAL and 2) is there a way to obtain the seed number and reuse it through a normal Excel Monte Carlo function (is data table able to do the same)? Much appreciated.
I don't understand what you mean by "substitute formula" in your first question. And, as far as I know, I don't think there's a formula that returns the seed number that was used in the previous run of the simulation, so you need to copy it by hand, at least with the add-in that I'm using here (SimVoi). If you use other Monte Carlo simulation add-ins, like @RISK and CrystalBall, they have an added functionality that allows you to automatically run several simulations while changing some of the inputs and the add-in takes care of keeping the seed constant for you. I don't use those add-ins in class because they don't run natively on Mac computers and more than half of my students use Macs.
@@TallysYunes Thanks so much for your detailed response. Sorry, I mean is there an inherent Excel function / formula to achieve the same as RANDTRUCNORMAL and ADD-IN do? Any way to achieve the same if no ADD-INs?
As far as I know, the answer is no. You need an extra add-in to get that. The add-in I use is pretty inexpensive, in case you're interested in trying it. Just go to treeplan.com. If you are a student, there are good discounts. (I'm not sponsored by that company.)
@@TallysYunes thank you :)
@@TallysYunes one thing I dont quite understand is why realised demand cannot be the same for both production run? The estimation of demand remain the same in this context. So only production gets changed. Is it right we maxmise profit by making optimal production when demand is sort of fixed in this context.
HI , The equation "=ROUND(NORM.INV(RAND(),mean,std.dev),0)" in @risk 8.1 software will be the right alternative to the command " =round(randtruncnormal(mean.std dev,min,max),0)" in simvoi.? Could you please check and confirm the alternative formula.Thank you.
I'm not familiar with @risk, but I can tell these two are not equivalent because yours is not using the minimum and maximum boundaries. It's very likely that @risk also provides a truncated normal function. Just Google it. Here's something I found: kb.palisade.com/index.php?pg=kb.page&id=33
@@TallysYunes thank you .
when I add randtruncnormal function it gives error, please help
It's probably because you don't have the simulation add-in installed in your Excel. Read the description box below the video. There's a link to a website where you can download it.
It looks like the randtruncnormal function is only available through SimVoi.
Yes. In order to use these simulation functions, you need to install an add-in that provides them. SimVoi is the one I use because it runs natively on both Windows and Mac computers and isn't too expensive. If you have a Windows machine, other good add-ins are @Risk and Crystal Ball.
@@TallysYunes Thank you. I'm buying SimVoi today. Thanks for the helpful video. It took me about 30 videos to find one that explained what I was trying to do, and yours was it!
Thank you for the feedback. I'm so happy to hear my video helped!
@@TallysYunes One further question if you don't mind. I purchased the SimVoi plug-in and managed to accomplish my goal. However, I would like to be able to share my Monte Carlo simulation (updating using F9 key, not the results table) using the randtruncnormal function with another person. Do you know if it's possible to do this without them also purchasing the software? Thank you for your time.
To be able to use the functions provided in the add-in, the person needs to have the add-in. You can run the simulation on your computer and share the results with them.