excel

Model

Advertising model
Inputs
Exposures to various groups per ad
	Timeless	Sunday Night Football	The Simpsons	SportsCenter	Homeland	Rachael Ray	CNN	Madam Secretary
Men 18-35	5	6	5	0.5	0.7	0.1	0.1	3
Men 36-55	3	5	2	0.5	0.2	0.1	0.2	5
Men >55	1	3	0	0.3	0	0	0.3	4
Women 18-35	6	1	4	0.1	0.9	0.6	0.1	3
Women 36-55	4	1	2	0.1	0.1	1.3	0.2	5
Women >55	2	1	0	0	0	0.4	0.3	4
Total exposures	21	17	13	1.5	1.9	2.5	1.2	24
Cost per ad	140	100	80	9	13	15	8	140
Cost per million exposures	6.667	5.882	6.154	6.000	6.842	6.000	6.667	5.833
Advertising plan
	Timeless	Sunday Night Football	The Simpsons	SportsCenter	Homeland	Rachael Ray	CNN	Madam Secretary
Number ads purchased	0.000	0.000	8.719	20.625	0.000	6.875	0.000	6.313
Constraints on numbers of exposures						Range names used:
	Actual exposures		Required exposures			Actual_exposures	=Model!$B$23:$B$28
Men 18-35	73.531	>=	60			Number_ads_purchased	=Model!$B$19:$I$19
Men 36-55	60.000	>=	60			Required_exposures	=Model!$D$23:$D$28
Men >55	31.438	>=	28			Total_cost	=Model!$B$31
Women 18-35	60.000	>=	60
Women 36-55	60.000	>=	60
Women >55	28.000	>=	28
Objective to minimize
Total cost	$1,870.000

Note: All monetary values are in $1000s, and all exposures to ads are in millions of exposures.

It's difficult to keep this example timely, with shows going off the air and new shows becoming hits. Feel free to substitute new shows (with reasonable data) for the ones shown here. Or add other shows to these.

The linearity assumption made here -- k times as many ads result in k times as many exposures -- is certainly questionable. Therefore, we present a nonlinear version of this example in Chapter 7.

Sensitivity Report 1

Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Advertising 1 Finished.xlsx]Model
Report Created: 11/1/2016 11:40:16 AM
Variable Cells
			Final	Reduced	Objective	Allowable	Allowable
	Cell	Name	Value	Cost	Coefficient	Increase	Decrease
	$B$19	Number ads purchased Timeless	0	10	140	1E+30	10
	$C$19	Number ads purchased Sunday Night Football	0	7.5	100	1E+30	7.5
	$D$19	Number ads purchased The Simpsons	8.71875	0	80	1.7438692098	29.0909090909
	$E$19	Number ads purchased SportsCenter	20.625	0	9	0.7619047619	0.4507042254
	$F$19	Number ads purchased Homeland	0	0.5	13	1E+30	0.5
	$G$19	Number ads purchased Rachael Ray	6.875	0	15	2.2857142857	1.1034482759
	$H$19	Number ads purchased CNN	0	2.25	8	1E+30	2.25
	$I$19	Number ads purchased Madam Secretary	6.3125	0	140	11.0344827586	6.9565217391
Constraints
			Final	Shadow	Constraint	Allowable	Allowable
	Cell	Name	Value	Price	R.H. Side	Increase	Decrease
	$B$23	Men 18-35 Actual exposures	73.53125	0	60	13.53125	1E+30
	$B$24	Men 36-55 Actual exposures	60	15	60	44	5.1162790698
	$B$25	Men >55 Actual exposures	31.4375	0	28	3.4375	1E+30
	$B$26	Women 18-35 Actual exposures	60	10	60	11	14.9310344828
	$B$27	Women 36-55 Actual exposures	60	5	60	44.8888888889	4.8888888889
	$B$28	Women >55 Actual exposures	28	2.5	28	6.2857142857	7.5862068966

Although you might prefer SolverTable to answer sensitivity questions, the classical sensitivity output shown here is certainly relevant. The nonzero reduced costs indicate how much less an ad would have to cost before it would be optimal to place an ad on that show. The shadow prices indicate how much it would cost to increase the exposure requirement to a particular group. Of course, the first and third shadow prices are 0 because the exposures to these groups already exceed the minimum.

Model Integer

Advertising model
Inputs
Exposures to various groups per ad
	Timeless	Sunday Night Football	The Simpsons	SportsCenter	Homeland	Rachael Ray	CNN	Madam Secretary
Men 18-35	5	6	5	0.5	0.7	0.1	0.1	3
Men 36-55	3	5	2	0.5	0.2	0.1	0.2	5
Men >55	1	3	0	0.3	0	0	0.3	4
Women 18-35	6	1	4	0.1	0.9	0.6	0.1	3
Women 36-55	4	1	2	0.1	0.1	1.3	0.2	5
Women >55	2	1	0	0	0	0.4	0.3	4
Total viewers	21	17	13	1.5	1.9	2.5	1.2	24
Cost per ad	140	100	80	9	13	15	8	140
Cost per million exposures	6.667	5.882	6.154	6.000	6.842	6.000	6.667	5.833
Advertising plan
	Timeless	Sunday Night Football	The Simpsons	SportsCenter	Homeland	Rachael Ray	CNN	Madam Secretary
Number ads purchased	0	0	8	16	2	6	0	7
Constraints on numbers of exposures
	Actual exposures		Required exposures
Men 18-35	71.000	>=	60
Men 36-55	60.000	>=	60
Men >55	32.800	>=	28
Women 18-35	60.000	>=	60
Women 36-55	60.600	>=	60
Women >55	30.400	>=	28
Objective to minimize
Total cost	$1,880.000

Note: All monetary values are in $1000s, and all exposures to ads are in millions of exposures.

This is an example where you might think the rounded solution from the original (noninteger) solution would be the optimal integer solution. However, this isn't quite the case. In fact, the rounded solution isn't feasible -- it doesn't quite satisfy the exposure constraints. (Try it out to see.) Also, it places no ads on Homeland, whereas the solution above places 2 ads on this show. This just demonstrates that the best integer solution is not necessarily "close" to the best noninteger solution. On the other hand, their objective values are reasonably close. The integer solution is "only" $10,000 dollars more expensive than the noninteger solution. Is it guaranteed to be more expensive? Let's just say that it can't be cheaper. Remember that constraints can only hurt the objective -- a more constrained problem can't have a better objective value.

SolverTableSheet

1
$D$22
1
700
850
25
$B$26
$A$31

image1.png