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.2 Optimizing with Solver, and Alternative Data Inputs
Loading...
Из курса от партнера University of Pennsylvania
Modeling Risk and Realities
1336 оценок
Из урока
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.
Познакомьтесь с преподавателями
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 Sergei Savin, and
this is session 2 of the first week of our course on modeling risk and realities.
In session 1,
we have looked at an example of decision making in low uncertainty setting.
And have formulated an algebraic model designed to guide us to the process of
finding the best possible decision.
In this session, we'll set up a spreadsheet version of that algebraic
model and we'll use an Excel tool called Solver to help us find the best option.
But we'll also see how this best option and
its impact change with the model inputs.
In the first session of week 1,
we have looked at the ways to set an optimization model in algebraic form.
By identifying decision variables,
objective function and constraints.
In this session, we will implement this algebraic model in a spreadsheet
form and solve it using Excel's optimization tool.
So as a reminder,
here's an algebraic representation of the advertising problem we're focused on.
Next, we will move to set up the elements of this model, decision variables,
objective function, and constraints in the spreadsheet format.
We have prepared for you an Excel template Hudson Readers_0,
which we will use for this purpose.
This is our Excel file Hudson Readers_0.
As I mentioned, it is posted on our course's site.
You can use it to follow the steps I'm going through.
In this preliminary Excel file version, I have entered the problem parameters and
already outlined the structure of the spreadsheet model we're going to create.
In our analysis, I will use Excel 2013 on the Windows platform and
all of my comments apply only to this version of Excel.
However, the steps you will have to go through will be similar, if you are using
Excel on Mac or if you're using another spreadsheet program, like Google Sheets.
We will be using Excel add in called Solver to find the best budget allocation.
And before we set up a spreadsheet model,
let us make sure that we locate the Solver.
We're using Excel 2013, and
in this version the solver can be found located under the top called Data.
In the portion called Analysis.
If you see the Solver button under this tab, you're ready.
If the Solver button is not there go to File > Options
> Add-Ins, here you will see the tab Excel options.
Find the manage Excel Add-ins at the bottom of the tab and click Go.
You will be able to see the tab called Add Ins,
make sure that the Solver Add In is checked and click OK.
Now the Solver should be visible under the Data tab.
In other versions of Excel, such as Excel for Mac or in the Google Sheets,
the steps you need to take to find Solver maybe different, but
just remember that the Solver is an add in.
Find where the add ins are and make sure that the Solver is activated.
Let's set up our model.
We need to translate our algebraic model into a spreadsheet form.
That is we need to specify on the spreadsheet.
Three main components of optimization model.
Decision variables, objective function and constraints.
First, the decision variables.
In an advertising problem we have decision variables representing
the advertising amounts for each of the four product market combinations.
On this spread sheet I have reserved cells B11,
C11, B12 and C12 to hold the values of these variables.
For example, the cell B11?
The whole value of the amount expressed in million of dollars,
spend advertising standard product in India.
Let us put some initial values in this decision variable cells.
Say, let's put 40 which stand for 40 million into each cell.
I think it is useful to have a simple color coding scheme in the optimization
spreadsheets to make sure that when the spreadsheet is opened by you or
someone else who wants to use your model.
It is easy to read and navigate as part of this color coding scheme.
Let's color the decision variable cells in blue and change the font to Bo ld.
We have our decision variable cells, and the Solver will be instructed to change
the values in those cells to find the best decision for us.
Now we move to the second element of our model, the objective function.
The objective to be maximized in our example, is the total nets sales increase.
So we needed to instruct Excel in Solver to calculate the net sales increase for
any combination of decision variable values.
On our spreadsheet, I have reserved the cell E6 for
the value of the objective function.
Let's calculate the total net sales increase that will be generated
by the advertising plan in the cells B11 through C12.
So we go to the cell E6 and type in the formula for the total net sales increase.
In order to calculate the impact of advertising spending amounts in
the decision variable cells.
We need to multiply the value in each decision variable cell
by the corresponding advertising effect value from the cells B6 through C7.
So the formula we want to place in the cell E6 is
B11*B6, 40*0.05
+ C11*C6,
40*0.4 +
B12*B7, 40*0.02
+ C12*C7,
40*0.03.
We press Enter, and the cell E6 reflects
the Total Net Sales Increase of $5.6 million.
Now this is all good but typing in this formula can become impractically
cumbersome if the number of decision variables in a model is large.
Fortunately, Excel has a convenient shortcut function called SUMPRODUCT.
SUMPRODUCT, as it's name suggests calculates products
of values in corresponding cells in two equally sized
groups of cells and then adds those products.
In our case, the formula in the cell E6 can be written as.
Some product of cells
B11 through C12 and
cells B6 through C7.
When we click enter, the same result as before is displayed and
it's no surprise, because the formula with the SUMPRODUCT form,
and the formula that we had before are equivalent.
You can find more information about SUMPRODUCT function in the Excel help.
By the way, in Excel for Windows,
I'm using a shortcut F2 to display the formula in any cell.
For example, in the objective function cell B6.
As we did for the decision variable cells,
let's also use a color coding for the objective function cell.
Let us use red color and bold font.
To make it stand out.
But formula in cell E6, we'll calculate the net sales increase for
any possible combination of decision variable values.
For example, we can change the value in the cell B11 to 50
million reflecting a potential increase.
In advertising for the standard product in India.
And we see that the total net sales increase is now 6.1 million.
We will later instruct Solva to change the values in our decision variable cells
to make the value of the objective function cell as large as possible.
Clearly we cannot put just any values in our decision variable cells but
only the ones that correspond to feasible solutions.
So it is time to specify the constraints for our problem.
The first constraint, is the budget constraint.
The budget value $195 million is stored in the cell D15.
We want to make sure that the sum of our decision variables, that is the sum of
spending amounts for two products in two markets, does not exceed this value.
My plan is to put the value of the sum into the cell B15.
Sum of four decision variables.
Currently it is $170 million.
Later, when we will ask the solver to find the best advertising policy.
We will also instructed to make sure that the value in the cell B15
never exceeds the value in the cell D15.
I put a corresponding sign in the cell C15.
Just to embellish the spreadsheet.
The sign will not play any role in the optimization.
It's just a visual cue.
So you don't have to use it if you don't want to.
Okay, the next constraint is the one that requires Hudson Readers to generate
at least three million in the net sales increase in the Indian market.
The limiting value three million is stored in the cell D16.
So in the cell B16, we calculate the net sales increase
in India resulting from the decisions in cells B11 through C12.
This increase can be calculated by multiplying the advertising
spending on the standard product in the Indian market by the respective
advertising effectiveness value.
And do the same for the enhanced product in the Indian market.
And then add those two products.
This sounds like a SUMPRODUCT and that's what we're going to use.
SUMPRODUCT of.
Spending in India on both types of products times
the corresponding effectiveness coefficients.
Now let's do the same thing for
the constrained on the minimum net sales increase in China.
We put the corresponding formula in the cell B17,
SUMPRODUCT of spending in China
multiplied by respective coefficients of sales effectiveness in this market.
Now the last constraint tells us that the total net sales increase for
the enhanced version must be at least 80% of that increase for the standard version.
So in the cell B18, we
we'll calculate the total net cell's increase for the enhanced version.
As before, we'll use a SUMPRODUCT formula.
So it's a SUMPRODUCT of spending on
the enhanced version across the tool markets
multiplied by the respective coefficients.
And now, in the cell D18 I'm going to put the formula for
the 80% of the net sales increase for the standard version.
So it's equal to 0.8 times SUMPRODUCT of
spending on standard version, across two markets,
multiplied by the two coefficients.
The constraint that will pass on to the Solver will be that
the value in the cell B10 can't be lower than the value in the cell D18.
We can put the corresponding visual cues in this three cells.
Okay.
We're now ready for the Solver.
So we go to data.
Click on solver.
The solver parameters dialogue box pops up.
Here in the set objective part, we click on the cell selection tool and
choose the cell E6 to represent our objective function.
The solver can maximize and minimize the selected objective or
it can find a combination of decision variables to produce a required value
of the objective function.
Here, we're maximizing the total net sales increase, so we select Max.
Now, we need to communicate to Solver where our decision variables are.
So, we use, by changing variable cells selection tool
to point this over to the cells D11 through C12.
Find all the constraints.
Here we use subject to the constraints part,
and we specify the budget constraint first.
B15 less or equal than D15.
By the way, we could specify constraints by using equal signs,
greater equal signs, we can force decision variables to take integer values or
binary values 0, 1, or we can ask over to find
a solution with all decision variables taking different values.
Next, we need to add constraints on sales increase in India,
China, and also enhanced versus standard version constraint.
So all of those three cells should be greater or
equal than the values in these three cells.
We click OK,
now we see all the constraints have been communicated to Excel.
What is left is adding the constraint that tells the solver that our decision
variables must take non-negative values.
Solver offers a convenient way of introducing non-negative to constraint for
all decision variables at the same time.
All we need to do is to check the box,
make all unconstrained variables none negative.
Next, we need to select a solving method.
Solver offers several choices here.
GRG nonlinear, LP, and even Evolutionary solver.
I would set the solver method as GRG nonlinear.
This solution method is very general and
will allow you to work with many different kinds of models.
A couple of things to remember,
since GRG nonlinear is a general method of solving optimization problems,
it will try to find the best possible solution for any model you formulate.
However, it may not always be able to guarantee, that what it finds and
gives you as a solution is actually the best possible decision.
In addition, the solution that the GRG Nonlinear method produces
may depend on the initial values in the decision variable cells.
So, when optimizing using this method,
run it several times with different starting values of the decision variables
to see if you can improve the objective function value.
Okay, let's run the Solver.
We need to click the Solve button.
And also to make sure that Solver found a solution is
displayed in the dialog box that appears.
Here, we have the optimal solution.
We will discuss it in a couple of minutes but
first a few words about solver messages.
This time the solver came up with a message solver found a solution.
This is the message that indicates to us
that everything went according to the plan.
However it is possible to get other, not so encouraging messages.
For example, suppose we forgot to include an important constraint.
Let's go to the solver dialogue and remove all the constraints.
If we solve our model now, the solver comes back with a complaint,
objective cell values do not converge.
This means that the net sales increase in this model can becoming
infinitely large, when you see a message like that
it probably means that you forgotten an important constraint.
Okay, let's
put back our
constraints.
Another possibility arises when you over constraint your model.
In other words, when there's no feasible solution.
To demonstrate how can this happen, let us reduce the advertising budget
to $180 million.
This was a previous solution.
Let's go here and change the budget to $180 million.
When we're around the model now.
The solver says that it could not find a feasible solution.
To summarize, when you miss a constraint or you put constraints
that contradict to each other, you will get one of those messages.
And then you have to go back to the drawing board to see how your model
should be corrected.
Here is the solution to the Hudson Readers problem.
We have stored the details in the excel file with the same name.
As the solution implies, the entire advertising budget is used up.
This of course is not surprising.
Given the advertising alternatives the company has,
the maximum net sales increase it can achieve using the budget
of $195 million is around $7.38 million.
In other words, the best return on advertising
spending that the company can achieve is 7.38
divided by 195, is around 3.8%.
The major portion of spending
goes to the advertising of the enhanced version in China.
They advertise in China with a return of 3%.
And only minor portion is devoted to advertising
channels with high returns, 4% and 5%.
This, of course, is a consequence of the constraints that the company faces.
It is important to keep in mind that the solver can be used to investigate
impact of changes in the model parameters on the optimal choice and
the optimal objective function value, and to answer various what if questions.
For example, if the company is interested in how the extra
advertising budget can change the way it plans its campaign, solver can help.
This is what happens if the problem is resolved, with a slightly higher
value of the advertising budget, namely, $200 million.
These extra five million, bring the total net sales from.
7.38 to 7.59 million.
The allocation of this new budget, in percentage terms, also changes.
Shifting away from promoting the standard version in China.
It turns out to be increasingly beneficial.
To use China market for the promotion of enhanced version, and
Indian market for the promotion of the standard version.
Of course, we can systematically change the budget value to see how it impacts
the Optimal Net Sales Increase Value and the budget allocation.
We can do it manually or we can do it in a more automated fashion
using an Excel Add-in Solver Table that you can download using this link.
The results are presented in the sheet called Analysis
in the Hudson Readers spreadsheet.
For example,
we can tabulate the values of the Optimal Net Sales Increase as a function of
the budget value to see how far the extra dollars of the Advertising Budget go.
In this case, extra budget brings extra net sales increase
at the rate of around 4.2 cents on a dollar.
As the budget increases in the range of budget values we looked at from
195 million to 170 million dollars,
this rate drops to around 3.9 cents on a dollar.
As the advertising budget increases,
it's allocation in percentage terms also changes.
In particular, the fraction of the advertising of the enhanced version
in China remains stable and high.
Around 56, 57% across the range of budget values we looked at.
While advertising the enhanced version in India remains unattractive.
As far as the standard version is concerned It's advertising shifts from
China to India as the advertising budget grows.
In the session we have solved our first optimization model in this course, and
there's more to come.
So far?
We have been dealing with the setting were outcome of any potential choice
is highly predictable.
In the next session, we'll continue working with optimization toolkit and
we'll look at an example where a decision maker uses a simple measure
to evaluate future uncertainty.
See you soon.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2019 Все права защищены.
