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.