Question

The Westchester Chamber of Commerce periodically sponsors public service seminars and programs. Currently, promotional plans are under way for this year's program. Advertising alternatives include television, radio, and online. Audience estimates, costs, and maximum media usage limitations are as shown:

Constraint	Television	Radio	Online
Audience per advertisement	100,000	18,000	40,000
Cost per advertisement	$2,000	$300	$600
Maximum media usage	10	20	10

To ensure a balanced use of advertising media, radio advertisements must not exceed 50% of the total number of advertisements authorized. In addition, television should account for at least 10% of the total number of advertisements authorized.

(a)	If the promotional budget is limited to $18,200, how many commercial messages should be run on each medium to maximize total audience contact? If your answer is zero enter “0”.
	Advertisement Alternatives No of commercial messages Television Radio Online
	What is the allocation of the budget among the three media?
	Advertisement Alternatives Budget ($) Television $ Radio $ Online $
	What is the total audience reached?
(b)	By how much would audience contact increase if an extra $100 were allocated to the promotional budget? Round your answer to the nearest whole number.
	Increase in audience coverage of approximately

Answer 1

Consider the provide details to solve the subparts. The details are given as below:

Audience TV 100000 Radh 18000 O.Ge 40000 Cast Massage M% Mar% $2,000 10 10% $300 20 50% $600 10

a.

According to the provided details, consider T is number of television spot advertisements, R is the number of radio advertisements and O is the number of online advertisements. So, the constraint for the promotional budget is given as:

2000T+300R+6000 £ 18200

The constraint for the maximum media usage are given as:

R£ 20 T£ 10 O £ 10

The constraints for the radio advertisement is must not exceed 50% of the total number of advertisement is given as:

R£ 0.5(T+ R+ 0)

The constraint for the television should account for at least 10% of the total number of advertisements authorized is given as:

T: 0.1(T+R+O

And, the objective function to maximize the audience is given as:

Max Z= 1000007 + 18000R+ 400000

Hence, the linear programming model that can be used to maximize the audience is given as:

Max Z= 1000007 + 18000R+ 400000

Subject to the constraints:

2000T + 300R+ 6000 £ 18200 R £ 20 T £ 10 O £ 10

R£ 0.5(T+R+O) T: 0.1(T+R+O)

And the non-negative constraints is given as:

T, R and 03 0

Enter the above obtained linear programming model into the Excel spreadsheet, the screenshot of the model is shown below:

А 5 6 7 TV Radio Online в Audience 100000 18000 40000 с о E F Cost Max Usage Min % Max% $2.000.00 0 10% $300.00 20 50% $600.00 10 9 Budget $18,200.00

Now introduce the decision variables, in the cell B14:B16 for three media, calculate the total spend cost per advertisement in the cell B19 and calculate the total audience in the cell B21 by using the SUMPRODUCT function, the screenshot of the formulas is shown below:

Audience TV Radio Online 100000 18000 40000 C D E F Cost Max Usage Min % Max% 2000 10 =10% 300 20 -50% 600 10 Budget 18200 non-OESTOPOS Messages 10 TV Radio Online Total =SUM(B14:B16) Spend 1=SUMPRODUCT(B14-B16.CS.CT) 21 Audience =SUMPRODUCT(B14-B16-B5-B7)

Now, click on the “Data” tab in the menu bar, and press the “Solver option” a new dialog box will appear. Set the objective as “Max” and fill the cell $B$21, in the changing cell option select the cell $B$14: $B$16. Press on the “Add” button to add the constraint for television should account for at least 10% of the total number of advertisements authorized, the screenshot is shown below:

Cell Reference: $B$14 E > Constraint: SES5*$B$17 OK Add Add Cancel

Now, press “ADD” button in the above dialog box, and add similarly the constraint for the maximum media usage, the screenshot is shown below:

Cell Reference: $B$14:5B916 Constraint: $D$5:$D$7 59514: SEST I OK SDSS:5057 i cancel OK . Add Add Cancel

Now, press “ADD” button in the above dialog box, and add similarly the constraint for radio advertisement is must not exceed 50% of the total number of advertisement, the screenshot is shown below:

Cell Reference: $B$15 Constraint: F$6*$B$17 * < Add D OK Cancel

Now, press “ADD” button in the above dialog box, and add similarly the constraint for total spend should be less than or equal to total budget, the screenshot is shown below:

Cell Reference: SB519 Constraint: S859 I OK . Add Cancel

Press “OK” option in the above dialog box, the screenshot is shown below:

Solver Parameters Set Objective: $B$21 To: Max Min value of: By Changing Variable Cells: $B$14:$B$16 Subject to the Constraints: $B$14 >= $E$5*$B$17 $B$14:$B$16 <= $D$5:$D$7 $B$15 <= $F$6*$B$17 $B$19 <= $B$9 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve close

Select the Solving method as “Simplex” and select the report as “sensitivity”. And press the solve button in the above dialog box.

The screenshot of the obtained output is shown below:

А TV Radio Online B Audience 100000 18000 40000 C D E F Cost Max Usage Min % Max% $2,000.00 10 10% $300.00 20 50% $600.00 10 7 9 Budget $18,200.00 14 Messages 4 14 TV Radio Online Total 10 16 17 28 19 Spend $18,200.00 21 Audience 1052000

Hence, the optimal number of audience is 1052000, for the budget of television $8000, for the budget of radio is $4200 and the budget of online is $6000.

b.

The sensitivity report obtained in the part (a) is given as below:

Variable Cells Cell Name $B$14 TV Messages $B$15 Radio Messages $B$16 Online Messages Final Reduced Objective Value Cost Coefficient 4 0 100000 14 0 18000 10 11826.08696 40000 Allowable Allowable Increase Decrease 20000 118000 1E+30 3000 1E+30 11826.08696 Constraints Final Shadow Cell Name Value Price $B$14 TV Messages 4 0 $B$15 Radio Messages 14 5217.391304 $B$19 Spend Messages $18,200.00 51.30434783 Constraint Allowable R.H. Side Increase 0 1.2 0 2.936170213 18200 13800 Allowable Decrease 1E+30 8.05 3450

According to the above sensitivity report, the shadow price for the budget constraint is 51.30 and the allowable increases is 1100. Thus, if increase by $100 in the budget, then it will provide an increase in the audience coverage.