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	150,000	17,000	35,000
Cost per advertisement	$1,500	$250	$500
Maximum media usage	12	18	11

To ensure a balanced use of advertising media, radio advertisements must not exceed 45% 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 $24,000, 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 Allocation 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

Lets first formulate the problem.

We have to maximize the total audience, which is the sum of audience through all 3 advertisement channels.

The constraints are-

• Radio number of commercials cant exceed 45% of total commercials.
• TV commercials must be at least 10% of total commercials
• The total cost cant exceed $24000
• Maximum media usage of each advertisement channel

Lets use solver in excel to solve (it can be found in Data-solver. if its not there, please activate it by going to options-addins-solver).

First we are formulating the problem. This is shown below and then a formula view is given for better understanding.

Formula view-

What we are doing is defining the maximizing requirement (the total audience, which is sum of all indiviual audiences from each channel).

The audience of each channel is the number used of that channel multiplied by the audience per advvertisement. The total used is total media usage across all channels. It will be useful for defining the 45% and 10% constraints.

Now, lets go to data and click on solver. There we will define everything from the above. This is shown below-

As can be seen, we are maximzing B8, which is total audience, by varying number used for each media channel. We have also defined constraints, as required. Clicking on solve, we get the following output-

With this, we can now answer the questions.

Television- 12 messages

Radio- 2 messages

Online- 11 messages

Television- $18000 budget

Radio- $500 budget

Online- $5500 budget

Total audience reached-2219000

B. All we need to do to calculate this is to change the budget constraint to 24100 rather than 24000. This will give us the following solution-

The new audience reached is 2225800, meaning an increase of 2225800-2219000=6800