Assignment Paper

The Case Study is found in chapter 3, P.65
3.1 A CASE STUDY: THE SUPER GRAIN CORP. ADVERTISING-MIX PROBLEM
Figure 3.1 shows the spreadsheet model formulated by Claire. Let us see how she did this, including why she structured the spreadsheet in this way, by considering each of the components of the model individually.
Four kinds of cells are needed for these four components of a spreadsheet model.

After carefully reviewing the case, write a minimum two page paper answering all three questions at the end of the case.
Review Questions
1. What is the problem being addressed in this case study?
2. What overall measure of performance is being used?
3. What are the assumptions of linear programming that need to be checked to evaluate the adequacy of using a linear programming model to represent the problem under consideration?
The paper should have 1″ margins around. Cover sheets are fine but they do not count toward the two page minimum total. The paper must be a full two pages (or more) in order to receive full credit. This means you will need to think through each question deeply, as a cursory, one or two sentence response will be inadequate.
Claire Syverson, vice president for marketing of the Super Grain Corporation, is facing a daunting challenge: how to break into an already overly crowded breakfast cereal market in a big way. Fortunately, the company’s new breakfast cereal—Crunchy Start—has a lot going for it: Great taste. Nutritious. Crunchy from start to finish. She can recite the litany in her sleep now. It has the makings of a winning promotional campaign.
However, Claire knows that she has to avoid the mistakes she made in her last campaign for a breakfast cereal. That had been her first big assignment since she won this promotion, and what a disaster! She thought she had developed a really good campaign. But somehow it had failed to connect with the most crucial segments of the market—young children and parents of young children. She also has concluded that it was a mistake not to include cents-off coupons in the magazine and newspaper advertising. Oh well. Live and learn.
But she had better get it right this time, especially after the big stumble last time. The company’s president, David Sloan, already has impressed on her how important the success of Crunchy Start is to the future of the company. She remembers exactly how David concluded the conversation. “The company’s shareholders are not happy. We need to get those earnings headed in the right direction again.” Claire had heard this tune before, but she saw in David’s eyes how deadly serious he was this time.
Claire often uses spreadsheets to help organize her planning. Her management science course in business school impressed upon her how valuable spreadsheet modeling can be. She regrets that she did not rely more heavily on spreadsheet modeling for the last campaign. That was a mistake that she is determined not to repeat.
Now it is time for Claire to carefully review and formulate the problem in preparation for formulating a spreadsheet model.
The Problem
Claire already has employed a leading advertising firm, Giacomi & Jackowitz, to help design a nationwide promotional campaign that will achieve the largest possible exposure for Crunchy Start. Super Grain will pay this firm a fee based on services performed (not to exceed $1 million) and has allocated an additional $4 million for advertising expenses.

Giacomi & Jackowitz has identified the three most effective advertising media for this product:
Medium 1: Television commercials on Saturday morning programs for children.
Medium 2: Advertisements in food and family-oriented magazines.
Medium 3: Advertisements in Sunday supplements of major newspapers.
The problem now is to determine which levels should be chosen for these advertising activities to obtain the most effective advertising mix.
To determine the best mix of activity levels for this particular advertising problem, it is necessary (as always) to identify the overall measure of performance for the problem and then the contribution of each activity toward this measure. An ultimate goal for Super Grain is to maximize its profits, but it is difficult to make a direct connection between advertising exposure and profits. Therefore, as a rough surrogate for profit, Claire decides to use expected number of exposures as the overall measure of performance, where each viewing of an advertisement by some individual counts as one exposure.
Giacomi & Jackowitz has made preliminary plans for advertisements in the three media. The firm also has estimated the expected number of exposures for each advertisement in each medium, as given in the bottom row of Table 3.1.
The number of advertisements that can be run in the different media are restricted by both the advertising budget (a limit of $4 million) and the planning budget (a limit of $1 million for the fee to Giacomi & Jackowitz). Another restriction is that there are only five commercial spots available for running different commercials (one commercial per spot) on children’s television programs Saturday morning (medium 1) during the time of the promotional campaign. (The other two media have an ample number of spots available.)
Consequently, the three resources for this problem are:
Resource 1: Advertising budget ($4 million).
Resource 2: Planning budget ($1 million).
Resource 3: TV commercial spots available (5).
Table 3.1 shows how much of the advertising budget and the planning budget would be used by each advertisement in the respective media.
• The first row gives the cost per advertisement in each medium.
• The second row shows Giacomi & Jackowitz’s estimates of its total cost (including overhead and profit) for designing and developing each advertisement for the respective media.1 (This cost represents the billable fee from Super Grain.)
• The last row then gives the expected number of exposures per advertisement.
Analysis of the Problem
Claire decides to formulate and solve a linear programming model for this problem on a spreadsheet. The formulation procedure summarized at the end of Section 2.2 guides this process. Like any linear programming model, this model will have four components:
1. The data
2. The decisions
3. The constraints
4. The measure of performance
The spreadsheet needs to be formatted to provide the following kinds of cells for these components:
Data → data cells
Decisions → changing cells
Constraints → output cells
Measure of performance → objective cell

