Question

*** Please show steps in excel***

Home Depot is trying to determine how to advertise in order to maximize their exposure. Their weekly advertising budget is $25,000. They are considering three possible media: tv, newspaper, and radio. Information regarding cost and exposure is given in the table below:

Medium	audience reached per ad	cost per ad (4)	maximum ads per week	minimum ads per week
TV	8,000	1,200	11	5
Newspaper	5,500	600	15	5
Radio	3,000	400	22	5

Formulate this media selection problem and then find the optimal solution.

Answer 1

Excel model:

A B с E F G I 1 2 3 TV Newspaper Radio 4 8000 5500 3000 5 No. of ads in Audience reached i.e. Budget maximum ads per week minimum ads per week =SUMPRODUCT($C$3:$E$3,C4:14) consumed =SUMPRODUCT($C$3:$E$3,C6:56) available <= 25000 6 7 1200 11 5 600 15 5 400 22 5 8

Solver inputs:

Set Objective: $F$4 To: Max Min O Value of: 0 ..... By Changing Variable Cells: $C$3:$E$3 KH Subject to the Constraints: $C$3: SE$3 <= $C$7:$E$7 $C$3:$E$3 >= $C$8:SE$8 $F$6 <= SH$6 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

Solution (Answer report)

Objective Cell (Max)
	Cell	Name	Original Value	Final Value
	$F$4	Audience reached	0	196,500
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	No. of ads in TV	0	6	Contin
	$D$3	No. of ads in Newspaper	0	15	Contin
	$E$3	No. of ads in Radio	0	22	Contin