contador flash

DTSimulator Overview
 

DOWNLOAD
THE FREE VERSION

DTSimulator Overview
 

Monte Carlo
simulation on Excel
 

Our company is committed to providing, free of charge or at very low prices, high-quality, simple and analytical solutions in Monte Carlo simulation on Excel, for professionals and students of business and projects and independent consultants. Only Excel running with macros is required to perform full analysis with DTSimulator. We affirm our social commitment by freely sharing knowledge and good practices on quantitative risk analysis and decision-making.

In this digital age where knowledge is free and affordable, we make available our experience and knowledge in quantitative risk analysis and management. Good practices, quantitative tools, computational methods become easy to acquire and use. All our applications come along with instructional videos, practical examples, and specific aids to learn how to use them.

DTSimulator is available for free in student versions to learn how to use it. For very inexpensive sums unmatched by other tools on the market, you can download professional versions of DTSimulator. We also offer training and consulting in quantitative risk analysis as part of our packages.

Monte Carlo simulation on Excel
DTSimulator

How Monte Carlo simulation
works

Learn how to use these powerful analytical tools for free, easily and conveniently through videos and help, without having to commit a huge budget in software investment.

DTSimulator is a toolbar for Excel that allows you to analyze risks in projects and businesses structured as models in Excel using Monte Carlo simulation.

INPUT DATA

When clicking on this button, you can add and remove inputs to your model. Remember an input does not necessarily have to be a cell with a DT distribution function. For example, in this Project Risk Model, cells on column M have been declared as inputs. Each one of them is the product of a frequency function by a severity function. Any input contains 2 components: a name and a referenced cell.

Adding inputs implies typing or making a reference to a cell that should contain one. DTSimulator automatically assigns a name on the same row to the input but, by clicking on it, you can edit and change that name.

Another tab allows you to remove a previously declared input. By selecting it, you will see its name displayed. Student version allows you to create a maximum of 20 inputs; on the professional one, as much as 100 inputs can be declared. You will know how many remaining inputs you can still define.

OUTPUT DATA

When clicking on this button, you can add and remove outputs to your model. Outputs are variables on your model for which you want to perform an analysis. For example, in this Project Risk Model, 7 outputs have been defined on the Outputs tab for summarizing information by categories of risk. After a simulation is run you may be able to generate analytical charts for each one of these cells. Any output contains 2 components: a name and a referenced cell.

Adding outputs implies typing or making a reference to a cell that should contain one. DTSimulator automatically assigns a name on the same row to the output but, by clicking on it, you can edit and change that name. Another tab allows you to remove a previously declared input. By selecting it, you will see its name displayed. Student version allows you to create a maximum of 10 outputs; on the professional one, as much as 20 outputs can be declared. You will know how many remaining outputs you can still define.

SHOW INPUTS/OUTPUTS & BACKGROUND OUTLINE

When you click on Background Outline, DTSimulator will paint the background of all cells containing inputs and outputs with a colored scheme. You can turn On or Off this feature by clicking on the icon. Inputs are green. Outputs are blue. Remember that a cell that contains a DT distribution function is not automatically declared as an input. Sometimes, the product or sum of several distribution functions will need to be considered an input to your model. For example in this case, it is the product of a frequency distribution times a severity distribution what constitutes an input.

This distinction between DT functions and inputs prevent, for example, the creation of tornado charts with endless lists of bars where each individual distribution is considered an input. By clicking on the Show Inputs/Outputs icon a dialog box appears listing them. By selecting any input or output and then clicking the Goto button, DTSimulator will take you to the cell containing it.

DISTRIBUTION CHARTS

When Distribution Charts icon is Enabled, you can position yourself on any given cell that previously contains a DT function and view its density function graph. This pop-up chart that comes as a comment to the hovered cell will show additional information on the shape, statistics and usage of each particular function. It works for both discrete distributions as the ones placed on column G of this mode,l as well as continuous distributions such as the ones on column L of this project risk example.

