In: Finance
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.
Let | T = number of television spot advertisements |
R = number of radio advertisements | |
N = number of newspaper advertisements |
Budget ($) | |
---|---|
T = —— | |
R = —— | |
N = —— | |
Total Budget = $ |
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
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 %