Advertising Strategy Case Problem – Case Problem 1

COB 291 – Dr. Busing

To Be Completed Using Microsoft Excel/Solver Add-In

***INDIVIDUAL WORK – DO NOT CONSULT WITH CLASSMATES ON THIS ASSIGNMENT.***

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:

  1. Use at least 30 announcements for combined television and radio coverage.
  2. Do not use more than 25 radio announcements.
  3. The number of radio announcements cannot be less than the number of television announcements.

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.

MANAGERIAL REPORT

Perform an analysis of advertising strategy for Duke Motors and prepare a report to Duke’s president presenting your findings and recommendations. Include, but do not limit your discussion to, a consideration of the following items.

  1. The recommended number of television and radio spot announcements.
  2. The relative merits of each advertising medium.
  3. The rating that would be necessary for the news program before it would make sense to increase the number of television spots.
  4. 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.
  5. The restrictions placed on the advertising strategy that Duke might want to consider relaxing or altering.
  6. The best use of any possible increase in the advertising budget.
  7. Any other information that may help Duke’s president make the advertising strategy decision.

NOTE: The report should be type-written and in memo format. In addition to the memo report, you should turn in the following:

a formulation of the problem with decision variables clearly defined;

a copy of your spreadsheet with formulas exposed;

the answer report (generated by Solver);

the sensitivity report (generated by Solver).

The end product, which includes the memo and all items listed above, should be stapled in the upper left hand corner. Don’t forget to include your name. Professionalism, including grammar and punctuation, is necessary but not sufficient for a good grade on this assignment.

DUE DATE:Tuesday, February 11, 2003 at the beginning of class. See syllabus for policy on late work.

Helpful Hints for Formatting Your Work: How to generate a spreadsheet with formulas exposed: On taskbar in Microsoft Excel, choose Tools, Options… and choose the “View” tab. Check “Formulas” and click “OK.” You may also need to change column widths to make sure that the formulas can all be seen.

Then go to File, Page Setup… and choose the “Sheet” tab. Check “Gridlines” and “Row and Column Headings.”

Save your work. After you’ve successfully run solver and when you’re ready to print, make sure you choose “entire workbook” in the “print what” dialog box.