Useful quantitative models help you to make informed decisions both in situations in which the factors affecting your decision are clear, as well as in situations in which some important factors are not clear at all. In this course, you can learn how to create quantitative models to reflect complex realities, and how to include in your model elements of risk and uncertainty. You’ll also learn the methods for creating predictive models for identifying optimal choices; and how those choices change in response to changes in the model’s assumptions. You’ll also learn the basics of the measurement and management of risk. By the end of this course, you’ll be able to build your own models with your own data, so that you can begin making data-informed decisions. You’ll also be prepared for the next course in the Specialization.
1.3 Adding Risk: Managing Investments at Epsilon Delta Capital
Loading...
En provenance du cours de University of Pennsylvania
Modeling Risk and Realities
1143 notes
University of Pennsylvania
1143 notes
Cours 3 sur 5 dans Specialization Business and Financial Modeling
À partir de la leçon
Week 1: Modeling Decisions in Low Uncertainty Settings
This module is designed to teach you how to analyze settings with low levels of uncertainty, and how to identify the best decisions in these settings. You'll explore the optimization toolkit, learn how to build an algebraic model using an advertising example, convert the algebraic model to a spreadsheet model, work with Solver to discover the best possible decision, and examine an example that introduces a simple representation of risk to the model. By the end of this module, you'll be able to build an optimization model, use Solver to uncover the optimal decision based on your data, and begin to adjust your model to account for simple elements of risk. These skills will give you the power to deal with large models as long as the actual uncertainty in the input values is not too high.
Rencontrer les enseignants
Sergei SavinAssociate Professor of Operations, Information and Decisions
The Wharton School
Senthil VeeraraghavanAssociate Professor of Operations, Information and Decisions
The Wharton School
Hi, I'm Sergey Seven.
Welcome to session three, the last session of the first week of our course.
In this session, we'll discuss the notions of risk and reward.
We will look at an investment problem that chooses a simple score to describe
the risk associated with alternative investment vehicles.
The simple treatment of risk will prepare us for
dealing with more complex risk measures in the coming weeks.
So far in Week 1, we have looked at the general structure of optimization models,
and at how the spreadsheet versions of such models can be set up and solved.
One important assumption we have relied upon so
far is the assumption that for the decision maker,
there's no uncertainty around the future impact of any potential action.
In this last session of Week 1, we will largely continue to rely on this
assumption, but we will also start looking at the notions of reward and risk.
In particular, we will look at an investment example
where we use a simple risk score to mimic the future uncertainly.
This example will prepare us for
a more systematic discussion of uncertainty and risk in the future weeks.
So, here is an example we're going to use in the session.
We're looking at a financial company that needs to allocate $125 million
among 4 groups of investment vehicles for the next year.
One characteristic of these groups of financial products
that guides company's' decision is the expected annual return values,
with government bonds having the lowest values, and consumer loans the highest.
Another characteristic is a quality score that a company uses internally
to describe the quality quote unquote of a particular group of financial products.
The quality score communicates the likelihood that the actual returns
next year, will deviate from the expected values in an undesirable way because of,
say, a default.
So here are the quality scores for
the groups of financial products the company considers.
Note that the higher is the quality of the product, the lower is the expected return.
Expected returns and quality scores are not the only factors that influence
the investment decision that the company faces.
Here are the additional requirements that the investment portfolio must meet.
First, the entire investment budget must be used.
Second, the average quality of the portfolio must be at least 2.5.
Third, there are minimum and maximum amounts
that can be allocated to any particular group of financial products.
Finally, government bonds must account for
at least 25% of the overall bond component of the portfolio.
Okay, as usual, we start by creating an algebraic model
that expresses the problem in a structured form,
using decision variables, objective function, and constraints.
Decision variables in this example are investment amounts for
each group of financial products.
The objective that the company wants to maximize is the expected return on its
investment over the next year.
And the requirements become a set of constraints.
Let's start with the decisions variables.
We have four product groups, so we have four decision variables,
how much to invest in government bonds, municipal bonds,
corporate bonds, and consumer loans.
Now, the objective function.
In order to calculate the total return on investment described
by any combination of decision variables, we should multiply each decision
by the corresponding return value and add the results.
Finally, the constraints.
The first is the budget constraint.
In this case, we must invest the entire amount.
So the way to express this constraint is to equate the sum of
the decision variables to the investment amount.
The next constraint, limits from below,
the average quality score of the investment portfolio.
We express this constraint in algebraic form, by requiring that the sum
of scores of the products participate in the portfolio, weighted by
the fraction of the total investment allocated to each group is at least 2.5.
Now, the minimum and the maximum investment constraints.
On the maximum side, no group can get more than 50%
of 125 million, which is 62.5 million.
So here's how the minimum and the maximum investment constraints can be expressed.
They're simply the lower and
the upper bounds on the values of our decisions variables.
Finally, the constraint on the minimum fraction of government bonds.
The way we express it is by calculating the fraction that the investment in
government bonds makes in the total bond investment and,
requiring that this fraction is at least 25 percent.
So here's our complete model.
Decision variables, objective function, and constraint.
Note that we have added as usual non-negativity constraint on investment
amounts.
Next, let's go to the Excel template we will use to set up and solve this model.
So this is our Excel template epsilon, delta,
capital underscore zero posted on the course site.
As usual, we need to specify to the Excel and
to the solver where our decisions variables are,
where our objective function is, and what constraints we are facing.
We start with the decision variables.
We have four decision variables.
We're presenting the investment amounts for four financial product groups.
On the spreadsheet, I will use the cells B14 through E14 for
this variables.
For example, cell B14 will represent the investment amount in government bonds.
I will put some initial values into the cells.
For example, I'm going to invest 50 million in the government bonds and
25 million into each of other
product groups.
I will also change the color and
the font in the decision variable cells to make them stand out.
Now it is time for the objective function.
The objective function we're maximizing in this case
is the total expected return of the portfolio.
On the spreadsheet, the objective function value would be placed in the cell G6.
So we need to be able to calculate
the objective function value for any combination of decision variables.
The total expected return for the investments reflected in the cells
B14 through E14 is given by the sum product of the investment amounts and
their returns expressed as percentages.
So let's this formula in G6,
SUMPRODUCT of our investment amounts and
expected returns, and let's not forget
that those expected returns are percentages.
Okay, again, as before, we should make objective function bold and red.
Now the constraints.
First, the investment amount.
We will use the cell G14 to calculate the sum of investment amounts.
And in E14, we have an investment budget that we have to allocate.
The next constraint we want to put in is the constraint on the average quality of
the portfolio.
What we're going to do first is we calculate the fractions
of the total investment amount that we'll put into each product group.
We're going to put the values of those fractions into the cells B16 through E16.
For example,
in B16 we're calculating the fraction that goes into the government bonds.
That's B14, currently 50 million, divided by
I14, which has 125 million.
And we would like to actually enter the address of
the cell I14
because we'd like to copy and paste this formula into the other cells.
So changing the addresses from relative to absolute allows us to instruct the Excel
not to change the address of the cell when the formula is copied into other cells.
So if you now copy the formula from B16 into C,
D, and E16, we're going to get the correct formulas.
Let's check. For example,
this is the formula in the cell E16.
That's 25 million divided by 125 million.
That's 20% as a fraction.
Okay.
Let's just return.
The borders to their past state.
Now we can use the investment fractions to calculate the average portfolio quality.
We'll put this calculation into the cell B18.
So what we need to do is we need to calculate the sum
product of the quality's course weighted by the investment fractions.
Let's have a look at the formula.
So the sum product of the investment fractions and
the quality score of each investment.
Now we want to make sure that the average portfolio quality is greater or
equal to 2.5.
Okay, finally, we need to make sure that the fraction allocated to
government bonds is at least 25 percent of the entire sum invested
in government, municipal, and corporate bonds.
So, the way we do it, is we just calculate the fraction directly here.
So we divide the government bond amount
by the sum of three bond investments.
Okay, we're ready to call solver.
Data, solver, three elements of the decision model.
The objective function, that's our G6.
I want to maximize the expected return.
Our decision variables are in the cells B14 through E14.
The constraints are we start with
the total investment budget
constraint, G14 = I14.
Then, we move to the risk score requirement.
The average risk score has to be at least 2.5.
And finally, the fraction allocated
to government bonds must be at least 25% of
all allocations to bond financial products.
Of course, we should not forget the minimum and
maximum investment amounts.
On the minimum side, we force all four decision variables to be greater or
equal than the minimum value which is 20 million.
And on the maximum fraction, we can just write it like this.
So the fraction for each investment
group cannot be more than 50%.
That's slightly different from how we express this constraint in the slides,
but the optimal solution, of course, will be exactly the same because this is
an equivalent way of writing this constraint.
Okay, so
now we click Solve The solver tells us that solution has been found.
And here it is.
Here again is the optimal solution.
In the previous session, we have talked about how important it is to not limit
the use of solver to simply calculating the optimal decision for
a particular combination of problem parameters.
It is useful to also investigate how the optimal solution changes with the values
of problem parameters.
Often this can generate import insights into the nature of your model.
For example, what if the company's willing to tolerate
lower quality of the portfolio, and therefore, higher risk?
How much more in terms of the expected return can the company obtain?
Here's what happens if we resolve our model for
the values of the required portfolio quality ranging from 2 to 3.5.
Here we've plotted the optimal expected return as a function of the minimum
portfolio quality.
These results are stored in a sheet called Analysis in the epsilon
delta capital solution file.
If you look at these results, you see the trade-off between what we can call risk,
that's the opposite of portfolio quality, and reward that is expected return.
And the trade-off is that if the company wants higher reward,
it should be willing to tolerate higher degree of risk.
The blue points on the graph depict the best possible level of reward
that can be achieved for a given level of risk.
In a similar way, a composition of the optimal portfolio is
influenced by the company's risk tolerance level.
Across a wide range of minimum portfolio quality levels,
two of the four product groups, municipal and corporate bonds, do not
really offer attractive combination of risk and reward features for this company.
Government bonds and consumer loans dominate the portfolio composition,
and as the risk tolerance increases so
does the fraction of consumer loans in the optimal portfolio.
We have completed the first week of our course.
So far, we have looked at the optimization approach designed to find the best way
forward in business environments with low levels of uncertainty.
As the level of control that a decisionmaker exercises in his or
her environment decreases, the need to explicitly manage potential
exposure to risk becomes more and more prominent.
In week two, we'll take a closer look
at a particular way of modeling future uncertainty, called scenario approach.
And we'll see how the optimization tool kit can be adapted to making decisions
in highly uncertain settings.
See you next week.
Explorer notre catalogue
Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.
Coursera propose un accès universel à la meilleure formation au monde,
en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.
© 2018 Coursera Inc. Tous droits réservés.
