0:00

Throughout this course you have learned how to use data

Â to create models that help you solve a variety of problems in business.

Â We started with models to make predictions and

Â then moved to models that prescribed solutions.

Â In the previous video, we introduced the idea of merging two analytic technologies,

Â simulation and optimization.

Â By setting chance constraints, we were able to account for

Â the uncertainty in some key input data to an optimization model.

Â In this video, we are going to build on that and

Â describe how simulation and optimization can be fully integrated.

Â The process of developing a simulation optimization model

Â is not very different from what we have been doing.

Â It requires the definition of the three main elements of an optimization model,

Â that is the decision variables, constraints, and the objective function.

Â In addition, we need to identify the sources uncertainty

Â that would be part of the simulation model.

Â Let's consider the following situation.

Â An investor is considering 4 assets to invest a $100,000.

Â The annual return of each asset is the major source of uncertainty in

Â the decision problem.

Â Through a statistical analysis of historical data, the investors has found

Â probability distribution functions to model the annual return of each asset.

Â The functions and the limits on the amounts that she feels comfortable

Â investing in each assets are shown in this statement.

Â The annual return of bonds is assumed to be a uniform

Â distribution between 4% and 6%.

Â The returns of a stock follow a lognormal distribution with mean of 11% and

Â a standard deviation of 4%.

Â The returns of mutual funds is considered to follow a normal distribution

Â with mean of 8% and standard deviation of 1%.

Â The money market is considered to have a fixed return of 1%.

Â In addition, the investor must deal with other risks,

Â such as changes in the economy or major global events.

Â One way to account for such uncertainty is to consider your risk factors for

Â each asset as shown in this table.

Â Negative values represent low risk and values greater than 1 represent high risk.

Â The investor specifies a limit on the total amount on risk per $1 invested.

Â Let's assume that in the case of our investor,

Â she has set a risk factor limit of 100,000.

Â The problem is to find how to invest the $100,000 in order to maximize total

Â respective return by staying within the investment limits set by the investor and

Â by not exceeding the total risk factor.

Â Locate and open the Excel file Portfolio Optimization.

Â The top table in the spreadsheet contains the data for

Â the four assets that the investor is considering.

Â First we have the parameters of the probability distribution functions, then

Â there are the investment limits for each asset, and then we have the risk factors.

Â The model is in the bottom table.

Â As usual, the light gold cells indicate the decision variables.

Â In this case the decisions are the amounts to be invested in each asset.

Â The total in this column is the sum of all the investments,

Â which needs to be less than or equal to $100,000.

Â The risk for each asset is the product of the risk factor and the amount invested.

Â The total risk must be less than or equal to a 100,000.

Â The return for the first three assets is a random variable.

Â As we have done in all our models, these uncertain cells are shown in green.

Â They contain the probability distribution functions, a uniform distribution for

Â bonds, a lognormal distribution for stocks, and a normal distribution for

Â mutual funds.

Â The value column contains the return value in dollars.

Â The sum of this column is the total return value that we're maximizing.

Â We use light orange for

Â the total value cell to indicate that this is both the output of the simulation and

Â also the objective function value for the optimization.

Â Click on the ASP tab to access the Solver model panel.

Â The optimization model consists of maximizing E14,

Â which is the total value of the returns.

Â The decision variables are the investment locations in B10 to B13.

Â There are two constraints, one for the total amount invested and one for

Â the total risk.

Â In this simulation model, we have the uncertainty variables

Â of the annual returns in cells D10 to D12 and

Â we have the uncertainty output function E14.

Â This is the solution that ASP found for this problem.

Â This visible solution meets all the criteria specified by the investor.

Â The value in E14 is not expected return, it is

Â just one trial of the simulation using the values of the decision variables.

Â To find out more about the performance of this portfolio, double click on E14.

Â The expected return of this portfolio is estimated to be $6352.

Â The frequency of this tuition can be used to estimate probabilities that

Â the portfolio achieves some desired return levels.

Â For example, the simulation predicts that there is

Â a 9.96% probability that the return will be $8,000 or more.

Â Simulation optimization is one of the most sophisticated tools in data analytics for

Â business.

Â It allows you to create models that include many of the complexities

Â of business decisions.

Â In many ways, these models can be considered to be at the highest level

Â of the analytical process.

Â With this, we conclude our course on data analytics for decision making.

Â I hope that this course has given you insights in how to use data to

Â create models that can help businesses make better decisions.

Â