Question

USING SOLVER IN EXCEL ONLY (Can you show the steps in Excel please? I have never used solver before!)

Duke Motors, Inc., has hired a marketing services firm to develop an advertising strategy for promoting Duke’s used car sales. The marketing firm has recommended that Duke use spot announcements on both television and radio as the advertising media for the proposed promotional campaign. Advertising strategy guidelines are expressed as follows: -

Use at least 30 announcements for combined television and radio coverage.

Do not use more than 25 radio announcements.

The number of radio announcements cannot be less than the number of television announcements. (DO I WRITE THIS AS A CONSTRAINT IN EXCEL? IF SO, HOW?)

The television station has quoted a cost of $1200 per spot announcement, and the radio station has quoted a cost of $300 per spot announcement. Duke’s advertising budget has been set at $25,500. The marketing services firm has rated the various advertising media in terms of audience coverage and recall power of the advertisement. For Duke’s media alternatives, the television announcement is rated at 600 and the radio announcement is rated at 200. Duke’s president would like to know how many television and how many radio spot announcements should be used to maximize the overall rating of the advertising campaign.

Duke’s president believes that the television station will consider running the Duke spot announcement on its highly rated evening news program (at the same cost) if Duke will consider using additional television announcements.

Questions:

1) The number of television spots that should be purchased if the news program is rated highly enough to make increasing the number of television spots advisable.

2) The restrictions placed on the advertising strategy that Duke might want to consider relaxing or altering.

Answer 1

Let T and R be the number of spot announcements on Television (T) and Radio (R) as the advertising media

Max 600T + 200R

s.t.

1200T+300R <= 25500

T+R >= 30

R <= 25

R - T >= 0

T, R >= 0

Solution using Solver follows:

D2 Q fx SUMPRODUCT(B2:C2,$B$9:$C$9) D E 2 Rating: 3 Total budget 4R<=25 285 SSolver Parameters 1200 300 25500 25500 25 <25 40 >= 30 10 >- Set Objective SDS O Max O Min By Changing aiable Cells: Value Of: o SBS9:SCS9 Subject to the Constraints: SDS3:SDS4 <SFS3:SFS4 9 Optimal Advtg mix: 15 10 25 Add Change 12 13 14 15 16 17 18 19 20 Delete Reset All Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. Solve Close 25

Formulas:

D2 =SUMPRODUCT(B2:C2,$B$9:$C$9)   copy to D2:D6

1) Refer sensitivity report as under:

AB WPs Office Sensitivity Report 2 Worksheet: [Book1]Sheet3 3 Report Created: 5/13/2018 10:15:31 AM 6 Variable Cells Fina ReducedObjective Allowable Allowable Coefficient Increase Decrease Cell 9 B$9 $C$9 Value 15 25 Name Cost Optimal Advtg mix: T Optimal Advtg mix: R 32 10 1E+30 12 Constraints 13 14 15 ŞD$3 Total budget 16 $D$4 R<25 17 $DS5 T+R 30 18 ŞDS6 R-T-0 19 20 21 Final Shadow Constraint Allowable Allowable Value 25500 0.003333333 Cell Name Price R.H. Side Increase Decrease 25500 25 30 12000 12000 25 40 10 10 10 1E+30 1E+30

Allowable increase for constraint, T+R >= 30 is 10. Therefore, 10 television spots can be increase.

2) The shadow price of constraint, R <= 25 is 8, which is the maximum of all. Therefore, this restriction, if relaxed, will result in the highest improvement in rating.