If you want a faster navigation or simply do not need these distribution charts, the same icon will disable this comment feature. This functionality does not work for inputs and outputs being generated after a simulation is run. It only works on DT distribution functions.

RECALCULATION MODE

When the recalculation mode is on, the user can see that the results of the cells with DT distribution functions are modified every time there is a shift to any other cell or when the Excel Recalculation button is clicked. This functionality is very useful while editing a model to validate the precedence of functions and check that the eventual simulation works properly.

By clicking on this same button, the functionality can be turned off, so that moving to other cells will not cause a recalculation of random values in cells that contain DT distribution functions. While this functionality is turned off, it is possible to copy and paste values and functions to other cells for the model creation and modification.

PARAMETERS

Parameters icon allows you to define: The number of samples used for drawing DT function distribution charts. Too few sampled data points such as 20, will not be enough to draw a continuous density function. More than 1,000 will generate more data points than needed and will take additional time to update. Some 6 or 700 samples is enough for a densified curve.

You can also define the number of iterations to run a simulation. Up to 1000 on the student version and up to 50,000 on the professional version. Also, you can choose the statistics you want to show along your histograms. It includes 2 percentile values of your choice, as well as 2 target values according to the scale of the variable being measured. This updated information will be shown on the next time you generate a histogram for an input or an output. Before exiting Parameters, you will be given the option to start a new simulation.

SIMULATE

This is the core of the whole process. Running a simulation consists on the iterative process of recalculating many random samples of the distribution functions along the model, storing the data out of which inputs and outputs are later analyzed. The larger the number of iterations the more precise or convergent the results will be. Also, the longer it will take for the simulation to finish. The speed of the process also depends on the number of distribution functions that the model contains and software and hardware capabilities of your computer.

Up to 1000 iterations can be run on the student version and up to 50,000 iterations on the professional version. After a simulation is executed, you will be able to see how fast the simulation ran by viewing any histogram’s bottom right corner. In this case, the simulation ran 1,000 iterations after 21 seconds for a model containing 40 distribution functions, 20 inputs and 7 outputs.

HELP VIDEOS

When clicking on Help Videos, you will be linked to DecisionTrain’s Youtube channel where we have placed hundreds of courses, lessons, example models, recommendations, tips and help videos.

This icon will take you directly to DTSimulator’s playlist within the channel. There are several other playlists you could check out. Please, make sure you subscribe in order to receive weekly updates on example models, best practices and tons of useful material for important topics such as: project risk quantification, decision-making models, selection of distribution functions as well as specific help on each one of DTSimulator’s commands and functionalities.

HISTOGRAM

A histogram is a graphic representation of a variable in the form of bars, where the area of each bar is proportional to the frequency of the represented values. It is used to obtain a general first view of the distribution of the dataset, in this case of the simulated numbers, with respect to a quantitative and continuous variable.

Place your cursor on top of the output or input for which you want a histogram to be produced. Then, when you click on the histogram icon, provided that a simulation has been previously run, DT simulator will show it. A set of statistics describing the simulated dataset appears to the right hand side of the chart. A .jpg file containing this chart has been saved on the same directory where this workbook is located. This will allow you to paste it on presentations or documents. Close this chart, position your cursor on any other input or output and repeat your process as many times as you want.

S-CURVE

The S curve, or cumulative frequency, also called the percentile graph, is a way of displaying accumulated information in the form of a chart. It shows the number, percentage, or proportion of observations that are less than or equal to particular values.

Position yourself on any cell with an input or output variable (Total Risk) and click on the icon. The Y-axis contains the cumulative points from 0% to 100%, the percentiles. The points correspondingly associated with it on the X-axis refer to the magnitude of the variable being evaluated. If, in this example, the Total Risk value of $ 20,000 on the X-axis is associated, according to the S-curve, with the percentage of 91% on the Y-axis, then it can be said that there is a 91% cumulative ascending probability, that is , read from left to right, the total risk is less than $ 20,000. Therefore, there is a 10% probability that the total risk is greater than $ 20,000. This S-curve should not be confused with the traditional S-curve in project management, which measures something similar, the percentage of progress of a project at a certain time or magnitude. They are similar but not identical concepts.

