A Marketing Example

[Page 125 ( continued )]

The Biggs Department Store chain has hired an advertising firm to determine the types and amount of advertising it should invest in for its stores. The three types of advertising available are television and radio commercials and newspaper ads. The retail chain desires to know the number of each type of advertisement it should purchase in order to maximize exposure. It is estimated that each ad or commercial will reach the following potential audience and cost the following amount:

	Exposure (people/ad or commercial)	Cost
Television commercial	20,000	$15,000
Radio commercial	12,000	6,000
Newspaper ad	9,000	4,000

The company must consider the following resource constraints:

The budget limit for advertising is $100,000.
The television station has time available for 4 commercials.
The radio station has time available for 10 commercials.
The newspaper has space available for 7 ads.
The advertising agency has time and staff available for producing no more than a total of 15 commercials and/or ads.

Decision Variables

This model consists of three decision variables that represent the number of each type of advertising produced:

x ₁ = number of television commercials

x ₂ = number of radio commercials

x ₃ = number of newspaper ads

The Objective Function

The objective of this problem is different from the objectives in the previous examples, in which only profit was to be maximized (or cost minimized). In this problem, profit is not to be maximized; instead, audience exposure is to be maximized. Thus, this objective function demonstrates that although a linear programming model must either maximize or minimize some objective, the objective itself can be in terms of any type of activity or valuation.

[Page 126]

For this problem the objective audience exposure is determined by summing the audience exposure gained from each type of advertising:

maximize Z = 20,000 x ₁ + 12,000 x ₂ + 9,000 x ₃

where

Z = total level of audience exposure

20,000 x ₁ = estimated number of people reached by television commercials

12,000 x ₂ = estimated number of people reached by radio commercials

9,000 x ₃ = estimated number of people reached by newspaper ads

Model Constraints

The first constraint in this model reflects the limited budget of $100,000 allocated for advertisement:

$15,000 x ₁ + 6,000 x ₂ + 4,000 x ₃ $100,000

where

$15,000 x ₁ = amount spent for television advertising

6,000 x ₂ = amount spent for radio advertising

4,000 x ₃ = amount spent for newspaper advertising

The next three constraints represent the fact that television and radio commercials are limited to 4 and 10, respectively, and newspaper ads are limited to 7:

x ₁ 4 television commercials

x ₂ 10 radio commercials

x ₃ 7 newspaper ads

The final constraint specifies that the total number of commercials and ads cannot exceed 15 because of the limitations of the advertising firm:

x ₁ + x ₂ + x ₃ 15 commercials and ads

Model Summary

The complete linear programming model for this problem is summarized as

Computer Solution with Excel

The solution to our marketing example using Excel is shown in Exhibit 4.10. The model decision variables are contained in cells D6:D8 . The formula for the objective function in cell E10 is shown on the formula bar at the top of the screen. When Solver is accessed from the "Tools" menu, as shown in Exhibit 4.11, it is necessary to use only one formula to enter the model constraints: H6:H10 <= J6:J10 .

[Page 127]

Exhibit 4.10.

Exhibit 4.11.

Solution Analysis

The solution shows

x ₁ = 1.818 television commercials

x ₂ = 10 radio commercials

x ₃ = 3.182 newspaper ads

Z = 185,000 audience exposure

This is a case where a non-integer solution can create difficulties. It is not realistic to round 1.818 television commercials to 2 television commercials, with 10 radio commercials and 3 newspaper ads. Some quick arithmetic with the budget constraint shows that such a solution will exceed the $100,000 budget limitation, although only by $2,000. Thus, the store must either increase its advertising or plan for 1 television commercial, 10 radio commercials, and 3 newspaper ads. The audience exposure for this solution will be 167,000 people, or 18,000 fewer than the optimal number, almost a 10% decrease. There may, in fact, be a better solution than this "rounded-down" solution.

[Page 128]

The integer linear programming technique, which restricts solutions to integer values, should be used. Although we will discuss the topic of integer programming in more detail in Chapter 5, for now we can derive an integer solution by using Excel with a simple change when we input our constraints in the Solver window. We specify that our variable cells, D6:D8 , are integers in the Change Constraint window, as shown in Exhibits 4.12 and 4.13. This will result in the spreadsheet solution in Exhibit 4.14 when the problem is solved , which you will notice is better (i.e., more total exposures) than the rounded-down solution.