Monte Carlo Analysis using R and Power BI

hey guys this is Ryan the geeky VP and today I'm going to show you how to use power bi and R to do a Monte Carlo analysis it's really cool so stick around I think you'll like it okay so if you've ever done a Monte Carlo analysis or simulation basically you're trying to take randomization and apply it to some kind of model you want to do it as many times as possible to see what different kind of results you get so I'm going to show you how to use that and in this instance I'm going to use a an example here where we're gonna do a promotion for credit cards okay so my CEO has come to me and he wants to run a promotion and so basically what it is is he wants to see an increase in usage for credit cards and he believes that if he pays $100 to every card that increases their usage by a certain percentage then he'll pay them but more often than not people will not meet the lemon but they will still increase their usage so here's how this works I'm going to kind of show you a real quick example here the number of cards at tranzact we're going to use a hundred fifty thousand and how much do they transact every month is $75 so you take that 75 times 12 to get you know the total that they would spend all year and then we have something called an interchange rate okay and this is the amount that this company or this bank would get for you multiply that by all the transactions and that's the income that they would get okay so we have that and then we're expecting the average increase of usage to go up by 50% per card okay and of those cards only 5% are going to meet the minimum and we're going to pay those cards hundred dollars okay so if that makes sense let's go on to show you what the results would be so prior to the promotion what you're doing is you're taking so we have ABCD and after taking the number of cards times their usage times at the interchange rate times twelve okay when we do that we expect to earn if we don't do anything 2.4 million right okay but then what happens when we do the promotion so we have the same thing again what they would have done times 1 plus D D is the expected increase so that's the increase okay but then you have to subtract out how many cards are we paying that $100 to so minus the number of cards times the promotion cost times the number of cards that would meet that minimum okay so even after all that we see that we have like a 2.9 is going to be our results after the promotion so 2.9 minus 2 2.4 and that gives us around $500,000 increase four hundred sixty-five thousand dollars to be exact okay yeah that sounds great okay but the problem is you are using one expected value for every single variable and that can get you into a lot of trouble so what you want to do is give your CEO here's the most likely outcome 465,000 but there's also a likely outcome that we could lose money or make a lot more money so your CEO says okay I get that so he's going to give you a couple of goals here and we're gonna go through that the first one is he wants to make sure that you're 75 percent certain that will make at least $200,000 okay that's a lot less than what the expected increase is but he wants to make sure we at least make some money and he and his threshold is $200,000 okay the second goal is going to be all right well I also don't want to lose any money right so he wants that percentage or the probability of losing money to be less than 5% okay we need to be able to tell him that what set percentage and then third one is we you know what is he just wants to know what's the probability that's gonna make between you know a couple of dollars you know what's that that range so his range is as long as it makes between a hundred thousand and four hundred thousand just give him that idea what's that probability all right so how do we do this let's go over to our okay and I should tell you this I'm not a data scientist I'm not an expert and are at all but I'm just from the little bits and pieces I've picked up I found there's a really cool way of doing this analysis so the first thing we want to do and I've already got it laid out here so we want to create the variables okay we're gonna define the variables and we're going to use something called our norm normal distribution and this might not be the right distribution for all of these variables but we're just going to use it in our example today so a member of variables we have the number of cards so the number of cards would be our norm okay and I I'll just retype it cards to okay and you're going to define a variable you always have to do the little arrow like this we're gonna do our norm okay and then within the brackets you tell it how many trials you're going to do so we're gonna do a million trials okay then you have to tell what the mean is okay and I said hundred fifty thousand and then the standard deviation okay so we think that you know there's a 68% of cards will fall within plus or minus five thousand of that 150 thousand right okay so I'm just gonna quickly get rid of that and that kind of explains how each of these works we have our cards we have our usage which is $75 and a standard deviation of 10 so one standard deviation says that cards are going to use go through 65 to 85 dollars worth of transactions each month as opposed to just saying $75 we have the rate that's the interchange rate again 1.8% but we've got a standard deviation of 20 basis points what's the promo cost it's going to be $100 no matter what right so if you make the minimum it's $100 there's no distribution there it either is or it isn't okay then the increase this is the increase in the usage okay we said it would be a 50% increase standard deviation of 10% the minimum is that the percentage of cards that meet the minimum is going to be 5% and again a 1% standard deviation okay so there are all my variables and then we're gonna set up what the income would look like prior so remember it's cards times usage times rate times 12 12 for 12 months what would it be after the promotion same thing times the increase okay - what it costs us so the number of cards that meet the minimum so cards times minimum times the promo okay hopefully you're following me here it's really basically it's algebra here but it's really kind of cool that you can do this within R so then the final variable I have here is what is the income after okay and then what is the income prior okay so to run any scripts that you have an R you can go to a line and you would hit control enter or you can highlight everything and hit control R on this last line here is a line that I use - it's going to put it into this folder here and I've already done it so I'm gonna go ahead and delete this file all right and it will create this file for me it'll automatically export it for me so what I'm going to do is I'm going to highlight everything all right I'm gonna have it run all this by hitting ctrl enter give it a couple seconds and boom you just if you saw that it autumn I could place it out there for me okay so are we're done with our we have our our analysis a lot of different trials okay and now let's go to power bi I've already set this all up so this time I'm going to go ahead and hit refresh and remember I've got a million trials so all those variables standard deviations expected values and it goes through a million different ways so if you look at this distribution you know this is the most likely outcome right here you know we're talking about right around four hundred and sixty four thousand actually if you look at Excel we thought it would be four hundred sixty five thousand and according to power bi it's pretty much right there almost four hundred sixty five thousand but look at this distribution there's a good chance you're gonna lose a lot of money here right or a good chance you're gonna make a good chunk of change right so we want to really understand that okay so I'm not going to show you how to do this but I connected to that CSV file I pulled it in and then I made a few Dax formulas to help me get through this and one little tip I have for you so here's my CSV file the data set that came in but and in that I have a result which is the you know how much time the increase I'm going to make have the number of trials and then I put it into bins so I did the grouping I did a new group I'm gonna hit edit group so you can see what I did all I did was I wanted to bin type just be the number of bins and two hundred counts so that just means put it in into two hundred different bins it just makes it look a little bit better okay now they have that here are the DAX formulas that I use the first one you want to do is how many trials okay we use count a just to count the number of trials okay and that number is going to change as I change this filter here let's filter and I'll create another one here real quick it's basically just taking filter and putting the result into it so you can change that okay we'll delete that one and we'll go back up to that filter so basically I'm saying the results can be anywhere between nine hundred and fifty thousand dollars loss to 2.6 million dollars in income okay so that's a wide range we have the ability to adjust this and see what the outcomes would be okay so I have the number of trials again and that changes as I change the results notice by going from 500,000 to the max now I went from a million trials to four hundred twenty eight thousand trials okay the next thing I want to show you is this formula here this is all trials and all this is is the same thing I'm using the number of trials that measure I just created and using the all function so that no matter what my result is here or how my filter is laid out I always have a million trials okay and here's why it's important to do that it's important because then I take the percentage of trials which is the number of trials divided by all trials okay and that gives me an idea of what the probability is so as I want to look at remember goal number one what is a certainty that he'll make at least $200,000 we can come up here to our filter change in number two hundred thousand and right there we see that seventy six point four nine percent of our trials will make us at least two hundred thousand dollars so we can go back to the CEO and say yeah there's at least at 75% chance seventy six percent so it's not you know that much over your threshold but yes it'll it'll make it it'll make your threshold okay so you get where I'm going with this now if I remove that filter you could see again the average result almost four hundred sixty-five thousand so then let's look at the other Gaul it must there must be less than a 5% chance of losing money okay so if we come over here and hit the max of 0 and enter there's actually an eight point seven eight percent chance that we're gonna lose money on this deal all right so that does not pass the test okay so you could say yes it passes test number one does not pass test number two and the final goal is he wants to know what is the probability that will make between a hundred thousand and four hundred thousand so again we come up here enter our dollars oops I need to change this so it'll accept if one hundred thousand but then it is a minimum and four hundred thousand as the maximum alright you go back and tell them look there's a thirty percent change you're going to make between a hundred and thousand and four hundred thousand so that's pretty good all right well what if it's between a hundred thousand is six hundred thousand there's a fifty percent change you're gonna be in that range so that's pretty good so anyways you can go back to him you could have lots of different goals you're looking for but the main thing is you want to be able to tell him well we look you know you your expected value is going to be that $465,000 but reality is there's a lot of variability here and you need to know what those in the impact can be from those that variability okay so anyways hopefully you found this video of value try it out yourself try R if you haven't gotten into R yet I highly recommend it there's a really great course that I highly recommend it's on udemy if you've ever used courses there and try the I think it's called our programming ADA Z and the guy who gives the class is Curiel Eremenko and he took me from knowing zilch about our to being able to do things like this and being able to do a little bit of damage you know I mean I'm having fun with it definitely not an expert this is very impactful stuff so anyways guys I'll see you at the next video bye

Channel: Geeky Veep

Views: 3,590

Rating: 4.9473686 out of 5

Keywords: Monte Carlo, R Programming, Power BI

Id: 9ACrGoi8mjA

Length: 15min 47sec (947 seconds)

Published: Thu Nov 01 2018