Reconsider the Profit & Gambit Co. advertising-mix problem presented in Section 2.7. Recall that a major advertising

Question:

Reconsider the Profit & Gambit Co. advertising-mix problem presented in Section 2.7. Recall that a major advertising campaign is being planned that will focus on three key products: a stain remover, a liquid detergent, and a powder detergent. Management has made the following policy decisions about what needs to be achieved by this campaign.

Sales of the stain remover should increase by at least 3 percent.
Sales of the liquid detergent should increase by at least 18 percent.
Sales of the powder detergent should increase by at least 4 percent.

The spreadsheet in Figure 2.21 shows the linear programming model that was formulated for this problem. The minimum required increases in the sales of the three products are given in the data cells Minimum Increase (G8:G10). The changing cells Advertising Units (C14:D14) indicate that an optimal solution for the model is to undertake four units of advertising on television and three units of advertising in the print media. The objective cell Total-Cost (G14) shows that the total cost for this advertising campaign would be $10 million.

FIGURE 5.21: The complete sensitivity report generated by Solver for the original Wyndor problem as formulated in Figure 5.1.

Variable Cells Allowable Allowable Final Value Reduced Objective Coefficient Cell Name Cost Increase Decrease $C$12 $D$1

After receiving this information, Profit & Gambit management now wants to analyze the trade-off between the total advertising cost and the resulting benefits achieved by increasing the sales of the three products. Therefore, a management science team (you) has been given the assignment of developing the information that management will need to analyze this trade-off and decide whether it should change any of its policy decisions regarding the required minimum increases in the sales of the three products. In particular, management needs some detailed information about how the total advertising cost would change if it were to change any or all of these policy decisions.
a. For each of the three products in turn, use graphical analysis to determine how much the total advertising cost would change if the required minimum increase in the sales of that product were to be increased by 1 percent (without changing the required minimum increases for the other two products).
b. Use the spreadsheet shown in Figure 2.21 (available on the CD-ROM) to obtain the information requested in part a.
FIGURE 5.21: The complete sensitivity report generated by Solver for the original Wyndor problem as formulated in Figure 5.1.

c. For each of the three products in turn, use a parameter analysis report to determine how the optimal solution for the model and the resulting total advertising cost would change if the required minimum increase in the sales of that product were to be systematically varied over a range of values (without changing the required minimum increases for the other two products). In each case, start the range of values at 0 percent and increase by 1 percent increments up to double the original minimum required increase.
d. Use Solver to generate the sensitivity report and indicate how the report is able to provide the information requested in part a. Also use the report to obtain the allowable range for the required minimum increase in the sales of each product. Interpret how each of these allowable ranges relates to the results obtained in part c.