Introduction to Monte Carlo Simulation and Risk Analysis using @RISK and RISKOptimizer

so a certified Microsoft partner we're an Intel chopper provider who recognized by a handful of professional organizations for teaching their membership in the use of at-risk and decision tools suite for probabilistic analysis and at risk of incision tool suite is a primary Montecarlo simulation tool at the majority of colleges and universities in North American Europe and that is rapidly expanding in Latin America and the Pacific Rim so we estimate that today in North America alone we have 100 about 150,000 analysts actively using the software and after every level of government military discipline all higher education levels every type of business industry and business size imaginable and that includes 93% of the fortune 500 companies so let's talk about one of the reasons Weber here risk analysis modeling using Monte Carlo simulation so the old way of risk modeling was to use single point estimates so you'd have a financial model some kind of spreadsheet and you have single numbers in the cells and hopefully you've written your functions and calculations properly so the bottom line reflected the relationship between the inputs properly the natural progression from single-mode point estimates was to create three point estimate or scenario analyses of the spreadsheets so now you'd have instead of one point estimate you'd have three but the question often arises well what what if things happen in between what if these ten things move in one direction at a certain time and those twelve you know twelve other items move in a different direction how does that cascade through a complex model and how is one able to capture that kind of uncertainty and variation that's how Manuel would've analyses developed and this is a very time-consuming cumbersome and error Laden procedure so the new way of risk modeling is using probability distributions that captures that that and the entirety of that uncertainty and variability to help make defensible business decisions from managing risk and so those probability distributions what they do is they furnish the decision maker with a full range of possible outcomes not just at one point or a three point estimate but also how likely those outcomes are to occur and also identifies the main drivers that's that impact the bottom line so let's talk about Monte Carlo simulation quickly it's a relatively simple concept essentially what you're doing is conducting a virtual experiment on your PC or laptop and you're repeating that experiment hundreds thousands tens of thousands of the times collecting that data and that data is controlled by you in terms of setting the parameters of the inputs in terms of how much they vary in their relationships they have once you've collected the data then you can organize it and that in that organization you're able to better analyze what's going on in complex systems to improve your decision making process so let's use a very simple example mowing a pair of dice so I'm going to ask you to imagine that you don't know how dice behave when you roll them and so one of the first things we need to do is define the parameters of rolling dice in this case you have one of the parameters is you have two dice and every time you roll them you have six possible outcomes for each die so what happens when we run a virtual experiment and roll those dice 48 times well we get a bunch of numbers and I'm going to rhetorically ask does anyone see a pattern in those numbers I certainly don't see a pattern so to make things simple for me is I'm going to look rather than all of the outcomes I'm going to look at just the total sort of condensed what's going on and so here we have the totals it's fewer numbers to look at and now it's a little bit easier to decipher what's going on or what the pattern is but again for me personally I don't see the pattern here is probably one in a thousand people that can quickly say oh look you know there's more 7s than anything else so this is where the software comes in is you can now organize the figures and in organizing the figures you're able to understand the behavior of rolling dice and from here we get the famous histogram and so you can see that lucky number seven is the most frequently occurring die that are rolled with I've heard these are called boxcars I heard that a couple of a couple of months ago double sixes or boxcars and Snake Eyes are the two twos they're the outliers but with 48 dies 48 rolls of the die this is not a smooth curve it's kind of jagged II so I'm going to actually have an example model that smooths this out for us okay so let's review Monte Carlo simulation is a virtual experiment that repeats a process or project a large number of times generating a large number of random samples found by specific set of parameters that the end user puts in those numbers are then collected organized and analyzed so the benefits of using at risk it's easy to use number one you're working in a familiar environment which is Microsoft Excel so at risk functions or true Excel functions means you can copy and paste copy and drag draw cell references across worksheets and workbooks and nest Excel functions with that risk functions it behaves exactly like you'd expect Excel to behave number two peace of mind we've been around for a long time we're approaching nearly 30 years of being in business and as we've put our software out there we have a pretty good cross-section of our customers that are vocal about any bugs that they might find or questions they may have an a we take that input very seriously and the result is that we have very stable highly regarded software and perhaps perhaps most importantly it becomes much easier to make dispensable business decisions after the heavy lifting the calculations of the Monte Carlo simulation is done with the click of a mouse you have access to dozens of charts and graphs and course to help you help them form the decision-making process okay I'm going to briefly go over the tools within the decision tool suite at risk is what we're going to be spending the bulk of our time with as well as risk optimizer these other tools I'll be I'll be mentioning here it's just a summary risk accelerator is a little built in function that harnesses the multiple CPUs within your device to help make the simulation optimizations run quickly precision tree is also an add-in to Microsoft Excel to decision tree and influence diagram software it integrates very nicely with at-risk top-ranked is a whatever sensitivity analysis tool some people sometimes confuse it with what at-risk does that risk actually does top-ranked work but it sometimes takes quite a bit longer to place all the parameters and distributions and correlations and that type of thing into a model to do a full analysis what top-rank does is it identifies very quickly what the top drivers are in any model so if you have a customer or a stakeholder who gives you a model without any explanation you can run a top-ranked analysis very quickly to get a sense of what the main drivers are in that model stat tools I like to call a nice little stats package it's not like a jump by SAS or a MATLAB or a Minitab it focuses on 37 of the most popularly used statistical procedures and unlike Microsoft Excel it it reduces a number of clicks you have to do to get to certain information for example you're trying to figure out what the coefficient value is between two data sets it's about half as many clicks to use stat tools and it's more intuitive than Microsoft Excel is Murrell tools and also an add-in to Microsoft Excel general network tool that trains a neural net based on your data set and then makes predictions of what may happen in the future Evolver I like to call the little brother or little sister of RISC optimizer these are both opt zatia tools Evolver does the optimization with discrete numbers risk optimizer does an optimization under uncertainty and I'll have a demonstration so you can understand that more fully shortly okay beyond that we have at risk for project four scheduled risk analysis we are not going to be focusing on scheduled risk analysis today I'm planning on scheduling another separate WebEx to focus on schedule risk analysis in potentially next month or the following month we also have developer kits folks professional training consulting custom development all to help support the use of the tool okay let's get into some modeling now hope there's a pricing but I'm going to get back to that in a little bit one moment here okay so at this point you should see Excel this is the ribbon menu that most of you are most familiar with with that risk it adds into Excel and you get our own tech we get we put a place in our own tab with our own set of menu items so I'm going to open up a very simple model which is a dice model so one thing to think about or to keep in mind all of my models the cells that are colored in yellow have at-risk inputs or distributions well the pink ones are at-risk outputs so in a dice model we have in this particular Dutch model I have to die and they're both normal everyday kind of diet Lisa are modeled that way so this is a syntax that we use to define how a die behaves when you roll it you don't have to worry about the syntax or typing it out because everything is menu-driven in in at risk so to do a visual examination of that distribution you simply click on define distributions and this is a distribution so you can do a visual inspection on rolling a die you have six possible outcomes all with the same likely sort of occurrence the same is true with dye number two and in cell c3 what we're doing is we're adding the two die together as we roll them but we're also assigned in at risk output so I'm going to delete this one right here to show you how to start from scratch empty cell we still go to define distributions and when we do that let me open up this further right now when our most commonly used distributions but we have 79 to choose from we shouldn't be overwhelmed by this because the majority of our customers only use two or three men perhaps for the most so the one I'm interested in is the uniform distribution so when I choose that I can now modify the default in a blank cell this is the default by using this portion or the control portion of the window so the first thing I'm going to do is I'm going to change the minimum from zero to one then we change the maximum from ten to six and that produces our distribution for a normal die I click OK and now that distribution is added to the cell for an at-risk output it's actually even more simple I'm going to delete the at-risk output function and this is just a normal everyday summation and to add an at-risk output to capture the uncertainty during the simulation we need to click on hat output when we do that at-risk tells us what the title should be we click OK and we're done now we're ready to simulate and to simulate we click on start simulation we're going to do a thousand iterations or a thousand virtual roles of each die so let's start that process so here we go you can see the numbers are changing in here and the graph is taking shape and actually I should probably do it a little bit more hold on a second do 5,000 so I want to show you that nice smooth curve so here we go this is much more smooth I'm going to speed up the simulation process and after 5,000 rules of each die this is the distribution get a nice smooth curve snake eyes and boxcars as the outliers and lucky number seven in the middle life is not symmetrical nor are our models so let's throw a monkey wrench into this we're going to add a third die and in that third die what I'm doing with that third I what I'm doing is I'm collecting or so many doing a summation of cell c3 and f3 so the summation here plus the additional third die let's take a look at that third die here's a syntax I'm going to click on define definition to define the distribution to get a visual inspection and this is our die so what I've done when I develop this this particular die is I created a virtual hunk of lead that I placed on the virtual six side of this virtual die so that the six most often just hits the table well the one is up in the air so when we simulate this one the numbers are all changing during the simulation and we can speed it up by clicking on this little button right here so that'll stop these numbers from changing visually they're still changing in the background and the simulation speed up quite a bit and so with three die to symmetrical guy and one that's kind of wacky this is the distribution we get 18 and 17 are very rare 16 and 17 are pretty rare too and here are the most frequently occurring numbers we can actually pull this over and say that this is 8 through 11 of the most frequently occurring item in fact 8 through 11 occur 38% of the time 3 through 7 occur 35.2 percent of the time and 12 through 18 occur 26.7 percent of the time so that's the basics the most fundamental basic part of using at risk what I'm going to do now is move into a very interesting NPV portfolio model it's more germane to business analysis okay if you have a commercial license of that risk or you have a trial license of that risk you can actually find this particular model by going to the help menu and then clicking on example spreadsheets and doing that opens up an index to example spreadsheets and you can find dozens of different kinds of models both based on a specific business application or highlighting a specific feature in the software so when you click on any one of these it actually opens up the actual model this is the index and again you can get there by going to the at-risk help menu and in example spreadsheets so with each example model the first tab is an explanation tab it gives a summary of what the particular model is about what we're trying to convey in this particular model what we have are eight different projects all with NPV information in it so this is the one we're going to be spending the most time with project one for project two is a slightly different model project 3s like a different model again for NPV so we have eight different projects that we're working with all of these projects are then fed into the model tab here and so what we're doing here and I'm going to talk about this a little bit more later is we're gauging the possible success of each of the projects and then we're going to be choosing which projects to take on to maximize our investment to maximize the return on investment but yet stay within a certain budget so right now if we selected all of the projects we're going to be generating two and a half million dollars in that present value but the cost is far far exceeds the budget that we have so before we get to that optimization problem what I'm going to do is I'm going to spend time in project one and going over several different features of that risk before we even begin to simulate so this will take a little while before we get to the simulation phase but bear with me and it is a little bit of a working project work-in-progress here so first let's focus on this set of items here right now our target NPV is three hundred and sixty-five thousand dollars for this particular project project one this is the expected value of this project based on cash flow being driven by market condition sales activity and production expenses so we're we're within one percent the estimated value is within one percent of our target NPV and so the question is do you have as a decision-maker enough information to approve this project and how confident are you in your decision in a deterministic model which is what your eyes see right now this is a difficult choice to make what are the chances that we'll go below three hundred sixty five thousand dollars if some of these items change and so this is why we're here we're trying to capture that uncertainty and variability so the first thing that we're going to do is we're going to do a visual inspection of a few items here so product development in years 2014 2015 and 2016 are indicated here so we're using a risk normal distribution and again we can do a visual examination by clicking on defined distribution and here we see that $50,000 is our mean and the standard deviation is defined by another data set in this case it's five thousand dollars or five percent or ten percent we look at project development number two now for 2015 it's a slightly different number and curve and then we have something different yet for 2016 product development and things that get a little bit more interesting when you get into capital expenses we're using a triangular distribution here and we're looking at a much higher speed triangular distribution and in 2015 were actually skewed other direction and then 2016 it's skewed even more so so that's just a way to do a visual examination of some of the some of the different kinds of distributions so the next thing I'm going to do is use cell references to define the parameters for these items here which is a sales volume so when we go in here we see a particular formula and this particular formula is drive some information within our cash flow but I want to but this is just a single number so I want to add variability to it because I'm not really sure whether I'm going to hit 3000 over just going to be below ER or above that and I have a data tab here that I'm going to be doing cell references to for the sales volume so to do that I'm going to go I'm going to click inside this cell and then go to define distributions and I'm going to choose a purchased rebuking so this this button allows you to do cell references qui quickly to define the parameters you can of course pop in here and just type in a cell reference but this is a quicker way of doing it so you click here it opens up this tiny little dialog window I'm now going to click on the data and estimates tab and I'm going to click here and do it this way like that and then here and actually I have to change this to this okay so now we're taking f-14 f15 and f-16 and the data tab to define the distribution the static value is our benchmark it doesn't affect the calculations of the distribution so when I click here in the corner we get a we pop back to our model and we get the distribution based on the cell references from the data and estimates tab to define the distribution for sales volume in 2016 I'm going to click OK and then what I'm going to do is I'm going to grab and drag all the way across and if you notice up here there's a lot of syntax in here but the cell references are kept intact so by building a distribution for one cell we can quickly populate all the other cells just like you could expect at risk to Excel to work so the next thing we're going to do is we want to study not only the net present value for the entire model but we may also want to study the cascading effects of all these market conditions sales activity and production expenses for each single year so what I can do is I can collect the net income for each of the years or select go up to add output remember at-risk automatically chooses a name based on the titles that are given and I click OK so now what we've done is we've added an at-risk output so we can stuck study what happens in every single year of our net present value model ok next number 5 so this this particular model I've modified it's available in our in your try licenses under example models but I did find it so you can see I'm sorry didn't define it I refined it so that there are certain steps I'm going to take and if you'd like to get this particular model and kind of repeat what I did just send me an email be happy to do that so the next thing is we're going to we're going to correlate some items so let's correlate product development for 2015 16 and 17 I think that's right 14 through 16 to do that what you do first is you select the data cells and then you click on the fourth menu item which is defined correlations and in doing so a matrix is developed with the product development numbers so what I want to do is I want to assign a coefficient value to to define the correlation between product development in 2014 and 2016 and I'm going to assume that this is a positive correlation and is a strong correlation so if we have high product development costs in 2014 are probably going to be high also in 2015 and all coefficient values for defining correlations are going to be between negative 1 and positive 1 so in this case let's say 0.75 and then I also want to do a correlation between product development 2015 and 2016 and maybe we can call this point 8 so very strong positive correlations you can do a further visual examination by clicking on this little tiny button right here it creates a scatterplot of the coefficient values that we've added and you can click on any one of these and kind of play around with with the scatter plot slider coefficient value of 0 creates essentially a perfect circle a negative coefficient value has a different angle from a positive coefficient angle let's go back here I think this was 8 click OK and if the matrix isn't self-consistent you can have at-risk make a consistent for you so we'll do that so the next step now is to place the matrix in our model so I'm going to click OK I'm going to go over here click OK and that matrix is now added to our model so when you share the model with stakeholders or decision-makers or other analysts they can see that in this particular model we've correlated these items together and when you click on the cell and take a look at the matrix you can see that we've nested the risk correlation function in there too so the same is true here and here alright so item number 6 um I'm going to do that a little bit later I'm going to actually skip to item number 7 so what I've done in cell F 20 sorry F 19 they've added a risk sim table so this allows you to do sort of a simple scenario analysis so for example in 2016 we may have a new president and in 2017 that president may drive the and a new Congress of course may drive the the tax rate up or down or keep it the same and so this is based on let me see the data and estimates tab pool so we'll go there and look at F 21 and H 21 which is here F G and H 21 so most likely or not most likely but the high may be as much as 50 percent the low may be a 42 percent tax rate or will just keep it at the current 26 percent tax rate so when I do a risk sim table I actually need to do three simulate three separate simulations each of a thousand iterations the first simulation will be based on a 46% tax rate the second simulation will be based on the 50% tax rate right here and a third simulation will be based on a 42 percent tax rate and so we can compare what the differences in tax rates may or how they may affect the bottom line okay and the next thing I want to show you is time series modelling this is a very new and very powerful tool that we've added to at risk so here we have inflation rate and right now when I go back to the data tab you'll see that we're just at 5% all the way across let's go back to the data tab here's our annual US inflation rate and what I've done for 2013 to 2023 is I just have hard numbers in here at 5% embedded so what I'm going to do rather than guess that we're going to be about 5% I'm going to delete all these and we're going to do a forecast based on historical data so I can just click in any any cell within my data set then go to a time series and click on fit we get a dialog window that describes a few things to us first we see a graph of how inflation behaved during you know since 1972 and then to achieve to prepare the data for forecasting one of the things we have to do is to do a data transformation to achieve stationarity if you know what you're doing you can choose how to go about that you can choose a function or a D trend or D salt DC's analyzation or you can have at risk - an auto detect so when I click on auto detect it chooses the D trending data transformation function and now I'm ready for the doing a forecast of how inflation may behave in the future next step is to click on fit and so this window shows us several things one is again the historical behavior of inflation and then here's the the cone of possible outcomes for inflation here's the mean and see it defined here and here's the band from 25 sorry 25 percent to 75 percent and here's the band from 5 percent to 75 95 percent and this represents one sample path that inflation may take you can animate this by clicking on this little button here and you can see that the inflation will Hana me so these are one one possible scenario another and yet another most of the time staying within the 5th to 95th percentile every once in a while bouncing out because we make it into the 99th and first percentiles another thing to take note is that we have two fitting tests that you can choose from and in the fitting once the fitting test is chosen then at-risk decides which time series modeling function is best for this particular set and it ranks them in order here so we have moving averages and conditionals heteroskedastic conditional works and Brun in geometric motion these are a lot of things that go over my head but these are very sophisticated time series modeling functions this one is ranked as the highest the next step is to write these to the cells of your day so when I click on right two cells the default is to choose 24 periods ahead but I only need to go a few years ahead so I'm going to click there and click ok so now we've added this very sophisticated function for time series modeling and we're looking at one possible path that may go through here when I engage the two dice up here and then I can choose tap on the f9 key we can see another possible path that may take and another and another and we can also simulate so let me change this to 1 and just very quickly change this to 500 and just so for 2013 we can simulate the probability distribution for inflation and there you have it so it looks like the mean in this case is just short of 3% the mode is a little bit lower than that and you can understand what the standard deviations are these output graphs are really great you can slide them around and maybe look at the 10th percentile is opposed to the fifth and if you can't quite hit it you can just type in 10 and it goes to the 10th percentile you may want to hit 1.9 and it moves it for you ok so let's go back to the project and now we've added a whole bunch of features so I think we're about ready to simulate now so let's keep our eye on the projected net present value remember now we've added everything we have a different number here to work with which is very interesting but that single number does not spell the whole picture we really need to look at the full range of possible outcomes and that is defined by running a simulation so let's go back to a thousand so we're going to do three separate simulations remember the first at 46% tax rate the next at 50 in the nextstep times ok so during the simulation all the numbers are changing and the graph is taking shape I'm going to pause this just a moment here and move it out of the way for you and it's restart it so in demo mode where all these numbers are changing its this is actually robbing my system of a lot of resources so it's going a little bit slow I can speed that up I essentially just turning these numbers so that they just appear static even though they're still simulating the background you can see the simulation speed up quite a bit more so now we're done with simulation one in beginning simulation two right here so this is taking into account the variability involved in capital expenditures and product development sales volume inflation rates and a few other items too and we're almost done with simulation number three okay great so if we look at this we have a we can do several things so again we can move around the parameter so for example we may want to go to zero net present value there's only at roughly a two percent chance that we're going to be earning less than zero like if we're going to be losing money on this particular venture but remember our target value is three hundred and sixty-five thousand dollars so let's change this to point three six five and move that over so this is what we're now faced with there's a forty six point four percent chance that we're going to earn less than three hundred and sixty-five thousand dollars which is our target and PB for project one on the other hand there's a forty eight point six plus five percent chance so roughly a fifty four percent chance that we're going to exceed that in fact we may make a lot of money so for example if we go up to six hundred thousand dollars there's nearly a thirty five percent chance of exceeding six hundred thousand dollars that recall initially this was within one percent of our target net present value it was a with a coin flip but now we have more information to inform our decision-making process on what to do next one of the common questions that comes after this is to ask well what's causing us you know to have these kinds of percentages and possibilities what are the drivers here this button right here is our tornado die Grahame allows you to look at five different ways of which drivers in different ways which drivers are impacting the bottom line I like to look at this one personally this gives us a cool efficient value and it's clear that the number of competitors are having a negative impact on our bottom line here are a couple of items that are having a positive impact but with a point 1 1 coefficient value it's almost negligible so the question comes up if we want to improve our odds of achieving our 365,000 dollar amount whoops I hit the wrong one if we want to improve the odds like lower this number but increase this number what we want to focus on is the number of competitors entering the marketplace maybe we need to get into the market place sooner grab more of a market share so that it becomes more difficult for our competitors to erode our ability to earn money let's go back to this item so remember we ran three separate simulations so now we can also study not only simulation one for a 46% tax rate but we can compare that with simulation number two so here's a fifty three point six percent chance we're going to be earning more than 365 this is the one at fifty percent tax rate it diminishes our ability to earn more than that and simulation number three we went down to 42 percent tax rate which improves it yet again and you can look at these all as one simulation this is a little bit difficult to look at but you have ways to sort of make these transparent for example clicking graph options and I believe it's curves and see you can change this to a line so and it makes this red one the very first one transparent you do that for the other ones and becomes a little bit easier to visually understand how these compared to one another of course you can look at the numbers too so if I pulled this out a little bit further and then you can expand these and now you can compare the means for example and the mode for example the standard deviations so with that look with a low tax rate there's a much larger standard deviation though our ability to earn money has improved so there's a lot of volatility and another thing to consider when making your decisions all right one of the nice things about at risk component that I mentioned earlier as you can now grab this data any charts and any graphs that you look at that are generated you right click and copy I'm going to bring my Microsoft Project over again and then you can simply paste any any chart or graph into a PowerPoint presentation Microsoft Word document into an email it's just very very easy to get the information and share it with other people so move that back which leads me to another item what if you have colleagues or stakeholders who don't have a copy of that risk they can't run the simulation in fact when they get this model all the yellow cells in pink cells if they don't have an at-risk license will have something similar to this syntax all over the place it'll just be a mess they won't be able to understand what's going on at risk has a great utility called a swap-out function when I click on swap-out function what happens is the router's functions are replaced with their static or expected value so when I click OK I'm not going to save the simulation results all these numbers now are cell references or equations or numbers so anyone can look at this in fact you can embed the simulation results as as their charts and graphs and anyone in another tab and they those will be retained as well so everything that you've done all the work that you've done can be shared with anybody else the only limitation with somebody who doesn't have an at-risk license is they can't run the simulation themselves okay let's say somebody makes a change and they say you know that Andrew guy who really doesn't know anything about product development this would really be fifty thousand and one dollar Oh change is five hundred thousand and one there we go then they spend the model back to you but fail to tell you the change that they've made when you click on swapping functions we're going to replace the expected values now with the at-risk functions are going to come back into play and you get a log of the changes that were made so you can with that love allows you to choose how to proceed you can either keep the recommended which embeds the 5001 can't quite see it here as the risk static function which luckily in this case it doesn't affect the calculation as a risk static value the cell references are retained which is important but you can keep that as a benchmark if you choose or you can go back to what you had before swap in at-risk functions and everything returns to the way it was a couple other quick things I'm going to run one more simulation this is just one simulation rather than three for to expedite things along I'm going to go back to the tornado diagram there's one other sensitivity analysis that's that's really neat which is the inputs ranked by effect on output mean this obviously differs from the correlation coefficient one that I showed you same basic information represented in a different way so what this does is it allows you to understand how the competitors the swing and impact has on the output from two hundred and thirty one thousand to six hundred and twenty two thousand depending on how many competitors enter in 2017 so it's quite significant one of the things you can do with these is you can actually grab any one of these bars and drag them out and then you can study this creates a scatter plot remember with a the number of competitors we're talking about a discrete distribution we only have one or two or three or zero or negative one competitors entering the marketplace in other words they may drop out and so we have these straight lines that define what's going on with the net present value based on the number of competitors that are entering the marketplace and again you can move these around to study how it affects the percentiles in each quadrant another interesting one to look at is this unlike the discrete distribution this is a continuous distribution and you can see with a point one one coefficient value this is about as random as you can get but one of the things you can do in here is you can go into the net present value line type in point three six five because that's our target it moves the line down to that point and you can say for example sales volume in 2016 if it's at three thousand or let's say twenty four hundred there's a twelve point seven percent chance that the sales volume will be twenty four hundred and you'll be earning less than three hundred and sixty five thousand the opposite of that is there's a forty almost forty three percent chance that you will be earning more than $365,000 and there will be sales item above 2400 units you can also take a look at the information tabular Lee so this little button right here allows you to view all the statistics for every single output and every single input so here are the basic statistics and then values at each percentile by fifth and if you'd like you can change you have the ability to add different numbers for example if you want to see the value at the first percentile just type in one and it finds that value for you or the 99 for example and it finds that value for you or you can type in a certain number like oops like that and it gives you the percentile value at that stage so just like the charts and graphs you can grab this and report it in Excel this looks like an Excel spreadsheet but it's actually not and in a moment we'll actually have an Excel spreadsheet so here's that report for the detailed statistics like uh I flipped the yep I flipped the the table so to my 199th my first my first percentile value and my 99th percentile values are actually horizontal rather than I must I accidentally clicked on the flip button but it's all available there sometimes people ask where's the oops I just deleted everything hold on sorry about that click the X button a little bit too quick for transparency sake the little X of I button shows you the values forever random number that was sampled for every single input and output so in this case I reduce the number of simulate iterations down to 500 and here are the values for all 500 iterations so this is the raw simulation data from which we can analyze from which we generate all the charts and graphs and other reports so that's available too if you need to take a closer look at another nice feature is a model window so sometimes we need to report our model and our assumptions as well when you cook on the model window and it opens up a window that shows you the distributions that were used for each of the functions are for each of the inputs it also identifies where the outputs are and any correlations that were put together and just like all the other ones you can report this in Excel for reporting purposes ok I apologize I'm clicking things a little bit too quickly here next out of at risk will be back up in a second okay there it is alright so now we've studied one of the projects we can go ahead and apply all you know the same types of principles to all the other projects but that would take a long time so what we're going to do now is we're going to focus on the model summary and we're going to begin to explore which projects should we take on to maximize our net present value model number while retaining the budget the amount of finite resource we have to devote to this particular portfolio we use risk optimizer to do that so let's click on risk optimizer and model definition so the model definition window what we're doing is we're word we're going to be maximizing cell D 24 which is right here and we're going to be doing that around a mean sometimes we want to do the optimization around the standard deviation to avoid volatility and so you instead of using a maximum you'd want to use a minimum for the standard deviation typically anyway another thing that we're doing is we're going to adjust cells that we have control over so the cells that we have control over are these which is which project do we select so during the optimization process at risk is going to be selecting some number of these running a simulation then selecting another set running another simulation and comparing the results and doing that over and over again until we come up with the optimal number or configuration of projects to have to retain it in our portfolio that will be within the budget and the budget is defined here it's our constraint so we're going to try and hit that budget within of 95% so in other words going to try to be about 5% lower than the budget so that's the basics for setting up the optimization model so the next step now is to start okay you know what I forgot to do something let me stop this for a moment I'm going to change is to one and just to sort of speed things up because we're already running short on time I'm change the iterations to 1 and then we're going to go back to the optimization ok so what's happening here is these numbers are being changed and as soon as we find a configuration that meets the constraints we keep that and start comparing it so what happened is we started at essentially this level so we're at 1.2 million dollars and then we found another better solution and every time we find another better solution I'm going to pause this just for a second the output graph is updated let's restart that okay and so we're up to a little bit more than 1.8 million dollars so I'm going to reduce this now so you can see those numbers change and the last time I ran this it took about 2 minutes or so to achieve a sort of a plateau like we couldn't find a better better solutions at about two million dollars so let's go back up so it's plateaued now at about one point eight and in a little in a few moments it'll you'll see a pop up to about two million dollars so what's happening now is we're taking each project in simulating all eight of those projects while choosing some projects to keep or not and then we're doing that again and comparing the results and then choosing another configuration of projects and comparing those are those savory six we popped up a little over two million dollars okay so we're going to stop this optimization now and we're not going to restore the original adjustable values if you recall these were all chosen and we're away over our budget instead we're going to use the configuration that at risk came up with a risk optimizer we're also going to take a look at an optimization summary so here's our summary our fancy little graph here's the goal of our optimization here's where we ended up for the best value fund found for so if it was C or D 24 which is two million one hundred and eight thousand dollars and it sort of tabular Lee explains which what we did with the adjustable cell values original is one we kept that one original is one but we discarded it and you can see that as you go down through here this is not so fun to look at I prefer to look at the actual model because this tells us which items which projects were chosen to maximize our profit while staying below our budget and that's the power of risk optimizer

Channel: Palisade

Views: 104,340

Keywords: Palisade Corporation, @RISK, Monte Carlo Simulation, Business Analysis, Risk and Decision Analysis, Probabilistic analysis

Id: KgAHLRxjBsA

Length: 53min 28sec (3208 seconds)

Published: Wed Jan 29 2014