How To Build an Optimization Model: Hudson Readers Ad Campaign (Modeling Risk and Realities)

Loading...

來自 University of Pennsylvania 的課程

Wharton Business and Financial Modeling Capstone

270 個評分

University of Pennsylvania

Wharton Business and Financial Modeling Capstone

270 個評分

課程 5（共 5 門，Specialization 商业与金融建模）

In this Capstone you will recommend a business strategy based on a data model you’ve constructed. Using a data set designed by Wharton Research Data Services (WRDS), you will implement quantitative models in spreadsheets to identify the best opportunities for success and minimizing risk. Using your newly acquired decision-making skills, you will structure a decision and present this course of action in a professional quality PowerPoint presentation which includes both data and data analysis from your quantitative models. Wharton Research Data Services (WRDS) is the leading data research platform and business intelligence tool for over 30,000 corporate, academic, government and nonprofit clients in 33 countries. WRDS provides the user with one location to access over 200 terabytes of data across multiple disciplines including Accounting, Banking, Economics, ESG, Finance, Insurance, Marketing, and Statistics.

從本節課中

Steps 1 and 2: Yahoo Finance

In this module, which correlates to Steps 1 and 2 in the Project Prompt, you'll be working with a historical data set to calculate performance data and to provide summary statistics on that data. These calculations will allow you to practice using Spreadsheets for financial calculations, and provides the foundational skills and numbers for the next steps of the project. First, you'll use the set to calculate daily returns on a set of securities. You'll then use your Spreadsheet skills to calculate summary statistics. You'll be given the opportunity to test your knowledge with a sample return to see if your calculations are correct. And you may want to refresh your recollection of the content from the Specialization with the lectures included here. The work you complete this week allows you to form the basis for comparing stock performance, which you will use in creating the investment portfolio for your final project as well as the comparison to the performance of a single stock.

Definition and Uses of Models, Common Functions (Fundamentals of Quantitative Modeling)14:58

How Models are Used in Practice (Fundamentals of Quantitative Modeling)10:42

Mathematical Functions (Fundamentals of Quantitative Modeling)20:27

Navigating a Spreadsheet and Crafting Formulas (Introduction to Spreadsheets)19:40

How To Build an Optimization Model: Hudson Readers Ad Campaign (Modeling Risk and Realities)13:19

Data and Visualization: Graphical Representation (Modeling Risk and Realities)22:09

與講師見面

Richard Lambert
Professor of Accounting
Accounting- Wharton School
Robert W. Holthausen
Professor
Accounting
Don Huesman
Managing Director, Wharton Online
Innovation Group- Wharton School
Richard Waterman
Professor of Statistics
Statistics-Wharton School

Hi, I'm Sergei Seven, I'm an Associate Professor at

the Department of Operations Information and Decisions at the Wharton School.

Welcome to week 1 of our Modeling Risk in Reality Course and

the ultimate goal of our course is to introduce you to the technique making day

to driven managerial decisions using spread sheets.

This week we'll begin by analyzing settings with low levels of uncertainty

and looking at the ways of identifying the best decisions on site settings.

In particular,

we'll go over a set of tools combined under the general name of optimization.

This optimization approach will prove very useful in the following weeks

when will be looking at more complex settings.

Settings with high degree of uncertainty and

study how different ways of quantifying uncertainty in risk

can be implemented using readily available spreadsheet tools.

Let's start, in week 1 we will working with the optimization tool kit

that is designed to analyse decisions in low uncertainty settings.

In the first of our three sessions we will use an advertising example.

To see how to build an algebraic model

of a business decision that ties together three main elements.

Decisions, objective and constraints.

In session 2, we will create a spreadsheet version of this model and

use an optimization tool called Solver to find the best decision.

Well also see how this best decision adapts to alternative data inputs.

In session 3, we will look at an example

that adds a simple representation of risk to our modelling.

Let's have a look at our first example.

In this example we'll look at a company that designs and sells e-reader devices.

The company is designing its advertising campaign for a new two version product.

And it is interested in understanding how it should distribute

its massive advertising budget of an equivalent of $195

million US I'm on to major Asian markets, India and China.

The company's decision on how to allocate it's advertising budget is influenced,

among other things,

by how far it's advertising dollars go in terms of generating extra sales.

A sort of return on advertising.

This return on advertising depends on the product and the market.

For example, a $1 million spent advertising

the standard product in India return $50,000.

Hudson Readers assumes that advertising effect adds up across markets and

products.

So, in order to project the cumulative effect

of spending $1 million on the enhanced version in China.

And $2 million on the standard version in India,

we just need to add two separate contributions to the net sales increase.

So, the company needs to decide how much to spend on

each product market combination to maximize the total advertising impact.

There's several requirements that the advertising campaign must satisfy.

In India, they must get at least $3 million in the net sales increase.

In China, at least $4 million.

And the Enhanced version must not lag too far behind the Standard version,

in terms of net sales increase.

One important aspect of this problem is that it is deterministic.

We only use numbers, not probability distribution.

For example advertizing effectiveness that return on company advertizing dollars

is a fixed number for each product market combination that we assume will be

realized for the probability 100% as opposed

to a distribution of numbers with different chances of being realized.

This is a very convenient assumption.

In particular, it tells us that the company knows exactly

the impact of any decision it contemplates.

Imagine that the actual advertising effectiveness for