FIGURE 3.1
The spreadsheet model for the Super Grain problem (Section 3.1), including the objective cell TotalExposures (H13) and the other output cells BudgetSpent (F8:F9), as well as the specifications needed to set up Solver. The changing cells NumberOfAds (C13:E13) show the optimal solution obtained by Solver.

The Data
It is best to begin structuring the spreadsheet by deciding where to locate the data cells. Most of these cells normally are placed in the upper left-hand portion of the spreadsheet. The structure of the rest of the model will then follow the structure of the data cells. When some or all of the data are being obtained from a well-constructed table, the clarity of the spreadsheet usually is enhanced by retaining the format of the table when inserting the data into the spreadsheet.
Claire followed these guidelines when she chose cells C4:E4 and C8:E9 to be the data cells to hold the information in Table 3.1 when using units of thousands of dollars. These data cells have been given the range names: ExposuresPerAd (C4:E4), CostPerAd (C8:E9), Budget-Available (H8:H9), and MaxTVSpots (C15).
The other relevant kind of data here is the information given earlier about the amounts available of the three resources for the problem (the advertising budget, the planning budget, and the commercial spots available). Claire chose the locations of the data cells holding this information (H8:H9 and C15) to fit with the constraints that will be inserted a little later.
The Decisions
The problem has been defined as determining the most effective advertising mix among the three media selected by Giacomi & Jackowitz. Therefore, there are three decisions:
Decision 1: TV = Number of commercials for separate spots on television.
Decision 2: M = Number of advertisements in magazines.
Decision 3: SS = Number of advertisements in Sunday supplements.
The decisions already made for the locations of the data cells have designated columns C, D, and E to be the columns for these media. Therefore, the changing cells to hold these three decisions have been placed in row 13 in these columns:
TV → cell C13M → cell D13SS → cell E13
These changing cells are collectively referred to by the range name NumberOfAds (C13:E13).
The Constraints
These changing cells need to be nonnegative. In addition, constraints are needed for the three resources. The first two resources are the ad budget and planning budget. Data for these two budgets already have ben placed in data cells in rows 8 and 9, so the constraints involving these budgets need to appear in these same two rows. The amounts spent from these two budgets are shown in the range BudgetSpent (F8:F9) and the amounts available from these budgets are shown in the range of data cells BudgetAvailable (H8:H9). As suggested by the ≤ signs entered into column G, the corresponding constraints are
Total spending on advertising ≤ 4,000 (Ad budget in $1,000s)
Total cost of planning ≤ 1,000 (Planning budget in $1,000s)
Using the data in columns C, D, and E for the resources, these totals are
Total spending on advertising = 300TV + 150M + 100SS
Total cost of planning = 90TV + 30M + 40SS
These sums of products on the right-hand side are entered into the output cells BudgetSpent (F8:F9) by using the SUMPRODUCT functions shown in the lower right-hand side of Figure 3.1. Although the ≤ signs entered in column G are only cosmetic (trial solutions still can be entered in the changing cells that violate these inequalities), they will serve as a reminder later to use these same ≤ signs when entering the constraints in Solver.
The third resource is TV spots for different commercials. Five such spots are available for purchase. The number of spots used is one of the changing cells (C13). Since this cell will be used in a constraint, we assign the cell its own range name: TVSpots (C13). The maximum number of TV spots available is in the data cell MaxTVSpots (C15). Thus, the required constraint is TVSpots ≤ MaxTVSpots.
Excel Tip: Range names may overlap. For instance, we have used NumberOfAds to refer to the whole range of changing cells, C13:E13, and TVSpots to refer to the single cell, C13.
The Measure of Performance
Claire Syverson is using expected number of exposures as the overall measure of performance, so let
Exposure = Expected number of exposures (in thousands) from all the advertising
The data cells ExposuresPerAd (C4:E4) provide the expected number of exposures (in thousands) per advertisement in the respective media and the changing cells NumberOfAds (C13:E13) give the number of each type of advertisement. Therefore,

