Problem 1. For the Super Grain case study example discussed in Section 3.1 of the textbook (summarized in the PPT slide deck in Chapter 3), make the following revisions for the problem description before you start working on the questions. Put all your answers on the spreadsheet template provided.

Revisions:

 Maximum number of units for magazine advertisements should not be more than 18 units.

 (Maximum number of TV spots stay the same.)

 The number of units of ad purchased can be fractional.

Pay attention to the units of measurement used and how they are expressed in the spreadsheet.

(1) Formulate the problem as a linear programming model on the spreadsheet. (Use right colors for different types of cells and use range names and sumproduct functions in your model.) Solve it using the Solver and also generate Answer and Sensitivity reports.

(2) How many units of each type of ads should be purchased? What is the total expected number of exposures?

(3) If the unit number exposures for TV spots were decreased from 1.3 million to 1.2 million, would the optimal solution change? Why? What would the new total expected number of exposures be?

(4) How much could the total expected number of exposures be increased for each additional $1,000 added to the advertising budget? What is range of the increase on advertising budget within which the answer is valid?

(5) How much could the total expected number of exposures be increased for each additional $1,000 added to the planning budget? What is range of the increase on advertising budget within which the answer is valid?

(6) Would the answers in (4) and (5) remain valid if the advertising budget and planning budget were increased $50,000 each? Explain why or why not.

(7) If only $50,000 can be added to either the advertising budget or the planning budget, where should it be added to do the most good? If $50,000 must be removed from either the advertising budget or the planning budget, from which should it be removed to do the least harm?