Textbook: Introduction to Management Science (5th edition)

Page 188 - Problem: Case 5-1 Selling Soap

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 TotalCost (G14) shows that the total cost for this advertising campaign would be $10 million.

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 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 to obtain the information requested in part (a).

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.

e) Suppose that all the original numbers in Minimum Increase (G8:G10) were to be increased simultaneously by the same amount. How large can this amount be before the shadow prices provided by the sensitivity report may no longer be valid?