Summary of the Formulation
The above analysis of the four components of the model has formulated the following linear programming model (in algebraic form) on the spreadsheet:
Maximize Exposure = 1,300TV + 600M + 500SS
subject to

and
TV ≥ 0M ≥ 0SS ≥ 0
The difficult work of defining the problem and gathering all the relevant data, including the information in Table 3.1, leads directly to this formulation.
Solving the Model
To solve the spreadsheet model formulated above, some key information needs to be entered into Solver. The lower left-hand side of Figure 3.1 shows the needed entries: the objective cell (TotalExposures), the changing cells (NumberOfAds), the goal of maximizing the objective cell, and the constraints BudgetSpent ≤ BudgetAvailable and TVSpots ≤ MaxTVSpots. Two options are also specified at the bottom of the Solver Parameters box on the lower left-hand side of Figure 3.1. The changing cells need nonnegativity constraints because negative values of advertising are not possible. Choose the Simplex LP (Excel’s Solver) or Standard LP/Quadratic Engine (Analytic Solver) solving method, because this is a linear programming model. Running Solver then finds an optimal solution for the model and displays it in the changing cells.
Excel Tip: With Excel’s Solver, the Solver dialog box is used to tell Solver the location on the spreadsheet of several of the elements of the model: the changing cells, the objective cell, and the constraints. With Analytic Solver, the Decisions, Constraints, and Objective menu on the Analytic Solver ribbon are used along with the Model pane.
The optimal solution given in row 13 of the spreadsheet provides the following plan for the promotional campaign:
Do not run any television commercials.
Run 20 advertisements in magazines.
Run 10 advertisements in Sunday supplements.
Since TotalExposures (H13) gives the expected number of exposures in thousands, this plan would be expected to provide 17,000,000 exposures.
Evaluation of the Adequacy of the Model
When she chose to use a linear programming model to represent this advertising-mix problem, Claire recognized that this kind of model does not provide a perfect match to this problem. However, a mathematical model is intended to be only an approximate representation of the real problem. Approximations and simplifying assumptions generally are required to have a workable model. All that is really needed is that there be a reasonably high correlation between the prediction of the model and what would actually happen in the real problem. The team now needs to check whether this criterion is satisfied.
One assumption of linear programming is that fractional solutions are allowed. For the current problem, this means that a fractional number (e.g., 3½) of television commercials (or of ads in magazines or Sunday supplements) should be allowed. This is technically true, since a commercial can be aired for less than a normal run, or an ad can be run in just a fraction of the usual magazines or Sunday supplements. However, one defect of the model is that it assumes that Giacomi & Jackowitz’s cost for planning and developing a commercial or ad that receives only a fraction of its usual run is only that fraction of its usual cost, even though the actual cost would be the same as for a full run. Fortunately, the optimal solution obtained was an integer solution (0 television commercials, 20 ads in magazines, and 10 ads in Sunday supplements), so the assumption that fractional solutions are allowed was not even needed.
Linear programming models allow fractional solutions.
Although it is possible to have a fractional number of a normal run of commercials or ads, a normal run tends to be much more effective than a fractional run. Therefore, it would have been reasonable for Claire to drop the assumption that fractional solutions are allowed. If Claire had done this and the optimal solution for the linear programming model had not turned out to be integer, constraints can be added to require the changing cells to be integer. (The TBA Airlines example in the next section provides an illustration of this type of constraint.) After adding such constraints, the model is called an integer programming model instead of a linear programming model, but it still can be readily solved by Solver.
Another key assumption of linear programming is that the appropriate equation for each of the output cells, including the objective cell, is one that can be expressed as a SUMPRODUCT of data cells and changing cells (or occasionally just a SUM of changing cells). For the objective cell (cell H13) in Figure 3.1, this implies that the expected number of exposures to be obtained from each advertising medium is proportional to the number of advertisements in that medium. This proportionality seems true, since each viewing of the advertisements by some individual counts as another exposure. Another implication of using a SUMPRODUCT function is that the expected number of exposures to be obtained from an advertising medium is unaffected by the number of advertisements in the other media. Again, this implication seems valid, since viewings of advertisements in different media count as separate exposures.
Linear programming models should use SUM or SUMPRODUCT functions for the output cells, including the objective cell.
Although a SUMPRODUCT function is appropriate for calculating the expected number of exposures, the choice of this number for the overall measure of performance is somewhat questionable. Management’s real objective is to maximize the profit generated as a result of the advertising campaign, but this is difficult to measure so expected number of exposures was selected to be a surrogate for profit. This would be valid if profit were proportional to the expected number of exposures. However, proportionality is only an approximation in this case because too many exposures for the same individual reach a saturation level where the impact (potential profit) from one more exposure is substantially less than for the first exposure. (When proportionality is not a reasonable approximation, Chapter 8 will describe nonlinear models that can be used instead.)
To check how reasonable it is to use expected number of exposures as a surrogate for profit, Claire meets with Sid Jackowitz, one of the senior partners of Giacomi & Jackowitz. Sid indicates that the contemplated promotional campaign (20 advertisements in magazines and 10 in Sunday supplements) is a relatively modest one well below saturation levels. Most readers will only notice these ads once or twice, and a second notice is very helpful for reinforcing the first one. Furthermore, the readership of magazines and Sunday supplements is sufficiently different that the interaction of the advertising impact in these two media should be small. Consequently, Claire concludes that using expected number of exposures for the objective cell in Figure 3.1 provides a reasonable approximation. (A continuation of this case study in Case 8-1 will delve into the more complicated analysis that is required in order to use profit directly as the measure of performance to be recorded in the objective cell instead of making this approximation.)
Next, Claire quizzes Sid about his firm’s costs for planning and developing advertisements in these media. Is it reasonable to assume that the cost in a given medium is proportional to the number of advertisements in that medium? Is it reasonable to assume that the cost of developing advertisements in one medium would not be substantially reduced if the firm had just finished developing advertisements in another medium that might have similar themes? Sid acknowledges that there is some carryover in ad planning from one medium to another, especially if both are print media (e.g., magazines and Sunday supplements), but that the carryover is quite limited because of the distinct differences in these media. Furthermore, he feels that the proportionality assumption is quite reasonable for any given medium since the amount of work involved in planning and developing each additional advertisement in the medium is nearly the same as for the first one in the medium. The total fee that Super Grain will pay Giacomi & Jackowitz will eventually be based on a detailed accounting of the amount of work done by the firm. Nevertheless, Sid feels that the cost estimates previously provided by the firm (as entered in cells C9, D9, and E9 in units of thousands of dollars) give a reasonable basis for roughly projecting what the fee will be for any given plan (the entries in the changing cells) for the promotional campaign.

Based on this information, Claire concludes that using a SUMPRODUCT function for cell F9 provides a reasonable approximation. Doing the same for cell F8 is clearly justified. Given her earlier conclusions as well, Claire decides that the linear programming model incorporated into Figure 3.1 (plus any expansions of the model needed later for the detailed planning) is a sufficiently accurate representation of the real advertising-mix problem. It will not be necessary to refine the results from this model by turning next to a more complicated kind of mathematical model (such as those to be described in Chapter 8).
Therefore, Claire sends a memorandum to the company’s president, David Sloan, describing a promotional campaign that corresponds to the optimal solution from the linear programming model (no TV commercials, 20 ads in magazines, and 10 ads in Sunday supplements). She also requests a meeting to evaluate this plan and discuss whether some modifications should be made.
We will pick up this story again in Section 3.4.