Microsoft Excel Tutorial - Random Walk Down Wall Street. Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In this episode, we will be discussing a trick that I learned from Simon Benninga, the author of the best-selling finance book about Excel. I had the pleasure of meeting Simon at Model Off a few years ago and he shared with me a story about a popular game called Penny Pitching that was played at his private school in New York. This game involved flipping a penny and depending on the outcome, either player A or player B would double their money. However, this game was not only popular but also illegal, so Simon decided to model it in Excel using the RAND function. For those who are familiar with the RAND function, it returns values between 0 and 1, making it perfect for a 50-50 proposition like Penny Pitching. By using the IF function and checking if the RAND value is greater than 0.5, we can simulate the game and see the cumulative results. To visualize this, we can create a chart and imagine that the results are from a hot young stock analyst on Wall Street who claims to have a winning streak. However, as we all know, past results are not a guarantee of future results, so we can press F9 to see how the results can vary. But the real trick here is taking this simulation to the next level. Instead of just modeling a few rounds of Penny Pitching, we can use a data table to model an entire year's worth of results. And the best part? The top-left corner cell of the data table is left blank. This may seem strange, but by leaving it blank and using the RAND function in the column input cell, we can run the simulation for 30 years and see the statistics for each year. This is a great way to do Monte Carlo simulations or any other random sampling in Excel. I want to give a special shoutout to Simon Benninga for sharing this amazing trick with us. Unfortunately, Simon passed away before he could see this tip included in my book, but I am grateful to have known him and to have learned from him. So, in honor of Simon, let's keep his memory alive by finding creative ways to use the data table with a blank corner cell. And if you want to learn more Excel tips and tricks, be sure to check out my book "MrExcel XL" which includes this trick and many more. Thank you for tuning in and I'll see you in the next episode of the MrExcel podcast. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #excelcharts #simulation #montecarlo #whatifanalysis #randomdata Table of Contents: (00:00) Random Walk Down Wall Street (00:25) Explanation of Penny Pitching (00:44) How to use the RAND function to model the game (01:06) Difference between RAND and RANDBETWEEN (01:16) Using the IF function to determine the outcome of the game (01:30) Creating a chart to visualize the results (01:56) Comparing the results to a hot young stock analyst on Wall Street (02:07) Reminder that past results do not guarantee future results (02:22) Using a data table to model a year's worth of results (03:17) Using a blank cell in the data table (04:06) Explanation of the data table results (04:34) Tribute to Simon Benninga and his contribution to the book (05:54) Using the data table to model multiple years of results (06:19) Recap (06:49) Clicking Like really helps the algorithm This video answers these common search terms: what if data table analysis in excel what if analysis table excel what if analysis excel what if analysis function in excel what if excel what if data table excel sensitivity analysis how to use data table what if analysis in excel how to use data table in excel what if analysis how to use what if table excel how to what if data table in excel how to use excel data table what if how to do what if data table in excel how to use what if data table in excel what if analysis in excel table how to excel what if data table multiple formulas what is what if excel data tables used for in excel how to make a what if data table in excel how to create a what if data table in excel how to use excel what if data table how to do what if analysis data table excel how to use what if analysis data table excel how to use what if analysis in excel data table how to set up a what if analysis in excel data table what if analysis and goal seek in excel what if analysis and scenario manager in excel what if analysis definition excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153862/

Microsoft ExcelExcelBill JelenMrExcelModeling the stock market in ExcelWhat is a random walk down wall street?Past results are not a guarantee of future returnsexcel videosexcel helpful videosexcel tipsexcel helpexcel tips and trickswhat if data table excel sensitivity analysishow to use excel data table what ifhow to use excel what if data tablehow to set up a what if analysis in excel data tablewhat if data table with random numbers in Excel