the standard product in India is random.

And can take two values with equal probabilities.

The advantage of replacing the random quantities by their expected values,

is that one can then have a good chance of solving very large problems

involving many products and many markets.

The down side of using such an approach is that there were common decision that come

from an analysis that relies on removing uncertainty may not be applicable to

settings where this uncertainty is significant.

We'll have closer look at such settings in the coming weeks.

So this is a short version of our problem formulation.

We'll first express this formulation using algebra and

then convert it into a spreadsheet form.

The first element of an algebraic optimization model is decisions or

decision variables.

In this example, the advertising amount for

each product market combination is what the company needs to decide upon.

The next element of the model is the objective or objective function,

something the decision maker wants to maximize or minimize.

In our problem, the company wants to maximize the total net sales increase.

So, this is our objective function.

The word function reflects the fact that the objective

is the function of decision variables.

The final element of the model is the constraints.

In other words, requirements that a decision maker must satisfy.

The four major requirements in our example.

First of all,

the total amount of advertising spending cannot exceed advertising budget.

Also, there are three more requirements.

The first two enforce the minimum sales increase in India and in China

while the third one stipulates that the net sales increase for the enhanced model

must be at least 80% of the net sales increase for the standard model.

So we have four major constraints.

We have three main elements of our model and

the overall task is to select the values of the decision variables to maximize

the objective while staying within limitations outlined by constraints.

Let's start with decision variables.

We have two products and two markets.

So, we have four decision variables.

The amount to spend advertising Standard version in India,

Standard version in China, Enhanced version in India,

and Enhanced version in China for variables.

A solution is a particular combination

of decision variables such as 70,

50, 50 and 25.

Now the objective function.

In our example, Hudson Readers want to select the value of decision variables

that will maximize the total net sales increase.

In order to understand how to express this subjective as a function of decision

variables, let's first calculate the total net sales increase that corresponds

to a particular solution.

For example, 70, 50, 50, and 25.

If the company spends $50 million to advertise these standard version in India,

the sum will produce sales effect of 5%.

So, the net sales increase resulting from this advertising

spending will be 0.05 times $70 million.

Similarly, the other three spending amounts will be multiplied by their

respective sales effect values, To

yield the total net sales increase of $7.25 million.

So, what if we look at any potential combination of advertising amounts?

How much net sales increase well result from ASI,

ASC, AEI and AEC?

Advertising amounts.

Well, we need to multiply each variable by its own sales effectiveness value,

and then add results.

This is an algebraic expression that will allow us to calculate

the impact of any decision we may want to explore.

Now the constraints.

First is the budget constraint.

Whatever the company spends cannot exceed the advertising budget of $195 million.

Again let's check if this constraint is satisfied for

the solution we looked at earlier.

Yes, it is satisfied.

That all spending is exactly $195 million.

Okay, what about the constraint on the total net sales increase

reached on the Indian market?

Okay let's calculate how much net sales increase is achieved in India.

The answer is 4.5 million.

This constraint is satisfied.

Now, what about net sales increase reached on the Chinese market?

Again, calculating the net sales increase for China, we get $2.75 million.

This is short of the goal of $4 million.

Now the third guideline, the net sales increase for

the standard version is $5.5 million.

And the net sales increase for

the Enhanced version is $1.75 million.

The third guideline is obviously violated.

Depending on whether they satisfy constraints or

not, all solutions can be separated into two groups.

Feasible solutions and infeasible solutions.

Even if solution violates only one constraint, it isn't feasible.

And the solution we looked at isn't feasible.

So, what kind of conditions should a feasible solution satisfy?

Let's start with advertising budget constraint.

In algebraic form, this is how we express this constraint.

The sum of all spending amounts must not exceed, $195 million.

Let's write down the constraint for the Indian market.

We need to multiply each spending amount on this market by

the respective sales effectiveness value and

request that the sum of these contributions is at least $3 million.

In the similar way, we can write a constraint for the Chinese market.

Finally, the constraint on the standard versus enhanced net sales increase.

On the left-hand side of this constraint

we calculate the amount of net sales increase realized for the enhance version.

And on the right-hand side the 80% of the net sales increase realized for

the standard version.

Now, we can put all the algebraic expressions we have created into one model

that combines decision variables, objective function,

and constraints.

Note that we have included non-negativity constraint

which stipulates that no spending amount can be negative.

This seems like a rather odd constraint to write down explicitly.

After all, we all know that advertising amounts cannot be negative.

But remember, we're about to pass this model to Excel and

Excel does not really know anything about advertising.

So, this constraint may turn out to be necessary.

In the session, we took a first look at the optimization

approach design to identify the best option

in business settings where a decision maker can predict with near uncertainty.

The impact of any decision he or she considers.

In any optimization model, three elements must be present.

Decisions, objective, and constraints.

Connecting these three elements using Algebra, we seem challenging or

even unnecessary at first.

But, as often a very important step that makes it

easier in practice to identify floored assumptions or missing modeling features.

Once a step is accomplished, it is time to use spreadsheet tools

to find the best option, we'll do that in session 2.

See you then.

探索我們的目錄

免費加入並獲得個性化推薦、更新和優惠。

Coursera 致力於普及全世界最好的教育，它與全球一流大學和機構合作提供在線課程。

© 2019 Coursera Inc. 保留所有權利。

通過 App Store 下載

通過 Google Play 獲取