TORNADO

The best way to understand the impact of individual risks, or risk categories, on overall risk results is through tornado charts. They help prioritize which risks are important and which are not. Thus, tornado charts are perhaps the most important ones in risk management. They show the longest bars at the top and, as you go down, the bars are shorter, forming a kind of funnel. That is, a tornado chart shows the strength of the relationship between one chosen output variable and the respective input variables that affect it on varying degrees.

Simply hover on the cell that contains the desired output (“Total Risk”) and click on the Tornado Icon. DTSimulator will show a tornado chart depicting linear or Pearson correlation coefficients for each of the input variables. These coefficients measure the relative degree of association of the input with respect to the output. The closer the coefficient gets to +1, the stronger the association of these two variables. In this case, for example, Availability of Construction Materials is the input variable with the highest degree of association with Total Risk. Project management should pay more attention to those variables on top of the tornado chart.

SCATTERPLOT

A scatterplot is a diagram that uses Cartesian coordinates to display the values of two variables for a data set, ideally an output versus an input. Hover on top of the cell that you want to display on the X axis of the scatterplot: Total Risk output in this case. Click on Scatterplot icon. Out of the dialog, select the input or output variable that you want on your Y axis, (“Logistics Risks” category for example) and click OK.

The plot will show as many dots as iterations were performed on the previous simulation. The fitted straight line will show the degree of association between the 2 variables: 0.69 in this case. The closer the dots are to this line, the stronger the relationship and the closer this number will approach +1. If you want to reverse the axes, simply hover over Logistics Risk. Click on Scatterplot. Select on Total Risk out of the dialog box and you will get the same chart with inverted axes keeping the same correlation coefficient. 69% of the variability of Total Risk can be explained by the variability of Logistics Risk category.

SIMULATED DATA

To provide a quick simulation, DTSimulator generates its simulation in RAM arrays rather than directly placing its results in a data spreadsheet. With or without saved results for the simulation, you can generate graphs and perform analysis immediately after running any simulation. If you want to explicitly save the results for later analysis, this will happen by saving the results just after the simulation is finished. Otherwise, the results will not be saved permanently and the next time you reopen the file you will have to run another simulation.

