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 newspaper. Audience estimates, costs, and maximum media usage limitations are as shown:

Constraint	Television	Radio	Newspaper
Audience per advertisement	100000	18000	20000
Cost per advertisement	$2000	$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.

1. If the promotional budget is limited to $31,100, how many commercial messages should be run on each medium to maximize total audience contact? What is the allocation of the budget among the three media? If required, round your answers to the nearest dollar.
   

   Let	T = number of television spot advertisements
   	R = number of radio advertisements
   	N = number of newspaper advertisements

   	Budget ($)
   T = ——
   R = ——
   N = ——
   	Total Budget = $

   
   What is the total audience reached? Round your answer to the nearest whole number.
   
   ——————————
   
2. 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.
   ————————————

Answer 1

In order to answer the question, we have to use the Solver function in Data menu tab of Excel.

Kindly see the attachment for Solver
Clear From Access Connections Solver Parameters Properties From Web From Other Existing Sort Refresh From Text Edit Links Co All Sources Sort Get External Data Connections SBS11 Set Objective X V f B11 10 To: Value Of: 0 Max Min A C By Changing Variable Cells: Newspaper Television Radio 1 SBS7:SDS7 Audience per advertisement 2 100000 18000 20000 Cost per advertisement Maximum media usage 2,000.00 $ 300.00 $ 600.00 3 Subject to the Constraints: 20 10 10 4 SBS7 <SBS4 SBS7 SBS6 0.1 SBS9 <31100 Add Total media authorised 6 40 SCS <= $B$6*0.5 <SC Change Actual Media usage 7 10 20 8.5 $20,000.00 $6,000.00 $5,100.00 Budget Total Promotional Budget 8 Delete $31,100.00 9 10 Reset All Total Audience Contact 11 1530000 Load/Save 12 13 Make Unconstrained Variables Non-Negative 14 Select a Solving Method: GRG Nonlinear Options 15 16 Solving Method 17 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. 18 19 20 21 Help Solve Close 22 23

Clear Connections From Access Properties Solver Parameters Fi From Web ZI Sort From Other Existing Refresh From Text Edit Links Sources Connections All Sort Get External Data Connections SBS11 Set Objective: X -B2 B7+C2*C7+D2*D7 B11 To Value Of: OMax Min B A By Changing Variable Cells: Newspaper Television Radio 1 SBS7:SDS7 Audience per advertisement Cost per advertisement Maximum media usage 18000 100000 20000 2,000.00 $ 300.00 $ 600.00 Subject to the Constraints: 4 10 20 10 SBS7 < SBS4 Add 5 Total media authorised Actual Media usage Budget Total Promotional Budget 40 = SBS6 0.5 SDS7 <=SDS4 6 SCS7 Change 10 20 .66666667 $20,000.00 $31,200.00 $6,000.00 $ 5,200.00 Delete 10 Reset All 11 Total Audience Contact 1533333.33 Load/Save 12 Make Unconstrained Variables Non-Negative 13 14 Select a Solving Method: GRG Nonlinear Options 15 16 Solving Method 17 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. 18 19 20 21 Help Solve Close 22

a)

	Television	Radio	Newspaper
Audience per advertisement	100000	18000	20000
Cost per advertisement	$    2,000.00	$     300.00	$       600.00
Maximum media usage	10	20	10

Total media authorised

40

=Total media authorised = sum of maximum media usage of television + radio + newspaper = 40

Actual Media usage	10	20	8.5
Budget	$ 20,000.00	$ 6,000.00	$   5,100.00
Total Promotional Budget	$ 31,100.00
Total Audience Contact	1530000

Actual Media Usage are the variables which have the mentioned constraints - Television media >=10% of total i.e >=4

Radio Media <= 50 % of total i.e <=20

Budget is calculated by = sum (Actual media usage * Cost per advertisement) of television + radio + newspaper

Budget <= $ 31,100

Total Audience Contact = sum (Actual media usage * Audience per advertisement) of television + radio + newspaper

Using Solver & Applying all constraints mentioned -

Total Audience Reached = 1530000

T = $ 20000

R = $ 6000

N = $ 5100

Budget = $ 31,100

b) Again using Solver , changing the constraint Total Budget <= $ 31,200

Total Audience Contact = 1533333.33

Audience contact increases by = (1533333.33 - 1530000)/1530000 = 0.22 %