Question

Part 1. A company is considering promoting its products using three media: Television, Newspaper, and Radio.

Television: cost per ad: $3500, max ads per month 100, public reached per ad 5,000

Newspaper: cost per ad: $1000, max ads per month 30, public reached per ad 1,000

Radio: cost per ad: $150, max ads per month 100, public reached per ad 500

Budget: $150,000; do not use more than $100,000 in one medium.

What is the media plan to maximize public reached?

The resulting linear model for this problem is given below.

Variables: T in ads, N in ads, R in ads

Max 5000*T + 1000*N + 500*R

S.T.:

Budget 3500*T + 1000*N + 150*R <= 150000

Budget T 3500*T <= 100000

Budget N 1000*N <= 100000

Budget R 150*R <= 100000

Television T <= 100

Newspaper N <= 30

Radio R <= 100

N-N T, N, R >= 0 AND INTEGER

Run Excel Solver and generate the Answer Report.

What is the optimal solution?

T=

N=

R=

What is the total public reached?

Part 2. Assume the budget is $200,000 instead of $150,000 and the TV cost per ad is $3,000 per ad instead of $3,500 per ad. With the new parameters, run Excel Solver and generate the Answer Report. What is the new optimal solution?

T=

N=

R=

What is the total public reached?

Answer 1

part 1)

Let T,N,R be the number of ads in Television, News paper and radio respectively

The total public reached using T,N,R ads is

We want to maximize this and hence this is the objective function.

The constraints are

Budget: $150,000; The total budget needed to air T,N,R ads needs to be less than 150000

do not use more than $100,000 in one medium.

Max ads per month

The LP model is

maximize

s.t constraints

Prepare the following sheet

get this

set up the solver, using data--->solver

click "Solve"

In the next dialogue box, highlight "Answer" and click ok, to get the answer report

get this

get this answer report

ans: the optimal solution is

Media	Number of Ads
Television (T)	28
Newspaper (N)	30
Radio ( R )	100

The total public reached is 220,000

part 2) Assume the budget is $200,000 instead of $150,000 and the TV cost per ad is $3,000 per ad instead of $3,500 per ad.

make these changes in the  sheet

Run the solver using (no changes here)

click "Solve"

In the next dialogue box, highlight "Answer" and click ok, to get the answer report

get this

Get this answer report

ans:

the new optimal solution is

Media	Number of Ads
Television (T)	33
Newspaper (N)	30
Radio ( R )	100

The total public reached is 245,000