The Simulated Data button allows you to view the results of your last saved simulation in a spreadsheet tab that appears called Data. You can hide this Data tab by clicking the Simulated Data button again.

  • INPUT DATA

    When clicking on this button, you can add and remove inputs to your model. Remember an input does not necessarily have to be a cell with a DT distribution function. For example, in this Project Risk Model, cells on column M have been declared as inputs. Each one of them is the product of a frequency function by a severity function. Any input contains 2 components: a name and a referenced cell.

    Adding inputs implies typing or making a reference to a cell that should contain one. DTSimulator automatically assigns a name on the same row to the input but, by clicking on it, you can edit and change that name.

    Another tab allows you to remove a previously declared input. By selecting it, you will see its name displayed. Student version allows you to create a maximum of 20 inputs; on the professional one, as much as 100 inputs can be declared. You will know how many remaining inputs you can still define.

  • OUTPUT DATA

    When clicking on this button, you can add and remove outputs to your model. Outputs are variables on your model for which you want to perform an analysis. For example, in this Project Risk Model, 7 outputs have been defined on the Outputs tab for summarizing information by categories of risk. After a simulation is run you may be able to generate analytical charts for each one of these cells. Any output contains 2 components: a name and a referenced cell.

    Adding outputs implies typing or making a reference to a cell that should contain one. DTSimulator automatically assigns a name on the same row to the output but, by clicking on it, you can edit and change that name. Another tab allows you to remove a previously declared input. By selecting it, you will see its name displayed. Student version allows you to create a maximum of 10 outputs; on the professional one, as much as 20 outputs can be declared. You will know how many remaining outputs you can still define.

  • SHOW INPUTS/OUTPUTS & BACKGROUND OUTLINE

    When you click on Background Outline, DTSimulator will paint the background of all cells containing inputs and outputs with a colored scheme. You can turn On or Off this feature by clicking on the icon. Inputs are green. Outputs are blue. Remember that a cell that contains a DT distribution function is not automatically declared as an input. Sometimes, the product or sum of several distribution functions will need to be considered an input to your model. For example in this case, it is the product of a frequency distribution times a severity distribution what constitutes an input.

    This distinction between DT functions and inputs prevent, for example, the creation of tornado charts with endless lists of bars where each individual distribution is considered an input. By clicking on the Show Inputs/Outputs icon a dialog box appears listing them. By selecting any input or output and then clicking the Goto button, DTSimulator will take you to the cell containing it.

  • DISTRIBUTION CHARTS

    When Distribution Charts icon is Enabled, you can position yourself on any given cell that previously contains a DT function and view its density function graph. This pop-up chart that comes as a comment to the hovered cell will show additional information on the shape, statistics and usage of each particular function. It works for both discrete distributions as the ones placed on column G of this mode,l as well as continuous distributions such as the ones on column L of this project risk example.

    If you want a faster navigation or simply do not need these distribution charts, the same icon will disable this comment feature. This functionality does not work for inputs and outputs being generated after a simulation is run. It only works on DT distribution functions.

  • RECALCULATION MODE

    When the recalculation mode is on, the user can see that the results of the cells with DT distribution functions are modified every time there is a shift to any other cell or when the Excel Recalculation button is clicked. This functionality is very useful while editing a model to validate the precedence of functions and check that the eventual simulation works properly.

    By clicking on this same button, the functionality can be turned off, so that moving to other cells will not cause a recalculation of random values in cells that contain DT distribution functions. While this functionality is turned off, it is possible to copy and paste values and functions to other cells for the model creation and modification.

  • PARAMETERS

    Parameters icon allows you to define: The number of samples used for drawing DT function distribution charts. Too few sampled data points such as 20, will not be enough to draw a continuous density function. More than 1,000 will generate more data points than needed and will take additional time to update. Some 6 or 700 samples is enough for a densified curve.

    You can also define the number of iterations to run a simulation. Up to 1000 on the student version and up to 50,000 on the professional version. Also, you can choose the statistics you want to show along your histograms. It includes 2 percentile values of your choice, as well as 2 target values according to the scale of the variable being measured. This updated information will be shown on the next time you generate a histogram for an input or an output. Before exiting Parameters, you will be given the option to start a new simulation.

  • SIMULATE

    This is the core of the whole process. Running a simulation consists on the iterative process of recalculating many random samples of the distribution functions along the model, storing the data out of which inputs and outputs are later analyzed. The larger the number of iterations the more precise or convergent the results will be. Also, the longer it will take for the simulation to finish. The speed of the process also depends on the number of distribution functions that the model contains and software and hardware capabilities of your computer.

    Up to 1000 iterations can be run on the student version and up to 50,000 iterations on the professional version. After a simulation is executed, you will be able to see how fast the simulation ran by viewing any histogram’s bottom right corner. In this case, the simulation ran 1,000 iterations after 21 seconds for a model containing 40 distribution functions, 20 inputs and 7 outputs.

  • HELP VIDEOS

    When clicking on Help Videos, you will be linked to DecisionTrain’s Youtube channel where we have placed hundreds of courses, lessons, example models, recommendations, tips and help videos.

    This icon will take you directly to DTSimulator’s playlist within the channel. There are several other playlists you could check out. Please, make sure you subscribe in order to receive weekly updates on example models, best practices and tons of useful material for important topics such as: project risk quantification, decision-making models, selection of distribution functions as well as specific help on each one of DTSimulator’s commands and functionalities.

  • HISTOGRAM

    A histogram is a graphic representation of a variable in the form of bars, where the area of each bar is proportional to the frequency of the represented values. It is used to obtain a general first view of the distribution of the dataset, in this case of the simulated numbers, with respect to a quantitative and continuous variable.

    Place your cursor on top of the output or input for which you want a histogram to be produced. Then, when you click on the histogram icon, provided that a simulation has been previously run, DT simulator will show it. A set of statistics describing the simulated dataset appears to the right hand side of the chart. A .jpg file containing this chart has been saved on the same directory where this workbook is located. This will allow you to paste it on presentations or documents. Close this chart, position your cursor on any other input or output and repeat your process as many times as you want.

  • S-CURVE

    The S curve, or cumulative frequency, also called the percentile graph, is a way of displaying accumulated information in the form of a chart. It shows the number, percentage, or proportion of observations that are less than or equal to particular values.

    Position yourself on any cell with an input or output variable (Total Risk) and click on the icon. The Y-axis contains the cumulative points from 0% to 100%, the percentiles. The points correspondingly associated with it on the X-axis refer to the magnitude of the variable being evaluated. If, in this example, the Total Risk value of $ 20,000 on the X-axis is associated, according to the S-curve, with the percentage of 91% on the Y-axis, then it can be said that there is a 91% cumulative ascending probability, that is , read from left to right, the total risk is less than $ 20,000. Therefore, there is a 10% probability that the total risk is greater than $ 20,000. This S-curve should not be confused with the traditional S-curve in project management, which measures something similar, the percentage of progress of a project at a certain time or magnitude. They are similar but not identical concepts.

  • TORNADO

    The best way to understand the impact of individual risks, or risk categories, on overall risk results is through tornado charts. They help prioritize which risks are important and which are not. Thus, tornado charts are perhaps the most important ones in risk management. They show the longest bars at the top and, as you go down, the bars are shorter, forming a kind of funnel. That is, a tornado chart shows the strength of the relationship between one chosen output variable and the respective input variables that affect it on varying degrees.

    Simply hover on the cell that contains the desired output (“Total Risk”) and click on the Tornado Icon. DTSimulator will show a tornado chart depicting linear or Pearson correlation coefficients for each of the input variables. These coefficients measure the relative degree of association of the input with respect to the output. The closer the coefficient gets to +1, the stronger the association of these two variables. In this case, for example, Availability of Construction Materials is the input variable with the highest degree of association with Total Risk. Project management should pay more attention to those variables on top of the tornado chart.

  • SCATTERPLOT

    A scatterplot is a diagram that uses Cartesian coordinates to display the values of two variables for a data set, ideally an output versus an input. Hover on top of the cell that you want to display on the X axis of the scatterplot: Total Risk output in this case. Click on Scatterplot icon. Out of the dialog, select the input or output variable that you want on your Y axis, (“Logistics Risks” category for example) and click OK.

    The plot will show as many dots as iterations were performed on the previous simulation. The fitted straight line will show the degree of association between the 2 variables: 0.69 in this case. The closer the dots are to this line, the stronger the relationship and the closer this number will approach +1. If you want to reverse the axes, simply hover over Logistics Risk. Click on Scatterplot. Select on Total Risk out of the dialog box and you will get the same chart with inverted axes keeping the same correlation coefficient. 69% of the variability of Total Risk can be explained by the variability of Logistics Risk category.

  • SIMULATED DATA

    To provide a quick simulation, DTSimulator generates its simulation in RAM arrays rather than directly placing its results in a data spreadsheet. With or without saved results for the simulation, you can generate graphs and perform analysis immediately after running any simulation. If you want to explicitly save the results for later analysis, this will happen by saving the results just after the simulation is finished. Otherwise, the results will not be saved permanently and the next time you reopen the file you will have to run another simulation.

    The Simulated Data button allows you to view the results of your last saved simulation in a spreadsheet tab that appears called Data. You can hide this Data tab by clicking the Simulated Data button again.

DTSimulator

© 2021 All rights reserved. Powered by STT INTERNACIONAL.