ROI is calculated as revenue minus cost divided by cost.

The decision variables in this model are the Budget Allocations

to the four different Ad types.

Enter some arbitrary values to check that the model works.

For example, enter 2,000, 2,000, 500 and

500 for Search, Display, YouTube and Gmail.

The objective function is the Total Net Revenue.

This is calculated with the sum product function.

Since multiplying ROI by the budget allocations results in revenue minus cost.

The model has seven constraints,

total add expenses is the sum of all the budget allocations.

This has to be less than or equal to $5,000.

To enforce the constraint that at least 60% of the budget much be spent in

display and search ads.

In cell I24 we add what we spend in display and center ads and

in cell K24, we calculate 60% of the budget.

The next constraint enforces that one is spent in YouTube ads,

does not exceed what is spent in Gmail ads.

The next constraint limits what we can spend in Gmail ads,

this should be less than 10% of the budget.

Cell calculates 10% of the budget, which results in $500.

Finally, we need to make sure that the number of

conversions that the plan is estimated to generate, is at least 1,500.

You can see that these can be calculated with the sum product of the budget

allocations and the conversions per cost.

Click on the ASP tab, the model pane shows the optimization model.

The objective is to maximize F-15.

The decision variables are C-16 to C-19.

And the constraints are shown on the normal constrains folder.

The mole includes non negativity bounds for the variables.

The arbitrary solutions that we entered is infant visible.

You can verify this by checking that all the constrains are satisfied.

The estimated net revenue is a little over $111,000.

To find the best plan, all you need to do is click on the Solve button.

First, however, check that the LP Standard Solver is selected.

Click on the Engine tab of the Model pane.

If the Standard LP Engine is not selected you can choose it from the dropdown menu.

Now, click on the Solve button.

The optimization model prescribes a budget allocation of $3,000 on such ads,

$1,000 on display ads, and $500 each on YouTube and Gmail ads.

And this solution is feasible and also optimal according to the company policies.

Now, click on the sensitivity report tab to figure out

the effect of some of these policies.

Let's interpret some of the shadow prices that are not zero.

The model estimates that ever additional dollar of advertising

generates $22.00 of net revenue.

This estimate is based on the ROI values in the model, and

it doesn't consider other factors that effect sales.

The model imposes a minimum of $1,000 to spent in display ads.

This constraint has a shadow price of minus $1.

These value indicates that net revenue will decrease by $1 for

each additional dollar that we insist on spending in display ads.

The opposite is true, for the $4 shadow price related to the limit on Gmail ads.

That is net revenue would increase by $4 for

every additional dollar that we allow the mail to allocate to Gmail ads.

The purpose of this video was to show you an additional application of optimization

in an area that is no logistics or transportation, I didn't want you to

think that this prescribed analytics too, is limited to operations problems.

The modeling part requires of their practice.

So I encourage you to think of problems that you can formulate as optimization

models.

Go through the steps of choosing the decision levels,

building the objective functions, and enforcing constraints.

The more you do it, the easier it gets.

This concludes our module on models for decision making.

We will build on this material in our next and final module of our course.